If you had to configure a WordPress MU installation without access to any details of your MySQL Configuration, what would you do?

What top five configuration settings would you use?

I asked the community this question, see For MySQL DBA fame and glory. Prize included and a number of brave soles responded for a chance to win a free copy of MySQL Administrators Bible by MySQL Community She-BA Sheeri Cabral.

There is no perfect answer and of course you would want to set more then five options, however the purpose of the competition was to seek what people would do with limited information and a limited choice of actions to enable people to focus on what is important.

Part of choosing the winner was for those that accurately read the question. There were a number of triggers. These included:

• Give an answer with five options. That was the question.
• WordPress MU gives you an indication of the schema size (about 300 tables), the default storage engine of MyISAM, and the technology stack includes PHP.
• The current MySQL configuration included just two existing options.

What would I do?

If I was asked this question, here is what I’d do.

1. Demand more information about the H/W the Mysql server running on. I also would do my own physical verification as client description != actual implementation.
2. Confirm if the server is shared or dedicated.
3. Get SHOW GLOBAL VARIABLES output.
4. Get 2 x SHOW GLOBAL STATUS output, separated from some time, e.g. 1 minute, 10 minutes, 1 hour.

Without the above information I’m not really prepared to tune a system in isolation. I’ll spend more time at a later time explaining why.

So my answers for reference.

key_buffer_size

We have as a pre-cursor to the top 5, the key_buffer_size. The key_buffer_size is used to store MyISAM Indexes, and is assigned as MySQL server startup. Setting this value to large is a waste of space, setting this too small will increase the amount of disk I/O. In this example, the total index space was around 100MB, so setting the value to 384-512M supports adequate growth. The default value for 5.0.67 is 8M. When you read on, see point 5, you may well consider lowering this value.

1. log-bin=/path/to/dir/file-prefix

If you are storing data in a database I’m sure you want to keep it. log-bin is necessary for point in time recovery. Period. You should also specify the actually path, and store this outside of the default MySQL data directory. The default is NOT enabled.

2. myisam_recover = force,backup

MyISAM is not the most optimal storage engine for online systems. This is a much larger discussion however for the stock default WP and general LAMP products we start with MyISAM. Setting myisam_recover will improve one disadvantage of MyISAM and that is the recovery on startup. The default is NOT enabled.

3. query_cache_size = 64M

In absence of greater information, enabling the Query Cache in a generally high read environment is a good starting point. You have the ability to set and unset this dynamically on a running server, so it is easy to remove if performance is not the optimal. You should also not go overboard with the size of the query cache. Start with a modest amount, such as 64M, assess the impact of the QC and re-assess the value. The default is NOT enabled. The Query Cache also uses the variables query_cache_type, which defaults to ON, and query_cache_limit which defaults to 1M.

4. table_cache=1024

The MU part of the WordPress is a trigger that the system will have several hundred tables. Monitoring Opened_tables, and Open_tables is as easy check to determine a more appropriate table_cache value. The default value on 5.0.67 is 64.

5. max_connections=150

This was a difficult choice, the first 4 are obvious for the current environment.

I have to question the reason why max_connections=500. Given that there is no other configuration settings, why would this value be so large. Start with a more realistic figure, such as the default of 150, and monitor max_used_connections, threads_running for a while. A review of the H/W confirmed this is unrealistic, the system has 16GB of RAM, but only a 32bit OS.

Moving forward

Tuning MySQL is not a trivial or simple task. It takes a certain amount of knowledge about the system. Recently I have seen some very unusual MySQL configurations, and I have to pass on this information for all readers and DBA’s.

Gather information about your environment, and the setting in question before making a change. Document this on an internal wiki. It is important, when in a year’s time somebody asks, why did you change this?

The bonus prize was for anybody that questioned the current configuration which only included two parameters.

I had to look up –safe-show-database. No wonder I didn’t know it, Deprecated since 4.0.2. I wonder sometimes when people add uncommon options. Shlomi the only person to at least raise a ? on that on.

And the winner, I had to consider 3 people that had 3 of my 5. Shlomi Noach made such a plea I have to give it to him, and I’ll throw in international shipping.