Optimizando MYSQL para trabajar con bases gigantes 50gb o mas

Tuve la necesita de migrar una base de datos gigantesca, con poquito mas de 50gb, ademas de ser demasiado tardado, casi 12hrs, con muy buenas condiciones de los servidores, y para no hacer el cuento largo, se termino corrompiendo el servidor, tuve que buscar alternativas para migrar la BD y fuera más fácil de manejar este asunto.

Afortunadamente estábamos preparando otro servidor mas potente para migrar nuestros servicios, lo que hicimos fue dotarle de las ultimas versiones de mysql(MariaDB), PHP, centos 7.5, etc. Con ayuda de 32 procesadores y 128gb RAM todo pinta de maravilla, pero lejos de las bellezas de este servidor, lo que nos preocupaba son las cuestiones de eficiencia de la BD, para las inserciones, tenemos un alto volumen de inserciones, las consultas no tienen mucho impacto pues no son tan altas. Y lo mas importante realizar backups mas fácilmente.

Aprovechando que MariaDB incluyó el formato de archivos Barracuda, la cual nos ofrece muchísimas ventajas, incluyendo la opción de utilizar el formato ROW_FORMAT=COMPRESSED la cual ya por si sola, es toda una maravilla, véase este caso real(En Ingles) y/o puedes pasarte a leer este documento, para conocer un poco mas de estas ventajas.

Para poder usar estas bondades de la compresión, debemos de especificar que vamos a usar Barracuda y que queremos generar un archivo por tabla. (Por default, e utiliza el tipo de archivo Antelope y se genera un archivo por todas nuestras tablas, mas informacion aquí).

 

Pues decidimos utilizarla para nuestros fines, cabe mencionar y hago una atenta invitación a leer primero la receta antes de siquiera abrir la linea de comandos. También recomiendo un nivel medio-alto para ejecutar esta receta. tu nivel lo definirás a leer completo el POST.

ALERTA DEL TIPO, MEJOR ME PELO PA’L GABACHO

Es necesario parar MYSQL, Eliminar los datos y reconstruir el servidor de base datos.

Procedamos a sacar un respaldo como lo hemos hecho antes, por aquí.

Luego, paremos MariaDB

Eliminamos estos archivos que son nuestra base en si.

Hecho esto, debemos configurar mariadb para comenzar a usar los beneficios de Barracuda, lo hacemos de la siguiente manera, abrimos el archivo my.cnf,

Busca la sección [mysqlnd] y ponle estas lineas

Hecho esto, ya podemos levantar MariaDB/Mysql 5.5

Y ya estamos listos para importar nuestro respaldo, esta vez lo haremos de esta manera:

Escribe tu contraseña y después le decimos que vamos a usar la base de datos donde vamos a importar

Esto comenzará a importar el respaldo en forma inmediata y de la forma mas eficiente.

Ya luego vamos a ejecutar esa linea para regenerar el perfomance_schema.

Pon tu contraseña y listo habremos regenerado nuestra base de datos con el formato de archivo Barracuda, ahora, para gozar de sus virtudes, ya podemos configurar el ROW_FORMAT de la tabla a COMPRESSED, este proceso tarda un poco, sin rayar en lo exagerado.

Volvemos abrir mariadb/mysql

La ventaja es que por linea de comandos, nos sale el porcentaje de la tarea.

 

Conclusiones.

Al final de importar la base de datos, y comprimir la tabla, ahorre 50% de espacio en disco, de 9gb que pesaba una tabla, la dejo en 4.4 gb, cabe mencionar que antes de hacer esta barbarie, genere una tabla con las mismas especificaciones, con muchos menos datos, y pude realizar delete, update e insert, sin ningún problema.

Pruebas de eficiencia, las búsquedas mejoraron de aprox. 5 segundos a 0.0005 segs. Esperando montarla en producción actualizaré la información de eficiencia.

, , ,

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

This site uses Akismet to reduce spam. Learn how your comment data is processed.