Timeout en la replicación del esclavo

Otro de los problemas que nos podemos encontrar en una replicación es la red. Si esta está congestionada o con desconexiones intermitentes podemos terminar teniendo graves como lag entre maestro y esclavos o la parada completa del esclavo. Últimamente me he encontrado con este problema en algunas instalaciones de replicación y los síntomas no ayudaban a conocer la causa. Conectándome al esclavo y ejecutando el típico show slave status no encontraba la razón por la cual la replicación se habia parado. Los dos procesos, IO y SQL estaban funcionando y Seconds Behind Master indicaba 0.

Cuando el esclavo pide los últimos logs al maestro, se queda esperando un tiempo para recibir la respuesta hasta que al final da timeout. Eso es un comportamiento normal, lo que ya no es normal es el valor por defecto de dicha espera, 3600 segundos, ¡una hora! El esclavo se quedará en el estado:

Slave_IO_State: Waiting for master to send event

Los valores que tendremos que modificar en nuestra configuración son:

slave-net-timeout = 30
master-connect-retry = 60

De esta forma, el esclavo se quedará esperando a los eventos del maestro 30 segundos y después dará timeout y se intentará reconectar. Si por alguna razón no puede conectarse al maestro, lo seguirá reintentando cada 60 segundos (valor por defecto).

Como bien dice Daniel Schneller, no te creas todo lo que te dice el show slave status :)

Nuevas trasparencias: administración avanzada de MySQL

Aquí os pongo unas nuevas transparencias de un curso que he dado recientemente. Abarca gran cantidad de temas relacionados con la administración de nuestra base de datos favorita :)

• Instalación
• Engines
• Optimización de consultas
• Optimización de tablas
• Optimización del servicio
• Usuarios y permisos
• Replicación
• Alta disponibilidad
• Backup
• etc. :)

Espero que os guste y os sea de utilidad. Cualquier sugerencia o crítica es bienvenida. Si hay algún fallo comentádmelo para solucionarlo lo antes posible.

¡Gracias a todos!

Los peligros de binlog-do-db en la replicación

A la hora de configurar una replicación, el punto más importante es aquel en el que decidimos que replicar. Y para ello debemos seleccionar que guardar en el log binario. Tenemos muchas opciones, pero hay algunas que debemos evitar:

• binlog-do-db
• binlog-ignore-db
• replicate-do-db
• replicate-ignore-db

Para ver la razón, nada mejor que un ejemplo practico de un sistema master-master.

El servidor A tiene dos bases de datos, VIDA y MUERTE. VIDA será la que se replicará al segundo maestro.

El servidor B solo tiene la base de datos VIDA.

Servidor A:

server-id=101
log-bin=mysql-bin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=1
binlog-do-db=vida

Servidor B:

server-id=102
log-bin=mysql-bin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=2
binlog-do-db=vida

Ejecutamos en el servidor A:

node1 [localhost] {msandbox} ((none)) > insert into vida.t values(10);
Query OK, 1 row affected (0.00 sec)
node1 [localhost] {msandbox} ((none)) > use vida;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
node1 [localhost] {msandbox} (vida) > create table muerte.t (i int);
Query OK, 0 rows affected (0.00 sec)

Ya nos hemos cargado la replicación por dos sitios. El valor 10 no se ha insertado en la tabla t de VIDA y por otro lado, el servidor B intentará crear la tabla t en la base de datos MUERTE (que en realidad no tiene).

Last_SQL_Error: Error 'Unknown database 'muerte'' on query. Default database: 'vida'. Query: 'create table muerte.t (i int)'

¿Y cual es la razón de este comportamiento si le hemos indicado que queremos solo la replicación de VIDA? Respuesta corta, binlog-do-db no hace lo que nosotros creemos :) Bien... ¿y la respuesta larga?

Los 4 parámetros para el log binario que hemos visto antes, se aplican si son la base de datos por defecto, esto es, si hemos hecho un USE VIDA. Una vez que convertimos mediante ese comando VIDA en la base de datos por defecto, todos los comandos que ejecutemos se logearán en el log binario.

Por lo tanto, si vemos los comandos SQL ejecutados anteriormente se puede entender la razón por la cual hemos roto la replicación:

• Hemos realizado un insert en la tabla t de VIDA sin que esta sea nuestra base de datos por defecto. MAL! No se logeará, el esclavo no recibirá las actualizaciones.
• Hemos creado una tabla t en MUERTE siendo VIDA nuestra base de datos por defecto. MAL! La sentencia se logeará y enviará al esclavo. Cuando este intente replicarla fallará por no tener dicha base de datos.

Ahora sabemos cual es el problema, hay que saber la solución. Y en esta ocasión tenemos dos:

• Hacer uso de replicate-wild-do-table=VIDA.% Esto logeará todo lo que modifique la base de datos VIDA, sea nuestra base de datos por defecto o no.
• Activar la replicación en base a filas (MySQL 5.1)

Este es un fallo muy común, por lo que os recomiendo revisar las configuraciones de vuestras replicaciones ante posibles fallos de configuración.

Archive Engine, almacenamiento masivo

A veces es necesario almacenar una gran cantidad de datos en MySQL, por ejemplo las típicas tablas con los logs del sistema, fichajes de empleados, estadísticas de correo, resultados de encuestas, etc.. Son tablas que aumentan constantemente y que nunca dejarán de hacerlo. Por otro lado son tablas que a pesar de almacenar tantos datos y ocupar tanto espacio no podremos eliminar, ya sea porque son necesarios o porque nuestro jefe sufre el Síndrome de Diógenes. En esos casos, cuya única función es el almacenamiento constante y la consulta esporádica, cosas como integridad referencial o transacciones nos importa bien poco. Lo que nos tiene que preocupar es el tamaño de la tabla y el espacio libre en disco duro.

Para este tipo de almacenamiento existe un engine que nos puede ayudar, Archive:

• Compresión de datos al vuelo según se van introduciendo
• Bloqueo a nivel de fila
• Autoincrementales
• No permite eliminar o actualizar, solo añadir
• No soporta índices (excepto en autoincrementales), claves externas o transacciones
• La velocidad en lectura es muy similar :)

Veamos la relación entre rendimiento/tamaño de las tablas, comparando InnoDB con Archive. La BBDD que usaremos será "employee", que se puede descargar de https://launchpad.net/test-db.

InnoDB

Tamaño de las tablas:

210 megas

Tiempo de lectura:

mysql [localhost] {msandbox} (employees) > select count(\*) from salaries where emp_no like '%';
+----------+
| count(\*) |
+----------+
|  2844047 |
+----------+
1 row in set (3.42 sec)
mysql [localhost] {msandbox} (employees) > select count(\*) from employees where emp_no like '%';
+----------+
| count(\*) |
+----------+
|   300024 |
+----------+
1 row in set (0.42 sec)

Archive

Tamaño del las tablas:

31 megas

Tiempo de lectura:

mysql [localhost] {msandbox} (employees) > select count(\*) from salaries where emp_no like '%';
+----------+
| count(\*) |
+----------+
|  2844047 |
+----------+
1 row in set (3.07 sec)
mysql [localhost] {msandbox} (employees) > select count(\*) from employees where emp_no like '%';
+----------+
| count(\*) |
+----------+
|   300024 |
+----------+
1 row in set (0.51 sec)

Ocupa menos y es ligeramente más rápido que InnoDB.

Ya sabes que hacer con tus tablas de almacenamiento masivo :)

Replicación semi-síncrona con MySQL 5.5

El problema más grave de la replicación en MySQL es su funcionamiento asíncrono. Cuando se añade o modifica algún dato en el master, este commitea los datos en local sin esperar a que los slaves lo hagan. Esto normalmente no supone un gran problema, ya que la replicación, si no hay ningún problema con índices o con la red, es casi instantanea. Pero aún así se pueden dar algunos problemas:

• El master commitea los datos sin esperar. Durante un tiempo, aunque pequeño, master y slave tendrán datos diferentes. Contra mas alto sea el valor seconds behind master, mayor será el problema.

• El master no comprueba que los esclavos hayan recibido los binlogs con los cambios.

• El master no comprueba que los esclavos hayan hecho efectivos los cambios en sus bases de datos.

Este es un problema solucionado en MySQL Cluster, donde la replicación es totalmente síncrona. Los nodos no commitean los cambios hasta que estos se hayan escrito correctamente en los node groups que correspondan. Si esto no es así, se hace un rollback. Pero en la replicación normal no tenemos tanta suerte.

Una de las novedades de MySQL 5.5 viene a medio solventar el problema. Con esta nueva versión de desarrollo disponemos de replicación semi-síncrona. Algo es algo :)

Su funcionamiento es simple. Ahora el master antes de hacer commit espera a que al menos uno de los slaves reciba los logs binarios. Pero aún así hay que tener en cuenta lo siguiente:

• El master solamente comprueba que un slave haya recibido los logs, pero no que si lo ha podido escribir correctamente o no. Esto es, no importa el estado del SQL Thread.

• Podemos tener 1000 slaves, pero con que solo uno reciba los logs ya se da por bueno.

• Si pasado un tiempo ninguno de los esclavos recibe los logs, el master cambia a modo asíncrono commiteando los cambios.

Vamos a hacer unas pruebas. Necesitaremos dos cosas, MySQL 5.5 y sandbox :) Creamos un entorno de replicación con un master y dos slaves:

punisher@shyris:~$ make_replication_sandbox --how_many_slaves=2 
/home/punisher/MySQL/mysql-5.5.1-m2-linux-x86_64-glibc23.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2

Una vez hecho, debemos cargar el plugin que nos permite hacer uso de la replicación semi-síncrona en todos los hosts:

master [localhost] {msandbox} ((none)) > INSTALL PLUGIN rpl_semi_sync_master
SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > INSTALL PLUGIN rpl_semi_sync_slave
SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
slave2 [localhost] {msandbox} ((none)) > INSTALL PLUGIN rpl_semi_sync_slave
SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

A continuación, debemos habilitar su uso en los ficheros de configuración:

Master:
rpl_semi_sync_master_enabled=1;
Slaves:
rpl_semi_sync_slave_enabled=1;

Reiniciamos mysql y ya lo tenemos :) Creamos una base de datos llamada prueba y comprobamos si al menos un slave ha recibido el binlog:

master [localhost] {msandbox} ((none)) > create database pruebas;
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} ((none)) > SHOW STATUS LIKE 'Rpl_semi_sync%tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_no_tx  | 0     |
| Rpl_semi_sync_master_yes_tx | 1     |
+-----------------------------+-------+
2 rows in set (0.00 sec)

Rpl_semi_sync_master_yes_tx es el número de queries correctamente replicadas. Rpl_semi_sync_master_no_tx es el número de queries que no se han replicado.

Hay que tener en cuenta, que estamos hablando de IO no de SQL. Para comprobarlo, paramos el SQL thread en los dos nodos:

slave1 [localhost] {msandbox} ((none)) > STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
slave2 [localhost] {msandbox} ((none)) > STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

Y a continuación insertamos un dato en el master:

master [localhost] {msandbox} (pruebas) > create table t(i int(10));
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (pruebas) > SHOW STATUS LIKE 'Rpl_semi_sync%tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_no_tx  | 0     |
| Rpl_semi_sync_master_yes_tx | 2     |
+-----------------------------+-------+
2 rows in set (0.00 sec)

Los slaves han recibido los datos, eso es suficiente para el master y se da por bueno. Ahora vamos a parar el IO Thread:

slave1 [localhost] {msandbox} ((none)) > STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
slave2 [localhost] {msandbox} ((none)) > STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

Y volvemos a meter datos en el master:

master [localhost] {msandbox} (pruebas) > create table z(i int(10));
Query OK, 0 rows affected (10.00 sec)
master [localhost] {msandbox} (pruebas) > SHOW STATUS LIKE 'Rpl_semi_sync%tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_no_tx  | 1     |
| Rpl_semi_sync_master_yes_tx | 2     |
+-----------------------------+-------+
2 rows in set (0.00 sec)

La query ha tardado 10 segundos en commitearse. Si durante esos 10 segundos ninguno de los slaves ha recibido los binlogs, el master hace commit y se cuenta como una query no sincronizada.