MySQL vs MariaDB: tablas temporales en disco — cómo detenerlas de verdad

Qué significan realmente las “tablas temporales en disco” (y por qué deberías importarte)

Cuando MySQL o MariaDB no pueden ejecutar una consulta puramente en memoria, crean una tabla temporal interna. Esto no es lo mismo que una CREATE TEMPORARY TABLE creada por el usuario. Las tablas temporales internas son blocs de trabajo creados por el motor usados para:

• GROUP BY y DISTINCT sin un índice utilizable
• ORDER BY que no puede satisfacerse con un índice (a menudo por falta o ineficacia de LIMIT)
• tablas derivadas / subconsultas / vistas que el optimizador decide materializar
• algunas funciones de ventana y operaciones de conjunto complejas

Lo que duele: las tablas temporales internas pueden vivir en memoria o en disco. En modelos mentales más antiguos, “tabla temporal en memoria” significaba el motor MEMORY/HEAP y “tabla temporal en disco” significaba MyISAM. MySQL moderno complica eso: las tablas temporales internas pueden usar InnoDB y su tablespace temporal. MariaDB tiene sus propias vueltas. Tu métrica de monitorización—a menudo Created_tmp_disk_tables—es un indicador rezagado que dice: “volcamos, pagamos I/O y ahora fingimos que es normal”.

Si ejecutas en NVMe local rápido con caché generosa, podrías sobrevivir. Si estás en almacenamiento en red, o en un volumen ruidoso de nube, las tablas temporales en disco son básicamente un impuesto de rendimiento que pagas en cada pico de tráfico.

Broma #1: Una tabla temporal basada en disco es como hacer la declaración de impuestos en una cinta de correr—técnicamente posible, emocionalmente costoso.

MySQL vs MariaDB: dónde difiere el comportamiento de tablas temporales en la práctica

La gente tiende a tratar a MariaDB como “MySQL con funciones adicionales.” Operativamente, así es como te despiertan a las 2 a. m. El comportamiento de tablas temporales no es idéntico, y las diferencias importan cuando intentas detener los volcados a disco “de verdad”.

1) Las opciones de motor para tablas temporales internas no son la misma historia

MySQL 8.0 usa ampliamente una implementación interna de tablas temporales, y cuando vuelca a disco con frecuencia aterriza en el tablespace temporal de InnoDB (aún “disco”). Verás artefactos en I/O temporal de InnoDB y a veces en el comportamiento de crecimiento de ibtmp1.

MariaDB (10.3+ especialmente) tiene un optimizador y mecanismos de tablas temporales distintos, y puede apoyarse en comportamientos de Aria/MyISAM según la configuración y la compilación. La terminología que ves en los contadores de estado puede parecer familiar mientras oculta mecánicas diferentes.

2) Los contadores de estado se parecen, pero no cuentan toda la verdad

Created_tmp_disk_tables existe en ambos, pero lo que “disco” significa puede variar según la versión y el motor interno. “Disco” podría significar:

• un archivo bajo tmpdir
• páginas en un tablespace temporal interno de InnoDB
• una tabla temporal que empezó en memoria pero se convirtió tras alcanzar un límite

3) Límites y umbrales difieren en casos límite

Ambos usan la danza entre tmp_table_size y max_heap_table_size para decidir si una tabla temporal puede existir en memoria. Pero los desencadenantes de conversión pueden ser más matizados (tipos de datos, columnas blob/text, formato de fila y operaciones específicas). Si solo subes esas dos variables y das el trabajo por hecho, reducirás algunos volcados y aún sufrirás otros.

4) Los cambios versión a versión son más grandes que los estereotipos de marca

MySQL 5.7 vs 8.0 puede comportarse más diferente que “MySQL vs MariaDB” a alto nivel. Lo mismo para MariaDB 10.1 vs 10.6. Cualquier ajuste serio comienza con: “¿Qué versión exacta está ejecutando y qué carga exacta está volcándose?”

Guía rápida de diagnóstico

Si tienes 20 minutos antes de la próxima revisión de incidentes, haz esto en orden. Está sesgada a aislar rápidamente si tratas con forma de consulta, umbrales de memoria o rendimiento/latencia del almacenamiento.

Primero: demuestra que es I/O de tablas temporales (no ruido aleatorio del disco)

1. Revisa los contadores globales de tablas temporales y su tasa de cambio.
2. Verifica si las consultas lentas corresponden a Sort_merge_passes, Created_tmp_disk_tables y un salto en lecturas del handler.
3. Revisa la latencia I/O a nivel de sistema de archivos en el volumen que aloja tmpdir (o el tablespace temporal de InnoDB).

Segundo: identifica los 1–3 patrones de consulta principales que generan volcados

1. Extrae las consultas más lentas durante la ventana del incidente.
2. Ejecuta EXPLAIN y busca “Using temporary” / “Using filesort” / materialización.
3. Busca GROUP BY y ORDER BY sin índices de soporte, además de grandes conjuntos de resultados intermedios.

Tercero: decide si arreglar SQL, ajustar umbrales o cambiar la superficie de volcado

1. Si una o dos consultas son responsables: arregla SQL/índices primero. Es el ROI más alto y lo más estable.
2. Si muchas consultas vuelcan por límites bajos: aumenta los límites de tablas temporales con cuidado, considerando concurrencia y presión de memoria.
3. Si los volcados son inevitables: mueve tmpdir a SSD/NVMe local rápido y asegúrate de que el sistema de archivos y las opciones de montaje no te saboteen.

Hechos e historia que explican las rarezas actuales

1. MySQL históricamente usaba MEMORY para tablas temporales en memoria y MyISAM para las de disco; ese modelo mental aún persigue las guías de tuning.
2. InnoDB se convirtió en el motor por defecto en MySQL 5.5, cambiando los patrones típicos de I/O en producción y haciendo los volcados de tablas temporales más visibles en sistemas ocupados.
3. MySQL 8.0 cambió mucho el comportamiento del optimizador, incluido el manejo de tablas derivadas y detalles de implementación de tablas temporales internas; las actualizaciones pueden cambiar las tasas de volcados sin cambiar el SQL.
4. MariaDB se desvió significativamente después del fork; no es solo “MySQL drop-in”, especialmente en torno a características del optimizador y motores de almacenamiento como Aria.
5. Las banderas EXPLAIN “Using temporary; Using filesort” son antiguas y siguen siendo útiles, pero no garantizan I/O en disco—solo que existe una estructura temporal.
6. tmpdir ha sido una mina de rendimiento durante décadas porque por defecto apunta a rutas temporales del sistema que pueden residir en volúmenes root lentos.
7. “Tablas temporales en disco” suelen ser síntoma de índices faltantes, no de RAM insuficiente; añadir memoria puede enmascarar un fallo de consulta hasta el siguiente salto de datos.
8. La migración a la nube empeoró esto: discos efímeros, volúmenes en red y vecinos ruidosos convierten volcados ocasionales en acantilados de latencia.

Mecánica: qué obliga a las tablas temporales a disco

Los tres grandes desencadenantes

1) Límites de tamaño. El servidor estima u observa que la tabla temporal crece más allá de los límites permitidos en memoria y la convierte a disco. Las perillas clásicas son:

• tmp_table_size
• max_heap_table_size

La capacidad efectiva de tabla temporal en memoria suele ser el mínimo de esos dos para tablas temporales basadas en MEMORY, pero las tablas temporales internas modernas no siempre usan MEMORY. Aun así, estas variables siguen siendo parte del árbol de decisión.

2) Tipos de columna y formato de fila. Si la tabla temporal necesita almacenar columnas BLOB/TEXT, u otras estructuras no admitidas eficientemente en memoria, puedes volcar incluso con límites generosos. Esta es una razón por la que “simplemente aumentar tmp_table_size” a menudo decepciona.

3) Forma de la consulta que crea resultados intermedios enormes. Incluso si el resultado final es pequeño, los resultados intermedios pueden ser masivos. Ofensor clásico: GROUP BY en una columna de baja cardinalidad mientras se une una tabla ancha sin predicados selectivos.

Por qué “detener las tablas temporales en disco” no es un interruptor único

Las tablas temporales existen porque el optimizador necesita un espacio de trabajo. Puedes reducir los volcados a disco, pero no puedes—y no deberías—intentar eliminar tablas temporales globalmente. El objetivo es:

• mantener las estructuras temporales pequeñas
• mantenerlas en memoria cuando sea seguro
• evitar crearlas arreglando SQL e índices
• cuando lleguen a disco, hacer que el disco sea rápido y predecible

También existe la trampa de la concurrencia: aumentar los límites temporales puede ayudar a una sola consulta, pero perjudicar al sistema bajo carga cuando 200 sesiones asignan cada una estructuras temporales más grandes. No quieres ganar un benchmark y perder producción.

Tareas prácticas (comandos, salidas, decisiones)

Estas son las tareas que realmente ejecutas durante una caza de rendimiento. Cada una incluye: un comando, salida realista, qué significa y la decisión que tomas.

Tarea 1: Confirma versión y sabor (no puedes afinar un 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

Qué significa: Esto es MySQL 8.0.x, así que el comportamiento de tablas temporales internas y el tablespace temporal de InnoDB están en juego.

Decisión: Usa la instrumentación de MySQL 8.0 (Performance Schema, vistas del esquema sys). No apliques variables exclusivas de MariaDB ni folclore antiguo de 5.6.

Tarea 2: Comprueba los contadores de creación de tablas temporales y su 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  |
+-------------------------+----------+

Qué significa: Existen tablas temporales en disco y no son raras. La proporción importa: 184k en disco de 2.4M de tablas temporales es ~7.5%.

Decisión: Si la tasa sube durante incidentes, estás volcándote bajo carga. Pasa a monitorizar por tasa y no por totales (siguiente tarea) e identifica culpables.

Tarea 3: Mide la tasa de tablas temporales en disco (no solo totales)

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 |
+-------------------------+--------+

Qué significa: 182 tablas temporales en disco en 10 segundos: ~18/seg. Eso es suficiente para presionar el almacenamiento si no son triviales.

Decisión: Si la latencia de almacenamiento se correlaciona, trata esto como sospechoso principal, no como ruido de fondo.

Tarea 4: Revisa los umbrales de memoria para tablas temporales

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 |
+--------------------+----------+

Qué significa: Ambos están en 16MB. Eso es conservador y forzará volcados a disco para operaciones GROUP BY/ORDER BY de tamaño moderado.

Decisión: Considera aumentarlos, pero solo después de identificar si los volcados se deben a tamaño vs tipos de datos vs forma de la consulta. No pongas 1G y te felicites.

Tarea 5: Comprueba dónde van los archivos temporales (tmpdir)

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

Qué significa: Los volcados temporales aterrizan en /tmp. En muchos servidores eso está en el filesystem root, que a menudo es el disco menos querido.

Decisión: Si tienes NVMe local, mueve tmpdir a un montaje dedicado con rendimiento predecible y monitorización de capacidad.

Tarea 6: Verifica el sistema de ficheros de /tmp y opciones de montaje

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)

Qué significa: Aquí /tmp es en realidad tmpfs (respaldado por RAM). Eso es rápido, pero también es presión de memoria esperando suceder.

Decisión: Si ves riesgo de OOM o intercambio, deja de usar tmpfs para temp de BD. Prefiere SSD local rápido con cuotas/monitorización, a menos que tengas margen de memoria y límites estrictos.

Tarea 7: Verifica el comportamiento del tablespace temporal de InnoDB (típico en MySQL 8.0)

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    |
+----------------------------+---------+

Qué significa: Hay actividad temporal en InnoDB. Los volcados podrían no aparecer como archivos en tmpdir como sugieren guías antiguas.

Decisión: Vigila I/O en el volumen de datadir también, no solo tmpdir. Si ibtmp1 crece o hay picos de I/O, tienes una segunda superficie de volcado.

Tarea 8: Encuentra “Using temporary” y “Using filesort” en consultas reales (muestreo rápido)

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

Qué significa: Un agregado con GROUP BY + ORDER BY es un candidato principal para tablas temporales y volcados de ordenamiento.

Decisión: Extrae este patrón exacto de consulta, ejecuta EXPLAIN y revisa el indexado. No ajustes memoria global hasta saber si falta un índice.

Tarea 9: Explica la consulta sospechosa y lee la columna “Extra” como un 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

Qué significa: El motor espera escanear muchas filas y luego construir una tabla temporal y ordenarla. Esto puede volcar a disco.

Decisión: Considera un índice compuesto que soporte el agrupamiento, o cambia la consulta para reducir filas escaneadas (rango de tiempo más estrecho, preagregación, tabla de resumen). Aumentar el tamaño temporal puede ayudar pero no arreglará el escaneo.

Tarea 10: Valida si los volcados temporales se correlacionan con la latencia del almacenamiento

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

Qué significa: %iowait y await del dispositivo son elevados durante el evento. Huele a presión de escritura inducida por volcados.

Decisión: Si la capa de almacenamiento es el limitador, reducir los volcados o moverlos a medios más rápidos es urgente. De lo contrario, optimizarás consultas y seguirás perdiendo frente a picos de latencia de I/O.

Tarea 11: Comprueba espacio libre y presión de inodos donde viven los archivos temporales

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

Qué significa: Datadir tiene margen pero está algo lleno. Temp está en tmpfs así que los inodos no son el problema aquí.

Decisión: Si las tablas temporales en disco caen en datadir (tablespace temporal de InnoDB), asegúrate de que el volumen de datadir tenga capacidad. Si /tmp es un FS real y está al 100%, arregla eso antes de afinar nada más.

Tarea 12: Inspecciona las sentencias en ejecución durante un pico

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 ...                |
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+

Qué significa: Tienes evidencia en vivo: “Copying to tmp table on disk” y “Creating sort index.” Ese es volcamiento + trabajo de ordenamiento ocurriendo ahora.

Decisión: Captura estos textos de consulta (desde logs de la app o Performance Schema), luego reproduce y arregla. Si esto está extendido en muchas sesiones, céntrate en indexado y umbrales temporales.

Tarea 13: Detecta “sort merge passes” que indican que la memoria para ordenamiento es demasiado pequeña

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  |
+------------------+---------+

Qué significa: Muchas pasadas de merge: el buffer de ordenamiento es pequeño para la carga. Eso puede aumentar la actividad en disco incluso sin que las tablas temporales sean el conductor primario.

Decisión: Aumenta sort_buffer_size con cuidado solo si entiendes la concurrencia (es por sesión). Prefiere arreglos de consulta/índice primero; inflar buffers es una forma clásica de cambiar picos de latencia por incidentes de memoria.

Tarea 14: Valida el margen de memoria antes de aumentar buffers por sesión

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

Qué significa: La memoria disponible es ~20GB, pero el swap está en uso. Es una señal amarilla: la máquina ya sufre presión de memoria a veces.

Decisión: No “arregles las tablas temporales en disco” añadiendo grandes buffers por hilo. Solo trasladarás el dolor de latencia de disco a tormentas de swap.

Patrones de consulta que generan tablas temporales en disco

Patrón 1: GROUP BY sin un índice de soporte

Si agrupas por customer_id pero solo tienes un índice en created_at, el motor lee filas por rango temporal y luego agrega en una estructura temporal. Si la cardinalidad del grupo es alta, la tabla temporal crece. Si crece más allá de los umbrales de memoria o contiene tipos no amigables, vuelca.

Qué hacer: Construye un índice compuesto que coincida con tu filtro y la clave de agrupación en un orden útil, por ejemplo (created_at, customer_id) o (customer_id, created_at) dependiendo de selectividad y forma de la consulta. Luego verifica con EXPLAIN y tiempos reales.

Patrón 2: ORDER BY sobre una expresión (o una columna distinta del filtro)

ORDER BY COUNT(*) DESC (orden agregado), ORDER BY LOWER(email), ORDER BY DATE(created_at)—estos frecuentemente fuerzan filesort y estructuras temporales.

Qué hacer: Si necesitas ordenamiento, considera precomputar valores ordenables, o reescribir para usar columnas indexadas, o aceptar ordenamientos aproximados. Si el negocio quiere “top N”, asegura que la consulta realmente use LIMIT y pueda explotarlo.

Patrón 3: DISTINCT sobre filas anchas

SELECT DISTINCT * es el equivalente SQL de pedir al servidor que deduplice un almacén de datos. Obtendrás una tabla temporal, presión de memoria y disco.

Qué hacer: Selecciona solo las columnas que necesitas. Usa una clave DISTINCT más estrecha. O mejor: arregla la lógica de joins para que no se creen duplicados en primer lugar.

Patrón 4: Tablas derivadas que se materializan

Subconsultas y vistas que “parecen limpias” pueden materializarse en tablas temporales, especialmente si incluyen agregación u ordenamiento. MySQL y MariaDB difieren en cuándo eligen materializar, y las actualizaciones de versión pueden cambiar el comportamiento.

Qué hacer: Prueba con EXPLAIN y observa la materialización de tablas derivadas. En muchos casos, reescribir una tabla derivada como un join con índices apropiados reduce el trabajo temporal.

Patrón 5: Unir conjuntos grandes antes de filtrar

Si el optimizador elige un orden de join que crea un gran resultado intermedio y solo filtra después, verás explotar las estructuras temporales y de ordenamiento.

Qué hacer: Asegura que los predicados selectivos sean sargables (utilizables por índices). Añade índices que soporten la condición de join y el filtro. Si es necesario, usa hints del optimizador con cautela (y documentalos como un arma cargada).

Ajustes que importan (y ajustes que te hacen perder el tiempo)

Los controles que realmente influyen en los volcados de tablas temporales

tmp_table_size y max_heap_table_size

Estos son las primeras palancas que todos tocan porque son fáciles. También funcionan—a veces. Los puntos clave:

• La capacidad efectiva de tabla temporal en memoria está limitada por el menor de estos dos (en casos clásicos).
• Aumentarlos incrementa el uso potencial de memoria por sesión bajo concurrencia.
• No solucionan volcados causados por tipos de datos o planes de consulta que generan resultados intermedios enormes.

Guía con opinión: Auméntalos en pasos medidos (p. ej., 16MB → 64MB → 128MB), solo después de identificar las principales consultas que vuelcan. Mide tasas y p95 de latencia. Si saltas directo a 512MB, te estás creando un incidente de memoria no conocido.

tmpdir

Esto controla dónde caen algunas estructuras temporales en disco. Si tmpdir está en almacenamiento lento, cada volcado se convierte en una tormenta de I/O.

Guía con opinión: Pon tmpdir en SSD/NVMe local rápido con monitorización y espacio libre suficiente. Evita usar el volumen root. Evita sistemas de archivos en red. Y no lo pongas en tmpfs a menos que tengas pruebas sólidas de que no harás OOM en picos de concurrencia.

internal_tmp_mem_storage_engine (MySQL)

En algunas versiones de MySQL, esto influye si las tablas temporales internas en memoria usan MEMORY o una implementación TempTable. Esto puede cambiar rendimiento y comportamiento de memoria, e interactúa con cómo se manejan BLOB/TEXT.

Guía con opinión: No lo cambies como primer movimiento. Es una palanca para cargas específicas después de confirmar el comportamiento del motor en tu versión.

Buffers por sesión: sort_buffer_size, join_buffer_size, read_rnd_buffer_size

Estos pueden reducir trabajo en disco para ordenamientos y joins, pero son por sesión y pueden inflar el uso de memoria. Los sistemas de producción no ejecutan una consulta a la vez. Ejecutan 400 consultas ligeramente equivocadas concurrentes.

Guía con opinión: Mantén estos conservadores. Arregla SQL e índices primero. Si debes afinar buffers, hazlo con cálculos de concurrencia y pruebas de carga, no con intuición.

Ajustes que la gente modifica y que usualmente no resuelven las tablas temporales en disco

• innodb_buffer_pool_size solo: ayuda I/O general, no evita que las tablas temporales necesiten espacio de trabajo.
• Ajustes de hilos/concurrencia: pueden cambiar patrones de presión pero no arreglan por qué estás volcándote.
• “Hacer el disco más rápido” como único plan: oculta SQL roto hasta el siguiente umbral de crecimiento de datos.

tmpdir, sistemas de archivos y almacenamiento: hacer que el volcado a disco duela menos

A veces no puedes evitar los volcados. Existen consultas de reporte. Ocurre análisis ad-hoc. Alguien ejecutará el “resumen mensual” a las 9:55 a. m. de un día hábil y jurará que es urgente.

Así que trata el I/O de volcados como una clase de carga de trabajo:

• Pon el I/O temporal en medios predecibles (SSD/NVMe local preferido).
• Separa dominios de falla: no permitas que los archivos temporales llenen el mismo filesystem que contiene tu datadir.
• Monitoriza capacidad y latencia del montaje temporal explícitamente.
• Usa opciones de sistema de archivos que coincidan con tu apetito de riesgo. Para volúmenes solo de temp, puedes aceptar menos durabilidad. Pero sé explícito y documenta.

Un diseño práctico de tmpdir

Un enfoque común en producción:

• /var/lib/mysql en almacenamiento redundante (o volumen gestionado con durabilidad)
• /var/lib/mysqltmp en disco local rápido, filesystem separado
• tmpdir=/var/lib/mysqltmp en la configuración

Esto no evita los volcados, pero evita que los volcados temporales destruyan la parte equivocada de tu sistema.

Broma #2

Si tus tablas temporales viven en el filesystem root, no estás ejecutando una base de datos; estás ejecutando una novela de suspenso.

Tres microhistorias corporativas desde el campo

Microhistoria #1: El incidente causado por una suposición equivocada

Una empresa SaaS mediana migró de un MySQL antiguo a una compilación más nueva con prisa—ventana de parcheo de seguridad, atención de ejecutivos, lo habitual. La suposición del equipo fue simple: los volcados de tablas temporales aparecen como archivos en /tmp, así que monitorizaron /tmp y listos.

El siguiente lunes, los paneles se pusieron rojos. Hilos de aplicación estaban bloqueados esperando respuestas de la base de datos y la latencia del almacenamiento se disparó. Curiosamente, el uso de /tmp parecía tranquilo. El ingeniero de guardia pasó la primera hora persiguiendo fantasmas: “Si /tmp no se está llenando, no pueden ser tablas temporales”.

El problema real: las tablas temporales internas habían cambiado de comportamiento con la versión más nueva y estaban golpeando el tablespace temporal de InnoDB en el volumen de datadir. El volumen de datadir era duradero pero no rápido, y se compartía con otras cargas ruidosas en la misma clase de almacenamiento.

Cuando empezaron a graficar los contadores correctos (actividad temporal de InnoDB y await de disco en el dispositivo del datadir), la historia tuvo sentido. La solución no fue glamorosa: mover las superficies de volcado a un volumen local rápido y luego arreglar las dos consultas GROUP BY principales que producían resultados intermedios masivos.

Actualizaron el runbook con una frase que importaba: “Las tablas temporales en disco pueden no tocar tmpdir.” Nadie celebró, pero el pager se calmó.

Microhistoria #2: La optimización que salió mal

Un equipo de comercio electrónico tenía una consulta de informe recurrente lenta. Un ingeniero decidió “resolverlo” subiendo drásticamente tmp_table_size y max_heap_table_size. En un benchmark de staging, la consulta mejoró. Todos chocaron manos y siguieron.

Producción tuvo ideas diferentes. Durante una venta pico, la concurrencia fue mucho mayor. Múltiples sesiones ejecutaron el informe pesado simultáneamente—más el tráfico normal de la aplicación. Las tablas temporales más grandes permanecieron en memoria más tiempo, lo que sonaba bien, hasta que el servidor empezó a hacer swap. La latencia explotó. La base de datos no se cayó; hizo algo peor. Se volvió impredeciblemente lenta.

El análisis post-incident mostró que la “optimización” no redujo el trabajo total; cambió dónde ocurría. Los volcados a disco se convirtieron en presión de memoria, que se convirtió en presión de swap, que se convirtió en desaceleración global. La consulta original seguía mal indexada y seguía escaneando un rango grande; ahora además competía más por RAM.

La solución eventual fue aburrida y efectiva: añadir un índice covering que soporte el filtro y la clave de agrupación del informe, y restringir el trabajo de informe a una réplica con límites de recursos. Mantuvieron los tamaños temporales algo más altos que antes, pero dentro de un rango justificado por cálculos de concurrencia.

Microhistoria #3: La práctica aburrida pero correcta que salvó el día

Una plataforma de servicios financieros tenía un proceso de cambios estricto. La gente se quejaba de que era lento. También tenían la costumbre de ejecutar pruebas de carga realistas para cambios de configuración de base de datos, incluyendo “qué pasa cuando tres consultas malas se ejecutan a la vez.” Era tedioso, y funcionaba.

Antes de un pico trimestral de tráfico, revisaron sus principales creadores de tablas temporales desde resúmenes de digest de Performance Schema. No persiguieron cada consulta—solo a los principales ofensores que se correlacionaban con picos de latencia p95. Para cada una, exigieron o bien un cambio de índice, una reescritura de consulta, o una aceptación documentada de que volcaría y, por ello, debía ejecutarse en una réplica de reporting.

También hicieron cumplir una regla simple de almacenamiento: las rutas de volcado temporal viven en volúmenes rápidos dedicados con alertas de capacidad y latencia. No porque fuera ingenioso, sino porque eliminó todo un modo de fallo: saturación del filesystem root y contención sorpresa de I/O.

Cuando llegó el pico de tráfico, todavía volcaron a disco a veces. Pero fue controlado, medible y no interfirió con el I/O transaccional. El incidente que no ocurrió nunca consigue presupuesto, pero debería.

Errores comunes: síntoma → causa raíz → solución

1) Síntoma: Created_tmp_disk_tables sube rápido durante horas de negocio

Causa raíz: Un pequeño número de consultas de alta frecuencia haciendo GROUP BY/ORDER BY sin soporte de índice.

Solución: Encuentra los digest ofensores principales, añade índices compuestos/covering, reduce el recuento de filas intermedias. Luego vuelve a comprobar la tasa, no los totales.

2) Síntoma: picos de latencia de almacenamiento repentinos, pero /tmp está tranquilo

Causa raíz: Los volcados están ocurriendo en el tablespace temporal de InnoDB en el volumen de datadir (común en el comportamiento de MySQL 8.0).

Solución: Monitoriza la actividad temporal de InnoDB y la latencia del dispositivo de datadir. Considera mover la carga, mejorar consultas y asegurar que la clase de almacenamiento de datadir pueda manejar I/O temporal.

3) Síntoma: el servidor hace swap después de “arreglar” volcados subiendo tamaños temporales

Causa raíz: Incremento de memoria por sesión bajo concurrencia; el uso agregado de memoria excede la RAM disponible.

Solución: Revierte aumentos extremos de buffers. Calcula memoria en peor caso. Prefiere mejoras de consulta/índice. Aumenta límites gradualmente con pruebas de carga.

4) Síntoma: las tablas temporales van a disco incluso con un enorme tmp_table_size

Causa raíz: La tabla temporal contiene BLOB/TEXT u otras propiedades que fuerzan representación en disco, o la consulta desencadena materialización que no se beneficia de tus ajustes de tamaño.

Solución: Reduce columnas seleccionadas, evita DISTINCT ancho, reescribe tablas derivadas y confirma el comportamiento del motor para tu versión exacta.

5) Síntoma: “Copying to tmp table on disk” en processlist, pero la consulta es “pequeña”

Causa raíz: La salida final es pequeña; el resultado intermedio no lo es. El orden de joins y filtros crea un gran espacio de trabajo.

Solución: Añade predicados selectivos, indexa claves de join y asegura condiciones sargables. Valida con EXPLAIN y estimaciones reales de filas.

6) Síntoma: tmpdir se llena, MySQL da errores y las apps fallan de maneras extrañas

Causa raíz: tmpdir en un filesystem pequeño (a menudo root), carga pesada de volcados, sin alertas.

Solución: Mueve tmpdir a un volumen dedicado. Añade alertas de espacio libre. Limita trabajos de reporte peligrosos o muévelos fuera del primario.

Listas de verificación / plan paso a paso

Paso a paso: detener las tablas temporales en disco de forma sensata

1. Firme el entorno: confirma la versión exacta de MySQL/MariaDB, el diseño de almacenamiento, la ubicación de tmpdir.
2. Mide la tasa de volcados: calcula Created_tmp_disk_tables/sec durante ventanas normales y de incidente.
3. Correlaciona con almacenamiento: revisa device await, %iowait y métricas de latencia del volumen.
4. Identifica a los principales ofensores: extrae digest de sentencias por tiempo total y frecuencia; aísla candidatos con GROUP BY/ORDER BY/DISTINCT/tablas derivadas.
5. Explica y reproduce: ejecuta EXPLAIN en consultas representativas; confirma “Using temporary/filesort” y grandes estimaciones de filas.
6. Arregla SQL/índices primero: índices compuestos para filtro+grupo, índices covering para patrones comunes, elimina SELECT * en DISTINCT.
7. Luego ajusta umbrales: aumenta tmp_table_size/max_heap_table_size modestamente si los volcados siguen altos y hay margen de memoria.
8. Endurece la ruta I/O temporal: mueve tmpdir a almacenamiento dedicado y rápido, asegura alertas de capacidad y evita compartirlo con el OS root.
9. Guardarraíles: restringe consultas de reporte pesadas a réplicas, programa trabajos y limita concurrencia en la capa de aplicación si hace falta.
10. Verifica el resultado: compara antes/después la tasa de volcados, latencia p95 y await de almacenamiento. Si no se movieron, no arreglaste el problema real.

Lista mínima “no te arrepientas luego”

• tmpdir no está en el filesystem root (a menos que el root sea realmente rápido y dimensionado para ello).
• Puedes graficar la tasa de tablas temporales en disco, no solo los totales.
• Conoces tus 5 digest principales que crean tablas temporales.
• Tienes una política para consultas de reporte (réplica, programación, límites).
• Los cambios en memoria relacionados con temp están probados con carga y concurrencia.

Preguntas frecuentes

1) ¿Puedo desactivar completamente las tablas temporales en disco?

No, no de manera realista. Puedes reducir la frecuencia de volcados y hacerlos menos dañinos. Pero algunas operaciones requieren espacio de trabajo, y a escala vas a volcar a veces. La victoria real es detener volcados patológicos causados por planes malos e índices faltantes.

2) Si pongo tmp_table_size a 1G, ¿mantendrá las tablas temporales en memoria?

En ocasiones, para algunas tablas temporales. También puede provocar agotamiento de memoria bajo concurrencia, o no ayudar cuando tipos de datos/operaciones fuerzan uso en disco. Valores grandes son una herramienta, no una estrategia.

3) ¿Por qué veo “Using temporary” pero Created_tmp_disk_tables no aumenta?

Porque una estructura temporal puede existir en memoria. “Using temporary” indica uso de tabla temporal, no que se haya volcado a disco. Además, los contadores pueden verse afectados por internals del motor/versión.

4) MySQL vs MariaDB: ¿cuál evita mejor las tablas temporales en disco?

Ninguno gana por defecto. Los factores más importantes son tu versión, el comportamiento del optimizador, los patrones SQL y los índices. Elige según ecosistema y ajuste operativo, y luego afina según mediciones.

5) ¿Debería poner tmpdir en tmpfs para hacer los volcados “rápidos”?

Sólo si tienes amplio margen de memoria y te sientes cómodo con ese modo de fallo. tmpfs hace los volcados rápidos hasta que hace enfadar al kernel. Para la mayoría de sistemas en producción, un disco rápido dedicado es la opción más segura.

6) ¿Las tablas temporales en disco siempre son malas?

No. Volcados ocasionales son normales. Lo que es malo es una tasa sostenida alta de volcados correlacionada con latencia y espera de I/O, o volcados que llenan filesystems y provocan errores.

7) ¿Cómo sé si un volcado va a tmpdir o al tablespace temporal de InnoDB?

Usa una combinación de evidencias: I/O y capacidad del filesystem tmpdir, I/O del dispositivo de datadir, contadores de estado temporal de InnoDB y estados del processlist. No confíes en una sola métrica.

8) ¿Cuál es la solución más efectiva para “Copying to tmp table on disk”?

Arregla el plan de consulta: añade el índice correcto, reduce el tamaño del resultado intermedio y evita DISTINCT ancho y ordenamientos por expresiones. Luego asegúrate de que los volcados caigan en almacenamiento rápido y predecible cuando aún ocurran.

9) ¿Esto es un problema de almacenamiento o de base de datos?

Ambos, y por eso es molesto. SQL malo crea volcados; almacenamiento lento o contendido convierte los volcados en outages. Trátalo como un problema de rendimiento full-stack: plan de consulta + umbrales de memoria + ruta de I/O.

Conclusión: siguientes pasos que realmente marcan la diferencia

Las tablas temporales en disco no son una falla moral. Son una pista. Te dicen que el optimizador necesitó un bloc de trabajo y no cupo en memoria—o ni siquiera lo intentó porque la operación demandaba estructuras amigables con disco. Tu trabajo es decidir si ese bloc de trabajo es trabajo legítimo o un síntoma de SQL descuidado e índices faltantes.

Haz las siguientes tres cosas:

1. Mide la tasa de volcados durante el dolor (Created_tmp_disk_tables/sec) y correlaciónala con la latencia del dispositivo.
2. Arregla a los principales ofensores con índices y reescrituras de consultas. No afines globalmente hasta saber qué está volcando.
3. Haz que los volcados aterricen en un lugar sensato: tmp dedicado y rápido, alertas de capacidad y un plan para cargas de reporte.

Regla práctica de fiabilidad, parafraseando una idea de Werner Vogels: no construyes sistemas fiables esperando; los construyes diseñando para fallos y midiendo la realidad.