Improve MySQL performance with MySQLTuner

MySQL is one of the most popular database packages available for any platform. It makes its mark primarily being the back-end to web applications and sites and usually the defaults are fine for most installations.

With larger demand and more data, defaults are most likely inefficient and needlessly slowing down the applications that interface with it.

A perl script called MySQLTuner can help you optimise MySQL configurations. This tool will suggest ways to better manage the database and ensure that enough resources are allocated to it, while at the same time ensuring MySQL resources are not being overextended.

To obtain MySQLTuner, download it via wget:

$ wget mysqltuner.pl

This may not look correct, but the mysqltuner.pl website actually redirects to download the script of the same name. Once it is downloaded, you can immediately execute it:

# perl mysqltuner.pl

>> MySQLTuner 0.9.9 - 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.45-log

[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 201M (Tables: 54)

[--] Data in InnoDB tables: 1017M (Tables: 55)

[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------

[--] Up for: 5m 1s (35 q [0.116 qps], 32 conn, TX: 24K, RX: 3K)

[--] Reads / Writes: 100% / 0%

[--] Total buffers: 757.0M global + 272.3M per thread (1024 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 273.0G (13983% of installed RAM)

[OK] Slow queries: 0% (0/35)

[OK] Highest usage of available connections: 0% (1/1024)

[!!] Key buffer size / total MyISAM indexes: 8B/171.6M

[!!] Key buffer hit rate: 0.0% (5 cached / 5 reads)

[!!] Query cache efficiency: 0.0% (0 cached / 15 selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)

[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)

[OK] Thread cache hit rate: 96% (1 created / 32 connections)

[OK] Table cache hit rate: 95% (126 open / 132 opened)

[OK] Open file limit used: 13% (143/1K)

[OK] Table locks acquired immediately: 100% (13 immediate / 13 locks)

[!!] Connections aborted: 53%

[!!] InnoDB data size / buffer pool: 1017.6M/512.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

MySQL started within last 24 hours - recommendations may be inaccurate

Your applications are not closing MySQL connections properly

Variables to adjust:

*** MySQL\'s maximum memory usage exceeds your installed memory ***

*** Add more RAM before increasing any MySQL buffer variables ***

key_buffer_size (> 171.6M)

query_cache_limit (> 1M, or use smaller result sets)

innodb_buffer_pool_size (>= 1017M)

You will need to provide credentials to log into the database, as root, and then MySQLTuner will analyse the server and provide information regarding performance. Based on its analysis, MySQL Tuner will make some recommendations.

In the sample above, you can see that MySQL is being seriously overextended, based on the amount of physical RAM available with the current buffer settings. With a maximum of 1,024 threads, each consuming 272MB, and then a global amount of allocated RAM being 757MB, MySQL is being told that it is allowed to consume 273GB RAM at peak connections -- all on a system with 2GB of physical RAM.

Obviously this would need to be adjusted or, under high load, the rest of the system would suffer and/or MySQL simply wouldn't be able to accommodate the requests. In this case, it would be prudent to decrease the number of maximum threads and perhaps decrease the values of the various buffers (sort_buffer_size, read_buffer_size, and read_rnd_buffer_size; in the above example, the first two buffer sizes were set to 128MB each with the last being 64MB).

Using MySQLTuner will help you find issues that need correcting if performance is to be improved with your MySQL install. The above example is quite severe, but serves to illustrate the potential consequences of a badly configured MySQL server.