My Boss told me to focus on scaling… so I am writing a blog post on MySQL tuning/scaling. Right now we are I would say in phase one of our WordPress scaling process ie we have one server. Phase 2 would be two servers one web one db and partitioning the db. Phase 3 would be load balance two web and with two db servers one master one slave and add Memcache… after that well I doubt we would ever get there and even this would likely be overkill.
Phase one has involved minor scaling at the application / server level. We implemented PHP opcode caching with APC, content caching using Donncha O Caoimh’s kick ass WP-Super-Cache and implemented some MySQL tuning techniques (what this post will be about).
Our Server
We have used a relatively modest server spec for the first two years 2 GB of RAM, 1 Intel(R) Xeon(R) CPU E5440 @ 2.83GHz with a 48GB SAN mount. This has seemed to work fine for the most part with almost 2000 users to date (these 2000 users are rarely writing to the DB 95% reads the main reason why this has been working) and modest traffic according to Webalizer averaging around 1.8 million page view/month. About a month ago the IT VM gods granted us an extra 2GB of ram bumping the total to 4GB which meant I could a little more MySQL tuning (you can’t really tune with minimal memory, with 2GB we were doing only query caching).
Warning
*Before I start, this tuning worked for the UBC Blogs setup you should not implement this unless you take the time to understand MySQL and each of the settings your are changing and also keep an eye on your app after implementing (CPU usage, response time etc). I am by no means a MySQL DBA (I worked with real whip smart DBA’s at the Bioinformatics Centre in the past I know what they are like and I frankly do not have the DB passion to memorize every error code, command, and the skills to model DB’s to handle multiple genomes etc.(real DBA stuff) but I have been using maintaining MySQL and building web apps based of the LAMP stack since 1999 (I am old) so I think I know enough to be get the work done (or enough to be dangerous) *.
So here are the tweaks that I found to work remember mileage may vary.
Before
Before you you begin tuning use some sort of tuning script to give you an idea of what’s going on under the hood. I used the mysqltuner.pl which is kind of old school so some of the recommends are not relevant but it gives you a good picture of what is going on. There are probably better ones out there but this has been in my toolbox for awhile.
Here is what it told me:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 17h 31m 13s (58M q [27.337 qps], 1M conn, TX: 2B, RX: 499M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 59.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 327.7M (16% of installed RAM)
[OK] Slow queries: 0% (2K/58M)
[!!] Highest connection usage: 100% (101/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/80.5M
[OK] Key buffer hit rate: 95.6% (384M cached / 16M reads)
[OK] Query cache efficiency: 54.1% (26M cached / 49M selects)
[!!] Query cache prunes per day: 379131
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Temporary tables created on disk: 40% (3M on disk / 9M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 3M opened)
[OK] Open file limit used: 0% (128/24K)
[OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 100)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 25M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
So some of the stuff is not useful as I said some is. I will go thought what I changed based on the recommends.
Optimize Tables
No brainer you have to do this every now and then. Pretty much the same as defragging your disk file system, it’s also important to do the same thing with MySQL data tables. If you don’t you may end up with slow or corrupted tables over time.
*I read on a blog post once someone wrote a php script to loop through all their databases and optimize the tables, that is a perfectly good waste of coffee drinkin’ time! Let MySQL do the work for you!
run this command:
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
*Note run this in off hours it does slow things down because it locks tables and if you have a large WPMU there will be a lot of MySQL tables to lock so it will take some time.
Query Caching
Like I said we were doing this in the past now with a little more RAM we could bump it up.
* Too much here is not a good thing the max recommended is 512MB .
From MySQL: “A Practical Look at the MySQL Query Cache” –
The MySQL query cache provides substantial benefits over other database engine caching strategies because not only is the overhead of hard parsing for identical queries avoided, but the sometimes very high overhead of recreating complex result sets from either disk or memory caches is averted as well, greatly lessening both physical and logical I/O. The end results are extremely fast response times for business applications (data warehouses, BI applications, Web applications, OLTP systems, etc.) where end user clients are repetitively executing the same MySQL queries.
I bumped up the query_cache_size up to 128M which has captured a lot of queries that would normally go to disk this is represented by the Qcache_hits 31758409 and is storing a fair amount of queries in memory as well with Qcache_queries_in_cache at 26371 .
mysql> SHOW GLOBAL STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 9566 |
| Qcache_free_memory | 28278048 |
| Qcache_hits | 31758409 |
| Qcache_inserts | 10434728 |
| Qcache_lowmem_prunes | 3865108 |
| Qcache_not_cached | 2203713 |
| Qcache_queries_in_cache | 26371 |
| Qcache_total_blocks | 68193 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Thread Cache Size
This was at the default which was 0 which is bad because this affects the thread cache rate.
Thread_cache_size determines how many threads MySQL will hold open in memory to handle new connections. The hit rate should be as close to 100% as possible. You can calculate your hit ratio by dividing the ‘threads_created’ status variable by the ‘connections’ status variable:
100 - ((Threads_created / Connections) * 100)
For UBC Blogs the thread cache rate was:
100 – ((1629684 / 1629684 ) * 100) = 0% == very bad. This is because the thread_cache_size was set to the default 0. MySQL was basically holding 0 threads in cache so it was forced to create new threads all the time.
I set the thread_cache_size to 16 to see if it helps (* You should start at a low value and move up).
Checking after a ~ couple weeks of running
mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Connections | 1315861 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 3057 |
+-----------------+-------+
1 row in set (0.00 sec)
Now doing the calculation again:
100 – ((3057 / 1315861 ) * 100 ) = 99.76% which is a huge improvement.
Table cache
Every time MySQL accesses a table, it places it in the cache. If the application accesses many tables like WordPress MU, it is faster to have more of these in the cache.
We had it set to the default 64.
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Opened_tables | 3948355 |
+---------------+---------+
1 row in set (0.00 sec)
30 minutes later:
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Opened_tables | 3955277 |
+---------------+---------
You can see the number go up and up. Increasing the table cache size will help a lot I doubled this to 128 to start will see how it works out.
Key buffer size
From MySQL:
You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine’s total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine’s total memory), your system might start to page and become extremely slow.
You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine’s total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine’s total memory), your system might start to page and become extremely slow.
The Key_reads/Key_read_requests ratio should normally be less than 0.01. The Key_writes/Key_write_requests ratio is usually near 1 if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.
Here is what we were getting:
mysql> SHOW STATUS LIKE 'Key_%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6405 |
| Key_blocks_used | 3166 |
| Key_read_requests | 386078455 |
| Key_reads | 16844790 |
| Key_write_requests | 3127706 |
| Key_writes | 1148023 |
+------------------------+-----------+
7 rows in set (0.00 sec)
key_read / key_reads_requests
Ours is over 4.36 which is not so good.
key_write / key_writes_requests = 0.36
The default key_buffer is 64Mb moved this up to 128MB to start.
A month later checking:
mysql> SHOW STATUS LIKE 'Key_%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 5994 |
| Key_blocks_used | 4059 |
| Key_read_requests | 311890660 |
| Key_reads | 15189451 |
| Key_write_requests | 3943721 |
| Key_writes | 1743544 |
+------------------------+-----------+
7 rows in set (0.00 sec)
key_read / key_reads_requests
Ours is over 4.87 which is not good even after moving up to 128MB, until we move to a dedicated MySQL server 128MB is the max we can set it at to be on the safe side. I think the fact this is off maybe due to the fact that there is a huge number of tables in a WordPress MU install with a significant number of users.
key_write / key_writes_requests = 0.44 up a bit.
That is it for now.
Benchmarking
Doing a Apache Bench web load test before and after with a search on the UBC Blogs homepage we get:
AB Testing a dynamic page **before** upgrade
[scott@rab ~]$ ab -n 1000 -c 100 http://blogs.ubc.ca/?s=outage
Concurrency Level: 100
Time taken for tests: 4.350686 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 16953480 bytes
HTML transferred: 16544000 bytes
Requests per second: 229.85 [#/sec] (mean)
Time per request: 435.069 [ms] (mean)
Time per request: 4.351 [ms] (mean, across all concurrent requests)
Transfer rate: 3805.38 [Kbytes/sec] received
same page after adding the RAM and tuning
[scott@rab ~]$ ab -n 1000 -c 100 http://blogs.ubc.ca/?s=outage
Concurrency Level: 100
Time taken for tests: 2.144289 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 16953000 bytes
HTML transferred: 16544000 bytes
Requests per second: 466.36 [#/sec] (mean)
Time per request: 214.429 [ms] (mean)
Time per request: 2.144 [ms] (mean, across all concurrent requests)
Transfer rate: 7720.51 [Kbytes/sec] received
You can see the Time per request was cut in half shows what some extra RAM and a little MySQL tuning can speed things up 
mostly the RAM helped in this case…
Still more to do but on the right track. MySQL and databases in general are not really out the box get up and go ready, you have to do some tuning to get the most out of the install and tweak for your app, all part of the fun I guess…
I recommend skimming through: “High Performance MySQL: Optimization, Backups, Replication, and More” contains some useful stuff.
Scott McMillan
bppres1