ALTER TABLE en caliente y sin parada de servicio

El funcionamiento de un ALTER TABLE es sencillo:

1- MySQL bloquea la tabla que se desea modificar.
2- Crea una nueva tabla vacia con la nueva estructura.
3- Copia los datos de la antigua a la nueva.
4- Borra la antigua.
5- Renombra la nueva tabla con el nombre original.

Esto en tablas pequeñas puede ser un proceso de segundos o minutos. En tablas muy grandes, podemos estar hablando de horas. Horas en las cuales las consultas a dicha tabla estarán bloqueadas y las conexiones y peticiones se encolarán. Por lo tanto, hacer un ALTER TABLE en producción puede tener consecuencias bastante desastrosas.

Si tenemos replicación master-slave, podemos hacer el ALTER TABLE por fases. Primero lo hacemos en el esclavo. Cuando termine, promocionamos el esclavo a maestro y el maestro a esclavo. Y vuelta a lanzar el ALTER TABLE en el antiguo maestro (ahora esclavo).

¿Y si no tenemos replicación? Eso es lo que voy a explicar ahora 😀

Estamos ante esta situación... nuestros amigos desarrolladores han tenido la magnifica idea de hacer un ALTER TABLE para modificar el tipo de dato de una columna, a las 15:00 de la tarde y sin posibilidad de parada. La primera opción es cagarnos en sus madres. Una vez terminado ese primer e indispensable paso, haremos uso de las herramientas openark para disminuir las posibilidades de parada al mínimo.

La herramienta que nos ayudará será oak-online-alter-table. Su funcionamiento a alto nivel es el siguiente:

1- Crea una tabla fantasma (o también llamada delta) sobre la que poco a poco va copiando filas de la original.
2- Cuando la copia se completa, se crean triggers AFTER INSERT, AFTER UPDATE, AFTER DELETE en la tabla original.
3- Cuando la sincronización se completa, se cambia una tabla por otra.

Para que esto funcione correctamente se deben cumplir una serie de requisitos:

  • La tabla debe tener al menos una columna con UNIQUE KEY.
  • La nueva tabla comparte una columna UNIQUE KEY con la tabla original.
  • En la tabla original no deben existir triggers AFTER.
  • La tabla no puede tener claves foráneas.
  • El nombre de la tabla no puede superar los 57 carácteres.

En la documentación se ven varios ejemplos de su funcionamiento:

Añadir una key

oak-online-alter-table --table=world.City --alter="ADD KEY(Population)"

Añadir una key v2.0

oak-online-alter-table --table=world.City --alter="ADD KEY(Population)" --chunk-size=5000 --sleep=20

En este caso, hay dos opciones importantes. --chunk-size indica que la primera sincronización se hará en grupos de 5000 filas. Contra más grande sea el número, menos tardará el alter table, pero se producirán bloqueos de tabla más largo. Y al contrario, contra menos filas copiemos, menos bloqueos pero más largo será el proceso. Si es MyISAM se bloqueará la tabla (READ LOCK) durante la copia de dichas filas, si es INNODB se bloquearán únicamente las filas que se están copiando.

--sleep por su parte indica cuantos milisegundos esperar entre cada copia de grupos de filas.

Vamos a lanzar, por ejemplo, el alter table en una tabla llamada "salaries" con casi 3 millones de registros:

CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
  KEY `emp_no_idx` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Antes de poder hacer uso de ella he elimindo las claves foráneas. Procedemos a lanzar el ALTER TABLE como hemos hecho siempre:

mysql> ALTER TABLE salaries MODIFY emp_no VARCHAR(40);
Query OK, 2844047 rows affected (57.75 sec)
Records: 2844047  Duplicates: 0  Warnings: 0

Un minuto ha tardado el proceso. Esto, en una página web con cientos de visitantes concurrentes, puede ser un desastre. Ahora, hagamoslo online:

[root@miguelangelnieto scripts]# time ./oak-online-alter-table --table employees.salaries 
--alter="MODIFY emp_no VARCHAR(40)" -H 127.0.0.1 -u root --ask-pass -q -c 50000
Password: 
real    4m25.628s
user    0m0.138s
sys 0m0.040s

Como vemos, ha tardado 4 minutos siendo un proceso más lento, pero en este caso no hemos tenido parada ni bloqueos largos de la tabla. Nadie se ha enterado y nosotros hemos hecho un ALTER TABLE en producción.

Durante el proceso nos crea la tabla fantasma __oak_salaries:

mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| __oak_salaries      |
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
7 rows in set (0.00 sec)

Para los administradores que prefieran PHP, Mark Callaghan de Facebook ha publicado su propio script para realizar ALTER TABLE's online, siguiendo un método muy similar al explicado aquí:

Script PHP Documentación en Facebook


Comments

    Hola Miguel Ángel,

    En primer lugar, gracias por el artículo, excelente! En segundo lugar, gracias por lo que nos toca a los desarrolladores algunas veces: "cagar..." :).

    Dejo un enlace para complementar el artículo, es un hack, aplica bajo ciertas condiciones, que puede ser útil cuando tenemos un caso en la que ni una herramienta como openark nos libera de tiempos de esperas altos. "Hacking FRM files to switch data types" http://goo.gl/chzTJ

    Un saludo.

    William on

    No conocía este "pequeño" detalle de MySQL, muy buen detalle las herramientas de open ark! gracias por la referencia.

    William ese hack solo sirve para MyISAM cierto?

    pedro on

    Respecto a el hack que enlaza William sobre la modificación de los frm, yo siempre pensé que se aplicaba solo a MyISAM, es la primera vez que oigo de alguien que lo hace para InnoDB. Así que, según indica el enlace, en algunos casos es posible aplicarlo a InnoDB.

    Miguel Angel Nieto on

    no hay una explicacion clara

    Jona on

    con alter table, ¿podría cambiar el orden de las columnas ya creadas en una tabla llena de datos sin que éstos desaparezcan?

    cristina aybar on