mysqltuner: automatically optimize MySQL settings

First published on May 26, 2012

If you run your own server, a virtual private server, or a cloud server with shell access, you might want to optimize your MySQL settings. This can greatly increase your site’s performance by making the best use out of memory and caching, and minimizing disk swapping. However, if you’ve read up on settings such as the InnoDB buffer pool size, query cache, table cache, and so on, you might not know where to start in terms of what to tweak. Among other things, mysqlreport can give you lots of data to analyze. My favourite, however, is mysqltuner, which is a Perl script that will run diagnostics on your MySQL database and make specific recommendations.

mysqltuner is easy to install and use. You can download the Perl file or use an installer, such as yum (yum install mysqltuner) on Red Hat and CentOS.

Then, you can run perl mysqltuner.tpl or mysqltuner, follow the prompts for the database credentials, and wait for the results:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 145d 4h 29m 13s (17M q [1.388 qps], 290K conn, TX: 100B, RX: 27B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (14% of installed RAM)
[OK] Slow queries: 0% (199/17M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/3.5M
[OK] Key buffer hit rate: 100.0% (120M cached / 13K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (751 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 922026
[!!] Temporary tables created on disk: 35% (1M on disk / 4M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 238K opened)
[OK] Open file limit used: 5% (58/1K)
[OK] Table locks acquired immediately: 99% (33M immediate / 33M locks)
[!!] InnoDB data size / buffer pool: 3.0G/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    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:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 3G)

Then, modify the settings you are comfortable with (typically in /etc/my.cnf or directly in the MySQL console to make temporary changes) and restart MySQL if necessary. You can then run mysqltuner at any time again in the future to get up-to-date recommendations.


Speak your mind

To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word