Probablemente este no sea un art铆culo, sino una breve nota sobre algunas caracter铆sticas del trabajo con tablas grandes en MySQL.
La raz贸n para escribir fue la adici贸n aparentemente cotidiana de una nueva columna a la tabla. Pero todo result贸 no ser tan simple como se esperaba.
Entonces, una noche, para no molestar a nuestros queridos clientes, tuvimos que agregar una columna a la mesa.
Para que quede m谩s claro, las caracter铆sticas de la tabla y la base:
- tama帽o de la mesa 110Gb
- n煤mero de filas: 7.5 millones
- motor de almacenamiento: InnoDB
- hay dos servidores sql conectados de acuerdo con el esquema maestro-esclavo, mientras que el maestro est谩 en el SSD y el esclavo est谩 en el HDD
Parece ser una soluci贸n obvia para agregar una columna: Alterar tabla.
alter table table_name add source varchar(32)
Lo usamos (s铆, entendimos que era malo, pero en este caso particular los riesgos eran m铆nimos).
Los resultados fueron bastante desagradables:
- en el asistente, el proceso de agregar una columna tom贸 aproximadamente una hora (!)
- en el esclavo, comenz贸 despu茅s del final del proceso en el maestro y dur贸 aproximadamente 8 horas (!!)
- durante la tabla alter, la replicaci贸n de datos (!!!) se detuvo por completo en el esclavo
Pero hay un lado positivo: una peque帽a ventaja fue que despu茅s de agregar una columna, el tama帽o de la tabla disminuy贸 en un 10%.
En los gr谩ficos a continuaci贸n es claramente visible.
Gr谩fico de carga de CPU en el asistente.
Gr谩fico de carga de CPU en el esclavo.
Retraso en la replicaci贸n.驴Qu茅 problemas les esperan a quienes hacen esto en las mesas de batalla?
Primero, durante la duraci贸n de Alter Table, no puede escribir datos en la tabla (pero puede leerlos). De hecho, depende de la versi贸n de MySQL, en el 煤ltimo no lo es, pero debe comprender de qu茅 es exactamente su versi贸n para evitar problemas.
En consecuencia,
si la tabla es grande, entonces el tiempo de indisponibilidad ser谩 significativo (como con nosotros, cuando se usa el SSD, tom贸 una hora y en un disco normal, 8 horas), lo que es poco probable que sus clientes esperen.
En segundo lugar, como en nuestro caso, durante la ejecuci贸n de Alter Table, la
sincronizaci贸n de todas las tablas , no solo la que cambiamos,
se detuvo por
completo en el esclavo. Por lo tanto, si sus datos en el segundo servidor son cr铆ticos y deben estar actualizados, corre el riesgo de quedarse sin actualizaciones con todas las consecuencias resultantes.
Otro punto no obvio que encontramos al agregar una columna (pero esta fue otra vez):
se necesita espacio en disco adicional .
El hecho es que algunos cambios en las tablas recrean la tabla desde cero, por lo que no necesita menos espacio que una tabla existente. Para mesas grandes, respectivamente, se necesita mucho espacio, por decirlo suavemente. Seg煤n la documentaci贸n, se crea una tabla temporal en el mismo directorio que el original.
Adem谩s, durante la ejecuci贸n de todo tipo de Alter Table, todos los cambios se escriben en el archivo de registro, de modo que despu茅s de los cambios, los datos se pueden transferir durante el tiempo durante el cual se realiz贸 la operaci贸n. Y aqu铆, tambi茅n, puede esperar una sorpresa desagradable: si la tabla cambia durante mucho tiempo y el volumen de operaciones es grande, entonces no solo puede finalizar el espacio en disco, sino que tambi茅n se puede exceder el l铆mite de tama帽o de archivo especificado en la configuraci贸n de SQL. En cualquier caso, "la operaci贸n DDL en l铆nea falla y las operaciones DML concurrentes no confirmadas se revierten" le espera.
Nos enfrentamos al hecho de que el directorio para archivos temporales era peque帽o, como resultado tuvimos que redefinir
innodb_tmpdir .
Para ver hacia d贸nde apunta actualmente la variable, puede hacer esto:
select @@GLOBAL.innodb_tmpdir;
Tenga en cuenta que el tama帽o del directorio temporal tambi茅n puede ser necesario el tama帽o de una tabla + 铆ndices. En general, abastecerse de espacio.
Para no repetir la documentaci贸n, lea con m谩s detalle en
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html驴Pero c贸mo hacerlo? De hecho, no existe una receta 煤nica para todas las ocasiones.
Una de las opciones posibles, como lo hacemos para las tablas que no son cr铆ticas para la actualizaci贸n:
- Cree una nueva tabla con la estructura deseada.
- Rellene los campos de la tabla anterior.
- Eliminar o renombrar una tabla antigua
- Renombrar el nuevo
Repito que esto funciona para tablas de actualizaci贸n no cr铆ticas. Y al mismo tiempo evita el bloqueo de replicaci贸n. Debe tenerse en cuenta que completar una nueva tabla debe hacerse de tal manera que permita que la replicaci贸n contin煤e, y dado que se ejecuta secuencialmente, no puede hacerlo con una sola expresi贸n sql, debe dividirla en varias consultas peque帽as entre las cuales se realizar谩 la replicaci贸n de otros datos. En otros casos, son posibles otras opciones, tal vez alguien comparta los comentarios.
UPD
Syavadee sugiri贸 usar el cambio de esquema en l铆nea percona. De hecho, implementa el algoritmo descrito anteriormente con extras adicionales.
UPD
Arheops recomienda habilitar la replicaci贸n paralela / gtid para resolver problemas de replicaci贸n.
Bueno, por cierto, a veces, para comprender qu茅 tan grande es la tabla y cu谩ntas filas hay, debe hacer
select count(*) from table_name
Pero en tablas grandes y cargadas, esta tampoco es la operaci贸n m谩s r谩pida, especialmente cuando tiene medio mill贸n de filas o m谩s.
Por lo tanto, para una estimaci贸n aproximada del volumen, puede usar el siguiente m茅todo:
SHOW TABLE STATUS FROM express where name='table_name'
Desafortunadamente, en el motor InnoDB, el tama帽o resultante puede diferir en un 50 por ciento (en nuestro caso, con la tabla anterior, el n煤mero real de registros es de aproximadamente 7,5 millones, y este m茅todo mostr贸 solo 5 millones), pero esto es bastante adecuado para una estimaci贸n indicativa.
Eso es todo, espero que esta nota ayude a alguien a evitar grandes problemas con comandos SQL supuestamente inofensivos.