mysql performance tuning

In this post I'll cover a lecture on MySQL performance tuning.

This lecture is given by Jay Pipes. Jay works at MySQL and has written a book on MySQL. It's called Pro MySQL and it covers intermediate and advanced features of the database. He also has an interesting blog, which I am long subscribed to - Jay Pipes blog.

In this lecture Mr. Pipes talks about core concepts of profiling and benchmarking, about the most common sources of performance problems, about indexing, schema, coding guidelines, and a little about server parameter tuning.

Here is his talk at Google:

The most interesting performance tuning tips from the video:

  • [02:20] Don't benchmark without a goal. Have a goal like "improve performance by 20%". Otherwise you'll waste a lot of time tuning milliseconds out of your application.
  • [02:50] Change just one thing at a time and re-run the benchmarks.
  • [03:40] Disable the query cache by setting the cache size to 0 when running MySQL benchmarks.
  • [05:22] The best tool for profiling MySQL queries is the EXPLAIN command. Understand it!
  • [06:40] Log slow queries and use mysqldumpshow to parse the log. It also has an option (--log-queries-not-using-indexes) of logging any query that does not use an index on a table.
  • [07:40] Jeremy Zawodny wrote the mytop utility for monitoring the threads and overall performance of MySQL servers.
  • [08:55 && 11:30] Repeated queries on an un-indexed field will kill your application faster than anything else.
  • [09:30] Don't de-normalize just because you think it will be faster. Start with normalized database schemes.
  • [10:15] Server parameter tweaking is not a catch-all. Tuning server parameters can help but it's very specific to certain situations.
  • [12:05] If you use MyISAM storage engine, exploit covering indexes.
  • [12:50] Ensure good selectivity on index fields.
  • [14:45] On multi-column indexes, pay attention to order of fields within the index definition.
  • [15:40] Be aware that as your database grows, the data in the indexed fields can gradate, deteriorating the usefulness of that index. As you data grows, always examine if the indexes you originally thought are still relevant to the data.
  • [17:02] Example of a common index problem, where an index is created on multiple fields.
  • [20:30] Use the smallest data types possible. Don't use bigint, when int will do. Or, don't use char(200), when a varchar or smaller char() would do. Using the right type will fit more records in memory or index key block, meaning fewer reads, resulting in faster performance.
  • [21:30] Consider horizontally spitting many-columned tables if they contain a lot of NULLs or rarely used columns.
  • [23:35] Get rid of surrogate keys (with example).
  • [24:05 && 33:20] Be an SQL programmer who thinks in sets, not procedural programming paradigms.
  • [24:35] InnoDB can't optimize SELECT COUNT(*) queries. Use counter tables! That's how to scale InnoDB.
  • [27:20] Always try to isolate index fields on one side of condition in a query (with example).
  • [28:20] Avoid using CURRENT_DATE() as it invalidates the cache.
  • [29:34] Example of using calculated fields when searching on top level domain. Idea - put a reversed TLD in the table.
  • [33:20] Avoid correlated subqueries. Think in sets not loops! Here is a great article on visualizing SQL joins.
  • [34:50] Example of using derived tables to avoid correlated subqueries.
  • [36:25] Be aware of global and per-thread server variables.
  • [37:50] Enable query cache if your application is doing a lot more reads than writes!
  • [28:50] MySQL uses MyISAM for internal data storage.
  • [40:00] MySQL loves ram!
  • [40:35] Q and A.

Jay recently published slides from his Join-Fu talk. Go get them!

I enjoyed this talk a lot. I am an intermediate MySQL user and I had not read his book. It was really informative!

If you want to learn more about MySQL, and don't yet have his book, why not get his book:

Comments

Frank Permalink
July 25, 2008, 14:51

The whole idea behind SQL was supposed to be that I could write what I wanted and the server would figure out how to get it for me. If I have to think and re-think how I ask for the data to trick the server into delivering optimal performance, then MySQL sucks and products like Oracle and Microsoft SQL Server are well worth the investment.

August 12, 2014, 14:45

I really love your blog.I wanted to thank you for this great read!! I definitely enjoying each and every little bit of the article and I have you bookmarked your site to check out new stuff you post.

independence day images
happy independence day images.

July 25, 2008, 17:02

Sure, Frank, because everybody knows you don't ever have to optimize queries in Oracle or MSSQL, because they do that for you. Somebody should tell Microsoft that the SQL profiler is useless, because they've sure wasted a lot of time and resources making sure people can needlessly optimize queries.

By your logic, if I have to optimize my C++ code to trick the compiler into making my program run faster, then I should just use C# because it's...more expensive, or something...nevermind, I guess I don't follow your logic.

July 25, 2008, 17:23

I've read Jay's answers throughout MySQL support forums and he's been very helpful. Definitely a visual guide that reinforce many optimization point. Understanding and optimizing MySQL is always in demand because you can't compare the cost benefits of MySQL vs. Oracle or MSSQL.

Paul Permalink
July 25, 2008, 20:08

So I frequently hear not to denormalize until it's been proven necessary. But in my experience, adding a column to a large MySQL table causes downtime. So my questions are:

1 am I doing the schema refactoring wrong?

2 if not, then wouldn't it be in my users interest to make some educated guesses and minimze downtime?

Matt Permalink
July 25, 2008, 21:26

Much of this list is good advice for any relational DB platform. I hope the OA does not mind a MS SQL Server version of his list:

http://blog.northcentralpositronics.net/2008/07/performance-tuning-practices.html

Wish Permalink
July 25, 2008, 21:31

Now the same words in a different order:
Think and I are delivering investment products into Microsoft. The Oracle trick sucks server data I ask for. If the re-think to like performance and SQL how Server, then have the well worth to the optimal.

Frank, The L in SQL equals "Language". If a language is used improperly, then one produces gibberish. Obviously, you can't just write whatever you want and expect a tuned query to materialize...

It's still programming...
Not magic.

August 07, 2008, 20:01

+1. Who more? :)

RNT Permalink
December 02, 2008, 12:59

Thank You for sharing...

It a nice topic.

Julio Horbert Permalink
April 08, 2014, 11:10

I have used www.examsdeal.com for all my Cisco certifications and got all. I am very happy with the results! The main difference between them and other sellers is that they provide most current exam questions without any errors. With basic understanding and their study material, you can get your certification in a very first attempt. my 2 cents!

May 23, 2014, 11:09
May 23, 2014, 11:09
May 23, 2014, 11:09
May 23, 2014, 11:09
June 04, 2014, 08:25

Superb information about mysql

faheen Permalink
June 22, 2014, 04:30

Ramadan Wallpaper
Ramadan Wallpaper HD Download
Ramadan Wallpaper 2014
Ramadan Wallpaper 2014 Download
Ramadan Wallpaper 2014 HD Images
Ramadan Wallpaper 2014 Images
Ramadan Wallpaper quotes
Ramadan wallpaper greetings
Ramadan wallpaper 2014 greetings
Ramadan Wallpaper 2014 quotes
Ramadan Kareem 2014 images
Ramadan Kareem Mubarak Images
Ramadan Kareem Mubarak 2014 images
Ramadan Screen Saver 2014 download
Ramadan Mubarak images
Ramadan images 2014
Ramadan Mubarak greetings
Wish you happy Ramadan 2014
Happy Ramadan Wallpaper 2014
Ramadan wallpaper 2014
Wish you happy Ramadan 2014
ramadan Hd wallpaper 2014
Wish you happy Ramadan wallpaper 2014
Ramadan 2014 images
Ramadan 2014 pictures
wish you happy Ramadan 2014 images
Wish you happy Ramadan 2014 pictures
Happy ramadan 2014 wallpaper
Ramadan Kareem 2014 Wallpaper
Ramadan Mubarak 2014 wallpaper
Wallpaper Ramadan 2014
Ramadan HD wallpaper
Ramadan 2014 HD wallpaper
wish you Ramadan 2014 HD wallpaper
Happy Ramadan HD wallpaper
Happy Ramadan 2014 HD wallpaper
Wish you Ramadan HD wallpaper Download 2014
Ramadan HD wallpaper Download 2014
Ramadan HD wallpaper 2014 Download
Ramadan HD wallpaper 1080p
Ramadan 2014 HD wallpaper 1080p
Best Ramadan 2014 HD wallpaper
Top 10 Ramadan 2014 HD wallpaper
Best Collections Of Ramadan 2014 HD wallpaper
Ramadan 2014 HD wallpaper For Android
Ramadan 2014 best Screensaver download
Ramadan 2014 Screensaver download
screensaver download for mobile Ramadan 2014
Ramadan 2014 HD Screensaver Download
Ramadan 2014 download best screen saver
Screensaver for 2014 ramadan
Ramadan mubarak screensaver download
Ramadan mubarak 2014 best screen saver
Ramadan Kareem Mubarak 2014 best screen saver
Ramadan mubarak 2014 best screen saver
screen saver 2014 download free for android
Ramadan Kareem Mubarak Greetings 2014
Best Collections of Ramadan 2014 Greetings
Best Collections of Ramadan 2014 Greetings
Top Collections of Ramadan 2014 Greetings
Ramadan 2014 Greetings
Ramadan 2014 Greetings Free
Ramadan 2014 Greetings Kareem best
Best Collections of Ramadan 2014 Kareem Greetings
Best Collections of Ramadan 2014 Kareem Greetings
Best Collections of Ramadan 2014 kareem greetings
Top Ramadan 2014 wallpapers
Top Ramadan 2014 pictures
Top ramadan 2014 pictures for pc
Top ramadan 2014 Wallpaper for Android
Ramadan 2014 best softwares
Ramadan 2014 Best Images of Desktop
Ramadan 2014 best pictures for desktop
ramadan 2014 collection of Hd wallpaper
Ramadan 2014 Best collections
Ramadan 2014 top Hd wallpaper
Ramadan 2014 in uk
When Ramadan 2014 in Uk
ramadan 2014 in United Kingdom
When Ramadan 2014 in United States
When ramadan 2014 in America
Ramadan date in uk
Ramadan date in uk
Ramadan facts in united kingdom
Ramadan date in France
Ramadan 2014 in france
Ramadan 2014 date in france
Ramadan 2014 date in Frace country
Happy Ramadan 2014 best wallpaper
Ramadan Wallpaper best collections
Ramadan Kareem Wallpaper collections
wish you happy Ramadan wallpaper 2014
happy Ramadan wallpaper 2014
wish you happy Ramadan Hd wallpaper
Happy Ramadan 2014 HD wallpaper
Hd Ramadan wallpaper 2014
Wallpaper 2014 Hd in Ramadan
Ramadan wallpaper 2014 for Desktop Background
Best Islamic Ramadan wallpaper 2014
Wallpaper 2014 for facebook Cover
Best Wallpaper 2014 Download
Ramadan HD wallpaper 2014 Download
When ramadan in India 2014
Ramadan in India 2014
Ramadan in India 2014
Ramadan Kareem Greetings
Ramadan kareem Mubarak Greetings
Ramadan kareeem 2014 Greetings
Ramadan 2014 kareem Greetings
Ramadan 2014 kareem greetings
Ramadan Kareem Mubarak 2014
Ramadan kareem Mubarak 2014
Ramadan kareem Mubarak 2014
Ramadan Kareem greetings 2014
Ramadan Kareem greetings 2014
Ramadan Kareem greetings 2014
Ramadan Kareem greetings 2014
Ramadan Kareem greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Ramadan Mubarak Greetings 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Inspirational pictures of ramadan 2014
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Ramadan 2014 Wallpaper and Mubarak pics
Awesome Post Ramadan Wallpaper Ramadan Wallpaper HD Download Ramadan Wallpaper 2014 Ramadan Wallpaper 2014 Download Ramadan Wallpaper 2014 HD Images Ramadan Wallpaper 2014 Images Ramadan Wallpaper quotes Ramadan wallpaper greetings Ramadan wallpaper 2014 greetings Ramadan Wallpaper 2014 quotes Ramadan Kareem 2014 images Ramadan Kareem Mubarak Images Ramadan Kareem Mubarak 2014 images Ramadan Screen Saver 2014 download Ramadan Mubarak images Ramadan images 2014 Ramadan Mubarak greetings Wish you happy Ramadan 2014 Happy Ramadan Wallpaper 2014 Ramadan wallpaper 2014 Wish you happy Ramadan 2014 ramadan Hd wallpaper 2014 Wish you happy Ramadan wallpaper 2014 Ramadan 2014 images Ramadan 2014 pictures wish you happy Ramadan 2014 images Wish you happy Ramadan 2014 pictures Happy ramadan 2014 wallpaper Ramadan Kareem 2014 Wallpaper Ramadan Mubarak 2014 wallpaper Wallpaper Ramadan 2014 Ramadan HD wallpaper Ramadan 2014 HD wallpaper wish you Ramadan 2014 HD wallpaper Happy Ramadan HD wallpaper Happy Ramadan 2014 HD wallpaper Wish you Ramadan HD wallpaper Download 2014 Ramadan HD wallpaper Download 2014 Ramadan HD wallpaper 2014 Download Ramadan HD wallpaper 1080p Ramadan 2014 HD wallpaper 1080p Best Ramadan 2014 HD wallpaper Top 10 Ramadan 2014 HD wallpaper Best Collections Of Ramadan 2014 HD wallpaper Ramadan 2014 HD wallpaper For Android Ramadan 2014 best Screensaver download Ramadan 2014 Screensaver download screensaver download for mobile Ramadan 2014 Ramadan 2014 HD Screensaver Download Ramadan 2014 download best screen saver Screensaver for 2014 ramadan Ramadan mubarak screensaver download Ramadan mubarak 2014 best screen saver Ramadan Kareem Mubarak 2014 best screen saver Ramadan mubarak 2014 best screen saver screen saver 2014 download free for android Ramadan Kareem Mubarak Greetings 2014 Best Collections of Ramadan 2014 Greetings Best Collections of Ramadan 2014 Greetings Top Collections of Ramadan 2014 Greetings Ramadan 2014 Greetings Ramadan 2014 Greetings Free Ramadan 2014 Greetings Kareem best Best Collections of Ramadan 2014 Kareem Greetings Best Collections of Ramadan 2014 Kareem Greetings Best Collections of Ramadan 2014 kareem greetings Top Ramadan 2014 wallpapers Top Ramadan 2014 pictures Top ramadan 2014 pictures for pc Top ramadan 2014 Wallpaper for Android Ramadan 2014 best softwares Ramadan 2014 Best Images of Desktop Ramadan 2014 best pictures for desktop ramadan 2014 collection of Hd wallpaper Ramadan 2014 Best collections Ramadan 2014 top Hd wallpaper Ramadan 2014 in uk When Ramadan 2014 in Uk ramadan 2014 in United Kingdom When Ramadan 2014 in United States When ramadan 2014 in America Ramadan date in uk Ramadan date in uk Ramadan facts in united kingdom Ramadan date in France Ramadan 2014 in france Ramadan 2014 date in france Ramadan 2014 date in Frace country Happy Ramadan 2014 best wallpaper Ramadan Wallpaper best collections Ramadan Kareem Wallpaper collections wish you happy Ramadan wallpaper 2014 happy Ramadan wallpaper 2014 wish you happy Ramadan Hd wallpaper Happy Ramadan 2014 HD wallpaper Hd Ramadan wallpaper 2014 Wallpaper 2014 Hd in Ramadan Ramadan wallpaper 2014 for Desktop Background Best Islamic Ramadan wallpaper 2014 Wallpaper 2014 for facebook Cover Best Wallpaper 2014 Download Ramadan HD wallpaper 2014 Download When ramadan in India 2014 Ramadan in India 2014 Ramadan in India 2014 Ramadan Kareem Greetings Ramadan kareem Mubarak Greetings Ramadan kareeem 2014 Greetings Ramadan 2014 kareem Greetings Ramadan 2014 kareem greetings Ramadan Kareem Mubarak 2014 Ramadan kareem Mubarak 2014 Ramadan kareem Mubarak 2014 Ramadan Kareem greetings 2014 Ramadan Kareem greetings 2014 Ramadan Kareem greetings 2014 Ramadan Kareem greetings 2014 Ramadan Kareem greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Ramadan Mubarak Greetings 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Inspirational pictures of ramadan 2014 Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics Ramadan 2014 Wallpaper and Mubarak pics oHack Anna University Paper Valuation Anna University result 2014 Thanks for sharing

yeshwanth Permalink
July 10, 2014, 06:53
yeshwanth Permalink
July 10, 2014, 06:53
Neo Parker Permalink
July 23, 2014, 10:53

damn awesome
Eid mubarak sms
Eid mubarak images
thanks

Neo Parker Permalink
July 23, 2014, 10:54
July 25, 2014, 15:04

Actually this post is about my personal interest and the mid of the post is truly informative and I love and elaboration quality of writer. I always try to find this type of info regarding this topic now here I got this thanks Happy Friendship Day SMS Friendship Day SMS Friendship Day Happy Friendship Day Independence Day Images Happy Independence Day Images Independence Day Images India India Independence Day independence day speech independence day sms Independence day wallpaper independence day message independence day songs again thanks for this great blog.

sanchit Permalink
July 25, 2014, 21:36

Hey its great post really enjoyed this one i found this on google ,i think there are still websmasters who provide quality contents to the reader and hope to come back soon and by the way Eid Mubarak Wishes
Eid Mubarak Sms in Hindi
Eid Mubarak sms In English
Eid Mubarak sms in Urdu
Eid Mubarak Wallpapers
Eid Mubarak Greetings
Eid Mubarak cardsto all of you also you can check Happy eid Mubarak 2014
really a great stuff

Hey its great post really enjoyed this one i found this on google ,i think there are still websmasters who provide quality contents to the reader and hope to come back soon and by the way
Hey its great post really enjoyed this one i found this on google ,i think there are still websmasters who provide quality contents to the reader and hope to come back soon and by the way

Thanks for sharing

hana Permalink
August 06, 2014, 06:12

He must have spent a lot of time to obtain useful information. Sometimes do not get good information. A method of obtaining information from the Internet. Glad that I met with your site.
اقامت ترکیه - خرید ملک در ترکیه - سایپا - سگ - تور چین - تور پکن - دوربین مدار بسته - اجرای نمای کامپوزیت - fotoselli kapı - garaj kapısı
.

sanchit Permalink
August 11, 2014, 19:05

I am very happy to visit this site and hope to visit here again and again. I have bookmarked your site due to the interesting and relevant stuff found here. Also I have recommended the same to my other friends.
Independence day india
Independence Day Images

Independence Day Wallpapers

Independence Day Speech in English

Independence Day Quotes

Independence Day Songs

Independence Day Poems

Independence Day SMS

This is my site, you can visit it and leave your valuable feedback.

sanchit Permalink
August 13, 2014, 12:14

I am very happy to visit this site and hope to visit here again and again. I have bookmarked your site due to the interesting and relevant stuff found here. Also I have recommended the same to my other friends.
Independence day india
Happy Independence day India
Desh Bhakti songs
Patriotic songs
Whatsapp Status
Fb status
Independence Day Speech in English

Independence Day Quotes

Modi Speech on Independece Day 2014
PM Modi LIVE Speech on Independece Day 2014

This is my site, you can visit it and leave your valuable feedback.

Leave a new comment

(why do I need your e-mail?)

(Your twitter name, if you have one. (I'm @pkrumins, btw.))

Type the first letter of your name: (just to make sure you're a human)

Please preview the comment before submitting to make sure it's OK.

Advertisements