MySQL vs MariaDB : tables temporaires sur disque — comment les arrêter vraiment

Que signifient vraiment les « tables temporaires sur disque » (et pourquoi vous devez vous en soucier)

Quand MySQL ou MariaDB ne peut pas exécuter une requête entièrement en mémoire, il crée une table temporaire interne. Ce n’est pas la même chose qu’une CREATE TEMPORARY TABLE créée par l’utilisateur. Les tables temporaires internes sont des calepins créés par le moteur utilisés pour :

• GROUP BY et DISTINCT sans index utilisable
• ORDER BY qui ne peut pas être satisfait par un index (souvent avec LIMIT absent ou inefficace)
• tables dérivées / sous-requêtes / vues que l’optimiseur décide de matérialiser
• certaines fonctions window et opérations d’ensemble complexes

Le point douloureux : les tables temporaires internes peuvent vivre en mémoire ou sur disque. Dans les anciens modèles mentaux, « table mémoire » signifiait le moteur MEMORY/HEAP, et « table disque » signifiait MyISAM. MySQL moderne complique cela : les tables temporaires internes peuvent utiliser InnoDB et son espace de tables temporaires. MariaDB a ses propres subtilités. Votre métrique de monitoring — souvent Created_tmp_disk_tables — est un indicateur retardé qui dit : « nous avons débordé, nous avons payé l’I/O, et maintenant on fait comme si tout était normal. »

Si vous êtes sur un NVMe local rapide avec un cache généreux, vous pouvez survivre. Si vous êtes sur un stockage en réseau ou un volume cloud bruyant, les tables temporaires sur disque sont essentiellement une taxe de performance que vous payez à chaque pic de trafic.

Blague n°1 : une table temporaire sur disque, c’est comme faire ses impôts sur un tapis roulant — techniquement possible, émotionnellement coûteux.

MySQL vs MariaDB : où le comportement des tables temporaires diffère en pratique

Les gens aiment considérer MariaDB comme « MySQL avec des fonctionnalités en plus ». Opérationnellement, c’est souvent ce qui vous réveille à 2h du matin. Le comportement des tables temporaires n’est pas identique, et les différences comptent quand vous essayez d’arrêter les débordements sur disque « pour de vrai ».

1) Les choix d’implémentation des tables temporaires internes ne racontent pas la même histoire

MySQL 8.0 utilise largement une implémentation interne de tables temporaires, et quand elles débordent, elles atterrissent souvent dans l’espace de tables temporaires InnoDB (toujours « disque »). Vous verrez des artefacts dans l’I/O temporaire d’InnoDB et parfois dans la croissance de ibtmp1.

MariaDB (10.3+ en particulier) a un optimiseur et des internals de tables temporaires différents, et peut s’appuyer sur des comportements Aria/MyISAM selon la configuration et la compilation. La terminologie que vous voyez dans les compteurs d’état peut sembler familière tout en masquant des mécanismes différents.

2) Les compteurs d’état se ressemblent, mais ne racontent pas toute la vérité

Created_tmp_disk_tables existe dans les deux, mais ce que « disque » signifie peut varier selon la version et le moteur interne. « Disque » peut vouloir dire :

• un fichier sous tmpdir
• des pages dans un espace de tables temporaires InnoDB interne
• une table temporaire qui a démarré en mémoire mais qui a été convertie après avoir atteint une limite

3) Les limites et seuils diffèrent dans des cas limites

Les deux jouent la danse entre tmp_table_size et max_heap_table_size pour décider si une table temporaire en mémoire peut exister. Mais les déclencheurs de conversion peuvent être plus nuancés (types de données, colonnes blob/text, format de ligne, et opérations spécifiques). Si vous ne touchez qu’à ces deux variables et que vous vous arrêtez là, vous réduirez certains débordements et en subirez d’autres.

4) Les changements entre versions sont plus importants que les stéréotypes marque-à-marque

MySQL 5.7 vs 8.0 peut se comporter de façon plus différente que « MySQL vs MariaDB » à un niveau élevé. Idem pour MariaDB 10.1 vs 10.6. Tout réglage sérieux commence par : « Quelle version exacte tourne, et quelle charge provoque des débordements ? »

Guide de diagnostic rapide

Si vous avez 20 minutes avant la prochaine revue d’incident, faites ceci dans l’ordre. C’est orienté vers l’isolation rapide : est-ce la forme de la requête, les seuils mémoire, ou le débit/latence du stockage ?

Première étape : prouvez que c’est de l’I/O de table temporaire (pas du bruit disque aléatoire)

1. Vérifiez les compteurs globaux de tables temporaires et leur taux de changement.
2. Vérifiez si les requêtes lentes correspondent à Sort_merge_passes, Created_tmp_disk_tables, et des lectures handler en hausse.
3. Vérifiez la latence I/O au niveau du système de fichiers sur le volume qui héberge tmpdir (ou l’espace de tables temporaires InnoDB).

Deuxième étape : identifiez les 1–3 patrons de requêtes principaux qui créent des débordements

1. Récupérez les requêtes lentes principales pendant la fenêtre d’incident.
2. Lancez EXPLAIN et cherchez « Using temporary » / « Using filesort » / matérialisation.
3. Cherchez GROUP BY et ORDER BY sans index de support, plus de grands ensembles de résultats intermédiaires.

Troisième étape : décidez corriger le SQL, ajuster les seuils, ou changer la surface de déversement

1. Si une ou deux requêtes sont responsables : corrigez le SQL/indexation d’abord. C’est le ROI le plus élevé et le plus stable.
2. Si beaucoup de requêtes débordent à cause de limites faibles : augmentez les limites des tables temporaires avec prudence, en tenant compte de la concurrence et de la pression mémoire.
3. Si les débordements sont inévitables : déplacez tmpdir vers un SSD/NVMe local rapide et assurez-vous que le système de fichiers et les options de montage ne vous sabotent pas.

Faits et historique qui expliquent les bizarreries d’aujourd’hui

1. MySQL utilisait historiquement MEMORY pour les tables temporaires en mémoire et MyISAM pour celles sur disque ; ce modèle mental hante encore les guides de tuning.
2. InnoDB est devenu le moteur par défaut dans MySQL 5.5, changeant les schémas d’I/O en production et rendant les débordements de tables temporaires plus visibles sur des systèmes chargés.
3. MySQL 8.0 a beaucoup changé l’optimiseur, y compris la gestion des tables dérivées et les détails d’implémentation des tables temporaires internes ; les mises à jour peuvent modifier les taux de débordement sans changer le SQL.
4. MariaDB a divergé significativement après le fork ; ce n’est pas juste « MySQL drop-in », surtout autour des fonctionnalités d’optimiseur et des moteurs de stockage comme Aria.
5. Les flags EXPLAIN « Using temporary; Using filesort » sont anciens et restent utiles, mais ils ne garantissent pas d’I/O disque — seulement qu’une structure temporaire existe.
6. tmpdir est une mine de performance depuis des décennies car il pointe par défaut vers des chemins temporaires système qui peuvent se trouver sur des volumes root lents.
7. Les « tables temporaires sur disque » sont souvent le symptôme d’index manquants, pas d’une RAM insuffisante ; ajouter de la mémoire peut masquer un bug de requête jusqu’à la prochaine montée en charge.
8. La migration vers le cloud a empiré la situation : disques éphémères, volumes réseau et voisins bruyants transforment des débordements occasionnels en falaises de latence.

Mécanique : ce qui pousse les tables temporaires sur disque

Les trois grands déclencheurs

1) Limites de taille. Le serveur estime ou constate que la table temporaire dépasse les limites autorisées en mémoire et la convertit en disque. Les leviers classiques sont :

• tmp_table_size
• max_heap_table_size

La capacité effective d’une table temporaire en mémoire est typiquement le minimum de ces deux valeurs pour les tables MEMORY, mais les tables temporaires internes modernes ne sont pas toujours MEMORY. Pourtant, ces variables restent une partie de l’arbre de décision.

2) Types de colonnes et format de ligne. Si la table temporaire doit stocker des colonnes BLOB/TEXT, ou d’autres structures mal prises en charge en mémoire, vous pouvez déborder même avec des limites généreuses. C’est une des raisons pour lesquelles « augmentez tmp_table_size » déçoit souvent.

3) Forme de la requête qui crée d’énormes résultats intermédiaires. Même si le résultat final est petit, les résultats intermédiaires peuvent être massifs. Coupable classique : GROUP BY sur une colonne à faible cardinalité tout en joignant une table large sans prédicats sélectifs.

Pourquoi « arrêter les tables temporaires sur disque » n’est pas un simple interrupteur

Les tables temporaires existent parce que l’optimiseur a besoin d’un espace de travail. Vous pouvez réduire les débordements sur disque, mais vous ne pouvez pas — et ne devriez pas — essayer d’éliminer globalement les tables temporaires. L’objectif est :

• garder les structures temporaires petites
• les maintenir en mémoire quand c’est sûr
• éviter de les créer en corrigeant le SQL et les index
• quand elles vont sur disque, rendre le disque rapide et prévisible

Il y a aussi le piège de la concurrence : augmenter les limites temporaires peut aider une requête unique, mais nuire au système sous charge quand 200 sessions allouent chacune des structures temporaires plus grosses. Vous ne voulez pas gagner un benchmark et perdre la production.

Tâches pratiques (commandes, sorties, décisions)

Voici les tâches que vous lancez réellement lors d’une chasse aux performances. Chaque élément inclut : une commande, une sortie réaliste, ce que cela signifie, et la décision que vous prenez.

Task 1: Confirm version and flavor (you can’t tune a rumor)

cr0x@server:~$ mysql -e "SELECT VERSION() AS version, @@version_comment AS comment\G"
*************************** 1. row ***************************
version: 8.0.36
comment: MySQL Community Server - GPL

Ce que cela signifie : Il s’agit d’un MySQL 8.0.x, donc le comportement des tables temporaires internes et l’espace de tables temporaires InnoDB sont en jeu.

Décision : Utiliser l’instrumentation MySQL 8.0 (Performance Schema, vues sys). Ne pas appliquer des variables propres à MariaDB ou des folklore de 5.6.

Task 2: Check temp table creation counters and their ratio

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 184229   |
| Created_tmp_files       | 912      |
| Created_tmp_tables      | 2441102  |
+-------------------------+----------+

Ce que cela signifie : Des tables temporaires sur disque existent et ne sont pas rares. Le ratio compte : 184k disque sur 2.4M de tables temporaires ≈ 7,5 %.

Décision : Si le taux augmente pendant les incidents, vous déversez sous charge. Passez au monitoring basé sur le taux (tâche suivante) et identifiez les coupables.

Task 3: Measure disk temp table rate (not just totals)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';" && sleep 10 && mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';"
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 184229 |
+-------------------------+--------+
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 184411 |
+-------------------------+--------+

Ce que cela signifie : 182 tables temporaires sur disque en 10 secondes : ~18/s. C’est suffisant pour mettre la pression sur le stockage si elles ne sont pas triviales.

Décision : Si la latence stockage se corrèle, traitez cela comme le suspect principal, pas du bruit de fond.

Task 4: Check the temp table memory thresholds

cr0x@server:~$ mysql -e "SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';"
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| tmp_table_size| 16777216 |
+---------------+----------+
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_heap_table_size| 16777216 |
+--------------------+----------+

Ce que cela signifie : Les deux sont à 16MB. C’est conservateur, et cela forcera des débordements sur disque pour des opérations GROUP BY/ORDER BY de taille modérée.

Décision : Envisagez de les augmenter, mais uniquement après avoir identifié si les débordements sont dus à la taille vs les types de données vs la forme des requêtes. Ne mettez pas aveuglément 1G et ne vous félicitez pas.

Task 5: Check where temp files go (tmpdir)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'tmpdir';"
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| tmpdir        | /tmp       |
+---------------+------------+

Ce que cela signifie : Les déversements temporaires atterrissent dans /tmp. Sur beaucoup de serveurs c’est sur le système de fichiers root, souvent le disque le moins doté.

Décision : Si vous avez du NVMe local, déplacez tmpdir vers un point de montage dédié avec des performances et une capacité prévisibles.

Task 6: Verify /tmp filesystem and mount options

cr0x@server:~$ df -hT /tmp && mount | grep ' /tmp '
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/nvme0n1p2 ext4  80G   72G  4.5G  95% /
tmpfs          tmpfs 16G   1.2G  15G   8% /tmp
tmpfs on /tmp type tmpfs (rw,nosuid,nodev,relatime,size=16384m)

Ce que cela signifie : Ici /tmp est en fait tmpfs (sauvegardé en RAM). C’est rapide, mais c’est aussi une pression mémoire prête à arriver.

Décision : Si vous voyez un risque d’OOM ou d’échange, arrêtez d’utiliser tmpfs pour les temporaires DB. Préférez un SSD local rapide avec quotas/monitoring, sauf si vous avez une marge mémoire ferme et des limites strictes.

Task 7: Check InnoDB temp tablespace behavior (MySQL 8.0 typical)

cr0x@server:~$ mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_temp_data_file_path'; SHOW GLOBAL STATUS LIKE 'Innodb_temp%';"
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+--------------------------+------------------------+
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Innodb_temp_tablespaces    | 0       |
| Innodb_temp_tables         | 19231   |
| Innodb_temp_table_pages    | 8112    |
+----------------------------+---------+

Ce que cela signifie : Une activité temporaire InnoDB est présente. Les débordements peuvent ne pas apparaître comme des fichiers dans tmpdir comme les anciens guides le suggèrent.

Décision : Surveillez l’I/O sur le volume datadir aussi, pas seulement tmpdir. Si ibtmp1 grossit ou si l’I/O augmente, vous avez une deuxième surface de déversement.

Task 8: Find “Using temporary” and “Using filesort” in real queries (quick sampling)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT customer_id, COUNT(*) FROM orders WHERE created_at BETWEEN ? AND ? GROUP BY customer_id ORDER BY COUNT(*) DESC
COUNT_STAR: 1932
total_sec: 812.4432

Ce que cela signifie : Un agrégat GROUP BY + ORDER BY est un candidat principal pour les tables temporaires et les débordements de tri.

Décision : Récupérez ce patron exact de requête, lancez EXPLAIN et vérifiez l’indexation. Ne touchez pas à la mémoire globale tant que vous ne savez pas si c’est juste un index manquant.

Task 9: Explain the suspect query and read the “Extra” column like a detective

cr0x@server:~$ mysql -e "EXPLAIN SELECT customer_id, COUNT(*) FROM orders WHERE created_at BETWEEN '2025-12-01' AND '2025-12-31' GROUP BY customer_id ORDER BY COUNT(*) DESC\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: range
possible_keys: idx_created_at
          key: idx_created_at
      key_len: 8
          ref: NULL
         rows: 2100000
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort

Ce que cela signifie : Le moteur s’attend à scanner beaucoup de lignes puis à construire une table temporaire et à la trier. Ça peut déborder.

Décision : Envisagez un index composite pour supporter le groupement, ou modifiez la requête pour réduire les lignes scannées (réduire la plage temporelle, pré-agréger, table de synthèse). Augmenter la taille des temporaires peut aider mais ne corrige pas le scan.

Task 10: Validate whether the temp spills correlate with storage latency

cr0x@server:~$ iostat -x 1 5
Linux 6.2.0-39-generic (db01) 	12/31/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    3.91   22.14    0.00   55.73

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   980.0  4200.0 49200.0  18.30   0.45  68.0

Ce que cela signifie : %iowait et le await du device sont élevés pendant l’événement. Ça sent la pression d’écriture induite par les débordements.

Décision : Si la couche stockage est le goulot, réduire les débordements ou les déplacer vers un média plus rapide est urgent. Sinon vous « optimiserez les requêtes » et perdrez quand même face aux pics de latence I/O.

Task 11: Check free space and inode pressure where temp files live

cr0x@server:~$ df -h /var/lib/mysql /tmp && df -i /var/lib/mysql /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p3  500G  410G   65G  87% /var/lib/mysql
tmpfs            16G  1.2G   15G   8% /tmp
Filesystem       Inodes   IUsed    IFree IUse% Mounted on
/dev/nvme0n1p3 32768000 812344 31955656    3% /var/lib/mysql
tmpfs           4194304    412  4193892    1% /tmp

Ce que cela signifie : Le datadir a de la marge mais commence à être rempli. Temp est en tmpfs donc les inodes ne sont pas le problème ici.

Décision : Si les tables temporaires sur disque atterrissent dans datadir (espace temporaire InnoDB), assurez-vous que le volume datadir a de la capacité. Si /tmp est un vrai système de fichiers et proche de 100 %, corrigez cela avant d’ajuster quoi que ce soit d’autre.

Task 12: Inspect current running statements during a spike

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;"
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                      |
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+
| 112 | app  | 10.0.3.21:51422 | prod | Query   |   18 | Creating sort index          | SELECT ... ORDER BY ...   |
| 145 | app  | 10.0.3.18:49811 | prod | Query   |   22 | Copying to tmp table on disk | SELECT ... GROUP BY ...   |
| 201 | app  | 10.0.3.19:53301 | prod | Query   |   11 | Sending data                 | SELECT ...                |
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+

Ce que cela signifie : Vous avez une évidence vivante : « Copying to tmp table on disk » et « Creating sort index. » C’est un débordement + tri en cours.

Décision : Capturez ces textes de requête (depuis les logs d’application ou Performance Schema), puis reproduisez et corrigez. Si c’est répandu sur de nombreuses sessions, concentrez-vous sur l’indexation et les seuils temporaires.

Task 13: Spot “sort merge passes” indicating sort memory is too small

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 18291 |
+-------------------+-------+
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 262144  |
+------------------+---------+

Ce que cela signifie : Beaucoup de passes de fusion : le sort_buffer est trop petit pour la charge. Cela peut augmenter l’activité disque même sans que les tables temporaires soient le principal moteur.

Décision : Augmentez prudemment sort_buffer_size seulement si vous comprenez la concurrence (c’est par-session). Préférez d’abord les corrections de requête/index ; l’inflation des buffers est un classique pour échanger des pics de latence contre des incidents mémoire.

Task 14: Validate memory headroom before raising per-session buffers

cr0x@server:~$ free -m
               total        used        free      shared  buff/cache   available
Mem:           65536       41220        2180        1320       22135       20240
Swap:           4096        1024        3072

Ce que cela signifie : La mémoire disponible est ~20GB, mais le swap est utilisé. C’est un drapeau jaune : la machine est déjà sous pression mémoire par moments.

Décision : Ne « corrigez » pas les tables temporaires sur disque en ajoutant de gros buffers par thread. Vous déplacerez simplement la douleur de la latence disque aux tempêtes d’échange.

Patrons de requêtes qui fabriquent des tables temporaires sur disque

Patron 1 : GROUP BY sans index de support

Si vous regroupez sur customer_id mais n’avez qu’un index sur created_at, le moteur lit les lignes par plage temporelle puis agrège dans une structure temporaire. Si la cardinalité du groupe est élevée, la table temporaire grossit. Si elle dépasse les seuils mémoire ou contient des types incompatibles, elle déborde.

Que faire : Construisez un index composite qui correspond à votre filtre et à la clé de groupement dans un ordre utile, par exemple (created_at, customer_id) ou (customer_id, created_at) selon la sélectivité et la forme de la requête. Puis vérifiez avec EXPLAIN et en conditions réelles.

Patron 2 : ORDER BY sur une expression (ou une colonne différente du filtre)

ORDER BY COUNT(*) DESC (tri d’agrégat), ORDER BY LOWER(email), ORDER BY DATE(created_at) — ces cas forcent souvent filesort et structures temporaires.

Que faire : Si vous avez besoin d’un ordre, envisagez de pré-calculer les valeurs triables, ou de réécrire pour utiliser des colonnes indexées, ou d’accepter un tri approximatif. Si le métier veut un « top N », assurez-vous que la requête utilise bien LIMIT et peut l’exploiter.

Patron 3 : DISTINCT sur des lignes larges

SELECT DISTINCT * équivaut à demander au serveur de dédupliquer un entrepôt. Vous obtenez une table temporaire, une pression mémoire, et du disque.

Que faire : Sélectionnez uniquement les colonnes nécessaires. Utilisez une clé DISTINCT plus étroite. Ou mieux : corrigez la logique de jointure pour que les doublons ne soient pas créés dès le départ.

Patron 4 : Tables dérivées qui se matérialisent

Les sous-requêtes et vues qui « ont l’air propres » peuvent être matérialisées en tables temporaires, surtout lorsqu’elles incluent agrégation ou tri. MySQL et MariaDB diffèrent sur le moment où elles choisissent la matérialisation, et les mises à jour de version peuvent changer le comportement.

Que faire : Testez avec EXPLAIN et surveillez la matérialisation des tables dérivées. Dans de nombreux cas, réécrire une table dérivée en jointure avec des index appropriés réduit le travail temporaire.

Patron 5 : Joindre de grands ensembles avant de filtrer

Si l’optimiseur choisit un ordre de jointure qui crée un grand résultat intermédiaire puis filtre après, vous verrez les structures temporaires et de tri exploser.

Que faire : Assurez-vous que les prédicats sélectifs sont sargables (utilisables par un index). Ajoutez des index qui supportent la condition de jointure et le filtre. Si nécessaire, utilisez des hints d’optimiseur avec prudence (et documentez-les comme une arme chargée).

Paramètres importants (et paramètres qui vous font perdre du temps)

Les réglages qui influencent réellement les débordements de tables temporaires

tmp_table_size et max_heap_table_size

Ce sont les premiers leviers vers lesquels tout le monde se tourne parce qu’ils sont faciles. Ils fonctionnent aussi — parfois. Les points clés :

• La taille effective d’une table temporaire en mémoire est contrainte par la plus basse de ces deux valeurs (dans les cas classiques).
• Les augmenter augmente l’utilisation mémoire potentielle par session sous concurrence.
• Ils ne corrigent pas les débordements causés par les types de données ou par des plans de requête qui génèrent d’énormes résultats intermédiaires.

Conseil d’opinion : Augmentez-les par étapes mesurées (ex. 16MB → 64MB → 128MB), seulement après avoir identifié les requêtes principales qui débordent. Suivez les taux et le p95 de latence. Si vous passez directement à 512MB, vous créez un incident mémoire que vous n’avez pas encore rencontré.

tmpdir

Cela contrôle où certaines structures temporaires sur disque atterrissent. Si tmpdir est sur un stockage lent, chaque débordement devient une tempête d’I/O.

Conseil d’opinion : Mettez tmpdir sur un SSD/NVMe local rapide avec monitoring et suffisamment d’espace libre. Évitez la racine. Évitez les systèmes de fichiers réseau. Et ne le mettez pas sur tmpfs à moins d’avoir la preuve solide que vous n’allez pas OOM sous la concurrence maximale.

internal_tmp_mem_storage_engine (MySQL)

Dans certaines versions de MySQL, cela influence si les tables temporaires internes en mémoire utilisent MEMORY ou une implémentation TempTable. Cela peut changer les performances et le comportement mémoire, et interagit avec la façon dont BLOB/TEXT sont gérés.

Conseil d’opinion : Ne changez pas cela en premier. C’est un levier pour des charges spécifiques après avoir confirmé le comportement du moteur dans votre version.

Buffers par session : sort_buffer_size, join_buffer_size, read_rnd_buffer_size

Ils peuvent réduire le travail disque pour les tris et jointures, mais ils sont par-session et peuvent gonfler l’utilisation mémoire. Les systèmes de production ne lancent pas une requête à la fois. Ils exécutent 400 requêtes légèrement incorrectes en concurrence.

Conseil d’opinion : Gardez ces valeurs conservatrices. Corrigez le SQL et les index en priorité. Si vous devez régler des buffers, faites-le avec des calculs de concurrence et des tests de charge, pas au feeling.

Paramètres que les gens modifient et qui ne résolvent généralement pas les tables temporaires sur disque

• innodb_buffer_pool_size seul : aide l’I/O général, ne prévient pas que les tables temporaires aient besoin d’espace de travail.
• Paramètres de thread/concurrence : peuvent changer les schémas de pression mais ne résoudront pas pourquoi vous débordez.
• « Rendre le disque plus rapide » comme seul plan : cela masque le SQL cassé jusqu’au prochain palier de croissance des données.

tmpdir, systèmes de fichiers et stockage : rendre le déversement sur disque moins douloureux

Parfois vous ne pouvez pas éviter les débordements. Les requêtes de reporting existent. L’analytics ad-hoc arrive. Quelqu’un lancera le « rollup mensuel » à 9h55 un jour ouvrable en jurant que c’est urgent.

Traitez donc l’I/O de déversement comme une classe de charge :

• Mettez l’I/O temporaire sur un média prévisible (SSD/NVMe local préféré).
• Séparez les domaines de défaillance : ne laissez pas les fichiers temporaires remplir le même système de fichiers que le datadir.
• Surveillez la capacité et la latence du point de montage temporaire explicitement.
• Utilisez des options de système de fichiers qui correspondent à votre appétit pour le risque. Pour des volumes dédiés aux temporaires, vous pouvez accepter moins de durabilité. Mais soyez explicite et documentez-le.

Un agencement tmpdir pratique

Une approche de production courante :

• /var/lib/mysql sur un stockage redondant (ou volume géré avec durabilité)
• /var/lib/mysqltmp sur un disque local rapide, système de fichiers séparé
• tmpdir=/var/lib/mysqltmp dans la configuration

Cela n’empêche pas les débordements, mais cela évite que les déversements temporaires détruisent la mauvaise partie de votre système.

Blague n°2

Si vos tables temporaires vivent sur le système de fichiers root, vous ne gérez pas une base de données ; vous gérez un roman à suspense.

Trois mini-récits d’entreprise depuis les tranchées

Mini-récit n°1 : L’incident causé par une fausse hypothèse

Une entreprise SaaS de taille moyenne a migré d’un ancien MySQL vers une nouvelle build en urgence — fenêtre de patch sécurité, attention des dirigeants, la routine. L’hypothèse de l’équipe était simple : les débordements de tables temporaires apparaissent comme des fichiers dans /tmp, donc ils ont monitoré l’utilisation de /tmp et se sont dits que c’était réglé.

Le lundi suivant, les tableaux de bord sont passés au rouge. Les threads applicatifs étaient bloqués en attente de réponses DB, et la latence du stockage montait en flèche. Curieusement, l’usage de /tmp semblait calme. L’ingénieur d’astreinte a passé la première heure à chasser des fantômes : « Si /tmp n’est pas plein, ça ne peut pas être des tables temporaires. »

Le vrai problème : le comportement des tables temporaires internes avait changé avec la nouvelle version et frappait l’espace de tables temporaires InnoDB sur le volume datadir. Le volume datadir était durable mais pas rapide, et il était partagé avec d’autres charges bruyantes sur la même classe de stockage.

Une fois qu’ils ont commencé à tracer les bons compteurs (activité temp InnoDB et await du device datadir), l’histoire est devenue claire. La correction n’était pas glamour : déplacer les surfaces de débordement vers un volume local rapide, puis corriger les deux requêtes GROUP BY principales qui produisaient d’énormes résultats intermédiaires.

Ils ont mis à jour le runbook avec une phrase qui compte : « Disk temp tables may not touch tmpdir. » Personne n’a applaudi, mais le pager s’est calmé.

Mini-récit n°2 : L’optimisation qui a mal tourné

Une équipe e-commerce avait une requête de rapport récurrente lente. Un ingénieur a décidé de « régler » le problème en augmentant drastiquement tmp_table_size et max_heap_table_size. Sur un benchmark de staging la requête s’est améliorée. Tout le monde s’est tapé dans la main et est passé à autre chose.

La production avait d’autres idées. Pendant une vente de pointe, la concurrence était bien plus élevée. Plusieurs sessions lançaient le lourd rapport simultanément — plus le trafic applicatif normal. Les tables temporaires plus grandes restaient en mémoire plus longtemps, ce qui semblait bien, jusqu’à ce que le serveur commence à swapper. La latence a explosé. La base de données n’a pas planté net ; elle a fait pire : elle est devenue imprévisible.

L’analyse post-incident a montré que « l’optimisation » n’avait pas réduit le travail total ; elle a changé l’endroit où le travail se produisait. Les débordements sur disque sont devenus pression mémoire, puis pression swap, puis ralentissement global. La requête initiale était toujours mal indexée et scannait encore une grande plage ; maintenant elle se disputait aussi la RAM.

La correction finale était ennuyeuse et efficace : ajouter un index couvrant pour supporter le filtre et la clé de groupement du rapport, et restreindre le job de rapport à une réplique avec des limites de ressources. Ils ont gardé les tailles temporaires un peu plus élevées qu’avant, mais dans une fourchette justifiée par le calcul de concurrence.

Mini-récit n°3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une plateforme de services financiers avait un processus de changement strict. Les gens se plaignaient que c’était lent. Ils avaient aussi l’habitude d’exécuter des tests de charge réalistes pour les changements de configuration DB, y compris « que se passe-t-il quand trois mauvaises requêtes tournent en même temps ». C’était fastidieux, et ça fonctionnait.

Avant un pic trimestriel de trafic, ils ont revu leurs principaux créateurs de tables temporaires depuis les résumés digest du Performance Schema. Ils n’ont pas chassé chaque requête — juste les principaux coupables qui se corrélaient aux pics de latence p95. Pour chacun, ils ont exigé soit un changement d’index, soit une réécriture de requête, soit une acceptation documentée qu’il déborderait et donc devrait tourner sur une réplique de reporting.

Ils ont aussi appliqué une règle simple de stockage : les chemins de déversement temporaires vivent sur des volumes rapides dédiés avec alertes sur capacité et latence. Pas parce que c’était astucieux, mais parce que cela a supprimé tout un mode de défaillance : la saturation du système de fichiers root et la contention I/O surprise.

Quand l’augmentation de trafic est arrivée, ils ont encore parfois débordé sur disque. Mais c’était contrôlé, mesurable, et n’interférait pas avec l’I/O transactionnelle. L’incident qui ne s’est pas produit n’obtient jamais de budget, mais il devrait.

Erreurs courantes : symptôme → cause racine → correction

1) Symptom: Created_tmp_disk_tables climbs fast during business hours

Cause racine : Un petit nombre de requêtes à haute fréquence réalisant GROUP BY/ORDER BY sans support d’index.

Correction : Trouvez les principaux digest coupables, ajoutez des index composites/couvrants, réduisez le nombre de lignes intermédiaires. Puis vérifiez le taux, pas seulement les totaux.

2) Symptom: sudden storage latency spikes, but /tmp is quiet

Cause racine : Débordements se produisant dans l’espace de tables temporaires InnoDB sur le volume datadir (courant avec MySQL 8.0).

Correction : Surveillez l’activité temporaire d’InnoDB et la latence du device datadir. Envisagez de déplacer la charge, d’améliorer les requêtes, et d’assurer que la classe de stockage du datadir peut gérer l’I/O temporaire.

3) Symptom: server swaps after you “fixed” spills by raising temp sizes

Cause racine : La mémoire par session a augmenté sous concurrence ; l’utilisation mémoire agrégée a dépassé la RAM disponible.

Correction : Annulez les augmentations extrêmes des buffers. Calculez le pire cas mémoire. Préférez les améliorations de requête/index. Augmentez les limites progressivement avec des tests de charge.

4) Symptom: temp tables go to disk even with huge tmp_table_size

Cause racine : La table temporaire contient BLOB/TEXT ou d’autres propriétés qui forcent une représentation sur disque, ou la requête déclenche une matérialisation qui ne bénéficie pas de vos paramètres de taille.

Correction : Réduisez les colonnes sélectionnées, évitez DISTINCT large, réécrivez les tables dérivées, et confirmez le comportement du moteur pour votre version exacte.

5) Symptom: “Copying to tmp table on disk” in processlist, but query is “small”

Cause racine : L’output final est petit ; le résultat intermédiaire ne l’est pas. L’ordre de jointure et les filtres créent un grand workspace.

Correction : Ajoutez des prédicats sélectifs, indexez les clés de jointure, et assurez-vous que les conditions sont sargables. Validez avec EXPLAIN et les estimations de lignes réelles.

6) Symptom: tmpdir fills up, MySQL errors, and apps fail in strange ways

Cause racine : tmpdir sur un petit système de fichiers (souvent root), charge de débordement importante, absence d’alerting.

Correction : Déplacez tmpdir sur un volume dédié. Ajoutez des alertes sur l’espace libre. Limitez les jobs dangereux ou déplacez-les hors du primaire.

Listes de vérification / plan étape par étape

Étape par étape : arrêter les tables temporaires sur disque de manière sensée

1. Empreintez l’environnement : confirmez la version exacte MySQL/MariaDB, la topologie de stockage, l’emplacement de tmpdir.
2. Mesurez le taux de débordement : calculez Created_tmp_disk_tables/sec pendant les fenêtres normales et d’incident.
3. Corrélez avec le stockage : vérifiez await du device, %iowait, et les métriques de latence du volume.
4. Identifiez les principaux coupables : récupérez les digests de statements par temps total et fréquence ; isolez les candidats avec GROUP BY/ORDER BY/DISTINCT/tables dérivées.
5. Expliquez et reproduisez : lancez EXPLAIN sur les requêtes représentatives ; confirmez « Using temporary/filesort » et les estimations de lignes élevées.
6. Corrigez SQL/index en priorité : index composites pour filtre+groupe, index couvrants pour patrons courants, supprimez SELECT * des DISTINCT.
7. Puis ajustez les seuils : augmentez tmp_table_size/max_heap_table_size modérément si les débordements restent élevés et que la mémoire le permet.
8. Renforcez le chemin I/O temporaire : déplacez tmpdir vers un stockage rapide dédié, assurez les alertes de capacité, et évitez le partage avec le root.
9. Garde-fous : restreignez les requêtes de reporting aux répliques, planifiez les jobs, et limitez la concurrence côté application si nécessaire.
10. Vérifiez le résultat : comparez le taux de débordement avant/après, la latence p95, et l’await du stockage. Si cela n’a pas bougé, vous n’avez pas résolu le vrai problème.

Une checklist minimale « ne pas regretter plus tard »

• tmpdir n’est pas sur le système de fichiers root (sauf si root est vraiment rapide et dimensionné pour cela).
• Vous pouvez tracer le taux de tables temporaires disque, pas seulement les totaux.
• Vous connaissez vos 5 principaux digests qui créent des tables temporaires.
• Vous avez une politique pour les requêtes de reporting (réplique, planification, limites).
• Les changements liés à la mémoire temporaire sont testés sous charge et concurrence.

FAQ

1) Puis-je désactiver complètement les tables temporaires sur disque ?

Non, pas de manière réaliste. Vous pouvez réduire la fréquence des débordements et rendre les débordements moins nocifs. Mais certaines opérations nécessitent un espace de travail, et à l’échelle vous déborderez parfois. Le vrai gain est d’arrêter les débordements pathologiques causés par de mauvais plans et des index manquants.

2) Si je règle tmp_table_size à 1G, cela gardera-t-il les tables temporaires en mémoire ?

Parfois, pour certaines tables temporaires. Cela peut aussi provoquer une exhaustion mémoire sous concurrence, ou ne pas aider quand les types de données/opérations forcent l’usage disque. Les grandes valeurs sont un outil, pas une stratégie.

3) Pourquoi je vois « Using temporary » mais Created_tmp_disk_tables n’augmente pas ?

Parce qu’une structure temporaire peut exister en mémoire. « Using temporary » indique qu’une table temporaire est utilisée, pas qu’elle a débordé sur disque. De plus, les compteurs peuvent être affectés par les internals du moteur/version.

4) MySQL vs MariaDB : lequel évite le mieux les tables temporaires sur disque ?

Aucun ne gagne par défaut. Les facteurs les plus importants sont votre version, le comportement de l’optimiseur, vos patrons SQL, et vos index. Choisissez selon l’écosystème et l’adéquation opérationnelle, puis ajustez selon les mesures.

5) Dois-je mettre tmpdir sur tmpfs pour rendre les débordements « rapides » ?

Seulement si vous avez une forte marge mémoire et que vous êtes à l’aise avec le mode de défaillance. tmpfs rend les déversements rapides jusqu’au moment où il rend le noyau mécontent. Pour la plupart des systèmes de production, un disque dédié rapide est le choix le plus sûr.

6) Les tables temporaires sur disque sont-elles toujours mauvaises ?

Non. Des débordements occasionnels sont normaux. Ce qui est mauvais, c’est un taux soutenu élevé de débordements corrélé à la latence et au wait I/O, ou des débordements qui remplissent les systèmes de fichiers et provoquent des erreurs.

7) Comment savoir si un débordement va dans tmpdir ou dans l’espace de tables temporaires InnoDB ?

Utilisez une combinaison d’indices : I/O et capacité du système de fichiers tmpdir, I/O du device datadir, compteurs d’état temporaires InnoDB, et états du processlist. Ne vous fiez pas à un seul métrique.

8) Quelle est la correction la plus efficace pour « Copying to tmp table on disk » ?

Corrigez le plan de requête : ajoutez l’index adéquat, réduisez la taille des résultats intermédiaires, évitez DISTINCT large et les tris d’expressions. Ensuite, assurez-vous que les débordements atterrissent sur un stockage rapide et prévisible quand ils se produisent encore.

9) Est-ce un problème de stockage ou de base de données ?

Les deux, et c’est pourquoi c’est agaçant. Du SQL mauvais crée des débordements ; un stockage lent ou en contention transforme ces débordements en pannes. Traitez-le comme un sujet full-stack : plan de requête + seuils mémoire + chemin I/O.

Conclusion : prochaines étapes qui font vraiment la différence

Les tables temporaires sur disque ne sont pas une défaillance morale. Ce sont des indices. Elles vous disent que l’optimiseur a eu besoin d’un calepin et qu’il n’a pas pu le placer en mémoire — ou qu’il n’a même pas essayé parce que l’opération exigeait des structures adaptées au disque. Votre travail est de décider si ce calepin est un travail légitime ou le symptôme d’un SQL négligent et d’index manquants.

Faites ces trois choses suivantes :

1. Mesurez le taux de débordement pendant la douleur (Created_tmp_disk_tables/sec) et corrélez-le avec la latence du device.
2. Corrigez les principaux coupables avec des index et des réécritures de requêtes. Ne touchez pas aux réglages globaux avant de savoir ce qui déborde.
3. Faites atterrir les débordements quelque part de sens : stockage tmp dédié et rapide, alertes de capacité, et plan pour les charges de reporting.

Règle de fiabilité approximative, paraphrasant une idée de Werner Vogels : on ne construit pas des systèmes fiables en espérant ; on les construit en concevant pour la défaillance et en mesurant la réalité.