Localizar y optimizar consultas lentas en MySQL

mysql-logoHace unas semanas estuve en un curso de Administración avanzada de MySQL. Fue un curso breve, pero muy interesante. Una de las cosas que mas me gusto fueron las herramientas que tiene MySQL para localizar las consultas lentas y para ayudarte a mejorarlas. Veamos por partes, primero como localizarlas, y luego como tratar de reducir el tiempo que tardan estas consultas.

1.- Localizar consultas lentas

Para localizar las consultas que tardan mas de un determinado tiempo en resolverse, tenemos que activar el log de MySQL llamado MySQL Slow y marcar un tiempo mínimo de registro de las consultas. Si ponemos 2 segundos, todas las consultas que pasen de dos segundos, se guardaran en este log. Para hacer esto, vamos a nuestro archivo de configuración de MySQL my.cnf, que puede estar en /etc/my.cnf o /etc/mysql/my.cnf dependiendo de servidores. Editamos el archivo y ponemos o descomentamos estas lineas, que estarán siempre debajo de la linea que pone [mysqld]:

log_slow_queries = /var/log/mysql-slow.log
log_queries_not_using_indexes =1
long_query_time = 2

  • log_slow_queries sirve para decirle al sistema que las registre y donde guardarlas, por defecto os aparecerá esa ruta, o /var/log/mysql/mysql-slow.log dependiendo del tipo de servidor.
  • log_queries_not_using_indexes se puede poner a 0 por si no queremos registrar las consultas que no usan index, que por lo normal, serán mas lentas.
  • long_query_time el tiempo en segundos. Aquí no hay un standard de lentitud o rapidez, dependerá de los números de registros y de la cantidad de información que guardemos.

Una vez hecho esto, hay que reiniciar el servicio y el sistema empezara a registrar las consultas. Para ello desde la linea de comandos service mysql restart o service mysqld restart. 

Para ver los logs, lo podemos hacer como para ver cualquier fichero, con more, tail, gedit,… Aquí veremos las consultas en orden de registro, con su tiempo de ejecución y algún dato mas, pero cuando el log se empieza a llenar, este sistema no es muy cómodo, por eso, MySQL nos ofrece otra herramienta, que es mysqldumpslow. La sintaxis seria la siguiente, desde linea de comandos:

mysqldumpslow /var/log/mysql-slow.log

Esto nos mostrará un resumen de las consultas, ordenadas de mayor tiempo a menor tiempo, y con un contador de veces que se ha repetido la consulta. Veamos un ejemplo:

Count: 146  Time=207.41s (30282s)  Lock=0.11s (15s)  Rows=6.1 (892), user[databse]@[server]

El Count es el numero de veces que se ha repetido esa consulta. El Time es el tiempo medio. El Lock, el tiempo medio que la consulta ha bloqueado la tabla. Rows el numero de registros que ha devuelto. Los valores entre paréntesis son los máximos. Debajo aparece la consulta, cambiando los valores dinámicos por ‘S’ para candes de texto y N para números.

De esta forma ya tendríamos localizadas las consultas que mas veces se repiten y mas lentitud generan, ahora es el momento de solucionar esto.

2.- Optimizar consultas lentas

Para poder revisar la consulta, necesitamos la consulta original (sin S ni N), así que buscamos en el log una de las consultas que se ha repetido. La copiamos y la pegamos en nuestro editor de consultas (MySQL Workbench, linea de comandos,…). La podemos ejecutar si queremos para saber mejor que es lo que hace esta consulta y para comprobar la lentitud. Una vez tenemos la consulta, vamos a utilizar el comando explain de MySQL. Colocamos explain antes de la consulta y ejecutamos. El resultado sera una tabla con diferentes campos explicando la consulta y subconsultas (si las habría). Aquí podríamos profundizar mucho en cada uno de los campos, pero vamos a basarnos en 4 de ellos:

  • Type: El tipo de unión que se esta utilizando. De mejor a peor tenemos system, const, eq_ref,  ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, y ALL
  • Posible_keys: Aquí aparecen los posibles indices que se pueden utilizar en la consulta. Si aparece NULL, es que no hay ninguno. Ya sabríamos cual es uno de los problemas.
  • Key: Nos indica el indice que esta utilizando para la consulta. Al igual que antes, si aparece NULL, ya tendríamos una idea de lo que pasa. Se puede crear un index mas apropiado a forzar a la consulta a usar ese indice con FORCE INDEX o USE INDEX.
  • Rows: Este campo nos indica la cantidad de filas que MySQL estima que va a utilizar para realizar la consulta. A mayor numero, mayor lentitud. Por ejemplo, una consulta de tipo ALL, va a revisar todas las filas de la tabla, incluso a veces, estima un numero mayor.

Esta claro que para mejorar nuestras consultas, hay que comprender muy bien los indices. Aquí os dejo un articulo sobre indices y optimización de consultas, en el que viene todo muy claro, ademas, en la pagina 4, veréis que vienen todos los tipo de consultas explicadas una a una.

Una vez que tenemos los indices creados y las consultas optimizadas, también hay que tener en cuenta la herramienta ANALYZE TABLE, con la que actualizamos los indices de la tabla, sobre todo si tenemos tablas en las que hay muchos registros.

¿Te ha gustado este artículo? ¿te ha servido de ayuda? No dudes en comentarlo o compartirlo!

Un saludo, Fran Aramayo

Deja una respuesta