Como optimizar MySQL configurando el archivo my.cnf

MySQL se ha convertido en uno de los motores de base de datos más utilizados del mundo al usarse de forma común en sistemas web desarrollados con PHP.
Han pasado muchas cosas desde que MySQL fue comprada por SUN en 2008 y la cosa se ha “mareado” aún más cuando el proyecto paso a las manos de Oracle en 2010.



Se especuló bastante con la posibilidad de que MySQL pasara a ser un motor de bases de datos de pago (ahora mismo el software libre) e incluso se creó un fork llamado MariaDB que es totalmente opensource y llega a ser más efectivo en algunas situaciones de alta carga.

La mayoría de las cosas que vamos a comentar en este artículo sirven tanto para MariaDB como para MySQL, por lo que podrás aplicarlas en ambos casos.
Actualmente en hosting web lo más normal es encontrarse MySQL, normalmente solo instalan MariaDB los administradores de sistemas que conocen sus ventajas para temas muy concretos.
Por ponerte un ejemplo, nuestros servidores de hosting compartido llevan MariaDB, y son totalmente compatibles con bases de datos MySQL.

Empecemos por el principio, ¿Qué es lo que vamos a hacer en este artículo? Vamos a ver como optimizar un servidor MySQL funcionando sobre un servidor Linux.

¿Para qué queremos optimizar un servidor MySQL? Pues para que pueda servir las peticiones más rápido usando menos recursos de CPU, RAM e I/O de disco, o simplemente para que aproveche mejor los recursos del sistema para garantizar una mejor estabilidad y una mayor velocidad de respuesta al acceder a datos almacenados en las bases de datos.

Archivo my.cnf

El 99% de las configuraciones del servidor MySQL se realizan desde un único archivo, por no decir directamente que todas las configuraciones se realizan desde un único archivo.
El archivo my.cnf en sistemas Linux se encuentra dentro de la carpeta /etc, la ruta completa al archivo my.cnf para ser editado con el editor “nano” sería algo así.

nano /etc/my.cnf

Una vez que abrimos el archivo, de forma predeterminada no trae todos los parámetros que debería traer, por lo que algunos los vamos a tener que añadir nosotros.

Debes tener en cuenta que desde este archivo, no solo tienes la posibilidad de optimizar el rendimiento del servidor MySQL, sino que también tienes la posibilidad de “reventar” el servidor MySQL haciendo que consuma todos los recursos del sistema, o directamente haciendo que el servicio MySQL no sea capaz de arrancar.
Con esto te quiero avisar de que debes tener mucho cuidado con lo que tocas en este archivo y solo tocar si estás seguro o si no se trata de un entorno en producción.

Parámetros de optimización de MySQL

A continuación, vamos a especificar algunos de los parámetros generales que influyen en el rendimiento y en la estabilidad de MySQL y que se deben parametrizar en el my.cnf.
Vamos a tratar de explicar para que sirve cada uno y más o menos cuales son los valores orientativos que debemos configurar.

• query_cache_type: Sirve para activar o desactivar cache, si ponemos 0 desactivamos el cache de consultas de MySQL, si ponemos 1 activamos el cache de consultas y si ponemos 2 se activará bajo petición. Lo recomendable es 1.

• max_allowed_packet: Este parámetro especifica el tamaño máximo de un paquete a la hora de que el servidor MySQL trabaje con él. Este parámetro normalmente lo tenemos que aumentar para importar bases de datos grandes o mover grandes volúmenes de datos en una base de datos.

• query_cache_size: Este parámetro especifica el tamaño del cache de consultas, este cache se guarda en RAM y se suele poner 64 MB de RAM por cada 1 GB de memoria física usable que tenga el servidor.

• key_buffer_size: Este parámetro especifica el tamaño del cache de los índices, cuanto más grande sea este cache, más rápido se ejecutarán los comandos SQL y más rápido se obtendrá una respuesta del servidor MySQL. Normalmente se configuran 32 MB por cada 1 GB de memoria física usable.

• table_cache: Especifica el máximo de tablas abiertas entre todos los threads o hilos de ejecución de MySQL, un buen valor es 64, aunque con MySQLTuner podremos ver si necesitamos más o menos número de tablas abiertas.

• sort_buffer_size: Con este parámetro configuramos el tamaño del cache de búsquedas de MySQL, lo recomendable es configurar 1 MB por cada 1 GB de memoria RAM física disponible.

• read_buffer_size: Con este parámetro configuramos el tamaño del cache de lecturas de MySQL, lo recomendable es configurar 1 MB por cada 1 GB de memoria RAM física disponible.

• read_rnd_buffer_size: Con este parámetro configuramos el tamaño del cache de lecturas usado tras una acción de búsqueda u ordenado, lo recomendable es configurar 1 MB por cada 1 GB de memoria RAM física disponible.

• join_buffer_size: Con este parámetro configuramos el tamaño del cache de JOIN sin índices, lo recomendable es configurar 1 MB por cada 1 GB de memoria RAM física disponible.

• thread_cache_size: Es el número máximo de hilos de ejecución que se pueden cachear y rehusar, se suelen configurar entre 32 y 64 para un uso normal.

• tmp_table_size: Esta variable especifica el tamaño máximo de una tabla temporal en RAM, cuando se alcanza el tamaño máximo especificado en este parámetro la tabla pasa a ser una tabla temporal en MyISAM.

• max_connections: Especifica el número máximo de conexiones totales que puede aceptar el servidor MySQL al mismo tiempo.

• wait_timeout: Es el tiempo de espera que tarda MySQL en cerrar una conexión.

• thread_concurrency: Especifica el número máximo de hijos de ejecución o procesos abiertos de MySQL, lo recomendable es configurar 2 por cada 1 núcleo de CPU disponible.

• query_cache_limit: Especifica un límite de tamaño de consulta a partir del cual no se cachearán, el valor por defecto es 1 MB. Si el límite es muy alto podemos llegar a saturar el servidor MySQL.

• innodb_buffer_pool_size: Es una variable que solo afecta a innoDB, pero que mejora bastante el rendimiento general de las tablas almacenadas en InnoDB. Un buen valor de configuración sería un valor similar al 70 u 80% de la memoria RAM disponible, pero depende también del tamaño de la base de datos, si trabajamos con bases de datos muy pequeñas no tiene sentido especificar un valor tan grande.

Optimizar MySQL con MySQLTuner

MySQLTuner es un script desarrollado en Perl que realiza una serie de monitorizaciones en el servicio MySQL durante unas horas para ofrecernos un informe completo de lo que podemos cambiar en el archivo my.cnf de nuestro servidor para conseguir un mejor rendimiento y estabilidad en nuestro servidor MySQL.

MySQLTuner es compatible con los siguientes motores de base de datos:

• MySQL desde la versión 3.23 a la versión 5.7, ambas incluidas.

• Maria DB desde la versión 5.5 a la 10.1, ambas incluidas.

Solo es compatible con sistemas operativos Linux, BSD o Solaris, Windows no es compatible, aunque se instale el intérprete Perl.

Algo que muchos administradores de sistemas no saben, es que el servidor MySQL mientras está funcionando esta almacenando ciertas estadísticas de uso y rendimiento, estas estadísticas si sabemos interpretarlas bien son de gran ayuda para ajustar los parámetros del archivo my.cnf.
Pero como solo 1 de cada 30 sysadmins como mucho son capaces de interpretar bien estos datos y configurar el my.cnf a ojo, pues existe MySQLTuner, que se encarga de interpretar estos datos y darnos consejos para “tunear” el my.cnf.

Se recomienda ejecutar MySQLTuner cuando el servidor MySQL lleva funcionando al menos 24 horas de forma ininterrumpida, para que los datos que tenga sean reales, pero en la práctica si puede llevar encendido recogiendo datos de una actividad normal durante 2 o 3 días es aún mejor.

En la parte final del informe, MySQLTuner te especificara que parámetros debes cambiar y que debes especificar en ellos más o menos.
Aunque MySQLTuner te indique algo, tú debes entender lo que estás haciendo, ya que puedes convertir fácilmente tu servicio MySQL en un devorador de RAM.

Para ejecutar MySQLTuner, simplemente debemos descargar el script desde su repositorio oficial y posteriormente debemos ejecutarlo, es fácil.

Podemos descargar MySQLTuner con el siguiente comando:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O mysqltuner.pl

Y ahora ejecutamos el archivo perl que nos acabamos de descargar, para ello usamos el siguiente comando:

perl mysqltuner.pl

Y con esto, se realizará el informe de cambios que podemos realizar para optimizar MySQL.

Discos SSD y bases de datos MySQL

Finalmente, hay un componente de hardware que influye mucho en el rendimiento de MySQL, sobre todo cuando hablamos de bases de datos grandes o servidores con mucha carga de trabajo donde los caches no consiguen “retener” todos los datos que deberían debido a constantes actualizaciones o a un gran tamaño de los datos.

Los discos SSD han conseguido mejorar mucho el rendimiento de los servidores de bases de datos, ya que las latencias en las lecturas y escrituras se han reducido mucho.

A continuación, exponemos dos casos reales realizados en un benchmark de MS SQL Server:

• Disco SSD: 0,917 segundos para 5000 inserts SQL en una base de datos MSSQL.

• Disco HDD: 2,12 segundos para 5000 inserts SQL en una base de datos MSSQL.

Y a continuación exponemos dos casos reales de un benchmark realizado con SQLite:

• Disco SSD: 10,38 segundos para 5000 inserts SQL en una base de datos SQLite.

• Disco HDD: 538,36 segundos para 5000 inserts SQL en una base de datos SQLite.

Y ahora otro benchmark, pero esta vez de lectura, en una base de datos MS SQL Server:

• Disco SSD: 1,56 segundos para leer 5000 registros en una base de datos MSSQL.

• Disco HDD: 1,65 segundos para leer 5000 registros en una base de datos MSQL.

Estos últimos tiempos de respuesta no parecen muy altos, es decir, parece que no hay mucha diferencia, pero cuando hay mucha fragmentación en el disco duro estos tiempos de lectura del disco HDD pueden subir más de un 500%.

De hecho, en otro benchmark de lectura de datos (una base de datos grande) se obtuvieron los siguientes resultados en MSSQL:

• Disco SSD: 7 segundos.

• Disco HDD: 22 segundos.

En este último caso la fragmentación del disco se nota bastante más al trabajar con bases de datos grandes con muchos datos.