How to Tune Up and Optimize your MySQL Configuration

A default installation of MySQL is set up using all of the default settings. However for systems with larger or smaller amounts of resources, the MySQL configuration can be changed to speed up database queries.

Install the Tuner

To install the tuner, run the following commands from inside of SSH: 

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

This program requires perl to be installed. If you are using cPanel, you already have perl.

This will download the program and make it executable.

Running the MySQL Tuner

Now that you have the tuner installed, you can run the tuner.

Run the tuner with the following command: 

./mysqltuner.pl

Once it starts, it may take some time to complete. When it has completed you will see output similar to the following: 

./mysqltuner.pl

 >>  MySQLTuner 1.4.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
[OK] Currently running supported MySQL version 5.5.45-cll-lve
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 2G (Tables: 7904)
[--] Data in MRG_MYISAM tables: 1M (Tables: 4)
[--] Data in InnoDB tables: 307M (Tables: 2492)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 34)
[!!] Total fragmented tables: 989

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 96d 7h 32m 28s (1B q [167.784 qps], 18M conn, TX: 6298B, RX: 543B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 296.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 711.2M (4% of installed RAM)
[OK] Slow queries: 0% (804/1B)
[OK] Highest usage of available connections: 51% (78/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/547.8M
[OK] Key buffer hit rate: 99.7% (44B cached / 117M reads)
[OK] Query cache efficiency: 81.1% (870M cached / 1B selects)
[!!] Query cache prunes per day: 375709
[OK] Sorts requiring temporary tables: 0% (87K temp sorts / 63M sorts)
[!!] Joins performed without indexes: 640263
[!!] Temporary tables created on disk: 91% (119M on disk / 130M total)
[OK] Thread cache hit rate: 95% (723K created / 18M connections)
[!!] Table cache hit rate: 0% (800 open / 9M opened)
[OK] Open file limit used: 4% (1K/26K)
[OK] Table locks acquired immediately: 99% (706M immediate / 706M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/307.6M
[OK] InnoDB log waits: 0
-------- 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_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 800)
    innodb_buffer_pool_size (>= 307M)

The general recommendations and variables to adjust are settings that you would add to or modify inside of /etc/my.cnf.

In the example, we would want to add or change settings like this following inside of /etc/my.cnf: 

query_cache_size=256M
join_buffer_size=256K
tmp_table_size=32M
max_heap_table_size=32M
table_open_cache=900
innodb_buffer_pool_size=307M

These values will be different for your server. If you have any mistyped lines, MySQL will be unable to start. Do not assign more memory than your server has, and always be sure to only use as much memory as your system can spare.

Once you have update the options inside of /etc/my.cnf you will need to restart MySQL to have the new settings in place.

The tuner works best after your server has been online for a while, and should be run periodically to help keep your settings current with your system's needs.