How can I tune MySQL?
Along with optimizing MySQL tables and queries, configuration changes can be made to the MySQL server itself to help tweak performance for your specific needs. Running a MySQL server with the default settings will likely cause a performance hit.
The global MySQL configuration file is my.cnf. On CentOS, the default location of the global configuration file is /etc/my.cnf. In Ubuntu and Debian, the default location is /etc/mysql/my.cnf. Please check your distro's documentation for other possible locations of the configuration file.
A great tool which analyzes MySQL's perfomance and gives recommendations on variables to adjust in order to increase perfomance is MySQLTuner. It is a Perl script, so you will need Perl installed on your server before you run the tuner.
Download the MySQL Tuning script
[1] You can download MySQLTuner to your server by running the following command via SSH when logged in as root:
wget http://mysqltuner.com/mysqltuner.pl</sytnaxhighlight>
[2] Make the script executable by running this command:
<syntaxhighlight lang="bash">chmod x mysqltuner.pl[3] To get reliable results, MySQL must be running for 24-48 hours. The longer MySQL has been running, the more reliable the recommended configuration changes will be. To see how long MySQL has been running, enter the mysql command prompt and issue the 'status' command:
mysql
mysql> statusExample output:
Connection id: 2639845
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.95-community MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 16 days 5 hours 6 min 26 secUsing MySQLTuner
[1] When MySQL has been running for 24-48 hours, reliable recommendations can be gathered from MySQLTuner. Run the script (you will want to be located within the directory where you downloaded the MySQLTuner script to) by running this command:
./mysqltuner.pl[2] Allow the script to run. The time it takes the tuner to run its queries and gather variable recommendations depends on the number of tables and amount of MySQL usage.
[3] Below is example output from the tuner script. Read the Performance Metrics to get an idea of how efficient your MySQL server is currently running. Pay close attention to the Recommendations, as these are what you are going to use to get the best performance out of MySQL.
Example output:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> 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.0.95-community
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: Archive -BDB Federated InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 33M (Tables: 551)
[--] Data in InnoDB tables: 880K (Tables: 51)
[!!] Total fragmented tables: 107
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 27d 0h 45m 45s (13M q [5.738 qps], 201K conn, TX: 65B, RX: 1B)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 66.0M global 6.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 716.0M (17% of installed RAM)
[OK] Slow queries: 0% (0/13M)
[OK] Highest usage of available connections: 30% (30/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/7.5M
[OK] Key buffer hit rate: 99.6% (24M cached / 92K reads)
[OK] Query cache efficiency: 87.5% (10M cached / 12M selects)
[!!] Query cache prunes per day: 1257
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 56K sorts)
[!!] Temporary tables created on disk: 28% (21K on disk / 73K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (100 open / 101K opened)
[OK] Open file limit used: 12% (129/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] Connections aborted: 23%
[OK] InnoDB data size / buffer pool: 880.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
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
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 32M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 100)In the General recommendations section, we can see some tables need to be optimized in the server's databases, databases are being queried inefficiently, and that applications are not properly closing connections to MySQL.
- Tables can be optimized from the MySQL command line or tools like phpMyAdmin.
- Script developers should be contacted for issues like innificient queries and MySQL connections not closing.
The tuner also gave a few variables that can be adjusted for better performance.
[4] Follow the recommendations and adjust the suggested variables in the server's global MySQL configuration file (my.cnf).
[5] Restart MySQL
CentOS:
service mysql restartUbuntu/Debian:
sudo /etc/init.d/mysql restart[6] Allow MySQL to run for another 24-48 hours
[7] Run the tuning script again to see how the suggested changes affected performance.
[8] Repeat steps 4-10 until MySQL has been sufficiently tuned.
