Posts tagged with “innodb”
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
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 :)