Tuning MySQL Performance with MySQLTuner

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Tue, 2008-09-02 11:00. :: MySQL

Tuning MySQL Performance with MySQLTuner

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 08/28/2008

MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

This document comes without warranty of any kind! I do not issue any guarantee that this will work for you!

 

1 Using MySQLTuner

You can download the MySQLTuner script as follows:

wget http://mysqltuner.com/mysqltuner.pl

In order to run it, we must make it executable:

chmod +x mysqltuner.pl

Afterwards, we can run it. You need your MySQL root password for it:

./mysqltuner.pl

server1:~# ./mysqltuner.pl

 >>  MySQLTuner 0.9.8 - 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: <-- yourrootsqlpassword

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software!  Upgrade soon!
[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: 301M (Tables: 2074)
[--] Data in HEAP tables: 379K (Tables: 9)
[!!] InnoDB is enabled but isn't being used
[!!] ISAM is enabled but isn't being used
[!!] Total fragmented tables: 215

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (20% of installed RAM)
[OK] Slow queries: 0% (17/1B)
[OK] Highest usage of available connections: 32% (32/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M
[OK] Key buffer hit rate: 99.9%
[OK] Query cache efficiency: 99.9%
[!!] Query cache prunes per day: 47549
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 28%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%
[!!] Connections aborted: 20%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Add skip-isam to MySQL configuration to disable ISAM
    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
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)

server1:~#

You should carefully read the output, especially the recommendations at the end. It shows exactly which variables you should adjust in the [mysqld] section of your my.cnf (on Debian and Ubuntu the full path is /etc/mysql/my.cnf). Whenever you change your my.cnf, make sure that you restart MySQL. You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance. This way, you can optimize MySQL step by step.

 

2 Links


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Fri, 2014-01-03 21:29.

if u get this error:

./mysqltuner.pl: line 4: syntax error near unexpected token `newline'
./mysqltuner.pl: line 4: `<!DOCTYPE html>'

try to download https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl instead.

 should work after. hope this helps out.

Submitted by livingdead (not registered) on Sat, 2013-04-06 15:20.

Hello,

 Just a silly question.

 I never found the output of mysqltuner very clear.

 When it output :

query_cache_limit (> 16M, or use smaller result sets)
    query_cache_size (> 128M)
    join_buffer_size (> 16.0M, or always use indexes with joins)
    table_cache (> 3000)
    innodb_buffer_pool_size (>= 13M)

 

does it mean that query_cache_limit must be set over 16M or does it mean that 16M is to high for query_cache_limit ???

 

Thank in advance for clarification.

 

Regards

 

Submitted by Anonymous (not registered) on Thu, 2012-12-20 21:03.
root@server [~]# ./mysqltuner.pl
./mysqltuner.pl: line 4: syntax error near unexpected token `newline'
./mysqltuner.pl: line 4: `<!DOCTYPE html>'
Submitted by DBA (not registered) on Fri, 2009-10-02 00:20.

Thanks for the walkthrough! mysqltuner is really great at giving a quick feedback on configuration issues.

Next, I'd recommend that you check your running server for bad sql. You can use Jet Profiler for MySQL

Submitted by colonel (not registered) on Tue, 2009-03-17 15:38.
Good health, I have this errors, what can I do:
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
Argument "" isn't numeric in numeric gt (>) at ./mysqltuner.pl line 403, <>
        line 2 (#1)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

[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: 1M (Tables: 304)
[!!] Total fragmented tables: 3
Illegal division by zero at ./mysqltuner.pl line 529, <> line 2 (#2)
    (F) You tried to divide a number by 0.  Either something was wrong in
    your logic, or you need to put a conditional in to guard against
    meaningless input.

Uncaught exception from user code:
        Illegal division by zero at ./mysqltuner.pl line 529, <> line 2.
 at ./mysqltuner.pl line 529
        main::calculations() called at ./mysqltuner.pl line 878

Submitted by kylehase (registered user) on Fri, 2009-02-06 09:30.

The script developer owns the domain "mysqltuner.pl" and wget assumes http by default so the wget line can be simplified to:

wget mysqltuner.pl

Anyone should be able to remember that.

Submitted by BlueC (registered user) on Thu, 2008-09-11 10:43.

Thanks for this. This is a really useful tool for helping to optimize MySQL performance.

Understanding the various my.cnf variables and how they affect performance can seem really complicated but this tool takes some of the pain away and makes it easier to understand the effects of each variable.

It is especially useful to be able to see the global memory usage,  memory usage per thread and the maximum possible memory usage - that is really valuable information that is otherwise complex to calculate!

Submitted by Rekha (not registered) on Mon, 2012-07-02 11:56.
HI,

Nice tutorial and good script for tuning mysql performance.

Good work Done.
Submitted by Twitter (not registered) on Wed, 2014-01-15 09:08.

I updated code like this:

# cd /opt

# wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

# chmod +x mysqltuner.pl