MySQL: agotamiento de espacio en disco para tablas predeterminadas temporales

Recientemente me enfrenté a un problema real de quedarme completamente sin espacio en disco en MySQL. Este era un problema grave debido a la continua interrupción del servicio, ya que el cliente tenía que reiniciar constantemente el servidor y esperar la próxima interrupción.

¿Que esta pasando? En este artículo, explicaré y propondré soluciones.

Horarios predeterminados

MySQL necesita crear tablas de tiempo predeterminadas para resolver ciertos tipos de consultas. Las preguntas que requieren una etapa de calificación la mayor parte del tiempo deben basarse en un cronograma. Por ejemplo, cuándo usarlo. AGRUPAR POR, ORDEN PARA o DISTINTO. Dichas solicitudes se ejecutan en dos etapas: la primera es recopilar los datos y ponerlos en un horario, la segunda es ejecutar la clasificación en el horario.

También se necesita una mesa temporal en caso de cualquier UNIÓN declaraciones de evaluación, para VISTA que no puede usar la fusión, para tablas derivadas cuando se usan subpreguntas, para ACTUALIZAR multicapa, y algunos otros casos.

Si la tabla temporal es pequeña, se puede crear en la memoria, de lo contrario, se crea en el disco. No hace falta decir que una tabla temporal en la memoria es más rápida. MySQL crea una tabla en la memoria y, si se vuelve demasiado grande, se convierte en una tabla en el disco. El tamaño máximo de las tablas temporales en la memoria está definido por u tmp_table_size o max_heap_table_size valor, el que es menor. El tamaño predeterminado en MySQL 5.7 es de 16 MB. Si ejecuta las consultas en una gran cantidad de datos, o si no optimiza las consultas, puede aumentar las variables. Al establecer el umbral, considere la RAM disponible y la cantidad de conexiones simultáneas que tiene durante sus picos. no puedes indefinido aumente las variables, ya que en algún momento necesita permitir que MySQL use tablas temporales en el disco.

Nota: La tabla temporal se crea en disco si las tablas involucradas tienen columnas TEXT o BLOB, incluso si el tamaño es menor que el límite configurado.

Motor de almacenamiento de tablas temporales

Hasta MySQL 5.6, todas las tablas temporales del disco se creaban como MyISAM. El horario se crea en la memoria o en el disco, según la configuración, y se descarta inmediatamente al final de la solicitud. Desde MySQL 5.7, se crean como InnoDB automáticamente. Entonces puede confiar en las funciones avanzadas.

El nuevo valor predeterminado es la mejor opción para el rendimiento general y debe usarse en la mayoría de los casos.

Una nueva variable de configuración está disponible para configurar el motor de almacenamiento para tablas temporales: internal_tmp_disk_storage_engine. La variable se puede establecer en innodb (predeterminado si no está establecido) o myisam.

Problema potencial con las tablas temporales de InnoDB

Incluso si usar InnoDB es lo mejor para el rendimiento, podría ser un nuevo problema potencial. En algunos casos especiales, puede experimentar cortes de disco e interrupciones del servidor.

Como cualquier otra tabla de InnoDB en la base de datos, las tablas temporales tienen su propio archivo de espacio de tablas. El nuevo archivo está en la carpeta de datos junto con el tablespace general, con el nombre ibtmp1. Almacena todas las tablas tmp. Un archivo de tablespace no se puede reducir y crece constantemente hasta que ejecute un manual de OPTIMIZE TABLE. tu ibtmp1 no importa, porque no puedes usar OPTIMIZE. La única manera de reducirlo. ibtmp1 tamaño a cero es reiniciar el servidor.

Afortunadamente, incluso si el archivo no se puede reducir, después de la ejecución de una solicitud, la tabla temporal se abandona automáticamente y el espacio en el espacio de tabla se puede reutilizar para otra solicitud entrante.

Considere el siguiente caso:

  • tiene preguntas no optimizadas que requieren la creación de tablas tmp muy grandes en el disco
  • ha optimizado las consultas, pero crean tablas tmp en el disco muy grande, por lo que hace un cálculo objetivo en un conjunto de datos muy grande (estadísticas, análisis)
  • tiene varias conexiones simultáneas que realizan las mismas consultas con la creación de tablas tmp
  • no tienes mucho espacio libre en tu volumen

En tal situación, es fácil ver que el tamaño del archivo ibtmp1 puede aumentar considerablemente y el archivo fácilmente puede quedarse sin espacio libre. Esto sucedía varias veces al día y había que reiniciar el servidor para reducirlo por completo. ibtmp1 espacio de tablas.

No es obligatorio que las solicitudes simultáneas se lancen exactamente al mismo tiempo. Dado que una solicitud con una gran tabla de tiempo tardará varios segundos o minutos en ejecutarse, basta con que las consultas se inicien en diferentes momentos mientras las anteriores aún se ejecutan. Además, tenga en cuenta que cada conexión crea su propio horario, por lo que exactamente la misma solicitud crea otra copia exacta del mismo horario en el tablespace. ¡Agotar el espacio en disco es muy fácil con hojas que no se encogen!

Entonces, ¿qué hace para evitar el agotamiento del disco y las interrupciones?

Una solución trivial: usar un disco más grande

Esto es realmente trivial y puede resolver el problema, pero no es la mejor solución. De hecho, no es tan fácil entender cuál debería ser el tamaño de su nuevo disco. Puede adivinar aumentando el tamaño del disco paso a paso, lo cual es bastante fácil de hacer si su entorno está en la nube o tiene dispositivos virtuales en una plataforma muy grande. Pero no es fácil de hacer en entornos locales.

Pero con esta solución, puedes terminar con gastos innecesarios, así que tenlo en cuenta.

También puede mover el ibtmp1 archivo en un disco dedicado grande configurando la siguiente variable de configuración:

Se requiere reiniciar MySQL.

Tenga en cuenta que la ruta debe especificarse como relativa a la carpeta de datos.

Establece un límite superior para el tamaño de ibtmp1

Por ejemplo:

En este caso, el archivo no puede tener más de 10 GB. Puede eliminar fácilmente las interrupciones, pero esta es una solución peligrosa. Cuando el archivo de datos alcanza el tamaño máximo, las consultas fallan con un error que indica que la tabla está llena. Esto es probablemente malo para sus aplicaciones.

Regrese a MyISAM para tablas temporales en el disco

Esta solución les parece contraintuitivo pero es Podría ser la mejor manera de evitar interrupciones en cuestión de segundos y se garantiza el uso de todas las tablas temporales necesarias.

Puede establecer la siguiente variable en mi.cnf:

Dado que la variable es dinámica, también puede configurarla en tiempo de ejecución:

Volver a MyISAM reduce en gran medida la capacidad de llenar completamente el espacio de su disco. De hecho, se crearán tablas temporales en varios archivos y se abandonarán inmediatamente al final de la aplicación. No más problemas en un archivo en constante crecimiento.

Y si bien siempre existe la posibilidad de ver el mismo problema, por si acaso puedes ejecutar las preguntas al mismo tiempo o realmente muy cerca. En mi caso real, esta fue la solución para evitar todas las interrupciones.

Optimize y sus preguntas

Esto es lo más importante que hay que hacer. Luego hice una copia de seguridad del motor de almacenamiento en MyISAM para mitigar la interrupción. eventos, absolutamente debe tomarse el tiempo para analizar las preguntas.

El objetivo es disminuir el tamaño de las tablas temporales en el disco. El propósito de este artículo no es explicar cómo investigar consultas, pero puede confiar en el registro lento, una herramienta como pt-query-digest y EXPLAIN.

Algunos consejos:

  • crear índices faltantes en las tablas
  • agregue más filtros en las consultas para recopilar menos datos, si realmente no los necesita
  • reescribir las preguntas para optimizar el plan de ejecución
  • Si tiene preguntas muy grandes a propósito, puede usar un administrador de colas en sus aplicaciones para serializar sus ejecuciones o para disminuir la competencia.

Esta será la actividad más larga, pero espero que, después de todas las optimizaciones, pueda restablecer el motor de almacenamiento temporal en InnoDB para un mejor rendimiento.

Conclusiones

A veces, las mejoras pueden tener efectos secundarios inesperados. El motor de almacenamiento InnoDB para discos temporales en el disco es una buena mejora, pero en algunos casos especiales, por ejemplo, si tienes aplicaciones no optimizadas y poco espacio libre, puedes experimentar interrupciones debido al error de «disco lleno». Devolver el motor de almacenamiento tmp a MyISAM es la forma más rápida de evitar interrupciones, pero optimizar las solicitudes es lo más importante para volver a InnoDB lo antes posible. Y sí, incluso una unidad más grande o dedicada puede ayudar. Es una sugerencia trivial, es, pero puede con seguridad Esto ayuda mucho.

Por cierto, hay una solicitud de función sobre el tema: https://bugs.mysql.com/bug.php?id=82556

Otras lecturas:
https://www.percona.com/blog/2007/01/19/tmp_table_size-and-max_heap_table_size/
https://www.percona.com/blog/2017/12/04/tablas-temporales-internas-mysql-5-7/
http://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/
https://dev.mysql.com/doc/refman/5.7/es/tablas-temporales-internas.html

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario