Optimizar MySQL en servidores Linux: MySQLTuner

Última actualización el Sábado, 18 de Abril de 2009 09:21 Escrito por Abdel Karim

Articulos - Sistemas

Uno de los mayores problemas a los que se enfrenta cualquier administrador de un servidor, es la optimización de MySQL, debido a las complejas programaciones de los programas más usados en Internet, como CMS, Blogs, Portales, galerías de imágenes.

Un elevado número de  consultas utilizando enrevesadas consultas, uso de campos no optimizados, tablas que guardan datos innecesarios y muy costosos, uso inadecuado o inexistente de los índices, y un sin fin de errores de programación hacen que muchas veces potentes servidores no puedan con la carga de trabajo de MySQL.

Aunque existen otras alternativas, como tunning-primer.sh en nuestro artículo describiremos el uso de MySQLTuner, un script creado en PERL, que nos permitirá encontrar una mejor optimización cuando no es posible optimizar la programación, que suele ser el 99,9% de los casos.

Optimizar MySQL con MySQLTuner

MySQLTuner es un script es un script escrito en PERL por Major Hayden que podremos encontrar disponible en MySQLTuner.pl bajo licencia GNU GPL versión 3.

Este script nos ayudará a optimizar MySQL a nivel administración del sistema, mediante la modificación del fichero de configuración de MySQL (my.cnf) siguiendo las recomendaciones que el programa nos ofrecerá, ganando en eficacia y estabilidad.

No es la solución adecuada para un servidor que solo aloja un sitio, de gran trafico y que presenta problemas, pues estos son inherentes a su programación, pero es de mucha utilidad allá, donde coexisten distintos usuarios y bases de datos, siendo imposible fomentar en los usuarios, una mejora de su código.

Requerimientos del sistema

Los requerimientos del sistema para MySQLTuner son pequeños y lo hacen compatible con la gran mayoría de los sistemas UNIX instalados (Linux, BSD y variantes, y Solaris). Para ejecutar el script necesitarás:

• Perl 5.6 o superior
• MySQL 3.23 o superior (el script esta parcialmente soportado por MySQL 5.1 y 6.0)
• Sistema Unix/Linux
• Usuario de acceso a MySQL sin restricciones (root recomendado para versiones anteriores a la 5.0)

Obtener e instalar MySQLTurner

Descargar la ultima versión estable desde su sitio oficial

# wget mysqltuner.pl 

O descargar la versión comprimida

# wget mysqltuner.pl/mysqltuner.pl.gz

Ejecutar el script haciéndolo primero ejecutable

# chmod +x mysqltuner.pl
# ./mysqltuner.pl

Trucos para su utilización

Lo más importante para obtener buenos resultados, es no realizar modificaciones recomendadas por el script, cuando el servidor MySQL no lleva mas de 24 horas activo, ya que los datos que recoge del comando SHOW STATUS no ofrecen datos suficientes para un análisis adecuado, y podemos seguir consejos erróneos.

Es conveniente ir ajustando poco a poco los valores, realizando comprobaciones rutinarias durante un tiempo.

Para evitar que cada vez que lo queramos ejecutar o para incorporarlo como veremos más adelante en una tarea cron que nos permita tener un seguimiento durante un tiempo de nuestros resultados:

 >>  MySQLTuner 0.9.9 - Major Hayden <major arroba mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: 
Please enter your MySQL administrative password:

Te proponemos usar el fichero de configuración de usuario, que te permitirá trabajar no solo con este script sin autentificarte, sino operar con el comando MySQL, mysqldump, y otros, sin necesidad de pasar los datos de usuario y contraseña.

# vi .my.cnf
[client]
user="root"
pass="PASSWORD"

Para hacer un seguimiento del script podemos crear una tarea cron que nos permita recibir en nuestro correo una copia del volcado del programa, y cada dos días por ejemplo, realizar las modificaciones planteadas.

Antes de ejecutar el script es conveniente y además una buena práctica para mejorar la velocidad de MySQL el optimizar todas las bases de datos con el comando mysqlcheck.

# crontab –e
@daily mysqlcheck --optimize --all-databases; /usr/local/mysqlturner/mysqltuner.pl   #MysqlTuner

Es importante ajustar de manera adecuada el máximo posible de uso de memoria por parte de MySQL. Este valor nos viene comunicado en la línea:

[OK] Maximum possible memory usage: X.XXG (XX% of installed RAM)

El valor vendrá dado por el numero máximo de usuarios, que será el factor de multiplicación de otros valores ajustables como son los tamaños de los buffers de las llaves (key buffers). 

En el caso de que en la misma máquina se ejecute un servidor Web como Apache, debemos tener en cuenta los consumos de memoria de Apache y su configuración, ya que esta estará relacionada directamente con el número máximo de usuarios, que deberá tener en cuenta el número máximos de conexiones concurrentes de Apache.

Debemos pues encontrar el equilibrio, entre ambas aplicaciones. En esto, MySQLTuner nos ayuda ya que en el caso de alcanzar el número máximo de conexiones de MySQL nos lo dirá en el informe. 

Es recomendable, una política conservadora en los primero paso de optimización para ir mejorando poco a poco, nuestra configuración.

Conozco casos de administradores que colocan valores, que en casos normales optimizan la máquina de forma adecuada, pero en caso de una concurrencia elevada, el servidor alcanza valores de estrés, que dejan inestable todos los servicios, siendo necesario recurrir a scripts de control para el reinicio de los servicios, con el fin de vaciar la pila de memoria.

Mantenimiento de los ficheros log de MySQL.

Si hacemos caso de las recomendaciones de configurar MySQL con la generación de log para las consultas lentas (muchas veces es inútil, pues todos sabemos cuales son, y están presentes en la mayoría de los CMS, y las aplicaciones instaladas), es conveniente crear una política de rotación de logs, pues el tamaño de este log puede ser muy elevado..

Podemos utilizar el sistema de Mantenimiento de ficheros de registro (log) de MySQL aunque yo prefiero el uso de las tareas cron para rotar


Pagina semi-oficial del proyecto