tables support que optimize not instead hace for doing does defragment better analyze all mysql alter-table performance

mysql - que - table does not support optimize, doing recreate+analyze instead



Optimizando MySQL para ALTER TABLE de InnoDB (6)

En algún momento pronto tendremos que hacer cambios de esquema en nuestra base de datos de producción. Necesitamos minimizar el tiempo de inactividad para este esfuerzo, sin embargo, las declaraciones ALTER TABLE se ejecutarán durante bastante tiempo. Nuestras tablas más grandes tienen 150 millones de registros, el archivo de tabla más grande es 50G. Todas las tablas son InnoDB, y se configuró como un archivo de datos grandes (en lugar de un archivo por tabla). Estamos ejecutando MySQL 5.0.46 en una máquina de 8 núcleos, 16G de memoria y una configuración RAID10.

Tengo algo de experiencia con la optimización de MySQL, pero esto generalmente se centra en las lecturas o escrituras de varios clientes. Hay mucha información que se puede encontrar en Internet sobre este tema, sin embargo, parece que hay muy poca información disponible sobre las mejores prácticas para ajustar (temporalmente) su servidor MySQL para acelerar ALTER TABLE en tablas de InnoDB, o para INSERT INTO. SELECCIONE DE (probablemente usaremos esto en lugar de ALTER TABLE para tener más oportunidades de acelerar un poco las cosas).

Los cambios de esquema que estamos planeando hacer es agregar una columna entera a todas las tablas y convertirla en la clave principal, en lugar de la clave principal actual. Necesitamos mantener la columna ''antigua'' también para que sobrescribir los valores existentes no sea una opción.

¿Cuáles serían los ajustes ideales para hacer esta tarea lo más rápido posible?

  1. Configurar esclavo
  2. Detener la replicación.
  3. Hacer ALTER en esclavo
  4. Deja que el esclavo alcance al maestro
  5. intercambia maestro y esclavo, de modo que el esclavo se convierte en un servidor de producción con una estructura modificada y un tiempo de inactividad mínimo

Desafortunadamente, esto no siempre es tan simple como lo staticsan el staticsan en su respuesta. Crear la nueva tabla mientras está en línea y mover los datos es bastante fácil, y realizar la limpieza en el modo de mantenimiento también es factible, sin embargo, la operación Mysql RENAME manipula automáticamente cualquier referencia de clave externa a su tabla anterior. Lo que esto significa es que cualquier referencia de clave externa a la tabla original seguirá apuntando a lo que sea que cambie el nombre de la tabla.

Por lo tanto, si tiene alguna referencia de clave externa a la tabla que está intentando alterar, está atascado, ya sea alterando esas tablas para reemplazar la nueva tabla, o peor, si esa tabla es grande, tiene que repetir el proceso con grande mesa número dos

Otro enfoque que nos ha funcionado en el pasado ha sido hacer malabares con un conjunto de réplicas Mysql que manejan el alter. No soy la mejor persona para hablar sobre el proceso, pero básicamente consiste en interrumpir la replicación en un esclavo, ejecutar el parche en esa instancia, volver a activar la replicación una vez que se completa la tabla alternativa para que se ponga al día con la replicación. Una vez que la replicación se ponga al día, ponga el sitio en modo de mantenimiento (si es necesario) para cambiar de su maestro a este nuevo esclavo parcheado como la nueva base de datos maestra.

Lo único que no puedo recordar es exactamente cuando apuntas con los otros esclavos al nuevo maestro para que también se aplique la alteración. Una advertencia a este proceso, normalmente lo usamos para cambiar los parches antes de que el código necesite el cambio, o después de que el código haya cambiado para que ya no haga referencia a las columnas / claves.

Es posible que desee ver pt-online-schema-change desde el kit de herramientas de Percona. Esencialmente lo que hace es:

  • Copia la estructura original de la tabla, se ejecuta ALTER.
  • Copia las filas de la tabla antigua a la que se acaba de crear.
  • Utiliza desencadenantes para rastrear y sincronizar cambios mientras se copia.
  • Cuando todo está terminado, cambia las tablas cambiando el nombre de ambas.

Funciona muy bien para bases de datos de una sola instancia, pero puede ser bastante complicado si usa la replicación y no puede darse el lujo de detener esclavos y reconstruirlos más tarde.

También hay un buen seminario sobre esto here .

PD: Sé que es una pregunta antigua, solo respondo en caso de que alguien haga esto a través del motor de búsqueda.

Necesitas pensar en tus requerimientos un poco más cuidadosamente.

En el nivel más simple, la forma "más rápida" de cambiar la tabla es hacerlo en la menor cantidad posible de instrucciones ALTER TABLE , preferiblemente una. Esto se debe a que MySQL copia los datos de una tabla para cambiar el esquema y hacer quince cambios mientras que hacer una sola copia es obviamente (y realmente lo es) más rápido que copiar la tabla quince veces, haciendo un cambio a la vez.

Pero sospecho que está preguntando cómo hacer este cambio con la menor cantidad de tiempo de inactividad. De la forma en que lo haría, básicamente sintetizaría la forma en que funcionaría un ALTER TABLE sin bloque. Pero tiene algunos requisitos adicionales:

  1. necesita una forma de realizar un seguimiento de los datos agregados y modificados, como con un campo de fecha "modificado" para el último, o un campo AUTO_INCREMENT para el primero.
  2. necesita espacio para tener dos copias de su tabla en la base de datos.
  3. necesita un período de tiempo en el que las modificaciones de la tabla no vayan demasiado lejos de una instantánea

La técnica básica es como sugirió, es decir, utilizando INSERT INTO ... SELECT ... Al menos está al frente porque está comenzando con una tabla InnoDB, por lo que SELECT no se bloqueará. Recomiendo hacer ALTER TABLE en la nueva tabla vacía, lo que permitirá que MySQL vuelva a copiar todos los datos, lo que significará que debe enumerar todos los campos correctamente en la declaración INSERT INTO ... SELECT ... Luego puedes hacer una simple sentencia RENAME para intercambiarla. Luego debe hacer otro INSERT INTO ... SELECT ... WHERE ... y tal vez una UPDATE ... INNER JOIN ... WHERE ... para capturar todos los datos modificados. Debe hacer INSERT y UPDATE rápidamente o su código comenzará a agregar nuevas filas y actualizaciones a su instantánea que interferirán con su actualización. (No tendrá este problema si puede poner su aplicación en modo de mantenimiento durante unos minutos antes de que RENAME ).

Aparte de eso, hay algunas configuraciones clave y relacionadas con el búfer que puede cambiar para una sola sesión que pueden ayudar a mover la información principal. Cosas como read_rnd_buffer_size y read_buffer_size serían útiles para aumentar.

Probé varias estrategias para acelerar una tabla alternativa. Eventualmente obtuve un aumento de velocidad de 10x en mi caso particular. Los resultados pueden o no aplicarse a su situación. Sin embargo, en base a esto, sugeriría experimentar con los parámetros de tamaño de búfer / archivo de registro de InnoDB.

En resumen, solo aumentar innodb_log_file_size e innodb_log_buffer_size tuvo un efecto medible (¡Cuidado! Cambiar innodb_log_file_size es riesgoso . Mire más abajo para obtener más información).

Basado en la velocidad de datos de escritura aproximada (iostat) y la actividad de la CPU, el cuello de botella se basó en Io, pero no en el rendimiento de datos. En los 500 más rápidos que se ejecutan, el rendimiento de escritura es al menos en el mismo campo de juego que cabría esperar del disco duro.

Probado optimizaciones de rendimiento:

Cambiar innodb_log_file_size puede ser peligroso. Ver http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/ La técnica (movimiento de archivo) explicada en el enlace funcionó muy bien en mi caso.

También vea http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ y http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ para obtener información sobre innodb y los tamaños de registro de ajuste. Un inconveniente de los archivos de registro más grandes es un tiempo de recuperación más largo después de la caída.

Ejecuciones de prueba y tiempos aproximados:

  • La carga simple de datos a una tabla recién creada: 6500s
  • cargar datos w. innodb_log_file_size = 200M, innodb_log_buffer_size = 8M, innodb_buffer_pool_size = 2200M, autocommit = 0; unique_checks = 0, foreign_key_checks = 0: 500s
  • cargar datos w. innodb_log_file_size = 200M, innodb_log_buffer_size = 8M: 500s
  • Mesa recta recta equivalente w. datainnodb_log_file_size = 200M, innodb_log_buffer_size = 8M: 500s

Detalles de la prueba : Tabla: InnoDB, 6M filas, 2.8G en el disco, archivo único (opción innodb_file_per_table), la clave principal es 1 entero, +2 restricciones / índices unque, 8 columnas, avg. Longitud de fila 218 bytes. Servidor: Ubuntu 12.04, x86_64, máquina virtual, 8 núcleos, 16 GB, disco de grado de consumidor sata, sin redada, sin actividad de base de datos, actividad minúscula de otros procesos, actividad minúscula en otras máquinas virtuales mucho más pequeñas. Mysql 5.1.53. La configuración inicial del servidor es bastante predeterminada, excepto por el aumento de innodb_buffer_pool_size de 1400M. La tabla alterar añade 2 pequeñas columnas. No registré la tabla de alteraciones sin procesar, sino que experimenté con una declaración equivalente de datos de carga equivalente, finalmente hice la tabla de modificaciones directas y obtuve un resultado comparable.

Esta pregunta está relacionada con al menos las siguientes preguntas:

Realmente no sé cómo optimizar eso, pero generalmente es una buena práctica poner el sitio en modo fuera de línea antes de realizar dichas actualizaciones.

Luego, puede ejecutar sus scripts de base de datos a las 3 am, por lo que no debería importar mucho si el tiempo de inactividad es mucho más largo de lo ideal.