¿Cómo puede MySQL modificar la estructura de la tabla para evitar el problema de las tablas inutilizables?

Cuando MySQL modifica la estructura de la tabla, puede interrumpir el funcionamiento normal del producto, afectar la experiencia del usuario y, lo que es peor, pueden perderse datos. No todos los administradores de bases de datos, programadores y administradores de sistemas saben que MySQL puede evitar esta situación. Los administradores de bases de datos a menudo encuentran este tipo de interrupción de la producción cuando los scripts de actualización modifican las capas de aplicación y base de datos, o cuando administradores y desarrolladores sin experiencia modifican archivos de especificaciones sin mucha comprensión del funcionamiento interno de MySQL.

El hecho es:

La tabla se bloqueará durante el proceso de modificación directa de la estructura de la tabla (antes de la versión 5.6).

El lenguaje de definición de datos en línea no siempre está en línea en la versión 5.6 y también bloqueará las tablas.

Incluso utilizando Percona Toolkit (modificación en línea de archivos de definición), existen varios pasos para bloquear la tabla.

¿Percona MySQL? El equipo de desarrollo del servidor anima a los usuarios a comunicarse con nosotros antes de planificar o realizar una migración de base de datos. Nuestro objetivo es proporcionar la mejor solución basada en diversas situaciones dadas por los usuarios. El propósito es evitar que los usuarios bloqueen la tabla cuando ejecutan DDL en una tabla muy grande, asegurando que la aplicación pueda ejecutarse normalmente, mientras se intenta mejorar el tiempo de respuesta o aumentar la funcionalidad del sistema. El peor de los casos es garantizar que los sistemas que no puedan soportar el colapso funcionen durante las horas de mayor actividad comercial.

La mayoría de los paquetes de instalación que utilizamos son aún más pequeños que MySQL5.6, lo que nos obliga a probar constantemente nuevos entornos de instalación para minimizar las pérdidas causadas por la migración de la base de datos. Esto puede requerir herramientas que puedan "modificar archivos de definición de especificaciones en línea" para actualizar o modificar archivos de especificaciones. MySQL 5.6 resuelve este problema al reducir los escenarios de reconstrucción y bloqueo de tablas, pero este método no puede cubrir todas las operaciones posibles. Por ejemplo, al modificar el tipo de datos de una columna, es necesario reconstruir toda la tabla. ¿Przemys? Aw y Malkowski discutieron los cambios de definición en MySQL 5.6 el año pasado con el mayor detalle posible.

Con las nuevas características de MySQL 5.7, busque operaciones DDL que no bloqueen tablas, como la optimización de tablas y el cambio de nombre de índice. (Más información)

Para los usuarios de Mysql5.6, el mejor consejo es mirar la matriz numérica para familiarizarse con la realización de cambios de definición fuera de Mysql. La buena noticia es que somos buenos resolviendo este problema.

Para ser honesto, las tablas de bloqueo a menudo se ignoran. Preferimos modificar directamente la tabla cuando operamos una mesa de tamaño 30M, pero se deben considerar las tablas de tamaño 30G y 300G. En los casos en que la utilización no sea alta o los requisitos de tiempo de bloqueo no sean muy altos, la operación directa puede ser mejor. Pero a menudo nos encontramos con un SQL que debe ejecutarse de inmediato, o que es necesario agregar un índice con urgencia para reducir el tiempo de carga debido a problemas de rendimiento.

¿Necesito modificar la definición de la tabla cuando el sistema está en línea?

Como se mencionó anteriormente, la modificación en línea de la definición de la tabla es un módulo en el flujo de trabajo. Suele ser una buena solución, pero hay situaciones en las que no se puede utilizar, como cuando una tabla utiliza activadores. En nuestro proyecto, es importante comprender el proceso de trabajo de pt-osc. Echemos un vistazo al código fuente:

[Moore @ localhost]$ egrep ' Paso ' pt-online-schema-change

#Paso 1: crear una nueva tabla

#Paso 2: Modificar la tabla vacía. Esto debería ser más rápido.

#Paso 3: Crea un disparador para capturar cambios en la tabla original

#Paso 4: Copia los datos.

#Paso 5: Cambiar el nombre de la tabla:

#Paso 6: Si la clave externa es una subtabla, actualice la clave externa.

#Paso 7: Elimina la tabla antigua.

Hice hincapié en los pasos tres a cinco anteriores, que es cuando bloquear la mesa puede provocar que el sistema se detenga.

Sin embargo, el diseño de la actualización de la clave externa en el paso 6 es una operación de bucle, cuyo objetivo es evitar la reconstrucción implícita de la tabla al actualizar la relación. Hay muchas formas de garantizar las restricciones de integridad en las tablas, que se detallan en la documentación de pt-osc. Obtenga una vista previa de la estructura de la tabla, incluidas las restricciones, antes de comenzar, y sepa cómo minimizar el impacto de modificar la definición de la tabla.

Recientemente, informamos a un usuario con un sistema de alta concurrencia y altas transacciones que ejecutara pt-osc en una tabla de datos grande. Esto es normal para ellos. Unas horas más tarde, nuestro personal de atención al cliente fue informado de que el cliente había encontrado un problema al superar el número máximo de conexiones. ¿Cómo surgió este problema? Cuando pt-osc llegue al paso 5, intentará bloquear los datos y cambiar el nombre de las tablas originales y ocultas. Sin embargo, esto no se ejecutará inmediatamente cuando comience la transacción, por lo que el hilo se pondrá en cola después del cambio de nombre. Esto se manifiesta como el cierre del sistema para las aplicaciones de los usuarios. Después de ejecutar el comando de cambio de nombre, la base de datos no puede abrir una nueva conexión y todos los subprocesos se bloquean.

Bloqueo de datos

La versión 5.5.3 explica que cuando se abre una transacción, los datos de todas las tablas que utiliza se bloquearán (independientemente del motor de almacenamiento cuando se realice la transacción). comprometido, el bloqueo se liberará. Esto garantiza que la definición de la tabla no se pueda modificar mientras una transacción esté abierta.

A largo plazo, podemos utilizar algunas tecnologías nuevas para evitar esta situación, como la opción pt-osc no predeterminada, es decir, no eliminaremos la tabla original y cambiaremos los datos a la nueva mesa. Esta unión rompe con las tablas y activadores ocultos, y deberíamos fomentar que las operaciones de cambio de nombre sean atómicas.

Revisión: La versión 2.2 de la herramienta percona agrega una variable-tries? Implementado junto con la variable -set-vars ***, resuelve la situación en la que varias operaciones pt-osc pueden bloquear la tabla. Al conectarse a un servidor de base de datos, pt-OSC (–set-vars) establecerá las siguientes variables de sesión de forma predeterminada.

wait_timeout=10000

innodb _ lock _ wait _ time out = 1

Bloquear tiempo de espera de espera =60

Cuando se usa –- Cuando los intentos podemos identificar la operación, el número de intentos y el tiempo de espera entre intentos. Esta combinación garantiza que pt-osc finalice sus procesos de sesión en espera en el momento adecuado, garantiza que la pila de subprocesos esté libre y nos proporciona operaciones de bucle para adquirir y administrar bloqueos causados ​​por activadores, cambios de nombre y modificación de claves externas.

–Número de tablas de intercambio intentadas: 5:0,5, número de activadores de descarte: 5:0,5

Explica que incluso utilizando herramientas como pt-osc, comprenda completamente lo que está intentando para resolver Las preguntas también son importantes. El siguiente diagrama de flujo le ayudará a comprender las precauciones al modificar la estructura de la base de datos MYSQL. Lea atentamente las sugerencias, aunque algunos dibujos no están marcados, como el espacio en disco, la carga de IO, etc.

Seleccione la operación DDL adecuada.

Asegúrese de comprender claramente el impacto en el sistema al modificar la estructura de la tabla y elija los métodos adecuados para minimizar este impacto. A veces, esto significa posponer los cambios hasta que el sistema no se utilice con frecuencia o utilizar herramientas que no puedan bloquear las tablas durante las operaciones. Cuando hay desencadenantes en la tabla, la estructura de la tabla generalmente se modifica directamente.

-En la mayoría de los casos, pt-osc es exactamente lo que necesitamos.

-pt-osc es necesario en muchos casos, pero su uso debe ajustarse ligeramente.

-En algunos casos, pt-osc no es adecuado, por lo que debemos considerar modificaciones de bloqueo local o cambiar la operación de transferencia a replicación en el conjunto de réplicas.