First Install mysqltuner
In order to tune mysql database that is running wordpress you must first download and install “mysqltuner”. On my CentOS 5.8 server simply run yum to install it.
yum install mysqltuner
Run mysqltuner to analyze your database
Invoke mysqltuner from shell and it should spit out a bunch of useful diagnostics.
[root@Linux ~]# mysqltuner
>> MySQLTuner 1.1.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 19)
[--] Data in InnoDB tables: 5M (Tables: 34)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 37
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10d 20h 6m 34s (2M q [2.351 qps], 64K conn, TX: 9B, RX: 360M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 573.8M (18% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/496.0K
[OK] Key buffer hit rate: 100.0% (13M cached / 275 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 272K sorts)
[!!] Temporary tables created on disk: 47% (159K on disk / 337K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 88% (138 open / 156 opened)
[OK] Open file limit used: 8% (82/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 5.3M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
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
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
Tackle the problems that need attention
Skip the “general recommendations” section which tells you to run OPTIMIZE TABLE, as the wordpress tables are InnoDB tables which don’t support defragmentation. If you don’t believe me you can run this command:
mysqlcheck -o --all-databases
It should spit out the following:
wordpress.wp_ak_popularity Table is already up to date wordpress.wp_ak_popularity_options Table is already up to date wordpress.wp_amm Table is already up to date wordpress.wp_commentmeta OK wordpress.wp_comments OK wordpress.wp_links Table is already up to date wordpress.wp_options OK wordpress.wp_popularpostsdata OK wordpress.wp_popularpostsdatacache OK wordpress.wp_postmeta OK wordpress.wp_posts OK wordpress.wp_seo_title_tag_category Table is already up to date wordpress.wp_seo_title_tag_tag Table is already up to date wordpress.wp_seo_title_tag_url Table is already up to date wordpress.wp_term_relationships OK wordpress.wp_term_taxonomy OK wordpress.wp_terms OK wordpress.wp_usermeta OK wordpress.wp_users OK wordpress.wp_wp125_ads note : Table does not support optimize, doing recreate + analyze instead status : OK
Look for the lines with [!!]. Those are the areas which will require tuning. You should see at the bottom there’s a section which says “Variables to adjust:”. For me they say:
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 400)
These settings can be adjusted in “/etc/my.cnf”. These recommendations are different depending on how big your databases are. I’m doubling the recommendations:
[mysqld] query_cache_size = 16M tmp_table_size = 32M max_heap_table_size = 32M thread_cache_size = 8 table_cache = 800
Make sure to restart your mysqld server for changes to take affect. Run mysqltuner again and check output. This time it should tell you to increase certain variables again. But take a look at the recommendations section. It should tell you to wait a while before you run the command again as it may not give you accurate recommendations.
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
Make note of the recommended variables to adjust and re-run mysqltuner again after 24 hours to see if they require re-adjusting.
Update: After 24 hrs
I have run mysqltuner again and updated the following settings:
tmp_table_size = 128M max_heap_table_size = 128M query_cache_limit = 4M
