soportan - Cambio de grandes tablas MySQL InnoDB

mysql lento con tablas grandes

Question

Agregar una nueva columna o agregar un nuevo índice puede llevar horas y días para las tablas innodb grandes en MySQL con más de 10 millones de filas. ¿Cuál es la mejor manera de aumentar el rendimiento en las tablas innodb grandes en estos dos casos? Más memoria, ajustar la configuración (por ejemplo, aumentar el sort_buffer_size o innodb_buffer_pool_size ), o algún tipo de truco? En lugar de alterar una tabla directamente, se podría crear una nueva, cambiarla y copiar los datos antiguos como nuevos, como esto, que es útil para tablas ISAM y múltiples cambios :

CREATE TABLE tablename_tmp LIKE tablename;
ALTER TABLE tablename_tmp ADD fieldname fieldtype;
INSERT INTO tablename_tmp SELECT * FROM tablename;
ALTER TABLE tablename RENAME tablename_old;
ALTER TABLE tablename_tmp RENAME tablename;

¿Es recomendable también para las tablas innodb o es solo lo que hace el comando ALTER TABLE?

Answer 1

Cambie el nombre de las tablas a las que se hace referencia.

Si tiene, por ejemplo, table_2 que es hijo de tablename , en ALTER TABLE tablename RENAME tablename_old; table_2 comenzará a apuntar a tablename_old .

Ahora, sin alterar table_2, no puede volver a señalarlo a tablename . Tienes que seguir haciendo modificaciones en cada niño y en la tabla a la que se hace referencia.

Answer 2

Edición 2016: recientemente (agosto de 2016) lanzamos gh-ost , modificando mi respuesta para reflejarla.

Hoy hay varias herramientas que le permiten hacer una tabla alternativa en línea para MySQL. Estos son:

• edición 2016: gh-ost : herramienta de migración de esquema sin trigger de GitHub (descargo de responsabilidad: yo soy el autor de esta herramienta)
• oak-online-alter-table , como parte del kit de openark (descargo de responsabilidad: yo soy el autor de esta herramienta)
• pt-online-schema-change , como parte de Percona Toolkit
• Cambio de esquema en línea de Facebook para MySQL

Consideremos el "normal" `ALTER TABLE`:

Una mesa grande tomará mucho tiempo para ALTER . innodb_buffer_pool_size es importante, al igual que otras variables, pero en una tabla muy grande todas son insignificantes. Solo lleva tiempo.

Lo que MySQL hace para ALTER una tabla es crear una nueva tabla con un nuevo formato, copiar todas las filas y luego cambiar. Durante este tiempo, la mesa está completamente cerrada.

Considera tu propia sugerencia:

Lo más probable es que realice la peor de todas las opciones. ¿Porqué es eso? Como está utilizando una tabla InnoDB, INSERT INTO tablename_tmp SELECT * FROM tablename realiza una transacción. una gran transacción. Creará incluso más carga que la ALTER TABLE normal.

Además, deberá cerrar su aplicación en ese momento para que no escriba ( INSERT , DELETE , UPDATE ) en su tabla. Si lo hace, su transacción completa no tiene sentido.

Qué proporcionan las herramientas en línea

Las herramientas no funcionan igual. Sin embargo, los conceptos básicos son compartidos:

• Crean una tabla "sombra" con un esquema alterado
• Crean y usan desencadenantes para propagar los cambios de la tabla original a la tabla fantasma
• Copian lentamente todas las filas de su mesa a la mesa de sombras. Lo hacen en trozos: digamos, 1,000 filas a la vez.
• Hacen todo lo anterior mientras aún puede acceder y manipular la tabla original.
• Cuando estén satisfechos, intercambian los dos, usando un RENAME .

La herramienta openark-kit ha estado en uso desde hace 3.5 años. La herramienta Percona tiene algunos meses, pero posiblemente más pruebas que la anterior. Se dice que la herramienta de Facebook funciona bien para Facebook, pero no brinda una solución general para el usuario promedio. No lo he usado yo mismo.

Edición 2016: gh-ost es una solución sin desencadenamiento, que reduce significativamente la carga de escritura maestra en el maestro, desacoplando la carga de escritura de migración de la carga normal. Es auditable, controlable, comprobable. Lo desarrollamos internamente en GitHub y lo lanzamos como fuente abierta; estamos haciendo todas nuestras migraciones de producción a través de gh-ost hoy. Ver más here .

Cada herramienta tiene sus propias limitaciones, observe detenidamente la documentación.

La forma conservadora

La forma más conservadora es usar una replicación Master-Master pasiva-activa, hacer ALTER en el servidor en espera (pasivo), luego cambiar roles y hacer ALTER nuevamente en lo que solía ser el servidor activo, ahora convertido en pasivo. Esta es también una buena opción, pero requiere un servidor adicional y un conocimiento más profundo de la replicación.