Optimizing Your Headway Website – Performance Tuning MySQL

Introduction

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.

MySQL Performance

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?

Spread the word!

2 Responses

    • I’ve never used InnoDB myself since it seems to be more for clustered MySQL servers, where you have heavy heavy heavy traffic that writes a lot to the DB. Most WP sites don’t need that, especially ones that are typically blogs or a website for a business. When you start talking about a website as an application (something like Facebook comes to mind), where you have a lot of users inputting data and everything is saved into the DB, then I would look at InnoDB. However, that’s only my limited understanding of the differences of the two engines. Take a look at http://markmaunder.com/2011/06/23/wordpress-myisam-vs-innodb-mysql/ and judge for yourself. 🙂

Leave a reply

Copyright © 2016 Vesped Inc. All Rights Reserved. Proudly Powered by Headway and WordPress