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