MySQL Performance Improvements

One problem that never seems to go away is poorly constructed MySQL queries.  Due to the nature of shared web hosting the workload is extremely varied.  It does not just vary server to server, but on the same server within different time periods  There are times we optimize certain aspects, only to have those same optimizations hurt performance at a later date.  We realized that doing these tiny tweaks were getting us nowhere as the gains were minimal and at times they hurt performance more than they helped.  In light of that, we started looking for ways to permanently improve performance, regardless of query type, size, or any other factors.

We finally found something that was generic and could significantly improve performance on our systems at all times.  We have now made it so MySQL offloads as much of it’s disk activity to memory as possible.  Previously, we had tweaked everything and tried our best to get things into memory but it was just not doing that a good chunk of the time.  Since this change, the disk activity has drastically dropped, resulting in significantly improved performance.  Since our machines all have at least 12GB of RAM we have plenty free to offload things like this to memory as we have plenty in our caches already.  This change was implemented on our servers a week ago and we’re already seeing great results.

We have dropped the disk i/o on a lot of our systems by about 30% so far.  This has led to a massive overall performance gain for all other applications which rely heavily on disk i/o.  As you can imagine, the end result here is a greatly increased amount of free i/o on all of our machines. The cascading effects of this result in fewer i/o related load spikes, the systems overall load has decreased significantly, and most importantly: your sites will load even more quickly than before! (didn’t think it was possible, did you?)

All the intensive queries that users had been running which took many seconds to run now take less than a second.  I don’t know the exact numbers but it would not surprise me if certain types of slow queries are now 10 times faster than they once were.  Although the slow queries should just be fixed, this change will provide a hefty band-aid so your site and our servers don’t suffer each time a query is executed. In light of that, our abuse team will be less likely to bug you about performance issues, since any poorly written queries will likely stay below our radar.

These performance improvements are now on all our servers as they were deployed last week.  It’s hopefully the first of many general purpose improvements to our systems.  We hope everyone enjoys the improvements.