MySQL-Tuner ou comment améliorer la configuration de MySQL

MySQL-Tuner est un script écrit en perl qui analyse le fonctionnement et la configuration de votre serveur MySQL. Les recommandations de cet outils permettra d'améliorer sensiblement l'optimisation de votre serveur MySQL.

Pour commencer, nous récupérons le script puis ensuite nous le rendrons exécutable.

cd /root
wget 'https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl' --quiet --no-check-certificate --output-document='/usr/local/bin/mysqltuner'
chmod +x '/usr/local/bin/mysqltuner'

Pour exécuter ce script il suffit de lancer la commande suivante :

mysqltuner

Important : Veuillez attendre au moins 48h après l'installation de mysqltuner. Ceci permettra au script d'avoir suffisamment de données pour que les recommandations puissent être pertinente.

Voici un exemple de recommandations données lors de l'exécution de mysqltuner.

 >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.25-0+deb7u1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM +PINBA
[--] Data in PINBA tables: 0B (Tables: 34)
[--] Data in MyISAM tables: 229M (Tables: 1460)
[--] Data in InnoDB tables: 440M (Tables: 114)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 82

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 32d 0h 4m 41s (31M q [11.422 qps], 876K conn, TX: 67B, RX: 4B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 4.2G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 4.6G (28% of installed RAM)
[OK] Slow queries: 0% (411/31M)
[OK] Highest usage of available connections: 35% (54/151)
[OK] Key buffer size / total MyISAM indexes: 2.0G/258.9M
[OK] Key buffer hit rate: 99.9% (550M cached / 477K reads)
[OK] Query cache efficiency: 78.0% (16M cached / 21M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 266K sorts)
[!!] Temporary tables created on disk: 74% (977K on disk / 1M total)
[OK] Thread cache hit rate: 95% (41K created / 876K connections)
[!!] Table cache hit rate: 0% (400 open / 163K opened)
[OK] Open file limit used: 48% (495/1K)
[OK] Table locks acquired immediately: 98% (8M immediate / 8M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/440.1M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 440M)