Cómo evitar el problema de la falta de disponibilidad de la tabla cuando MySQL modifica la estructura de la tabla
Cuando MySQL modifica la estructura de la tabla, puede interrumpir el funcionamiento normal del producto, afectar la experiencia del usuario e incluso provocar la pérdida de datos. No todos los administradores de bases de datos, programadores y administradores de sistemas saben lo suficiente sobre MySQL para evitar esta situación. Los administradores de bases de datos a menudo experimentan interrupciones de 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 una comprensión completa del funcionamiento interno de Mysql.
El hecho es:
Modificar la estructura de la tabla directamente bloqueará 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, por lo que no siempre está en línea. Las tablas se bloquearán.
Incluso cuando se usa Percona Toolkit (que permite la modificación en línea de archivos de definición), hay varios pasos que bloquearán las tablas.
El equipo de desarrollo de Percona MySQL Server alienta a los usuarios a hacer esto al planificar o ejecutar migraciones de bases de datos. Comuníquese con nosotros antes. Nuestro objetivo es brindar la mejor solución en función de cada situación proporcionada por nuestros usuarios. Diseñado para evitar el bloqueo de tablas cuando los usuarios realizan DDL en tablas muy grandes para garantizar que la aplicación funcione correctamente y al mismo tiempo intentar mejorar el tiempo de respuesta o aumentar la funcionalidad del sistema. El peor de los casos es garantizar que los sistemas que no pueden dejar de funcionar estén funcionando durante las horas de mayor actividad comercial.
La mayoría de los instaladores que utilizamos son aún más pequeños que MySQL 5.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 un sistema donde los archivos de definición de especificaciones se puedan modificar en línea. MySQL 5.6 resuelve este problema al reducir la cantidad de escenarios de reconstrucción y bloqueo de tablas, pero este enfoque no cubre todas las operaciones posibles, como la inevitable necesidad de reconstruir toda la tabla al modificar el tipo de datos de una columna. Przemysaw y Malkowski discutieron las definiciones de tiempo de ejecución de MySQL 5.6 con el mayor detalle posible el año pasado.
Con las nuevas características de MySQL 5.7, buscamos 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 MySQL 5.6, el mejor consejo es mirar la matriz de conteo y familiarizarse con la realización de cambios de definición fuera de MYSQL, y la buena noticia es que somos bastante buenos resolviendo esto. problema.
Para ser honesto, las operaciones de bloqueo de tablas a menudo se pasan por alto cuando se trata de tablas de 30M, preferimos modificarlas directamente, pero también es necesario considerar las tablas de 30G y 300G. La operación directa puede ser mejor cuando la utilización del sistema no es alta o los requisitos de tiempo de bloqueo no son altos. Sin embargo, a menudo nos encontramos con SQL que debe ejecutarse de inmediato o que es necesario agregar índices con urgencia para reducir los tiempos de carga debido a problemas de rendimiento.
¿Es necesario cambiar la definición de la tabla mientras el sistema está en línea?
Como se mencionó anteriormente, cambiar la definición de la tabla en línea 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 la tabla utiliza activadores. Es importante entender cómo funciona pt-osc en nuestro proyecto, echemos un vistazo al código fuente:
[moore@localhost]$ egrep 'Step' pt-online-schema-change# Capítulo Paso 1 : Crear una nueva tabla
# Paso 2: Modificar para borrar la tabla. Esto debería ser más rápido, # Paso 3: Cree un activador para capturar cambios en la tabla original
# Paso 4: Copie los datos. # Paso 5: Cambiar el nombre de la tabla:
# Paso 6: Actualizar la clave externa (si es una subtabla).
# Paso 7: Eliminar la tabla antigua.
Los pasos 3 a 5 anteriores pueden provocar un tiempo de inactividad del sistema, por lo que los enfatizo aquí. Pero el paso 6 está diseñado para hacer que las actualizaciones de claves externas sean una operación de bucle, evitando así la necesidad de reconstruir implícitamente la tabla cuando se actualiza la relación. Hay muchas formas de garantizar las restricciones de integridad de la tabla; consulte la documentación de pt-osc para obtener más detalles. Obtenga una vista previa de la estructura de la tabla, incluidas las restricciones, antes de comenzar y aprenda cómo minimizar el impacto de modificar la definición de la tabla.
Recientemente, notificamos a un usuario con un sistema altamente concurrente y transaccional para que ejecutara pt-osc en una tabla de datos de gran tamaño. Este incidente era algo común para ellos y, unas horas más tarde, nuestro personal de atención al cliente fue informado de que el cliente estaba experimentando un problema con un número de conexiones superior al máximo. ¿Cómo surgió este problema? Cuando pt-osc llega al paso 5, intenta bloquear los datos y cambiar el nombre de las tablas original y oculta, pero esto no se hace inmediatamente cuando se abre la transacción, por lo que el hilo se pone en cola detrás del cambio de nombre. Esto se refleja en las aplicaciones de los usuarios como tiempo de inactividad del sistema. Después de emitir 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 estipula que cuando se abre una transacción, bloqueará los datos de todas las tablas que se utilizarán (independientemente del motor de almacenamiento) y liberará cuando se confirma la transacción. Esto garantiza que la definición de la tabla no se modifique mientras la transacción esté abierta.
A largo plazo, podemos evitar esto utilizando nuevas tecnologías como opciones pt-osc no predeterminadas, lo que significa que la tabla original no se eliminará y los datos serán reemplazados por la nueva tabla. . Esta federación elimina las tablas ocultas y los activadores, y deberíamos fomentar que las operaciones de cambio de nombre sean atómicas.
Calibración: la versión 2.2 de la herramienta percona agrega una nueva variable ?tries y una variable ?set-vars ***, que se implementan juntas para dar cuenta de varios puntos que pueden bloquear la operación OSC de las tablas.
wait_timeout=10000innodb_lock_wait_timeout=1lock_wait_timeout=60
Al usar "tries", podemos identificar con precisión la operación, el número de intentos y el tiempo de espera entre intentos. Esta combinación garantiza que pt-osc finalice la sesión que está esperando en el momento adecuado, garantiza que la pila de subprocesos esté libre y nos proporciona operaciones repetidas para obtener y administrar los cambios causados por activadores, cambios de nombre y claves externas modificadas. bloqueo generado. Pruebe swap_tables:5:0.5, drop_triggers:5:0.5
La documentación está aquí
Aclara el hecho de que incluso utilizando herramientas como pt-osc uno debe comprender completamente lo que está intentando problema resuelto. El siguiente diagrama de flujo le ayudará a comprender las consideraciones para modificar la estructura de la base de datos MYSQL. Aunque algunos gráficos no están etiquetados, como espacio en disco, carga de IO, etc., lea las recomendaciones con atención.
Elija las operaciones DDL adecuadas
Asegúrese de comprender claramente cómo la modificación de la estructura de la tabla afectará a su sistema y elija el enfoque correcto para minimizar el impacto. A veces, esto significa posponer los cambios hasta que el sistema llegue a un punto en el que se utilice con poca frecuencia o utilizar herramientas que no bloqueen las tablas durante las operaciones. Cuando utiliza activadores en una tabla, normalmente modifica la estructura de la tabla directamente.
-En la mayoría de los casos, pt-osc es exactamente lo que necesitamos
-En muchos casos, se necesita pt-osc, pero el uso debe ajustarse ligeramente
p>
: en los pocos casos en los que pt-osc no es adecuado, debemos considerar bloquear los cambios localmente o usar un transporte para replicar los cambios en un conjunto de réplicas.
-------------------------------- línea divisoria--- ----------------------------------
Instalar MySQL en Ubuntu 14.04
p>La guía definitiva de MySQL (segunda edición)"Borrar PDF escaneado en chino
Ubuntu 14.04 LTS Instalación de LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Creación de un Servidor maestro-esclavo MySQL en Ubuntu 14.04
Ubuntu 12.04 LTS construyendo un clúster MySQL distribuido de alta disponibilidad
Instalación fuente de MySQL 5.6 y Python-MySQLdb en Ubuntu 12.04 Instalación del código fuente
Instalación binaria universal de MySQL-5.5.38
------------------------ ------ --------- Línea dividida - ---------------------------------- ---
Este artículo se actualiza permanentemente en el siguiente enlace: