Jeff Widman

Back when I first started managing my own servers, I had no idea how to tune MySQL. I would read various forum posts where people talked about the necessity of ‘proper tuning’, but when I looked at the official docs, I struggled to differentiate between what really needed tuning versus what were arcane options that only mattered in edge cases.

So here’s the blog post I wish someone else had written.

It’s a longish read, but that’s primarily because I explained why a particular change is suggested… the actual changes are very straightforward and quick. You won’t be a MySQL guru afterwards, but it’ll be good enough for 90% of website admins. And from here you’ll know enough that the official docs will start to make sense.

This is aimed at admins running websites with < 5M pageviews a month off a single server–generally blogs or forums. For example, I run a server for a friend that has a handful of forums getting ~ 3M pageviews a month on a 8GB RAM, 6 core Linode using these tuning recommendations, and the average pageview generally spends < 10ms in MySQL query time.

First, most of these settings will be set in a global my.cnf file somewhere on your server. Typically /etc/my.cnf or /etc/mysql/my.cnf.

Tuning MySQL starts with understanding the table engine. Basically this is how MySQL manages the data underneath the covers, and is typically invisible to the app developer or SQL analyst.

In the early days, MySQL shipped with a table engine called MyISAM, which is blazingly fast for single-user workloads and allowed MySQL to win many database benchmarks that only tested single-user performance. Unfortunately, MyISAM has two huge flaws:

First, it implemented table-level locking for all queries, meaning that if one user had a query running against a particular MyISAM table, no other queries could access that table until the first one finished. This doesn’t matter if you just have a single user running queries, but it causes problems as soon as there are more than a handful of concurrent website visitors.

Second, and more important, MyISAM wasn’t fully ACID compliant. It could (and did) cause data corruption in the event of a random power failure. Most admins never hit this problem, but those that did were royally screwed.

A few years later, Percona developed an alternative table engine called InnoDB. InnoDB was fully ACID compliant, so generally the data corruption issue was fixed. That alone makes it my preferred table engine.

Additionally, InnoDB switched most queries to row-level locking, meaning that only the specific row that a query was looking at would be locked. This allowed multiple queries to run simultaneously against the same table, at the expense of slightly slower throughput on any single query. For websites, InnoDB is almost always a better choice than MyISAM.

There there are some built-in tables that MySQL uses to manage itself–these are MyISAM by default and should not be changed. Only change MyISAM tables in your application’s database. Additionally, there are some special MySQL table types like ‘memory’ that should generally not be swapped over to InnoDB. If an application creates a table using one of these alternative table engines, there’s probably a very good reason for it.

Unfortunately, InnoDB didn’t support full-text indices until MySQL 5.6 (Maria DB 10). Many linux distributions still ship with MySQL 5.5 as the default, so any tables that include full-text indices can’t be migrated to InnoDB until MySQL is upgraded.

Sidenote for Xenforo admins: This is why the xf_search_index table defaults to MyISAM–it uses a full-text index. If you swap it to InnoDB, be sure to also disable the ‘Enable Delayed Insert SQL Queries’ option under ACP > Performance.  Theoretically the type of table engine shouldn’t matter to the client application, but I know of at least once person who started getting weird search results after switching xf_search_index to InnoDB. Personally I haven’t experienced any problems, and I actually experienced significantly faster updates to the Xenforo search index under InnoDB. That said, my personal recommendation is if your Xenforo forum is big enough that you’re worried about tuning MySQL, you should switch to the official Xenforo Elastic Search plugin. Then since the xf_search_index isn’t used anymore, TRUNCATE it to save space. For the xf_sessions and xf_sessions_admin tables, InnoDB is fine but even better is setting up session caching using memcache.

You can set the default table type for new MySQL tables using the default_storage_engine parameter. I strongly recommend setting it to InnoDB. Additionally, it’s easy to swap existing tables from MyISAM over to InnoDB using the SQL command  ALTER TABLE. You will need to stop the web application from accessing the table while it’s being converted–typically I just stop the webserver, as even the largest tables for most blogs/forums generally don’t take more than 20 minutes to swap if you’ve got fast SSDs.

When you alter to InnoDB, make sure to specify the row_format. Options are compressed, dynamic, or the default compact. dynamic  is preferred, compact is the default only due to legacy reasons. If you are bottlenecked on RAM/disk, but not CPU, then use compressed for tables where compression saves a measurable amount of space (ie, lots of text columns)… For Xenforo admins, the xf_posts table is a good candidate.

In order use the dynamic  row format, you need to change the innodb_file_format  from Barracuda to Antelope.

If possible, try to completely migrate all MyISAM tables in your application databases over to InnoDB. That way, you only need to tune InnoDB cache settings and don’t have to worry about MyISAM cache settings.

However, if you still have tables in your application database that for one reason or another are required to stay on MyISAM, then try to set key_buffer_size to slightly larger than your largest MyISAM table. If all your application tables are InnoDB, then drop key_buffer_size down to 64M. Don’t drop it all the way to 0 because the internal MySQL tables will use it since they’re still MyISAM (and shouldn’t be changed).

For InnoDB, the most important variable to change is innodb_buffer_pool_size. Basically, this is how much RAM MySQL can allocate to the InnoDB cache. For safety, the default is insanely low to make sure that a default MySQL install won’t kill your dev server. However, in production you want this number as high as possible without running out of RAM. Ideally, it’s big enough to fit all your InnoDB tables in RAM. Often that isn’t possible, especially on a VPS where typically the webserver, the db, and Linux’s filesystem cache are all competing for limited RAM. In that case, try to set the InnoDB buffer pool to at least several hundred megabytes.

Storytime: A few months after I bought RockClimbing.com, we migrated to a new host and the site promptly fell over every 10 minutes. I’d rented a pretty beefy dedicated box, and the stats showed we had plenty of unused RAM/CPU capacity. I’d never run my own server before, so it took some digging to realize that the underlying issue was the InnoDB buffer pool was still at 64M. As soon as I increased it to a couple of gigs, the problems disappeared.

For the typical forum or blog, a handful of posts get the majority of the traffic. As long as those posts can fit in the InnoDB buffer pool, you’ll typically be fine. For example, I know of a forum with ~30M posts, with total InnoDB table size of ~20gb, but a buffer pool of only ~1GB. Site works just fine because vast majority of traffic hits the most recent 100K posts.

If you’ve got a InnoDB buffer pool larger than 1 GB, then you’ll also need to adjust the innodb_buffer_pool_instances. Adding more buffer pools adds a little more concurrency, although it’s a tradeoff between having many small pools versus a few larger pools. The rule of thumb is a minimum of 1 GB per buffer pool instance. However, MariaDB (explained shortly) defaults to an insane 4 or 8 buffer pool instances once you bump over 1 GB innodb_buffer_pool_size, so be sure to manually set it lower. The benefits of more buffer pools drop off pretty quickly–even on rockclimbing.com (my largest site) where the buffer pool is 12GB, I only have 4 buffer pools.

Lastly, if your version of MySQL/MariaDB supports it, enable innodb_save_on_shutdown and innodb_load_on_startup. Basically it pre-warms your cache by saving the cache to disk whenever you shut down MySQL and reading it from disk whenever you start MySQL. That way when you reboot your server MySQL isn’t super slow for the first few pageviews.

Once you swap everything over to InnoDB and then tune the InnoDB Buffer pool, you’re pretty much good for 98% of websites.

What is MariaDB? Another history lesson: MySQL started as open source, then was acquired by Oracle. While Oracle can’t legally make the database closed source, they’ve been trying to close source as much as possible. The guy who started MySQL started a MySQL replacement project called MariaDB. It adds a number of nice features, including a small speed increase.

It’s binary compatible with MySQL, so it shouldn’t cause any issues with your app. It’s also easy to install, just use the pre-packaged RPMs they provide. I’ve been using it on CentOS 7 as a drop-in replacement for MySQL with zero issues.

You’ll want to switch to MariaDB 10, as that’s the equivalent of MySQL 5.6 and adds the full text indices to InnoDB. MariaDB 10 also adds some nifty features like a regex-based search-and-replace. Very handy for updating urls after migrating an old vBulletin forum over to Xenforo.

One MySQL tuning tip that you’ll find in many blog posts is to increase the size of the MySQL Query Cache. However, the benefits are hotly debated, many people say it actually slows things down because in many situations because you’re losing more time checking the cache and finding it doesn’t have what you want than if you just bypassed the cache altogether. I just disable it most of the time.

Keep yourself out of MySQL encoding hell! I’ve been there, it’s not fun. Set the default character set in the mysql server. UTF-8 is probably what you want. MySQL’s UTF-8 is actually pseudo-UTF8, the real UTF-8 is UTF-8MB4. WordPress switched to MB4 in WP 4.2, Xenforo still on UTF-8. You’ll also want to set the default collation, likely to utf8mb4_unicode_ci .

Sidenote: I hate how MySQL has sloppy engineering. For example, MyISAM not actually being ACID compliant and losing data. UTF-8 not actually being UTF-8. Drives me nuts. If I have the choice, I much prefer PostgreSQL. They may be slower on feature releases, but at least they get it right when they do release something.

Lastly, a security tip. If you’re running MySQL on the same box as your webserver, then make sure MySQL only listens on localhost and rejects all other connections. For remote access, just SSH in, then access MySQL. Most GUIs, including MySQL Workbench (my favorite) support this type of connection.