Projects Tech Stuff

MySQL Optimization for WordPress

We’ve been running one WordPress multisite installation here at work for about three years, and over the summer launched a second. But this one is a whole other kettle of fish, as Dr. Winzenburger used to say.

Rolling out an installation with 1600 blogs — one for each student plus a number of mentors and faculty members — and then running mass training sessions for them — leads us into some pretty rough waters. Almost immediately after the classes started, people were getting “Could Not Connect To The Database” screens.

Is it any surprise that the out-of-the-box MySQL options are inadequate for this kind of traffic? Pretty embarrassing, and time for a true fire drill.

First we added an extra process to the virtual box, and doubled the memory. No dice. We made a few tweaks to Apache. Nothing. My revolutionary flash of insight was, if we’re seeing “can’t connect to database,” maybe there’s something wrong with the database.

So started to play with the settings. You can do each of these by running a query, either in a database app like phpmyadmin or Navicat, or from the command line on the box itself. A query might look like this:


SET GLOBAL query_cache_size=64000000;
SET GLOBAL max_connections=1000;

That lets you see right away if there’s an effect, but the changes are lost next time the system restarts. To change these permanently, look for your my.cnf file. Maybe it’s in /etc? Depends on your flavor of Linux. Then BEFORE the second that looks like this:


[mysqld_safe]

For our bigger installation with ~15,000 tables we added this:

max_allowed_packet=32000000
thread_cache_size=128
max_connections=1000
table_cache=768
query_cache_size=64000000
query_cache_type=1
key_buffer_size=96000000
tmp_table_size=64000000

Nothing like that exists, so don’t worry. These are overriding the defaults.

For the smaller, with ~3,000 tables, this:

max_allowed_packet=32000000
thread_cache_size=128
max_connections=1000
table_cache=512
query_cache_size=64000000
query_cache_type=1
key_buffer_size=64000000

Not much of a difference.

Then find the directory that has mysqld (maybe /something/something/init.d, again depending on your flavor) run ./mysqld restart. All should be well.

One thing we ran into before goosing up our tmp_table_size enough was that a table_cache of 512 had the odd side-effect of making all of our databases disappear. That wouldn’t be good. I was relieved to find that lowering it back below whatever phantom threshold there was made them re-appear again (is that an understatement?) Once I increased the other values, it was possible to run the number up higher.

Just for comparison’s sake, the default values are:

max_allowed_packet=1000000
thread_cache_size=0*
max_connections=100
table_cache=64
query_cache_size=0*
query_cache_type=0
key_buffer_size=8000000
___
*caching is turned off altogether by default!

If you have phpmyadmin running, the Runtime Information screen highlights in red some of the settings to watch out for, along with hints on what to change.

These were the “final” settings; at the close of business the day earlier we’d put in somewhat lower numbers — table_cache was 256, key buffer size was 64M — and it worked without a hitch. The next day is going to be extra busy, so hopefully these will give us even more headroom.

Tom
Tom McGee has been building web sites since 1995, and blogging here since 2006. Currently a senior developer at Seton Hall University, he's also a freelance web programmer and musician. Contact him if you have the need for a blog, web site, redesign or custom programming!

Leave a Reply

Your email address will not be published. Required fields are marked *