Open Source

Improve MySQL performance with MySQLTuner

Vincent Danen covers the basics of the MySQL Tuner tool, which analyzes your configuration and suggests changes that will boost the performance of your MySQL installation.

MySQL is one of the most popular database packages available for any platform. It makes its mark primarily being the backend 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 optimize 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 Web site 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 analyze 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 1024 threads, each consuming 272 MB, and then a global amount of allocated RAM being 757 MB, MySQL is being told that it is allowed to consume 273 GB 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 64 MB).

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.

Get the PDF version of this tip here.

Delivered each Tuesday, TechRepublic's free Linux and Open Source newsletter provides tips, articles, and other resources to help you hone your Linux skills. Automatically sign up today!

About

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

4 comments
Tony Hopkinson
Tony Hopkinson

to see how it works. I've had a few twiddles with MySQL, but that was more of a suck it and see type of thing.

Photogenic Memory
Photogenic Memory

I'm gonna start learning how to use mySQL. I'm gonna make mistakes, LOL! I hope this help me track down simple goofs. Do you think this would ruin a production server? And how can it have "a last settings" option?

bob
bob

I just downloaded it and ran it per the instructions and it came back with some nice recommendations. I will implement those recs and see how the MySQL performs. Very nice tool!!!

Editor's Picks