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 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 :
- Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
- “PGA memory operation”, “Acknowledge over PGA limit” et ORA-04036 après migration vers 12c (Benjamin VESAN) [Oracle]
- Nouveautés MySQL 8.0 : Configuration automatique de variables avec innodb_dedicated_server (Capdata team) [MySQL]
- Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]
- Question bête: Qu’est-ce qu’une grosse table ? (Benjamin VESAN) [SQL Server]