0

MySQL et les tables temporaires internes

twitterlinkedinmail

MySQL créé des tables temporaires automatiquement lors de certaines sélections de données, en particulier lors de l’utilisation des clauses ORDER BY ou GROUP BY.

Le détail des cas de création de tables temporaires est disponible dans la documentation MySQL :

Ces tables temporaires sont créées en mémoire par défaut, dans la limite de la taille définie par le paramètre tmp_table_size [taille par défaut dépendante de l’OS]. Au delà de cette limite, la table temporaire est créée sur disque. Attention toutefois, si la valeur du paramètre max_heap_table_size [16M par défaut] est inférieure à celle de tmp_table_size, alors les tables temporaires en mémoire seront limitées à max_heap_table_size. Pour simplifier les choses, positionnez les deux paramètres à la même valeur.

Il est évidemment plus performant de provoquer le maximum de création en mémoire en positionnant la valeur du paramètre tmp_table_size à une taille relativement importante (16M à 32M).
Attention toutefois au débordement mémoire, en effet, cette taille peut potentiellement être consommée par chaque thread connecté !

La colonne Extra de la commande EXPLAIN pour une requête de type SELECT permet d’obtenir une information sur la création d’une éventuelle table temporaire.
Dans ce cas (utilisation d’un GROUP BY), une table temporaire sera créée :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_evenement
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14988
Extra: Using temporary; Using filesort

En revanche, cette commande ne permet pas de savoir si la table temporaire est créée sur disque ou en mémoire, pour cela, il faudra s’appuyer sur les compteurs MySQL évoqués dans la suite de l’article.

Le graphe suivant présente un état des tables temporaires créées en mémoire et sur disque pour notre instance de production  :

Tables temporaires

Tables temporaires MySQL

Les compteurs MySQL utilisés pour relever ces valeurs sont Created_tmp_table et Created_tmp_disk_tables. Il n’est malheureusement pas possible de connaitre la taille consommée en mémoire ou sur disque par ces tables temporaires.

Ici, la quasi totalité des tables temporaires est créée sur disque. La taille maximum d’une table temporaire en mémoire est pourtant positionnée à une valeur relativement importante (48M).

Le fait d’augmenter la valeur du tmp_table_size ne changera rien dans ce cas ! Explications :

MySQL utilise le moteur de stockage MEMORY pour la création des tables temporaires internes. Ce moteur se comporte quasiment comme MyISAM, cependant, une  contrainte  de ce moteur vient perturber le fonctionnement des tables temporaires internes : Il n’est pas possible d’utiliser des colonnes de type BLOB ou TEXT pour les tables MEMORY.

De ce fait, si MySQL doit créer une table temporaire pour un ordre SELECT contenant ces types de colonne, elle sera automatiquement créée sur disque.

Deux possibilités s’offrent à vous dans ce cas :

  • Limiter l’utilisation des colonnes de type BLOB ou TEXT
  • Monter un système de fichier mémoire pour le stockage des tables temporaires interne MySQL

[ Cédric P ]

Continuez votre lecture sur le blog :

twitterlinkedinmail

Benjamin VESAN

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.