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.
Very cool! Any chance you can give more details on what has been done? For those of us who have private servers this could help us optimize our mysql instances in a similar way as you did on the shared servers.
I agree that when you host many different sites, it is almost impossible to optimize mysql beyond the basic settings to fit all cases.
I already have a few ideas like changing mysql’s tmp directory (tmpdir) to a tmpfs mount or even /dev/shm directly.
I did something similar using ramdrives, on a database dedicated server, I’ll assume that you use them as well, but I’m also curious about the specific setup that you use.
I second the Idea of a detail article about this.
awesome work guys.
We first evaluated using a ram disk to do it but then realized it be a waste of memory. The reason being a lot of the temporary tables are not that big but we’d need to set aside a lot more to compensate for the rare cases there were large tables. So that would have meant probably 2GB set aside as a ram disk and 99.9% of it not being utilized the majority of the time.
So what we opted to do instead was to use /dev/shm because it should be memory based when there is a lot of memory being used for caches. It’s obvious the majority of the time these tmp tables are now memory based. The reason being /tmp is it’s own partition but not a tmpfs so it’s disk based. Since switching the amount of disk hits has dropped off drastically.
This is probably the biggest gain you could see from such randomized usage patterns I think. There are large temporary tables that don’t fit into heap and tmp table sizes. There are also cases where it cannot be memory based in MySQL when there are certain field types. I believe txt fields are one of those cases so mediumtext, longtext etc. So you could have the tmp table and heap table sizes 1gb and it would still not put them into memory if the query contained those field types. This fix forced those types of queries to now be memory based although MySQL itself does not realize this.
Such a simple change it’s one line change with drastic improvements. I don’t know if it be a great solution when you are running your own server. In those cases you’d fix your queries. Unfortunately we cannot fix the bad queries users throw at our servers. What I regret is we did not do this years ago but instead just started using it recently. Though I imagine a lot of providers don’t do this so it’s not like we’re slow on implementing this. Very few people have been implementing such an optimization.
In previous posts you’ve mentioned bad WordPress plugins, known to use heavy, non-optimized MySQL queries.
What I also would be glad to see is a list of such “plugins to avoid” from you (since you can monitor and estimate MySQL performance).
BTW, similar “internal MySQL tips” for other popular MySQL-based systems (Drupal, phpBB, etc.) would also be very appreciated 🙂
What’s the percentage of improvements like?