1 Optimización de consultas en MySQL

En esta práctica vamos a estudiar cómo podemos configurar MySQL para detectar aquellas consultas que tienen un tiempo de ejecución elevado y necesitan ser optimizadas.

Se recomienda la lectura de la sección The Slow Query Log de la documentación oficial.

1.1 Archivo de configuración de MySQL en Ubuntu - mysqld.cnf

El archivo de configuración que necesitamos modificar lo encontramos en la siguiente ruta:

/etc/mysql/mysql.conf.d/mysqld.cnf

1.2 Opciones de configuración

Los parámetros de configuración que vamos a utilizar son los siguientes:

• slow_query_log
• slow_query_log_file
• long_query_time
• log-queries-not-using-indexes

1.2.1 slow_query_log

Permite habilitar o deshabilitar el registro de consultas lentas. El valor 0 deshabilita el registro y el valor 1 lo habilita. Ejemplo:

slow_query_log = 1

Puedes encontrar más información en la documentación oficial.

1.2.2 slow_query_log_file

Permite indicar la ruta y el nombre del archivo que se usará para registrar las consultas lentas. Ejemplo:

slow_query_log_file = /var/log/mysql/mysql-slow.log

Puedes encontrar más información en la documentación oficial.

1.2.3 long_query_time

Si el tiempo de ejecución (en segundos) de una consulta es superior al valor especificado en este parámetro, la consulta será interpretada como una consulta lenta y si está habilitado el registro de consultas lentas, ésta se registrará en el archivo de log. Ejemplo:

long_query_time = 2

Puedes encontrar más información en la documentación oficial.

1.2.4 log-queries-not-using-indexes

Permite registrar en el archivo de log aquellas consultas que no están haciendo uso de índices.

Puedes encontrar más información en la documentación oficial.

1.3 Ejemplo de una posible configuración

Los parámetros que hemos mencionado anteriormente tienen que estar dentro de la sección [mysqld]. Por ejemplo, una posible configuración podría ser la siguiente:

[mysqld]
...
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
...

Una vez que hemos modificado el archivo de configuración tenemos que reiniciar el servicio de MySQL para que se apliquen los cambios.

sudo /etc/init.d/mysql restart

1.4 Ejemplo de configuración en Docker - mysqld.cnf

En primer lugar vamos a crear el archivo mysqld.cnf en un directorio de nuestra máquina local que luevo vamos a utilizar como volumen en el contenedor Docker.

En este ejemplo el archivo mysqld.cnf estará ubicado en el directorio local $(pwd)/conf.

El contenido del archivo es el siguiente.

# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
symbolic-links=0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Una vez creado el archivo de configuración mysqld.cnf vamos a crear una instancia de mysql y vamos a crear un volumen entre el directorio local donde tenemos el archivo de configuración y el directorio /etc/mysql/mysql.conf.d del contenedor Docker.

El comando que utilizaremos para crear y ejecutar la instancia de Docker es el siguiente:

docker run -d --rm --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -v $(pwd)/conf/:/etc/mysql/mysql.conf.d mysql:5.7.25

1.5 Comprobamos la configuración desde MySQL Workbench

Desde MySQL Workbench podemos consultar el valor de las variables globales slow_query_log, slow_query_log_file, long_query_time y log_queries_not_using_indexes para comprobar que la configuración se ha realizado correctamente.

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

1.6 Variables globales de configuración

También es posible modificar el valor de las variables globales slow_query_log, slow_query_log_file, long_query_time y log_queries_not_using_indexes desde MySQL Workbench haciendo lo sigiente:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 1;

Tenga en cuenta que el valor de estas variables se perderán una vez que se cierre la sesión, sin embargo si las configuramos a través del archivo de configuración mysqld.cnf mantendrán su valor.

1.7 Archivo de log

Para ver en tiempo real cómo va cambiando el contenido del archivo de log que registra las «consultas pesadas», podemos ejecutar el siguiente comando:

tail -f /var/log/mysql/mysql-slow.log 

1.8 Visualización del archivo de log con mysqldumpslow

La utilidad mysqldumpslow nos permite parsear los archivos de log que contienen las consultas pesadas y mostrar un resumen con la información más relevante.

A continuación se muestra un ejemplo de uso:

mysqldumpslow /var/log/mysql/mysql-slow.log

Por defecto, mysqldumpslow muestra un listado de las consultas ordenado por la media del tiempo de ejecución (equivalente a la opción -s at, pero podemos modificar el criterio de ordenación de la lista con la opción -s.

Los valores que podemos usar con la opción -s son los siguientes:

• t: Ordena por el tiempo de ejecución.
• at: Ordena según la media del tiempo de ejecucuión.
• l: Ordena por el tiempo de bloqueo.
• al: Ordena según la media del tiempo de bloqueo.
• r: Ordena por el número de filas enviadas.
• ar: Ordena según la media del número de filas enviadas.
• c: Ordena por el número total de filas.

Puedes encontrar más información sobre mysqldumpslow en la documentación oficial.

1.9 Ejercicios

1. Configura tu sistema gestor de base de datos para que registre aquellas consultas que tardan más de 3 segundos en ejecutarse.

2. Realice algunas consultas sobre las bases de datos jardineria y universidad que tengan un tiempo de ejecución superior a 3 segundos.

3. Configura tu sistema gestor de base de datos para que registre aquellas consultas que no usan índices.

4. Realice algunas consultas sobre las bases de datos jardineria y universidad que no hagan uso de índices.

5. ¿Cómo podemos optimizar el tiempo de ejecución de las consultas que hemos detectado? Realice las operaciones necesarias para optimizar las consultas anteriores y vuelva a realizar las consultas para comparar los tiempos de ejecución en ambos casos.

1.10 Referencias

• MySQL
• The Slow Query Log

2 Licencia

Esta página forma parte del curso Bases de Datos de José Juan Sánchez Hernández y su contenido se distribuye bajo una licencia Creative Commons Reconocimiento-NoComercial-CompartirIgual 4.0 Internacional.