5 formas de encontrar y optimizar consultas MySQL problemáticas

A la hora de detectar problemas en servidores MySQL (cuellos de botella, uso excesivo de CPU, bajo rendimiento, lentitud, etc) es conveniente conocer las distintas opciones que nos ofrece MySQL para dar con el problema. Vamos a ver algunas de ellas:

Activar el Slow Query Log

Activar el log de consultas lentas en MySQL supondrá que en el momento que una consulta SQL supere el tiempo de ejecución establecido con la variable long_query_time quedará registrada en el log establecido. Para activarlo añadimos las siguientes directivas en el fichero de configuración my.cnf:

log-slow-queries=/var/log/mysql-slow-queries.log
long_query_time = 5

Este tema ya lo traté en un artículo anterior, para profundizar en ello acceded a la entrada a través del siguiente enlace: Slow Queries en MySQL.

Usar SHOW PROCESSLIST

Tanto desde la propia shell desde MySQL como usando mysqladmin podemos visualizar a tiempo real las consultas SQL que se están ejecutando en el servidor:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 35 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Como podéis observar, nos muestra el estado de la consulta (State), la base de datos, el comando que está ejecutando, el usuario, el host y el tiempo de ejecución, siendo este último de los puntos más importantes. Recordad que podéis formatear la salida con \G:

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 35
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
1 row in set (0.00 sec)

Y con mysqladmin desde la shell bash:

$ mysqladmin processlist -u root --pXXXX
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 42 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Si queréis ver el contenido completo de la consulta SQL que se está ejecutando probablemente necesitéis utilizar SHOW FULL PROCESSLIST; en lugar de SHOW PROCESSLIST:

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 35 | root | localhost | NULL | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

Optimizar las consultas SQL con EXPLAIN

Gracias a EXPLAIN podemos obtener toda la información sobre el modo en el que una consulta SQL se ejecutaría en el servidor. Es extremadamente útil para conocer la configuración de índices en las tablas, los índices que podrían ser configurados para mejorar su rendimiento, el número de filas que se revisan, el tipo de query, etc.

Aquí tenéis un ejemplo de EXPLAIN contra la tabla City de la base de datos de muestra de MySQL (world). Para profundizar en el funcionamiento de EXPLAIN os recomiendo revisar la documentación oficial de MySQL.

mysql> EXPLAIN select * from City;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from City \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
        Extra:
1 row in set (0.00 sec)

Activar el log general

Una medida quizás un poco agresiva y que no debería ser ejecutada en servidores de producción. Este log (General Query Log) registrará todas las consultas SQL que se ejecuten en el servidor. Para activarlo debemos especificar la ruta del log mediante –general_log_file= (MySQL 5.1.29+). También podemos activarlo y desactivarlo a tiempo real desde la shell MySQL del siguiente modo:

mysql> SET GLOBAL general_log = 'OFF';
mysql> SET GLOBAL general_log = 'ON';

MySQL Query Analyzer

Si pese a las opciones anteriores seguís sin poder detectar las consultas SQL problemáticas y optimizarlas, quizás MySQL Query Analyzer sea una opción factible. Es un software de pago perteneciente a la versión Enterprise que de forma gráfica monitoriza las consultas SQL del servidor y detecta el origen de los problemas. A mi entender, las otras opciones deberían ser suficiente (junto con buenos conocimientos de DBA todo sea dicho) para detectar y solucionar cualquier problema que se presente.

 

Como habréis podido observar, la combinación de estas opciones en distintos ordenes nos permitirá arreglar muchos problemas con consultas SQL, primero detectamos los problemas con los logs de slow_queries o SHOW PROCESSLIST y después optimizamos con EXPLAIN las consultas detectadas en los pasos anteriores.