Tuning MySQL Performance with MySQLTuner

Version 1.0
Author: Falko Timme

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 <[email protected]>
>>  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

MySQLTuner: http://rackerhacker.com/mysqltuner
MySQL: http://www.mysql.com

view as pdf | print

Share this page:

13 Comment(s)

Add comment

Comments

By:

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!

By: Rekha

HI,

Nice tutorial and good script for tuning mysql performance.

Good work Done.

By: Twitter

I updated code like this:

# cd /opt

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

# chmod +x mysqltuner.pl

By:

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.

By: colonel

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

By: DBA

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

By: Anonymous

[email protected] [~]# ./mysqltuner.pl

./mysqltuner.pl: line 4: syntax error near unexpected token `newline'

./mysqltuner.pl: line 4: `<!DOCTYPE html>'

By: livingdead

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

By: Anonymous

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.

By: Tom Dings

I built some additions so the script is directly optimizing my servers and restarts them once per x hours. Currently I am using this feature. It went weird. Most providers stopped providing Facebook (closed) Group to Social Rss Feeds so my sites were loaded with many people and even more requests.Started to worry .. MySQL maybe MariaDB. I knew this script and did something extra and now all 64 MySQL servers are running smoothly. No complaints.

By: Scott Johnston

I running the MYSQL Tuner, and the recomendation never seem to change.

I updating the MY.CNF and restarting but it display i the recomendation itkeeps comming back telling to turn on SLow Query Logging.

Here is My.CNF file....

[mysqld]

local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log-slow-queries=1

log-slow-queries=/var/lib/mysql/slow.log

long_query_time=10

query_cache_size = 268435456

query_cache_type=1

query_cache_limit=1048576

#long_query_time=1

#log-slow-queries=/var/log/mysql/log-slow-queries.log

#log-queries-not-using-indexes

By: Tancredo Lester Montilla Duran

Thanks for this recomendation, Really I need this post. Good job

By: Yilmaz

Perfect sollution

Log in or Sign up

On this page