MySQL Slow Queries

Vie, 05/07/2010 - 19:49

Uno de los problemas más comunes de carga en el servicio MySQL es debido a que hay consultas SQL que tardan un tiempo excesivo en ejecutarse y completarse.

Existe la opción de activar el logging para este tipo de consultas, de modo que podamos hacer debug e identificar las consultas lentas (Slow Queries) y optimizarlas. A continuación configuraremos nuestro MySQL para poder loguear las slow-queries.

Configuración

1.- El primer paso es activar el logging, para ello revisamos primero si está activado:

mysqladmin var |grep log_slow_queries

log_slow_queries                 OFF                       

En caso de estar desactivado lo activamos, añadiendo lo siguiente al fichero de configuración de mysql (my.cnf):

log-slow-queries=/var/log/mysql-slow-queries.log
long_query_time = 5
log-queries-not-using-indexes

log-slow-queries será el fichero en el cual se logearán las consultas lentas.

long_query_time será el tiempo a partir del cual se considera una query lenta, en este caso 5 segundos.

log-queries-not-using-indexes lo activaremos si queremos loguear las consultas que no utilicen índices (útil pues suelen ser las más lentas y menos optimizadas).

 

2.- Creamos el fichero del log y le asignamos el propietario correspondiente:

touch /var/log/mysql-slow-queries.log
chown mysql.root /var/log/mysql-slow-queries.log

3.- Reiniciamos el servicio para que los cambios surtan efecto:

/etc/init.d/mysql restart

 

Análisis

Analicemos una consulta de ejemplo mayor de 5 segundos, que ha sido logueada como slow query:

# Time: 080213 13:54:17
# User@Host: XXX_phpbb1[XXX_phpbb1] @ localhost []
# Query_time: 11  Lock_time: 0  Rows_sent: 6  Rows_examined: 8776
SELECT t.forum_id, t.topic_id, p.post_time

Tenemos los siguientes campos:

• Time: Hora en la que se ha ejecutado la consulta.
• Query_time: Tiempo que ha tardado la consulta en ejecutarse.
• Lock_time: Tiempo de bloqueo que se ha necesitado para que la consulta se ejecute.
• Rows_sent: Registros enviados desde la consulta SQL.
• Rows_examined: Cantidad de registros examinados por la consulta. Este valor es de extrema utilidad para revisar si la consulta está correctamente optimizada, y si es necesario añadirle indices)
• User@Host:Usuario y Host desde el que se ha ejecutado la consulta, local o remoto.
• Finalmente la consulta SQL

 

Comando mysqldumpslow

El comando mysqldumpslow nos permite visualizar y determinar las consultas más lentas, ejemplo:

mysqldumpslow -t 5 /var/log/mysql-slow-queries.log

Con dicho comando sacaremos por pantalla las 5 consultas más lentas parseando el log mysql-slow-queries.log,