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.