Las consultas de larga duración pueden ser un serio problema para el rendimiento de su base de datos MySQL, causando de todo, desde tiempos de respuesta lentos hasta cuellos de botella en toda regla que afectan a todos los usuarios. Controlar estas molestas consultas (saber qué son, por qué ocurren y cómo administrarlas) es clave para mantener su base de datos funcionando sin problemas.

Ya sea para detectarlos temprano, detenerlos en seco o configurar una manera de manejarlos automáticamente, esta guía lo tiene todo cubierto.

¿Qué es una consulta de larga duración?

Una consulta de larga duración en MySQL es una consulta que tarda un período inusualmente largo en ejecutarse.
La duración específica que clasifica una consulta como "de larga duración" puede variar, según los estándares de rendimiento de su aplicación. Generalmente, si una consulta dura más de lo habitual y comienza a ralentizar su base de datos, se considera de larga duración.

Causas fundamentales de las consultas de larga duración

Las causas de las consultas de larga duración pueden ser diversas:

• Falta de indexación adecuada – Sin una indexación adecuada, MySQL debe escanear toda la tabla para recuperar los datos requeridos. Este proceso es muy ineficiente, especialmente para tablas grandes, ya que consume mucho tiempo y recursos.

• Situaciones de carga pesada – Cuando el servidor maneja un gran volumen de consultas o procesa algunas complejas simultáneamente, los recursos disponibles (como CPU y memoria) se agotan. Esta competencia por los recursos puede retrasar la ejecución de las consultas, lo que provoca tiempos de ejecución más largos, especialmente durante los períodos de uso pico.

• Contención de bloqueo: esto ocurre cuando varias transacciones requieren acceso a los mismos datos al mismo tiempo, pero se bloquean porque otras operaciones mantienen los bloqueos necesarios. Por ejemplo, si una transacción está actualizando una fila, otra transacción que quiera leer o actualizar la misma fila tendrá que esperar hasta que la primera se complete y libere el bloqueo.

• Normalización inadecuada: si bien la normalización ayuda a evitar la redundancia de datos y mejora la integridad de los datos, las bases de datos demasiado normalizadas pueden generar consultas complejas que involucran múltiples uniones. Estos pueden degradar el rendimiento. Por otro lado, la normalización insuficiente puede provocar una duplicación excesiva de datos, lo que da como resultado tablas más grandes y consultas más lentas.

• Uniones grandes: las consultas que implican unir tablas grandes, especialmente sin índices adecuados, pueden ser lentas. La base de datos debe hacer coincidir las filas de las tablas según las condiciones de unión, un proceso que puede consumir muchos recursos y ser lento sin una indexación eficiente.

Identificación de consultas de larga duración

Para gestionar eficazmente consultas de larga duración, primero debe identificarlas. A continuación se muestran algunos métodos:

1. Usando MOSTRAR LISTA DE PROCESOS

El MOSTRAR LISTA DE PROCESOS; El comando es una forma rápida de obtener una instantánea de todas las consultas activas que se ejecutan en su servidor. Este comando muestra cada consulta junto con varios datos clave, incluido el tiempo que lleva ejecutándose cada consulta. Las que tienen un valor alto de "Tiempo" probablemente sean consultas de larga duración. Así es como puedes usar este comando:

MOSTRAR LISTA DE PROCESOS COMPLETA;

Este comando enumerará todos los procesos actuales, mostrará quién los inició, qué tipo de comando están ejecutando y, lo más importante, cuánto tiempo han estado en ello. Si detecta alguna consulta que se ha estado ejecutando durante un tiempo inusualmente largo, esas son sus consultas de larga duración. Luego puedes decidir si profundizas en su optimización o simplemente los eliminas si están afectando el rendimiento de tu sistema.

2. El registro de consultas lento

Configurar el registro de consultas lento es otra gran estrategia para detectar esas consultas problemáticas. Esta práctica característica de MySQL registra cualquier consulta que tarda más en ejecutarse que un cierto umbral. No se trata solo de detectar consultas de larga duración: también puede ayudarle a identificar consultas que no utilizan índices de manera eficiente.

Para que el registro de consultas lentas esté en funcionamiento, necesitarás modificar algunas configuraciones en tu archivo de configuración de MySQL (ya sea my.cnf o my.ini):

• Slow_query_log – Establezca esto en 1 para habilitar el registro.
• Slow_query_log_file: especifique la ruta del archivo donde desea guardar el registro.
• Long_query_time: establece el tiempo mínimo de ejecución (en segundos) que califica para que una consulta se registre. Por ejemplo, configurarlo en "2" registrará cualquier consulta que tarde más de dos segundos en ejecutarse.

3. Esquema de desempeño

El esquema de rendimiento de MySQL es invaluable para una investigación más detallada. Esta herramienta está diseñada para monitorear eventos del servidor y rastrear métricas de rendimiento, brindándole una visión más clara de la ejecución de consultas y el rendimiento general del sistema.

Asegúrate de que esté habilitado en tu configuración de MySQL agregando la siguiente línea:

[mysqld]
esquema_rendimiento = ENCENDIDO

Una vez activado, puede explorar una variedad de tablas de esquemas de rendimiento para analizar el rendimiento de sus consultas. Por ejemplo, si desea identificar consultas de larga duración, es posible que desee consultar la tabla events_statements_history_long. Así es como puedes consultarlo:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS 'Duración (segundos)'
DESDE performance_schema.events_statements_history_long
DONDE TIMER_WAIT > 10000000000;

Esta consulta le ayuda a encontrar cualquier consulta que se haya estado ejecutando durante más de 10 segundos. Le brinda detalles como el texto SQL y cuánto tiempo ha estado ejecutándose cada consulta.

Eliminación manual de consultas de larga duración

Cuando haya identificado una consulta que está tardando demasiado y agotando los recursos de su sistema, tiene la opción de finalizarla manualmente. Esto se hace usando el comando KILL seguido del ID del proceso específico de la consulta.

Puede encontrar el ID del proceso ejecutando el comando SHOW PROCESSLIST, que muestra todos los procesos en ejecución actuales y sus respectivos ID. Busque en la lista consultas que muestren un valor alto de "Tiempo", que indica cuánto tiempo han estado ejecutándose.

Una vez que haya identificado una consulta problemática y haya anotado su ID de proceso, puede finalizarla usando el comando KILL:

KILL [ID de proceso];

Reemplace [ID de proceso] con el número real de la salida MOSTRAR LISTA DE PROCESOS.

Tenga cuidado con este enfoque. Detener abruptamente una consulta a veces puede causar problemas, como dejar sus datos en un estado inconsistente si la consulta estaba en medio de escribir o actualizar información.

Cómo automatizar la eliminación de consultas de larga duración

Configurar la automatización para manejar consultas de larga duración puede ser un verdadero salvavidas, evitando que esas consultas lentas o no optimizadas acaparen los recursos de su base de datos y ralenticen, o incluso bloqueen, todo el sistema. Pero tenga cuidado: utilizar esta herramienta sin las comprobaciones adecuadas puede ocultar problemas de rendimiento más profundos que requieren su atención.

Asegúrese siempre de contar con un registro y monitoreo completos para analizar el impacto de las consultas eliminadas en su aplicación, y considere mejorar esas consultas en lugar de simplemente eliminarlas automáticamente. Piense en la terminación automática como parte de una estrategia más amplia para optimizar el rendimiento, no como una solución que lo soluciona todo.

1. Habilite el Programador de eventos

En primer lugar, debe habilitar el Programador de eventos MySQL, que está deshabilitado de forma predeterminada. El Programador de eventos le permite crear y programar tareas que desea que el servidor ejecute automáticamente en momentos predefinidos. Ejecute el siguiente comando:

ESTABLECER GLOBAL event_scheduler = ON;

2. Cree un evento para eliminar consultas de larga duración

Con el programador habilitado, el siguiente paso es definir el evento real que monitoreará y eliminará las consultas de larga duración. El evento se ejecutará cada minuto para verificar si hay consultas que se ejecuten por más tiempo que un umbral especificado (por ejemplo, 60 segundos). Una vez identificado, eliminará automáticamente estas consultas. Aquí hay un desglose del código SQL para configurar este evento:

`CREAR EVENTO kill_long_running_queries
EN HORARIO CADA 1 MINUTO: especifica la frecuencia con la que se ejecuta el evento
HACER
COMENZAR
DECLARAR hecho INT DEFAULT FALSE;
DECLARAR proc_id INT; -- Variable para almacenar el ID del proceso de cada consulta
DECLARAR cur1 CURSOR PARA SELECCIONAR ID DE information_schema.processlist
DONDE Comando = 'Consulta' Y Tiempo > 60; -- Cambia '60' a tu umbral en segundos
DECLARAR CONTINUAR MANEJADOR PARA NO ENCONTRADO CONJUNTO hecho = VERDADERO;

ABRIR cur1;

read_loop: BUCLE
FETCH cur1 EN proc_id;
SI se hace ENTONCES
DEJAR read_loop;
FINALIZAR SI;
MATAR proc_id; -- Elimina el proceso identificado por proc_id
FINALIZAR BUCLE;

CERRAR cur1;
FIN;`

3. Configurar el tiempo máximo de ejecución de consultas en MySQL

Controlar el tiempo máximo de ejecución de una consulta ayuda a evitar que la base de datos quede ocupada por consultas que duran demasiado. Esto se hace usando la variable de sistema max_execution_time en MySQL 5.7.8 y versiones posteriores estableciendo un límite de tiempo de ejecución en todo el sistema para todas las consultas SELECT de solo lectura:

ESTABLECER GLOBAL max_execution_time = 2000;

Esto establece el límite en 2000 milisegundos (2 segundos)

Recuerde, esta configuración no se aplica a procedimientos, funciones o activadores almacenados y se restablece a los valores predeterminados al reiniciar el servidor a menos que se agregue a su archivo de configuración de MySQL:

[mysqld]
max_execution_time = 2000

4. Configurar el tiempo máximo de declaración en MariaDB

MariaDB, aunque está bifurcado de MySQL, ofrece un enfoque similar pero distinto para gestionar los tiempos de ejecución de consultas. A partir de MariaDB 10.1.1, puede utilizar la variable de sistema max_statement_time para este propósito:

ESTABLECER GLOBAL max_statement_time = 2;

Esto limita el tiempo de ejecución a 2 segundos para todas las consultas.

Para una configuración persistente mediante reinicios del servidor, agregue esta línea a su archivo de configuración de MariaDB:

[mysqld]
max_statement_time = 2

Identifique y solucione consultas de larga duración con Query Analytics de Releem

La herramienta de análisis de consultas de Releem revoluciona la forma de monitorear y optimizar el rendimiento de su base de datos. Recopila automáticamente información detallada sobre las 100 consultas principales, proporcionando métricas clave como el tiempo promedio de ejecución y el impacto general de cada consulta en la eficiencia operativa de su base de datos.

Con Releem, no hay necesidad de buscar manualmente en la salida de PROCESSLIST o mirar en el registro de consultas lentas para identificar consultas de bajo rendimiento. La herramienta cuenta con un panel intuitivo que le permite ordenar y detectar sin esfuerzo consultas que están retrasadas o consumen mucho tiempo. Esta información inmediata le ayuda a identificar y resolver cuellos de botella en poco tiempo.