Accueil > Docs > mysqltuner sur geodiversite.net

mysqltuner sur geodiversite.net

lundi 14 mai 2012, par b_b

Depuis le 10/05/2012 le serveur qui héberge le site subissait des grosses montées en charge notamment au niveau du serveur mysql. Le nombre de slow queries ne cessait de monter comme on peut le voir sur les graphes qui suivent.

Afin de tenter de comprendre d’où ce problème peut venir j’ai d’abord fouillé dans l’historique des mises à jour de la machine :

zgrep -h " installed " /var/log/dpkg.log* | sort | uniq | less

Voilà donc la liste des paquets qui ont été installés sur la machine le 10 mai.

2012-05-10 12:37:07 status installed libtasn1-3 2.4-1ubuntu0.1
2012-05-10 12:37:07 status installed php5-common 5.3.2-1ubuntu4.15
2012-05-10 12:37:10 status installed libapache2-mod-php5 5.3.2-1ubuntu4.15
2012-05-10 12:37:10 status installed php5 5.3.2-1ubuntu4.15
2012-05-10 12:37:10 status installed php5-curl 5.3.2-1ubuntu4.15
2012-05-10 12:37:10 status installed php5-gd 5.3.2-1ubuntu4.15
2012-05-10 12:37:10 status installed php5-mysql 5.3.2-1ubuntu4.15
2012-05-10 12:37:11 status installed libc-bin 2.11.1-0ubuntu7.10

Utilisation de mysqltuner

MySQLTuner est un script en perl qui analyse votre serveur msqyl et vous affiche ensuite des recommandations afin de l’optimiser.

 >>  MySQLTuner 1.2.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.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-0ubuntu0.10.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 14M (Tables: 143)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 4

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 12m 29s (14M q [81.595 qps], 39K conn, TX: 11B, RX: 2B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (23% of installed RAM)
[OK] Slow queries: 4% (609K/14M)
[!!] Highest connection usage: 100%  (151/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/11.5M
[OK] Key buffer hit rate: 100.0% (30B cached / 2M reads)
[OK] Query cache efficiency: 73.2% (10M cached / 13M selects)
[!!] Query cache prunes per day: 1556312
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 304K sorts)
[!!] Joins performed without indexes: 883
[!!] Temporary tables created on disk: 49% (158K on disk / 318K total)
[OK] Thread cache hit rate: 97% (1K created / 39K connections)
[!!] Table cache hit rate: 0% (316 open / 603K opened)
[OK] Open file limit used: 43% (446/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    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 (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 64)

Variables to adjust : wait_timeout (< 28800)
wait_timeout = 200 le 12/05/2012 à 16h26 / le load descend direct

General recommendations : Run OPTIMIZE TABLE to defragment tables for better performance
To defragment your database use the following query syntax :
mysqlcheck -or -A -u USERNAME —password=PASSWORD

OR

use https://github.com/pdufault/mysqlfragfinder

Maj du 30/08/2012

Après avoir observé les logs d’accès du serveur, il semble que le load monte en flèche lors du passage du bot d’indexation de Bing. En attendant mieux, la solution la plus simple était d’installer l’écran de sécurité de SPIP sur chaque site hébergé par la machine.

L’écran de sécurité a été déployé entre 14h30 et 15h00, on voit bien le nombre de slow queries et le load de la machine chuter brusquement à partir de ce moment :)

Maj du 16/03/2013

Ce chacal de bingbot envoyait toujours un paquet de requêtes vers le serveur (jusqu’à 7 requêtes/seconde) pour indexer le contenu du site. Même si l’écran de sécurité fait bien son boulot, le serveur MySQL montait en charge avant qu’on envoie bouler bingbot à coup de 503. Le problème avec bingbot est bien connu, et on peut trouver pas mal de pages web qui abordent le sujet :

La solution proposée par Bing est d’ajouter une règle Crawl-delay au fichier robots.txt du site :

User-agent: bingbot
Crawl-delay: 1

Après avoir ajouté cette règle au robots.txt sur lestaxinomes.org, j’ai pu observer le retour au calme dans les graphes Munin de la machine qui héberge le site. Et en analysant les logs Apache, je constate que bingbot semble bien respecter cette règle de Crawl-delay car il n’envoie plus qu’un seul hit par seconde maximum...

MAJ du 30/06/2015

Quelques liens à lire pour continuer l’optimisation :

http://www.debianhelp.co.uk/mysqlperformance.htm

http://club.orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/

http://www.mysqlplus.fr/tag/wait_timeout/

http://www.rackspace.com/knowledge_center/article/how-to-change-the-mysql-timeout-on-a-server

Liens en vrac

Investigating Sort aborted issues : http://sureshkuna.blogspot.fr/2011/06/investigating-sort-aborted-issues.html

Tuning / Optimizing my.cnf file for MySQL : http://www.linuxweblog.com/tune-my.cnf

https://github.com/pdufault/mysqlfragfinder

http://serverfault.com/questions/257366/how-to-determine-whats-causing-my-servers-load-average-to-jump-to-90