Cómo habilitar el slow query log de MySQL

A la hora de optimizar nuestras bases de datos, posiblemente nos encontremos con el problema de que no seamos capaces de distinguir qué consultas tardan un elevado tiempo en ejecutarse. MySQL incluye una pequeña utilidad exactamente para este tipo de situaciones, así que vamos a ver cómo configurar en mysql el low_slow_queries.

Si estamos notando que nuestra base de datos empieza a resultar pesada incluso si no ha cambiado la cantidad de consultas o visitas a nuestro servidor web, puede que estemos sufriendo algún tipo de problema relacionado con el tiempo de ejecución de consultas, y aunque lo primero que podamos llegar a pensar es que cambiando la base de datos a un disco duro SSD sea lo más fácil y rápido, en muchas ocasiones bastará con simplemente tunear un poco nuestras consultas o incluso variar alguna configuración como el tamaño del caché.

En este caso lo que vamos a ver es la forma de habilitar el registro de las consultas que tarden un determinado tiempo en ejecutarse, para así poder o bien editar las tablas en caso de no contener índices, o incluso ajustar las consultas si vemos que se están haciendo una forma poco óptima.

Cómo guardar las consultas lentas en MySQL

En un principio estamos trabajando con linux, pero esta configuración nos valdrá también para cualquier máquina con otro sistema operativo, ya que la configuración de mysql es independiente del sistema que lo hospede.

En primer lugar comentar que hará falta acceso a la consola y a la cuenta de super usuario, sobre la cual será lo más normal tener configurado el servidor mysql. Una vez conectados con nuestra cuenta de superuser vamos a editar el archivo my.cnf (CentOS, cada so lo llama de una forma).

1. Editar my.cnf
   Desde la consola ejecutaremos el siguiente comando (en lugar de nano puedes usar vi si te es más cómodo)

   nano /etc/my.cnf

2. Habilitar el log de long queries
   Ahora par habilitar el registro de las consultas largas buscaremos la parte donde indica [mysqld], y justo a partir de la siguiente línea ( o dentro de esta misma sección) incluiremos las siguientes líneas:

   log_slow_queries = 1;
   long_query_time = 10
   slow_query_log_file = /var/log/mysqld_long_query.log
   

   log_slow_queries: habilitará la función de guardado de las consultas lentas
   long_query_time: en segundos, indicará el tiempo mínimo que tarda una consulta para considerarse lenta
   slow_query_log_file: el archivo donde queremos que nos guarde el registro de las consultas lentas

3. Guardar y reiniciar
   Ahora solo nos faltara guardar el archivo que estamos editando, cerrarlo y ejecutar el comando para reiniciar el servidor mysql, en CentOS por ejemplo es

   service mysqld restart

A partir de este momento podremos estar al tanto en el archivo que hayamos indicado para seguir el progreso de las consultas que tarden más de los segundos que hayamos indicado. De esta forma seremos capaces de identificar un problema de retraso de escritura en mysql u otros varios que sean provocados por un guardado lento en nuestra base de datos.

¿MySQL te ha empezado a ir lento? ¿Cómo has optimizado tus consultas?