MySQL Database Optimization on WordPress

Do you suspect your MySQL database is slowing down your WordPress website but just don’t know what to do? What the heck is “database optimization” anyhow? This post is for all who have asked these questions.

What The Heck is MySQL Database Optimization?

A lot of smart people will go on and on about this topic but, bottom line is this simply means getting your database to respond quicker so, your website performs faster. We speed up our database by doing one thing, setting up caching.

MySQL caching will save in memory your common database queries so, the database can “bam!” return your database information quickly from memory instead of having to do a database query of the same. The second type of caching is file system caching but since that is another topic were going to stick to MySQL caching for this post.

How To Optimize Your MySQL Database

You will have to find your “my.cnf” configuration file for MySQL because you need to add some variables in this file, often located at /etc/my.cnf. Then you will add some configuration settings, restart mysql and, then watch your database performance. Based on the performance, you will have to tweak you’re my.cnf configuration setting. Let’s begin:

First, Get Memory and Database Sizes

Step #1 – get db sizes

You need to know how big your database MYISAM and INNODB table sizes are. The best way to get this is to login to MySQL as root with the command:
>mysql -u root -p
Then run the sql command:

mysql> Select sum(data_length)/1024/1024 as data_size, sum(index_length)/1024/1024
as index_size, sum(data_length+index_length)/1024/1024 as total, engine from
information_schema.tables group by engine;
| data_size    | index_size  | total        | engine |
|   0.00000000 |  0.00000000 |   0.00000000 | CSV    |
| 320.70312500 | 46.71875000 | 367.42187500 | InnoDB |
|   0.00000000 |  0.00000000 |   0.00000000 | MEMORY |
|   7.22693157 |  2.39062500 |   9.61755657 | MyISAM |

Note: the above results tell you your InnoDB size is 367.4Mb and your MyISAM size is 9.6Mb.

Step 2 – get your total memory size

Now enter this linux command:

# free -m
             total       used       free     shared    buffers     cached
Mem:          1728        927        800          0          0          0
-/+ buffers/cache:        927        800
Swap:            0          0          0

Note: you have 1728Mb total of RAM

My.cnf Starting Variables


Initial Variables Explained

#1. query_cache_limit (max size of any one query result, 1M is usually a good setting for WordPress)

#2. query_cache_size (Total amount of memory to use for cache)

#3. query_cache_type (#0=off, #1=on)

#4. innodb_flush_log_at_trx_commit=0
(default = 1): The default “1″ behavior flushes the log buffer to disk with every commit plus by default the system also writes commits every second. Setting to “0″ makes the system not write commits to disk at transaction time at all. This means if the system crashes you could loose at most one second of data…who cares? I don’t care enough to double my disk writes which can make a big difference on a VPS which has slow disk I/O.

#5. innodb_buffer_pool_size
(the value must be at least as large as your InnoDB total size per step #1 above, and is recommended to be 50% – 75% of your total RAM)

Now what – monitor performance

Restart MySQL: Typical Linux command is “service mysql restart”.

Wait a few hours: This allows the system to build up some performance data.

Check MySQL Performance: Do this by following these instructions.
1. Login to MySQL with command: mysql -uroot -p
2. Run this query:

show status like ‘%qcache%’;

mysql> show status like '%qcache%';
| Variable_name           | Value  |
| Qcache_free_blocks      | 146    |
| Qcache_free_memory      | 378816 |
| Qcache_hits             | 40313  |
| Qcache_inserts          | 5739   |
| Qcache_lowmem_prunes    | 1109   |
| Qcache_not_cached       | 2036   |
| Qcache_queries_in_cache | 2838   |
| Qcache_total_blocks     | 6143   |

Tweak your settings

Notice in the example that Qcache_lowmem_prunes = 1109. This means that the cache memory was not large enough to hold all the queries in memory; so, 1109 cached queries had to be thrown out, “pruned”, to make room for new ones. This is bad because we are essentially not caching enough queries and have to keep throwing out good cached queries just to make room for new ones. Let’s up the cache_size and make sure we allow for enough tables opened by setting table_cache=1000!




(NOTE:should be = number of tables in any query * # max_connections by default=100 max connections * 10 tables per query = 1000. This should be more than enough)

Check Performance again

mysql> mysql> show status like ‘%qcache%’;

| Variable_name           | Value    |
| Qcache_free_blocks      | 13       |
| Qcache_free_memory      | 21251312 |
| Qcache_hits             | 15313    |
| Qcache_inserts          | 2521     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 677      |
| Qcache_queries_in_cache | 1878     |
| Qcache_total_blocks     | 3931     |

There are plenty of other database caching settings to consider but this is looking much better.