Hablando de cosas mundanas… tuve un problema de corrupción con una base de datos y tuve que rescatarla de un respaldo. Estoy usando MariaDB y la base de datos contiene 110k registros con índices full text.
zcat base_de_datos.sqlz | mariadb -uroot -p -v basededatos
No tenía idea de cuanto tiempo iba a tomar recuperar el respaldo; calculaba 15 minutos a lo mucho… pero pasaron 30 y seguía trabajando. Dos horas y seguía… así lo dejé hasta que al siguiente día… ¡seguía trabajando!
El CPU estaba apenas al 25% por lo que no podía ser que el CPU fuera el cuello de botella. Por otra parte el IO estaba saturado todo el tiempo. Tomemos en cuenta que es un viejo servidor de 2 nucleos, con apenas 4GB de RAM y un disco duro mecánico de 80MB/s. By the way, I use Arch.
Hice la misma operación en mi computadora de trabajo y no tardó más de 5 minutos. Obviamente algo estaba muy mal con el servidor.
Luego de investigar por un par de horas encontré unos ajustes pertenencientes a los buffers:
innodb_buffer_pool_size = 1073741824
innodb_buffer_pool_instances = 2
buffer pool size le dice a MySQL que use n cantidad de bytes como buffer, y buffer pool instances le dice que use 2 pools. Con estos ajustes MySQL tiene a su disposición hasta 2GB de buffer. Lo recomendado para un servidor dedicado sería el 80% de la RAM total del servidor. Como en mi caso uso el servidor para muchas otras cosas más, solo le asigné el 50% de RAM… tal vez es demasiado, pero una vez que terminé de insertar los registros lo regresé a 25%.
Por default innodb_buffer_pool_size viene configurado con 128MB, lo puedes verificar con:
SELECT @@innodb_buffer_pool_size;
La fórmula para calcular los valores adecuados para estos dos ajustes podría ser:
innodb_buffer_pool_size = TOTAL_RAM_IN_BYTES * 0.8 / CORES
innodb_buffer_pool_instances = CORES
Una vez hechos estos ajustes volví a insertar los 110 mil registros y esta vez estuve monitoreando y tanto el CPU como el IO estaban casi al 100%, lo nuevo es que podía insertar cerca de 4 mil cada ~15 segundos… en total no tardó más de 8 minutos.