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.

I’ve decided to give people two more days for a chance to win a free MySQL Book — Sheeri Cabral’s MySQL Administrators Bible.

I have had five people so far provide recommendations for a simple MySQL configuration question as stated in For MySQL DBA fame and glory. Prize included. Shlomi Noach the current front runner.

Try your MySQL Performance Tuning skills. This is a good opportunity for new MySQL DBA’s and experienced DBA’s to provide basic input.

Recently on a client site I had to fight the pain of having no way to confirm loss of data integrity when optimizing data types. Due to MySQL’s ability to perform silent conversion of data, when converting a number of columns we enabled sql_mode to catch any truncations as errors.

sql_mode=STRICT_ALL_TABLES

This ensured that should any data truncations occur, an error is thrown not a warning. The following shows an example case study for converting an INT to TINYINT UNSIGNED and shows that without sql_mode silent conversions occur.

mysql> drop schema if exists tmp;
Query OK, 25 rows affected (0.40 sec)

mysql> create schema tmp;
Query OK, 1 row affected (0.01 sec)

mysql> use tmp
Database changed
mysql> create table t1(i1 INT NULL);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1),(2),(3),(256),(65536),(NULL);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+-------+
| i1    |
+-------+
|     1 |
|     2 |
|     3 |
|   256 |
| 65536 |
|  NULL |
+-------+
6 rows in set (0.03 sec)

mysql> set session sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
ERROR 1264 (22003): Out of range value for column 'i1' at row 4
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
Query OK, 6 rows affected, 2 warnings (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 4 |
| Warning | 1264 | Out of range value for column 'i1' at row 5 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| i1   |
+------+
|    1 |
|    2 |
|    3 |
|  255 |
|  255 |
| NULL |
+------+
6 rows in set (0.00 sec)

The process however for the client included converting a number of DATETIME columns to DATE columns. The input data specification called for the input values to be dates only so storing and using DATETIME was simply a waste of space.

Unfortunately the use of sql_mode didn’t help here, so the combination of changing multiple columns in one time simply meant it was impossible to determine if any truncations occurred.

This combined with the sheer data volume, tables between 10GB – 500GB, with 10 – 100 million rows.
When you see 14+ millions rows modified, 35+million rows and of course SHOW WARNINGS by default only shows a very small number of actual warnings

It is of course impossible to run individual ALTER statements on columns. Of the top 10 tables, excluding the largest, alter times were between 3 and 15 hrs.

The only solution is to run pre SQL checks on the data to search for the boundary conditions manually. This is not the ideal solution for a customer.

As the recipient of the 2009 MySQL Community Member of the Year award I received a MySQL crystal ball. While it looks good in my bookcase, unfortunately the best advice I can offer during this time of uncertainty is “watch this space”.

A number of topics where information is still very much unknown and I’m either asked about, or am following includes:

• The Oracle acquisition of Sun, owner of MySQL.
• MySQL 5.4 Alpha release and schedule for production release
• The end of MySQL 5.0 Community/Enterprise split
• The future of Falcon in MySQL 6.0?

Thanks to Julian Cash of the Human Creativity Project of his photograph at the 2009 MySQL Conference.

View Photo. View all 2009 MySQL Conference photos.

Today, while on the MySQL manual page, I typed in ‘select’ in the search manual box to confirm the SELECT syntax.

The result was not what I expected, the “SELECT” command. Instead I only got two options “Speed of SELECT …” and “Optimizing SELECT and …”.

Ok, well that’s not what I want, there is a suggestion box to the right so I pick the top option “mysql select”. Not only is this worse with “Type Conversion in …”, “Searching on Two Keys” I also get 3 totally useless “Keymatch” records

Download MySQL -http://dev.mysql.com/downloads/
MySQL Training – http://www.mysql.com/training/ KeyMatch
Buy MySQL Enterprise -http://shop.mysql.com/enterprise/

I know in the past just entering ‘SELECT’ worked, because I’ve been presently writing tests on JOIN syntax and I wanted to link in my blog reference.

Images of my searches.