Slow Queries en MySQL

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.

El primer paso es activar el logging, para ello primero revisamos 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, y 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 logear las consultas que no utilicen índicides (útil pues suelen ser las más lentas y menos optimizadas).

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

Tendremos que reiniciar el servicio MySQL para que los cambios surtan efecto:

/etc/init.d/mysql restart

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

Por último comentar el comando mysqldumpslow, que nos será de extrema utilidad para 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, a partir de aquí, puedes hacer muchas cosas, como enviartelas por correo cada día creando un crontab, generar un fichero a partir de ellas para semanalmente revisar las de toda la semana, etc, etc.

Para más opciones:

mysqldumpslow --help