Optimizar el rendimiento de una base de datos puede ser una tarea muy compleja, ya que en muchas ocasiones no disponemos de suficiente información o la que tenemos nos hace intentar optimizar consultas o procesos que no merecerá la pena ser optimizados ya que el beneficio puede ser ínfimo.

MySQL pone a nuestra disposición una serie de parámetros y utilidades que pueden ahorrarnos mucho trabajo y facilitarnos la vida a la hora de intentar arreglar aquellas consultas de las que realmente podamos sacar una mejora sensible en el rendimiento.

El primer paso será activar el log dedicado al trazado de consultas lentas. Podémos comprobar fácilmente si lo tenemos ya activado utilizando este comando:

mysqladmin var |grep log_slow_queries
log_slow_queries | OFF

Si el resultado fuera ON, ya tenemos el parámetro activado, pero, al venir en estado OFF por defecto, lo más habitual será que no esté activado, por lo que deberemos activarlo.

Para sistemas Debian/Ubuntu, el fichero de configuración de MySQL normalmente estará en la ruta /etc/mysql/my.cnf , para activar la monitorización cambiaremos las siguientes líneas del fichero:

[mysqld]
long_query_time = 5
log-slow-queries = /var/log/mysql/mysql-slow.log

De esta forma le estamos indicando al servidor que monitoree las consultas más lentas de 5 segundos y guarde el log en /var/log/mysql/log-slow-queries.log (ambos parámetros se pueden ajustar a nuestras necesidades). Para que los cambios tengan efecto, se debe reiniciar el servidor MySQL (ojo, esto debe realizarse en un periodo en el que estemos seguros de que no va a afectar):

sudo /etc/init.d/mysql restart

A partir de ahora ya podremos disponer de una traza de todas las consultas que tarden más de lo definido en el fichero de configuración para poder estudiarlas. Para ello, disponemos del comando mysqldumpslow, que nos devolverá un resumen de las consulta, incluyendo el tiempo que ha tardado y los bloqueos realizados entre otros datos.

Una vez identificadas las consultas, podremos utilizar el comando EXPLAIN para obtener el plan de ejecución de la consulta y revisar la mejor manera de optimizarla.

A partir de MySQL 5.0, podemos monitorizar las consultas de administración más costosas con el parámetro de servidor --log-slow-admin-statements y aquellas consultas que se están ejecutando sin utilizar índices con --log-queries-not-using-indexes, para ambas opciones es necesario reiniciar el servidor.

Todo sea dicho, en Drupal el módulo devel nos proporciona una visión más específica de las consultas ejecutadas y su duración, además de indicarnos el proceso que las ha ejecutado.

La ventaja que tiene el módulo devel reside en que no necesitamos reiniciar el servidor para obtener estos resultados, pero por otro lado, la monitorización a nivel de servidor es menos costosa, ya que solamente se ocupa de aquellas consultas que tardan más tiempo y más potente, ya que nos ofrece herramientas de estudio de los datos obtenidos como mysqldumpslow.

Más información aquí y mucha más en la documentación oficial de MySQL.