Debian Tutorials

Debian Tutorials

Step by step tutorials showing you how to install and configure various applications and services on Debian based Linux distros.

May 2024


Tuning MySql with MySqlTuner to increase efficiency and performance

Ástþór IPÁstþór IP

mysqltuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance.

1. Install MySqlTuner

apt-get install mysqltuner

2. Run MySqlTuner


Input your MySql administrative login and password

Please enter your MySQL administrative login:
Please enter your MySQL administrative password:

Here are sample results:

-------- General Statistics --------------------------------------------------
[!!] There is a new version of MySQLTuner available
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2-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: 98M (Tables: 81)
[!!] InnoDB is enabled but isn't being used
-------- Performance Metrics -------------------------------------------------
[--] Up for: 56d 10h 58m 7s (137M q [28.243 qps], 3M conn, TX: 2B, RX: 1B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 2.6M per thread and 106.0M global
[OK] Maximum possible memory usage: 368.5M (18% of installed RAM)
[OK] Slow queries: 0% (75K/137M)
[!!] Highest connection usage: 100% (101/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/79.3M
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 78.4%
[!!] Query cache prunes per day: 269788
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 99%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 1%
[OK] Open file limit used: 27%
[OK] Table locks acquired immediately: 99%
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Reduce or eliminate persistent connections to reduce connection usage
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
Variables to adjust:
max_connections (> 100)
wait_timeout (< 28800) interactive_timeout (< 28800) query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 200)

3. Adjust your MySql config file (/etc/mysql/my.cnf) according to the recommendations. Don’t increase or decrease the values too much because it may have negative impact on the server. If this is a production server, just make minor changes each time and test again a few hours/days later and adjust the values again if needed. It may take a few days to figure out the best values for your server.

4. Restart MySql after you have made changes to the config file

/etc/init.d/mysql restart

Comments 1
  • Jordah
    Posted on

    Jordah Jordah


    edit /etc/my.cnf

    key_buffer = 32M
    max_allowed_packet = 1M
    max_connections = 200
    table_cache = 1024
    sort_buffer_size = 512K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    net_buffer_length = 8K
    tmp_table_size = 64M
    thread_stack = 128K
    query_cache_size = 32435456