Optimize MySQL performance with mysqltuner
The following tutorial describes the steps to optimize the performance of a MySQL database with the mysqltuner script.
Login to your server on the shell, then execute the following commands:
Download the mysqltuner script:
cd /usr/local/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Run mysqltuner
/usr/local/bin/mysqltuner.pl
Then enter root as username and the mysql root password.
You will get a output similar to this:
root@v221:/usr/local/bin# /usr/local/bin/mysqltuner.pl
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password:
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-24+lenny2 [!!] 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: 26M (Tables: 215) [!!] InnoDB is enabled but isn't being used [!!] Total fragmented tables: 33
-------- Performance Metrics ------------------------------------------------- [--] Up for: 96d 23h 3m 41s (10M q [1.239 qps], 686K conn, TX: 701M, RX: 1B) [--] Reads / Writes: 44% / 56% [--] Total buffers: 58.0M global + 2.6M per thread (100 max threads) [OK] Maximum possible memory usage: 320.5M (12% of installed RAM) [OK] Slow queries: 0% (20/10M) [OK] Highest usage of available connections: 33% (33/100) [OK] Key buffer size / total MyISAM indexes: 16.0M/8.5M [OK] Key buffer hit rate: 99.9% (57M cached / 30K reads) [OK] Query cache efficiency: 78.6% (5M cached / 6M selects) [!!] Query cache prunes per day: 483 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 408K sorts) [!!] Temporary tables created on disk: 36% (269K on disk / 745K total) [OK] Thread cache hit rate: 99% (427 created / 686K connections) [!!] Table cache hit rate: 2% (64 open / 3K opened) [OK] Open file limit used: 11% (120/1K) [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB 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 Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 16M) tmp_table_size (> 32M) max_heap_table_size (> 16M) table_cache (> 64)
The script recommends to adjust or add the following variables in the mysql my.cnf file. The location of my.cnf is normally /etc/my.cnf or /etc/mysql/my.cnf depending on the Linux distribution that is installed on your server.
Open the my.cnf file:
vi /etc/mysql/my.cnf
and increase or set the variables in the [mysqld] section of the file. Mine looks now like this:
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 table_cache = 128 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M
query_cache_size = 32M tmp_table_sizee = 64M max_heap_table_sizee = 32M
Then save the file and restart mysql. After a few hours, rerun mysqltuner and check again if the values are fine now or if the have to be increased to a higher value.
