So far we’ve been spending a lot of time setting up a LAMP and LEMP server. We’ve built firewalls, setup the very basics, and then started to configure Apache, MySQL and PHP; and Nginx, MySQL and PHP.
In the next few tutorials we are going to look at improving speed in the various parts of the web stack. This tutorial specifically we are going to look at MySQL.
When we installed MySQL, we really left it at the base installation. The problem with this? Nothing, if you think your server will never fail. 🙂
There are a lot of variables in MySQL Servers you can tune, but only a few of them are really important for the most common workload. When we get these settings right other changes will most commonly offer only incremental performance improvements.
Kick MySQL Into Gear
Database tuning focuses more on memory management than anything else. Your RAM will always be an order of magnitude faster than reading blocks off a disk. RAM also costs more per MB than disk, so we have to select some sort of memory management strategies carefully.
WordPress is a read-intensive operation. In other words, the majority of SQL statements are SELECTs rather than INSERTs, UPDATEs, or DELETEs. With that in mind, we can tune MySQL with a read-intensive strategy. Some of the best guidance I’ve found was in Peter Zaitsev’s “What to tune in MySQL Server after installation.”
Here’s some changes I’ve made to my MySQL configuration file, in the [mysqld] section are:
Found in /etc/my.cnf
key_buffer = 6M; (Actually, a decrease from the default)
The 20MB query cache limit seems to be just about the right size for most of the servers I’m managing. I can get very close to the buffer, without going over.
Using Query Cache
Query Cache is our best friend when it comes to performance tuning MySQL. While MySQL is a scalable relational data store, there’s not reason we can’t continue to work on it to allow WordPress to run better and faster. We’ll want to make sure that query cache is enabled and performing well for our sites.
We’ll start out with changing the settings in an easy way. In the same file (/etc/my.cnf) locate the following:
query_cache_type = 1 query_cache_limit = 1M query_cache_size = 16M
I’ve went ahead and changed these to what I set them at.
Other MySQL Optimizations
While query cache is great, there’s always other options you can look at. Before I talk briefly about these options, I want to introduce a great tool for performance tuning your website.
New Relic is the a great tool that provides your WordPress site with such features as MySQL performance and monitoring of third-party services and APIs. You can get a free account and learn more about New Relic’s WordPress features.
And with that, here’s a list of potential changes you could make to your my.cnf file. Before making changes to these, I defintely recommend that you spend some time using something like New Relic to help you out.
key_buffer_size – This is very important if you use MyISAM tables. You should be setting 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload — the key is to remember that MyISAM uses OS cache to cache the data so you need to leave memory for it as well. Check hoever if all of key_buffer is used over time. If you use few MyISAM tables you’ll want to keep it low but still at least 16-32MB so it is large enough to accommodate indexes for any temporary tables created on the disk.
innodb_buffer_pool_size – This is very important as it’s most of what most databases are for WordPress. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations.
innodb_additional_mem_pool_size – This one does not really effect performance too much, at least on an OS with decent memory allocators. Still you might want to have it set to 20MB (sometimes larger) so you can see how much memory Innodb allocated for misc needs.
If those three seem to be over the top, figuring out performance, especially the performance of MySQL, you really need to spend some time with a performance monitoring tool.
Where Do We Go?
So if this was over your head, please don’t be alarmed! This is very confusing stuff if you don’t grasps the concepts.
I would recommend that if you are looking for a performance gain, make sure you have setup query cache. After that there are other places you can look, especially with WordPress, before having to tweak MySQL more.
Any of our developers following the series, what other settings do you change?