MySQL - Configurar my.cnf

[Swarlog]

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

Código [Seleccionar] Expandir

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:
   

Código [Seleccionar] Expandir

wget http://mysqltuner.pl/ -O mysqltuner.pl
Y ahora ejecutamos el archivo perl que nos acabamos de descargar, para ello usamos el siguiente comando:

Código [Seleccionar] Expandir

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