copiar a la tabla tmp que modifica la tabla en el incremento automático

Question

Tenemos un problema con MySql (5.5.x) y espero que puedas ayudarnos. En algún momento durante el día, noto un proceso con el estado "copiar a la tabla tmp" y esta consulta:

ALTER TABLE `MyTableName` AUTO_INCREMENT=200000001

Después de esto, todas las demás consultas obtienen un "Esperando bloqueo de metadatos de tabla", y todas las consultas se congelan y no se procesa nada.

Necesito matar ese proceso, y desde ese punto todas las consultas se reiniciaron.

¿Por qué? ¿Como puedo solucionar este problema?

Answer 1

En MySQL 5.5, una ALTER TABLE como la que ejecutó hace una copia de toda la tabla. Cuanto más grande sea la mesa, más tiempo llevará. Especialmente si tiene un almacenamiento lento.

¿Cuál es el tamaño de su mesa (puede obtener esto SHOW TABLE STATUS LIKE 'MyTableName'\Gy mirar el data_length + index_length)?

Acabo de hacer una prueba en mi computadora portátil. Llené una tabla en una instancia de MySQL 5.5, hasta que el tamaño de la tabla es de aproximadamente 175 MB. La ejecución de una tabla de modificación para establecer el valor de incremento automático tarda entre 5 y 6 segundos. Sus resultados pueden ser diferentes, dependiendo de la potencia de su servidor y la velocidad de almacenamiento.

Mientras se ejecuta la tabla de modificación, el hilo que realiza esa operación mantiene un bloqueo de metadatos en la tabla, que bloquea todas las demás consultas, incluso las declaraciones SELECT de solo lectura.

ALTER TABLE se mejoró en 2013, como una característica de MySQL 5.6. Algunos tipos de modificaciones se optimizaron para que se realicen "in situ", de modo que no tengan que copiar toda la tabla si no es necesario. Cambiar AUTO_INCREMENT es una de estas operaciones. No importa qué tan grande sea la tabla, si modifica la tabla para cambiar AUTO_INCREMENT, es rápido porque solo cambia un atributo de la tabla, sin requerir copiar ninguna fila de datos.

Ver https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

En MySQL 5.5, estas optimizaciones no se implementaron. Por lo tanto, cualquier otra mesa lleva mucho tiempo, proporcional al tamaño de la mesa.

Recomendaría que la mejor manera de solucionar este problema en su caso es actualizar a una versión más nueva. MySQL 5.5 está más allá de su final de vida. Incluso MySQL 5.6 está llegando al final de su vida útil en febrero de 2021. Es hora de actualizar.

Si no puede actualizar, debe investigar qué cliente está haciendo esta instrucción ALTER TABLE. Dijiste que lo notaste en algún momento del día. Rastrea eso. En la lista de procesos, le indicará el host del cliente desde dónde se está ejecutando esa declaración SQL. También le dirá el usuario de MySQL con el que inició sesión. Es posible que también deba hacer una búsqueda en su código fuente de cualquier aplicación o script que use esta base de datos. O pregúntale a tus compañeros de equipo.

Una vez que haya encontrado al cliente que está haciendo ALTER TABLE, intente cambiar la hora en que el cliente ejecuta esta declaración a una hora del día en la que ALTER TABLE no bloqueará consultas importantes. ¿O preguntar al desarrollador responsable si es realmente necesario hacer esta tabla de cambios con tanta frecuencia?

Answer 2

El problema podría deberse a un reinicio del servidor , ya que InnoDb almacena el último índice de incremento automático en la memoria y lo vuelve a calcular al reiniciar el servidor ( InnoDB AUTO_INCREMENT Counter Initialization ):

Si especifica una columna AUTO_INCREMENT para una tabla InnoDB, el identificador de la tabla en el diccionario de datos InnoDB contiene un contador especial llamado contador de incremento automático que se utiliza para asignar nuevos valores a la columna. Este contador se almacena solo en la memoria principal, no en el disco.

Para inicializar un contador de incremento automático después de reiniciar el servidor, InnoDB ejecuta el equivalente de la siguiente declaración en la primera inserción en una tabla que contiene una columna AUTO_INCREMENT.

SELECCIONE MAX (ai_col) FROM table_name PARA ACTUALIZAR;

InnoDB incrementa el valor recuperado por la declaración y lo asigna a la columna y al contador de incremento automático de la tabla. De forma predeterminada, el valor se incrementa en 1. Este valor predeterminado puede ser anulado por la configuración de auto_increment_increment.

Si la tabla está vacía, InnoDB usa el valor 1. Este valor predeterminado puede ser anulado por la configuración de auto_increment_offset.

Mire los registros de mysql e intente averiguar si el servidor se está reiniciando , lo que hace que la tabla ALTER reinicie el contador de autoincremento.

Intente reiniciar el servidor mysql para ver si obtiene este comportamiento .

Si este es el caso, puede intentar:

• Evite reiniciar el servidor mysql, tal vez haya un proceso cron que lo reinicie una vez al día
• Actualice su versión de mysql a 8 ( Autoicrement guardado en metadatos de tabla ):

En MySQL 8.0, este comportamiento se cambia. El valor actual máximo del contador de incremento automático se escribe en el registro de rehacer cada vez que cambia y se guarda en una tabla del sistema privado del motor en cada punto de control. En un reinicio del servidor después de un apagado normal, InnoDB inicializa el contador de incremento automático en memoria utilizando el valor de incremento automático máximo actual almacenado en la tabla del sistema del diccionario de datos.

• Puede intentar acelerar las operaciones de "copiar a la tabla tmp", omitir la copia a la tabla tmp en el disco mysql

Referencias

¿Cómo hacer que la tabla InnoDB no restablezca el autoincremento al reiniciar el servidor?

https://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself