How can I tune MySQL?

From Acenet Knowledgebase
Jump to navigation Jump to search

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:

<syntaxhighlight lang="bash">wget http://mysqltuner.com/mysqltuner.pl</syntaxhighlight>

[2] Make the script executable by running this command:

<syntaxhighlight lang="bash">chmod x mysqltuner.pl</syntaxhighlight>

[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:

<syntaxhighlight lang="bash">mysql mysql> status</syntaxhighlight>

Example output:

<syntaxhighlight lang="bash">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 sec</syntaxhighlight>

Using 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:

<syntaxhighlight lang="bash">./mysqltuner.pl</syntaxhighlight>

[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:

<syntaxhighlight lang="bash"> >> 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)</syntaxhighlight>

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: <syntaxhighlight lang="bash">service mysql restart</syntaxhighlight>

Ubuntu/Debian: <syntaxhighlight lang="bash">sudo /etc/init.d/mysql restart</syntaxhighlight>

[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.

Retrieved from "https://kb.acenet.us/index.php?title=How_can_I_tune_MySQL%3F&oldid=554"