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.
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 will download the program and make it executable.
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
Once you have update the options inside of /etc/my.cnf you will need to restart MySQL to have the new settings in place.