Detectar consultas lentas en MySQL

Es posible que, a veces, las páginas web alojadas en nuestro servidor tarden mucho en cargarse. Si nuestras páginas utilizan MySQL, es posible que algunas consultas no sean óptimas y esto sea la causa de la lentitud de las mismas.

A medida que crecen las aplicaciones es más difícil localizar qué consultas son las que están ralentizando nuestro servidor, y localizar el problema puede ser un trabajo largo y complicado.

Afortunadamente, el propio MySQL nos facilita esta tarea, brindándonos la posibilidad de obtener un registro completo de las consultas lentas que se ejecutan en nuestro servidor. Para ello, tan solo debemos activar el log de consultas lentas para que MySQL las vaya recopilando y así poder ver cuáles son aquellas que más se repiten y poder actuar en consecuencia.

Dependiendo de la versión de MySQL instalada en el servidor tendrás que realizar unas u otras modificaciones. Vamos a ver cómo hacerlo para versiones anteriores a la versión 5.7 y en la versión 5.7 y posteriores.

Para consultar la versión de MySQL instalada, ejecuta el siguiente comando por SSH.

mysql -V

Y obtendrás un resultado similar a este, donde puedes ver la versión de MySQL.

mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper

Versiones anteriores a MySQL 5.7

Accede por SSH al servidor y abre el archivo de configuración de MySQL utilizando el comando

/etc/mysql/my.cnf

Localiza las líneas donde se configura el log:

# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

Y descomenta las líneas que necesites. Este es el significado de cada una de ellas:

- log_slow_queries: indicamos el nombre del fichero que creará el
servidor.

- long_query_time: indicamos el valor, en segundos, a partir del cual MySQL ha de considerar que una consulta es lenta.

- log-queries-not-using-indexes: si descomentamos esta línea, MySQL también almacenará en el log aquellas consultas que no usan ningún índice.

En este ejemplo solo vamos a descomentar las dos primeras, indicando que guarde en el log aquellas consultas cuyo tiempo de ejecución supere los 2 segundos. El archivo quedaría así:

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes

Guarda el fichero y reinicia el servicio MySQL con el comando:

/etc/init.d/mysql restart

Versiones posteriores a MySQL 5.7

Accede por SSH al servidor y abre el archivo de configuración de MySQL utilizando el comando

nano /etc/my.cnf

Localizamos las líneas donde se configura el log:

# Here you can see queries with especially long duration
# slow-query-log = 1
# slow-query-log-file = /var/lib/mysql/slow.log
# long-query-time = 2
# log-queries-not-using-indexes

Y descomentamos las líneas que nos interesen. Este es el significado de cada una de ellas:

- slow-query-log: activar o desactivar el log de consultas lentas.

- slow-query-log-file: indicamos el nombre del fichero que creará el servidor.

- long-query-time: indicamos el valor, en segundos, a partir del cual MySQL ha de considerar que una consulta es lenta.

- log-queries-not-using-indexes: si descomentamos esta línea, MySQL también almacenará en el log aquellas consultas que no utilizan ningún índice, independientemente del tiempo que se tarde en ejecutar.

En nuestro ejemplo, vamos a descomentar las tres primeras líneas, indícandole que guarde en el log aquellas consultas cuyo tiempo de ejecución supere los 2 segundos. Por tanto, las líneas quedarían así:

# Here you can see queries with especially long duration
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/slow.log
long-query-time = 2
#log-queries-not-using-indexes

Guardamos el fichero y reiniciamos el servicio MySQL:

/etc/init.d/mysql restart

A partir del reinicio, MySQL empezará a recoger en el fichero de log aquellas consultas que tarden más de 2 segundos en ejecutarse.

Ahora solo queda esperar a que se recojan los suficientes datos para poder tener un listado completo de aquellas consultas que están dando problemas.

Para consultar el log, MySQL también nos proporciona una herramienta: mysqldumpslow. Con ella, podremos leer el log filtrando y ordenando los resultados de forma que nos resulte fácil interpretar los datos. Por ejemplo, una lectura común es que sólo queramos ver las 15 consultas más lentas almacenadas en el log. Para ello utilizaremos la siguiente línea de comandos:

mysqldumpslow -s c -t 15 /var/mysql-slow.log

Con la opción "-s" indicamos el criterio de ordenación (en el ejemplo, "c" es por número de ejecuciones de la consulta) y con "-t" indicamos el número de consultas que nos ha de mostrar en pantalla (en nuestro ejemplo serán 15).

Para ampliar la información, podéis dirigiros a la página oficial de MySQL sobre el log de consultas lentas o consultar al detalle el uso de mysqldumpslow (en inglés).

¡Esperamos que os sirva de ayuda!

Nota: las rutas utilizadas pueden variar dependiendo de la distribución Linux instalada en el servidor.