Optimizing MySQL database using mysqltuner.pl

MySQLTuner is a script written in perl which based to MySQL logs, it generates some pretty safe suggestions about which variables can be changed in MySQL config file for a little extra performance

First, we download the script and make it executable:

wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

Then we run it:

./mysqltuner.pl

It is for best, that MySQL has not little uptime when running the script, the longer the more accurate suggestions it will be able to output. As opposed to the "MySQL started within last 24 hours – recommendations may be inaccurate." error message if you attempt to run the script right after a MySQL restart.

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  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.1.68-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 5367)
[--] Data in InnoDB tables: 400M (Tables: 4079)
[--] Data in MEMORY tables: 0B (Tables: 33)
[!!] Total fragmented tables: 4684

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 10h 40m 6s (68M q [51.568 qps], 1M conn, TX: 794B, RX: 12B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 78.0M global + 7.5M per thread (800 max threads)
[OK] Maximum possible memory usage: 5.9G (76% of installed RAM)
[OK] Slow queries: 0% (1K/68M)
[OK] Highest usage of available connections: 8% (66/800)
[OK] Key buffer size / total MyISAM indexes: 36.0M/229.4M
[OK] Key buffer hit rate: 98.5% (572M cached / 8M reads)
[OK] Query cache efficiency: 70.8% (38M cached / 54M selects)
[!!] Query cache prunes per day: 723587
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 109545
[!!] Temporary tables created on disk: 31% (1M on disk / 4M total)
[OK] Thread cache hit rate: 99% (66 created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 83M opened)
[OK] Open file limit used: 3% (1K/48K)
[OK] Table locks acquired immediately: 99% (26M immediate / 26M locks)
[!!] InnoDB data size / buffer pool: 400.4M/8.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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 1024)
    innodb_buffer_pool_size (>= 400M)

You should carefully read the results, especially the recommendations at the end which shows exactly which variables you should adjust in the [mysqld] section of your my.cnf file (/etc/mysql/my.cnf for Debian and Ubuntu). Whenever you change your my.cnf, make sure that you restart MySQL service.

After changes to my.cnf file, you can come back after 2 or 3 days and run mysqltuner.pl again, in order to see if there are any additional suggestions to improve the MySQL performance. This way you are able to optimize your MySQL's settings step by step. Give extra attention to "Query cache efficiency" percentage and note down it's change.

This method is ideal for learning as well, while you may come up with random configurations across the Internet matching your server's specification, they could become confusing when you have to change a bunch of variables at once or it wouldn't give you the time to study how every variable change affects your MySQL server performance.

Please note: We hold no responsibility for any changes you make to your my.cnf config file. Also we strongly suggest you to take backups not only from my.cnf config file itself but take whole database backups as well.