Je suis amené à réaliser régulièrement des audits de serveurs MySQL.Voici le premier volet d’une série d’articles où je vais essayer de vous donner quelques points clés pour mieux comprendre le fonctionnement de MySQL.

La configuration du serveur est un des points que je regarde, et l’une des erreurs les plus courantes concerne le paramétrage des options tmp_table_size et max_heap_table_size.

tmp_table_size permet de fixer la taille maximale au-delà de laquelle les tables temporaires en mémoire créées par MySQL (avec le moteur Memory) se transforment en table MyISAM en migrant les données sur le disque. max_heap_table_size permet de fixer la taille maximale des tables avec pour moteur de stockage Memory (Heap est l’ancien nom de Memory).

Extraits du livre « Audit et optimisation – MySQL 5; Bonnes pratiques pour l’administrateur »

Ce qui est important de savoir c’est que la limite des tables temporaires en mémoire créées par MySQL est la plus petite de ces deux valeurs. Ce que je vois souvent dans les fichier de configurations, c’est un tmp_table_size à 64 Mo (par exemple) et un max_heap_table_size qui lui n’y figure pas et qui par conséquent prend ça valeur par défaut, c’est à dire 16Mo. Votre table temporaire créée par le serveur (lors de votre GROUP BY par exemple) sera sur disque dès 16Mo de données et non 64 Mo comme vous le pensiez.

Alors pourquoi faut il limiter le nombre de tables temporaires créées sur le disque ? Tous simplement car sur disque, le temps d’exécution de la requête sera beaucoup, beaucoup plus long, de l’ordre de fois 10 voir fois 100 !

Lorsque vous renseignez l’option tmp_table_size, pensez également à donner la même valeur à l’option max_heap_table_size.

viaAudit MySQL – tmp_table_size & max_heap_table_size | dasini.net – Journal d’un expert MySQL.