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.

mysqlLa 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í.

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.

mysql optimizarDebes 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.

mysqlSe 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:

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

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

mysqltuner

 

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.

mysqltunerLos 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.

Share on twitter
Compartir en Twitter
Share on facebook
Compartir en Facebook
Share on pinterest
Compartir en Pinterest

¿Te gusta el blog?

Apúntate al boletín y te enviaremos los mejores artículos una vez al mes.

¿Te ayudamos?

Escríbenos si tienes dudas o necesitas una solución específica. Nuestros expertos te ayudarán en todo lo posible.

Sé el primero en enterarte de ofertas, sorteos y novedades.

Tenemos 17 comentarios en

"Como optimizar MySQL configurando el archivo my.cnf"

17 respuestas

  1. Hola Alvaro, realmente excelente tu artículo, he aprendido un montón. Si quisiera pedirte un ejemplo completo de cómo quedaría una configuración inicial e ideal para un VPS de un 1Gb de RAM. Me queda claro que con MySQLTuner se ajustaría sobre la marcha, pero tener uno inicial ideal puede ser de mucha ayuda. Gracias y felicidades!!

    1. Hola Sergio, depende mucho de como este configurado el VPS y con que RAM libre se juega, lo normal es empezar con los ajustes por defecto, que son bastante «controlados» y pasadas mas de 24 horas con el servidor online y en producción meterle MySQLTuner para ajustarlo.
      Se hace así porque lo que hace MySQLTuner es jugar con las estadísticas recogidas en ese tiempo de funcionamiento.

      Un saludo.

  2. Hola Alvaro, gracias por tu articulo.. Quisiera saber donde puedo encontrar esas estadisticas que un ABD debe saber interpretar para estudiarlas y no depender del script.. Saludos!

  3. Hola, Eduardo:

    Para interpretar esas estadísticas suelen usarse herramientas de monitorización, puesto que facilitan su explotación.

    No obstante, si quieres verlas «en bruto» tendrías dos opciones principales:

    – La tabla «performance_schema» de base de datos guarda información de rendimiento.
    – El comando «mysqladmin» te permite acceder a variables y campos de control con algunos de sus comandos (p.ej.: «mysqladmin status», «mysqladmin variables» o «mysqladmin extended-status»).

    Adicionalmente, recuerda que mysqltuner es software libre, por lo que podrías revisar su código para ver como lo hace el propio script.

    Un saludo.

  4. Un articulo super útil pero tengo una duda. Podréis ver que estoy bastante verde en la materia. Mi pregunta es, cuando te refieres a «memoria RAM física disponible» es del total que tengas contratado en el servidor en cada parámetro, o habría que ir «repartiendo memoria» por cada uno de los procesos.

    1. Hola, Jaime:

      Has de establecer una base de memoria RAM a usar en todos esos cálculos acorde a tu servidor. No uses siempre el 100% porque es un escenario irreal (el sistema también necesita memoria), así que haz un reparto mental y calcula más o menos en base a eso.

      Si, por ejemplo, tienes un stack Lamp (Linux Apache, MySQL, y PHP) con 8GB de RAM podrías determinar que 3GB son para MySQL (número inventado, habría que ver el uso del servidor para llegar a un número más acertado) y realizar los cálculos en base a eso.

      Cualquier cosa nos dices.

      ¡Un saludo!

    1. Hola, Erick:

      si tienes un servicio de hosting compartido no podrás hacerlo ya que necesitarías acceso «root» a la máquina maestra.

      Solo podrás configurar MySQL a tu gusto si usas un servidor tipo VPS o Dedicado y tienes el acceso de administrador.

      Un saludo.

  5. que tan ciegamente puedo confiar en el mysqltuner, por que me recomienda desactivar el query_cache_type pero me hace mas sentido como tu lo escribiste y dejarlo activo.

    1. Hola, Renato:

      Mysqltuner te recomienda desactivar esa directiva porque la caché de consultas está deprecada y será eliminada en versiones futuras de MySQL. Detalles en este enlace.

      El motivo es que la caché de consultas entra en conflicto con el buffer de InnoDB, causando interbloqueos y haciendo que el uso de CPU se dispare. Puesto que MyISAM terminará siendo deprecado esto no es más que una consecuencia lógica.

      En resumidas cuentas, desactiva query_cache si:

      – Usas tablas MyISAM.
      – No usas el buffer de InnoDB.

      En cualquier otro caso te recomiendo desactivarlo y configurar apropiadamente el buffer, o bien, convertir tus tablas a InnoDB.

      Un saludo,
      Héctor

  6. Hola. Configuración me recomiendas? Es mejor un Xeon de 4 nucleos con discos duros mecánicos, o un Ryzen 7 con SSD?

  7. Muy instructivo el artículo y sin dudas me será útil. Llegué aquí buscando otro tema en realidad: Tengo una tabla cuyos registros contienen 96 campos de tipo varchar(127) caracteres cada uno. Cuando comienzo a cargar datos en los campos llega un punto en que MariaDB da el error #1118, porque entiendo que estoy superando un límite de 8126 caracteres por registro (parece que necesitaría unos 12500). Aclaro que utilizo siempre los 127 caracteres de cada campo. (excepto cuando están en NULL).
    ¿Existe algún parámetro que pueda modificar en my.cfg para solucionar esto?
    Muchas gracias.

  8. Hola! Buen artículo, sin embargo tengo una duda, qué configuraciones estarían por defecto si instalado un server desde 0?

    Actualmente uso AWS un servidor Centos 7, e instale VestaCP. Sin embargo, no sé si la RAM del servidor AWS que es 1 GiB, influya o si sea un número adecuado para una RAM pienso que sí. Bueno, mi problema es, cada vez que hago una query al servidor, a veces se caé mysqld, y se reinicia después de unos segundos como un tipo timeout, y WordPress aparece con el mensaje de «Error al establecer conexión con la db», y pues se restaura después que paso la query, mi duda es cuál variable puede ser la que debería cambiar para que no vuelva a pasar eso? Muchas gracias.

Deja una respuesta

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

Utilizamos cookies propias y de terceros para obtener información estadística, mostrar publicidad personalizada a través del análisis de tu navegación, así como para interactuar en redes sociales. Si continúas navegando, consideramos que aceptas nuestra Política de cookies. ACEPTAR

Aviso de cookies