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

Backups y restauraciones de tablas en InnoDB

Mucha gente cree erroneamente que gracias a la opción innodb-file-per-table te permite, como MyISAM, portar una tabla en binario de un servidor a otro de forma transparente o recuperar el backup de una tabla. El problema viene cuando realmente necesitan hacer uso de ese backup y no funciona como ellos esperaban.

Al contrario que con MyISAM, donde los ficheros de tablas MYD e MYI son independientes del resto y portables, todas las tablas de InnoDB dependen de un tablespace común donde se almacenan las definiciones de las tablas y además depende de los IDs de transacciones entre otras cosas. Por lo que, si restauras un .idb, no recuperarás los datos.

Todo esto se aplica a la versión original de MySQL, la desarrollada por Oracle. Pero Xtrabackup y Percona Server nos permite esquivar esta limitación y trabajar con los ficheros binarios como si se tratasen de tablas MyISAM, moviéndolas y restaurándolas de un servidor a otro.

Lo primero que sorprende es que el servidor de origen no tiene porque ser Percona Server, puede ser el MySQL del repositorio de tu distribución. Los únicos requisitos son:

• El servidor origen y destino deben tener --innodb-file-per-table
• Hacer el backup con xtrabackup
• Recuperar el tablespace en un Percona Server

El proceso de realización del backup no cambia, pero si la posterior reparación (--prepare). El comando de reparación será el mismo, solo que añadimos la opción --export.

xtrabackup --prepare --export --innodb-file-per-table --target-dir=/mnt/backups/mysql-data

Esta opción generará un fichero extra .exp

/mnt/backups/mysql-data/db/table.exp
/mnt/backups/mysql-data/db/table.ibd

Ahora, ¿cómo importamos esa tabla en otro MySQL?

Tal y como hemos comentado antes, el destino debe ser un Percona Server. Únicamente tendremos que ejecutar los siguientes comandos:

ALTER TABLE db.table DISCARD TABLESPACE;

Copiamos las tablas exportadas al subdirectorio db/ del servidor destino.

ALTER TABLE db.table IMPORT TABLESPACE;

Y listo.

Tenéis información más actualizada en el propio manual de xtrabackup.

http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup:export_and_import

Pre-cachear los datos de InnoDB en el buffer pool

Cuando tienes un entorno activo-pasivo o montas un esclavo para las lecturas, el mayor problema que te puedes encontrar al poner los nuevos servidores en producción es que las cachés se encuentren frias (cold cache). Como dichos servidores no han recibido consultas, todas sus cachés, como query cache o innodb_buffer_pool se encuentran vacias y todas las consultas tendrán que ir a disco duro durante los primeros minutos u horas. En esos primeros instantes, el rendimiento de tu backend será pésimo.

Hasta ahora, para evitar en la medida de lo posible ese problema, se lanzaban SELECT contra las tablas que obligasen a leerse todas las filas. Eran consultas muy pesadas que tardaban mucho tiempo en ejecutarse y ralentizaban aún más el rendimiento, pero... no había otra solución. Un ejemplo de está solución se puede leer en el blog de Santi Saez Woop!. Pero esto ya ha cambiado.

En la versión de desarrollo de MySQL 5.6 ya es posible guardar el estado actual del buffer pool en un fichero y recuperarlo en memoria después del reinicio del servicio. No más esperas ni trucos de scripting, ahora hablamos de un procedimiento nativo dentro del propio servicio.

Lanzar un dump del buffer pool:

mysql> SET innodb_buffer_pool_dump_now=ON;

Configurar un dump cuando se realice el apagado del servicio:

mysql> SET innodb_buffer_pool_dump_at_shutdown=ON;

Recuperar un dump en memoria:

mysql> SET innodb_buffer_pool_load_now=ON;

Y en my.cnf podemos poner esta línea, para que se cargue automáticamente durante el arranque del servicio:

innodb_buffer_pool_load_at_startup=ON

Podemos ver el progreso del dump:

mysql> SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';

Y el progreso de la restauración:

mysql> SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

Así como cancelarlo :)

mysql> SET innodb_buffer_pool_load_abort=ON;

Es importante recalcar que si estás utilizando Percona Server, su engine xtradb dispone también de esta funcionalidad, por lo que no tienes que esperar. Simplemente, los comandos cambian:

Lanzar un dump del buffer pool:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;

Recuperar un dump en memoria:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;

Programar un dump cada X segundos:

innodb_auto_lru_dump = X

Cargar automáticamente el dump en el arranque:

innodb_buffer_pool_restore_at_startup=1

Sniffing del protocolo de red de MySQL

En ocasiones es necesario hacer un análisis de que está pasando en un servidor con MySQL, comprobar que queries se están ejecutando, cuanto tardan, donde están los cuellos de botella, etc. Hay diferentes formas de hacerlos:

• A lo pobre: ejecutar SHOW FULL PROCESSLIST cada pocos segundos, identificar a ojo las querys que pueden ser interesantes y luego analizarlas.
• A lo basto: habilitar el log general de MySQL y almacenar todas las querys que se ejecutan. Te llevarás una gran cantidad de IOPS y puede que el fichero termine siendo tan grande que analizarlo sea un infierno.

Como casi siempre, las maatkit vienen a ayudarnos en esta tarea. En esta ocasión, mk-query-digest nos va a permitir analizar la ejecución de querys y generarnos un reporte. Esta utilidad es capaz de coger datos de los logs, pero aquí vamos a utilizar un parámetro que nos ayudará a capturar el tráfico directamente de la interfaz de red y hacer un reporte de lo que está pasando ahora mismo.

Para ello solo necesitamos tcpdump y mk-query-digest :)

Iniciamos la captura de tráfico y se la pasamos a mk-query-digest por STDIN:

root: ~ # tcpdump -s 65535 -x -n -q -tttt -i lo0 -c 10000
 port 3306 | ./mk-query-digest --type tcpdump

Ahora mismo, tcpdump estará capturando el tráfico del puerto TCP 3306 y pasándolo por pipe a mk-query-digest. Pasados unos tiempo (10000 paquetes) veremos el reporte:

listening on lo0, link-type NULL (BSD loopback), capture size 65535 bytes
10000 packets captured
17211 packets received by filter
7070 packets dropped by kernel
# 2.2s user time, 30ms system time, 16.28M rss, 2.33G vsz
# Current date: Sat Jul 16 20:50:09 2011
# Hostname: iMac-Punisher.local
# Files: STDIN
# Overall: 2.20k total, 9 unique, 668.40 QPS, 0.38x concurrency __________
# Time range: 2011-07-16 20:50:05.650775 to 20:50:08.940697
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             1s       0    95ms   569us     1ms     2ms   287us
# Rows affecte       1.91k       0       1    0.89    0.99    0.31    0.99
# Query size       835.46k      25     433  389.05  420.77  118.11  420.77
# Warning coun           0       0       0       0       0       0       0
# Boolean:
# No index use   0% yes,  99% no
# Profile
# Rank Query ID           Response time Calls R/Call Apdx V/M   Item
# ==== ================== ============= ===== ====== ==== ===== ==========
#    1 0xCAEC22E79B0EFD3B  0.9023 72.1%  1955 0.0005 1.00  0.01 INSERT t?
#    2 0xA21B9C02CA2BF472  0.2649 21.2%   139 0.0019 1.00  0.00 SELECT t?
#    3 0xBCFD2D3AD85C5621  0.0475  3.8%     1 0.0475 1.00  0.00 CREATE TABLE t? `t1`
# MISC 0xMISC              0.0368  2.9%   104 0.0004   NS   0.0 <6 ITEMS>

Podemos mejorarlo aún más, haciendo que las queries pasen por el explain automáticamente. Esto último hay que hacerlo con cuidado, porque al contrario de lo que cree mucha gente, un explain no siempre evita que parte de la query se tenga que ejecutar consumiendo recursos.

tcpdump -s 65535 -x -n -q -tttt -i lo0 -c 10000 port 3306 | ./mk-query-digest 
--type tcpdump --explain h=localhost,u=root

# Query 2: 60.04 QPS, 0.11x concurrency, ID 0xA21B9C02CA2BF472 at byte 269106
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.00
# EXPLAIN sparkline: a
# Query_time sparkline: |  .^_   |
# Time range: 2011-07-16 20:59:46.310625 to 20:59:48.425763
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5     127
# Exec time     21   242ms   143us    15ms     2ms     4ms     2ms     2ms
# Rows affecte   0       1       0       1    0.01       0    0.09       0
# Query size     0   7.19k      58      58      58      58       0      58
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Databases    mysqlslap
# Errors       none
# Hosts        127.0.0.1
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ###########################
#   1ms  ################################################################
#  10ms  ##
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM 'mysqlslap' LIKE 't1'\G
#    SHOW CREATE TABLE 'mysqlslap'.'t1'\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: t1
#    partitions: NULL
#          type: ALL
# possible_keys: NULL
#           key: NULL
#       key_len: NULL
#           ref: NULL
#          rows: 34
#         Extra:

No solo puedes analizar las queries de tu MySQL en tiempo real, si no incluso hacer un explain y comprobar sus planes de ejecución :)

Mantener la consistencia de los datos en la replicación

Las replicaciones necesitan de un chequeo constante en la integridad de los datos. Fallos de disco, corrupción de de logs, mezcla de tablas transaccionales y no transaccionales y otros problemas pueden tumbar la consistencia de nuestros datos. Por lo tanto, podemos tener una replicación funcionando, pero los datos, si no hay una comprobación activa, pueden ser diferentes en las dos máquinas. MySQL no tiene comprobaciones activas de consistencia, por lo que es trabajo nuestro. Para ello, instalamos las herramientas maatkit de Percona:

apt-get install maatkit

Las herramientras que usaremos serán mk-table-checksum y mk-table-sync. El funcionamiento de la herramienta se basa en la replicación en base a sentencias de mysql. mk-table-checksum realiza una comprobación mediante un algoritmo de hashing en las tablas, escribiendo los resultados en la base de datos. Estas sentencias se replicarán en el esclavo y se volverán a ejecutar, realizando por lo tanto el mismo hashing en las tablas del esclavo. De esta forma, unicamente debemos comparar los resultados en el maestro y en el esclavo para comprobar si los datos son exactamente iguales o no.

Tenemos dos MySQL en Master-Slave.

NODO 1:

Puerto TCP 19369

master [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)

NODO 2:

Puerto TCP 19370

slave1 [localhost] {msandbox} (vida) > select * from producto;
+---+---------+
| i | nombre  |
+---+---------+
| 1 | VPS     |
| 2 | Cloud   |
| 3 | Storage |
+---+---------+
3 rows in set (0.00 sec)

¿Como comprobamos si los datos son o no consistentes?

Primero, le decimos a mk-table-checksum que haga un checksum de todas las tablas de la base de datos vida y guarde el resultado en test.checksum.

~$ mk-table-checksum u=root,p=msandbox
--socket=/tmp/mysql_sandbox19369.sock --databases=vida
--replicate test.checksum --create-replicate-table
Cannot connect to P=19369,S=/tmp/mysql_sandbox19369.sock,h=SBslave1,p=...,u=root
Cannot connect to P=19369,S=/tmp/mysql_sandbox19369.sock,h=SBslave1,p=...,u=root
DATABASE TABLE    CHUNK HOST        ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
vida     producto     0 soporteit69 InnoDB          2         935b4964    0 NULL NULL NULL

Comprobamos los resultados en Master:

master [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 935b4964 |        2 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Comprobamos los resultados en Slave:

slave1 [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 6120c018 |        3 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Como podemos ver, el crc del slave (this_crc) es diferente al del master (master_crc) por lo tanto, tenemos incosistencias en los datos de la tabla producto de la base de datos vida.

¿Cómo lo arreglamos?

Podriamos hacerlo volviendo a montar la replicación de cero usando un dump de mysqldump. Esto vale si tu BBDD ocupa solo unos cientos de megas, pero si hablamos de gigas, puedes tardar horas y provocar caidas del sistema. Así que haremos uso de mk-table-sync.

~$ mk-table-sync --execute --replicate test.checksum 
--sync-to-master u=root,p=msandbox --socket=/tmp/mysql_sandbox19370.sock

Comprobamos si ahora los datos son consistentes:

master [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (vida) > select * from producto;
+---+--------+
| i | nombre |
+---+--------+
| 1 | VPS    |
| 2 | Cloud  |
+---+--------+
2 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from checksum;
+------+----------+-------+------------+----------+----------+------------+------------+
| db   | tbl      | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt |
+------+----------+-------+------------+----------+----------+------------+------------+
| vida | producto |     0 | 1=1        | 935b4964 |        2 | 935b4964   |          2 |
+------+----------+-------+------------+----------+----------+------------+------------+
1 row in set (0.00 sec)

Listo! Replicación consistente y en marcha :)