mysql - comandos - Throttle ALTER TABLE utilización del disco

clusvcadm

Question

Empezaré con algo de la página MySQL Online DDL Limitations :

No hay ningún mecanismo para pausar una operación DDL en línea o para acelerar el uso de E / S o CPU para una operación DDL en línea.

Sin embargo, todavía estoy interesado en soluciones que podría haber perdido.

La situación: los índices son cada vez más grandes y se agrandan tanto que no habrá suficiente memoria para las consultas que se utilizan, lo que hace que la E / S del disco se dispare y todo caiga en un caos total. Se han creado nuevos índices compuestos que son más pequeños, pero el problema es ejecutar ALTER TABLE sin romper nada.

Los hechos son los siguientes:

1. Es una tabla InnoDB.
2. La tabla no tiene clave principal o índice único.
3. Ninguna combinación de columnas es adecuada como clave principal o índice único.
4. La tabla no tiene claves externas.
5. La tabla está dividida por mes (actualmente 50).
6. La tabla debe aceptar escrituras en todo momento.
7. Las últimas particiones de 3-6 deben aceptar lecturas.
8. Hay una columna de id , pero esto no es único.
9. La tabla consiste en aproximadamente 2 mil millones de filas.
10. La partición del mes actual es la única que recibe escrituras.
11. Las particiones se hacen con 1 mes de anticipación; siempre hay una partición vacía.

The SHOW CREATE TABLE (no incluí todas las particiones):

CREATE TABLE `my_wonky_table` (
`id` bigint(20) unsigned NOT NULL,
`login` varchar(127) DEFAULT NULL,
`timestamp` int(10) unsigned NOT NULL,
`ip` varchar(32) CHARACTER SET ascii DEFAULT NULL,
`val_1` int(10) unsigned DEFAULT NULL,
`val_2` varchar(127) DEFAULT NULL,
`val_3` varchar(255) DEFAULT NULL,
`val_4` varchar(127) DEFAULT NULL,
`val_5` int(10) unsigned DEFAULT NULL,
KEY `my_wonky_table_id_idx` (`id`),
KEY `my_wonky_table_timestamp_idx` (`timestamp`),
KEY `my_wonky_table_val_1_idx` (`val_1`,`id`),
KEY `my_wonky_table_val_2_idx` (`val_2`,`id`),
KEY `my_wonky_table_val_4_idx` (`val_4`,`id`),
KEY `my_wonky_table_val_5_idx` (`val_5`,`id`),
KEY `my_wonky_table_ip_idx` (`ip`,`id`),
KEY `my_wonky_table_login_idx` (`login`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

En cuanto a las consultas: siempre es SELECT en la id , con todo lo demás utilizado para filtrar.

Lo que me gustaría evitar:

• Desactivando la instancia de la base de datos.
• E / S de disco de 100%

Pensé en usar la herramienta pt-online-schema-change para acelerar, pero me topé con el muro sin clave principal. Una solución diferente sería hacer esto en código, moviendo efectivamente los desencadenantes a la base de código y copiando lentamente datos usando fragmentos algo extraños (por ejemplo, trozos de una hora de datos utilizando una columna de marca de tiempo) porque no hay un índice único.

¿Hay otras soluciones y / o herramientas disponibles?

Answer 1

1. Cree una new tabla similar a la tabla real , pero con los índices revisados. Incluya una PRIMARY KEY para que no quede atrapado de nuevo. - Este es el ALTER , pero aún no es el "populate".
2. En la nueva tabla, use particiones trimestrales o anuales para cosas viejas; mensual para particiones actuales y (posteriores) futuras. - Esto es para mantener el número total de particiones. Mi regla de oro es "no más de 50 particiones". (Avíseme si tiene un problema con este plan).
3. Escriba un script para copiar lentamente todos los datos de las particiones antiguas en la new tabla. Mi consejo sobre la fragmentación puede ser útil aquí.
4. Justo antes de quedar atrapado, crea una nueva partición. Pero no copie de eso todavía. Detenga el script "copiar" al final de la partición anterior.
5. Cuando esté atrapado excepto por esta nueva partición , deje de escribir.
6. Copia la última partición - Aquí es donde el paso # 4 vale la pena.
7. Cambio atómico: RENAME TABLE real TO old, new TO real; . Y activa la escritura de nuevo.

Se recomienda encarecidamente escribir todos los guiones y practicar en otras máquinas. La práctica puede ser en un pequeño subconjunto del total, pero debe tener al menos unas pocas particiones.

Answer 2

Presento esto como una respuesta separada, ya que la parte más interna es totalmente diferente.

Al igual que con mi otra respuesta, necesita la new tabla con nuevos índices, más un script para copiar toda la información. Sin embargo, la carne es simular el disparador en su aplicación .

Afortunadamente, tienes id , aunque no es la PRIMARY KEY . Y, aunque no sea UNIQUE , puede usarse (suponiendo que no tenga miles de filas con el mismo ID; si lo hace, podemos hablar más).

El "script de copia" y la aplicación hablan entre sí.

El script de copia está en un bucle largo:

• SELECT GET_LOCK(''copy'', 5), high_water_mark FROM tbl; - (o algún otro tiempo de espera)
• Copie las filas con id BETWEEN high_water_mark AND high_water_mark + 999 .
• UPDATE tbl SET high_water_mark = high_water_mark + 1000;
• Pausa brevemente (1 segundo?)
• Loop hasta que no haya más ids

La aplicación, al leer, continúa leyendo en la tabla anterior. Pero al escribir, lo hace:

• SELECT GET_LOCK(''copy'', 5), high_water_mark FROM tbl; - (o algún otro tiempo de espera)
• Si se agota el tiempo, algo necesita ser arreglado.
• Escribir en la tabla anterior - (por lo tanto, lee continuar trabajando)
• Si id <= high_water_mark , escriba también en la nueva tabla.
• SELECT RELEASE_LOCK(''copy'');

Monitorea el progreso. En algún momento, tendrá que detener todo, copiar las últimas filas y hacer la RENAME TABLE .

No conozco sus valores óptimos para tiempos de espera, suspensión o tamaño de fragmento. Pero no creo que sea sensato que el tamaño del fragmento sea mayor que 1K.

Esta técnica tiene ventajas para una variedad de cambios que podría necesitar hacer en el futuro, así que mantenga las agallas en su lugar.

Answer 3

Esto se reducirá a la variante y versión de MySQL que esté utilizando, pero si es un hilo por conexión (my.cnf thread_handling=one-thread-per-connection , que podría ser el predeterminado en su compilación), y puede ponerlo su carga de trabajo ALTER TABLE en una nueva conexión, entonces la carga de trabajo es un PID único, y puede usar ionice / renice en él.

Tengo una respuesta algo cutre, pero es menos invasiva que las otras opciones.

Si mira ps -eLf |grep mysql , puede ver los subprocesos / procesos ligeros, y solo necesita averiguar qué PID pertenece a su conexión específica. Si se conecta a través de TCP, puede hacer coincidir su puerto de conexión local y asignarlo a lsof para encontrar el hilo específico. Otras formas son posibles con / strace, systemtap y más, o ejecutando una consulta inicial que puede observar.

Después de eso, puede usar ionice / renice para afectar el PID en el sistema. Realmente querrás asegurarte de capturar qué PID era, y restablecer el nivel agradable y de prioridad luego, para no afectar a nada más.

Al igual que con los demás, realmente necesita remodelar esta tabla a largo plazo. Las particiones son útiles, pero no el final del juego, ya que está ejecutando 1.3TiB de datos en línea, y usted declara que solo necesita leer de las más recientes 3-6 particiones. Viniendo de MySQL antes de que se agregaran las particiones nativas, creo que este sería un buen caso para una VISTA y tablas separadas (actualice atómicamente la VISTA cuando necesite volcar). También le permitiría mover trivialmente algunas tablas antiguas al almacenamiento fuera de línea.