Les requêtes de longue durée peuvent constituer une sérieuse épine dans le pied des performances de votre base de données MySQL, provoquant tout, depuis des temps de réponse lents jusqu'à de véritables goulots d'étranglement qui affectent chaque utilisateur. Maîtriser ces requêtes embêtantes (savoir ce qu'elles sont, pourquoi elles se produisent et comment les gérer) est essentiel au bon fonctionnement de votre base de données.

Qu'il s'agisse de les repérer tôt, de les arrêter net ou de mettre en place un moyen de les gérer automatiquement, ce guide est là pour vous.

Qu'est-ce qu'une requête de longue durée ?

Une requête de longue durée dans MySQL est une requête dont l'exécution prend une période inhabituellement longue.
La durée spécifique qui classe une requête comme « de longue durée » peut varier en fonction des normes de performances de votre application. Généralement, si une requête s'exécute plus longtemps que d'habitude et commence à ralentir votre base de données, elle est considérée comme de longue durée.

Causes profondes des requêtes de longue durée

Les causes des requêtes de longue durée peuvent être diverses :

• Manque d'indexation appropriée – Sans indexation appropriée, MySQL doit analyser la table entière pour récupérer les données requises. Ce processus est très inefficace, en particulier pour les grandes tables, car il consomme beaucoup de temps et de ressources.

• Situations de charge importante – Lorsque le serveur gère un volume élevé de requêtes ou en traite quelques-unes complexes simultanément, les ressources disponibles (comme le processeur et la mémoire) sont limitées. Cette concurrence pour les ressources peut retarder l'exécution des requêtes, entraînant des durées d'exécution plus longues, en particulier pendant les périodes de pointe d'utilisation.

• Lock Contention – Cela se produit lorsque plusieurs transactions nécessitent un accès simultané aux mêmes données mais sont bloquées car d'autres opérations détiennent les verrous nécessaires. Par exemple, si une transaction met à jour une ligne, une autre transaction qui souhaite lire ou mettre à jour la même ligne devra attendre que la première termine et libère le verrou.

• Normalisation incorrecte – Bien que la normalisation aide à éviter la redondance des données et améliore l'intégrité des données, les bases de données trop normalisées peuvent conduire à des requêtes complexes impliquant plusieurs jointures. Ceux-ci peuvent dégrader les performances. D’un autre côté, une sous-normalisation peut entraîner une duplication excessive des données, ce qui entraîne des tables plus volumineuses et des requêtes plus lentes.

• Grandes jointures – Les requêtes qui impliquent de joindre de grandes tables, en particulier sans index appropriés, peuvent être lentes. La base de données doit faire correspondre les lignes des tables en fonction des conditions de jointure, un processus qui peut être très gourmand en ressources et lent sans une indexation efficace.

Identification des requêtes de longue durée

Pour gérer efficacement les requêtes de longue durée, vous devez d'abord les identifier. Voici quelques méthodes :

1. Utilisation de SHOW PROCESSLIST

Le AFFICHER LA LISTE DES PROCESSUS ; La commande est un moyen rapide d'obtenir un instantané de toutes les requêtes actives en cours d'exécution sur votre serveur. Cette commande affiche chaque requête ainsi que plusieurs informations clés, notamment la durée d'exécution de chaque requête. Celles avec une valeur « Temps » élevée sont probablement vos requêtes de longue durée. Voici comment utiliser cette commande :

AFFICHER LA LISTE COMPLÈTE DES PROCESSUS ;

Cette commande listera tous les processus en cours, montrera qui les a démarrés, quel type de commande ils exécutent et, surtout, depuis combien de temps ils y sont. Si vous repérez des requêtes exécutées depuis une période inhabituellement longue, il s’agit de vos requêtes de longue durée. Vous pouvez ensuite décider si vous souhaitez approfondir leur optimisation ou simplement les supprimer s'ils nuisent aux performances de votre système.

2. Le journal des requêtes lentes

La configuration du journal des requêtes lentes est une autre excellente stratégie pour détecter ces requêtes problématiques. Cette fonctionnalité MySQL pratique enregistre toute requête dont l'exécution prend plus de temps qu'un certain seuil. Il ne s'agit pas seulement de détecter les requêtes de longue durée : cela peut également vous aider à identifier les requêtes qui n'utilisent pas efficacement les index.

Pour que le journal des requêtes lentes soit opérationnel, vous devrez modifier quelques paramètres dans votre fichier de configuration MySQL (soit my.cnf, soit my.ini) :

• Slow_query_log – Définissez ceci sur 1 pour activer le journal.
• Slow_query_log_file – Spécifiez le chemin du fichier dans lequel vous souhaitez enregistrer le journal.
• Long_query_time – Définissez le temps d'exécution minimum (en secondes) qui qualifie une requête à enregistrer. Par exemple, le définir sur « 2 » enregistrera toutes les requêtes dont l'exécution prend plus de deux secondes.

3. Schéma de performances

Le schéma de performances de MySQL est inestimable pour une enquête plus détaillée. Cet outil est conçu pour surveiller les événements du serveur et suivre les mesures de performances, vous offrant ainsi une vue plus claire de l'exécution des requêtes et des performances globales du système.

Assurez-vous qu'il est activé dans votre configuration MySQL en ajoutant la ligne suivante :

[mysqld]
performance_schema = ON

Une fois activé, vous pouvez explorer une variété de tableaux de schéma de performances pour analyser les performances de vos requêtes. Par exemple, si vous souhaitez identifier les requêtes de longue durée, vous souhaiterez peut-être consulter la table events_statements_history_long. Voici comment l'interroger :

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS 'Durée (secondes)'
DE performance_schema.events_statements_history_long
OÙ TIMER_WAIT > 10000000000;

Cette requête vous aide à trouver toutes les requêtes exécutées depuis plus de 10 secondes. Il vous donne des détails tels que le texte SQL et la durée d'exécution de chaque requête.

Suppression manuelle des requêtes de longue durée

Lorsque vous avez identifié une requête qui prend trop de temps et sollicite les ressources de votre système, vous avez la possibilité d'y mettre fin manuellement. Cela se fait à l'aide de la commande KILL suivie de l'ID de processus spécifique de la requête.

Vous pouvez trouver l'ID du processus en exécutant la commande SHOW PROCESSLIST, qui affiche tous les processus en cours d'exécution et leurs ID respectifs. Recherchez dans la liste toutes les requêtes qui affichent une valeur « Temps » élevée, ce qui indique depuis combien de temps elles sont en cours d'exécution.

Une fois que vous avez identifié une requête problématique et noté son ID de processus, vous pouvez y mettre fin en utilisant la commande KILL :

TUER [ID de processus];

Remplacez [ID de processus] par le numéro réel de la sortie SHOW PROCESSLIST.

Soyez prudent avec cette approche. L'arrêt brusque d'une requête peut parfois entraîner des problèmes, comme laisser vos données dans un état incohérent si la requête était en cours d'écriture ou de mise à jour d'informations.

Comment automatiser la suppression des requêtes de longue durée

La configuration de l'automatisation pour gérer les requêtes de longue durée peut être une véritable bouée de sauvetage, empêchant ces requêtes lentes ou non optimisées de monopoliser les ressources de votre base de données et de ralentir, voire de bloquer, l'ensemble du système. Mais soyez prudent : l'utilisation de cet outil sans les vérifications appropriées peut en réalité masquer des problèmes de performances plus profonds qui nécessitent votre attention.

Assurez-vous toujours de disposer d'une journalisation et d'une surveillance complètes pour analyser l'impact des requêtes supprimées sur votre application, et envisagez d'améliorer ces requêtes plutôt que de simplement les supprimer automatiquement. Considérez la résiliation automatique comme faisant partie d'une stratégie plus large d'optimisation des performances, et non comme une solution miracle.

1. Activez le planificateur d'événements

Tout d'abord, vous devez activer le planificateur d'événements MySQL, qui est désactivé par défaut. Le planificateur d'événements vous permet de créer et de planifier des tâches que vous souhaitez que le serveur exécute automatiquement à des heures prédéfinies. Exécutez la commande suivante :

SET GLOBAL event_scheduler = ON;

2. Créez un événement pour supprimer les requêtes de longue durée

Une fois le planificateur activé, l'étape suivante consiste à définir l'événement réel qui surveillera et tuera les requêtes de longue durée. L'événement s'exécutera toutes les minutes pour vérifier les requêtes exécutées plus longtemps qu'un seuil spécifié (disons 60 secondes). Une fois identifié, il tuera automatiquement ces requêtes. Voici une ventilation du code SQL pour configurer cet événement :

`CRÉER UN ÉVÉNEMENT kill_long_running_queries
ON SCHEDULE CHAQUE 1 MINUTE -- Spécifie la fréquence à laquelle l'événement s'exécute
FAIRE
COMMENCER
DECLARE done INT DEFAULT FALSE;
DÉCLARE proc_id INT ; -- Variable pour stocker l'ID de processus de chaque requête
DÉCLARE cur1 CURSOR FOR SELECT ID FROM information_schema.processlist
OÙ Commande = 'Requête' ET Heure > 60 ; -- Remplacez « 60 » par votre seuil en quelques secondes
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OUVERT cur1;

read_loop : BOUCLE
FETCH cur1 INTO proc_id;
SI c'est fait ALORS
QUITTER read_loop;
FIN SI;
TUER proc_id ; -- Tue le processus identifié par proc_id
FIN DE BOUCLE;

FERMER cur1;
FIN;`

3. Configurer le temps d'exécution maximal des requêtes dans MySQL

Le contrôle du temps d'exécution maximum d'une requête permet d'éviter que la base de données ne soit saturée par des requêtes trop longues. Cela se fait à l'aide de la variable système max_execution_time dans MySQL 5.7.8 et versions ultérieures en définissant une limite de temps d'exécution à l'échelle du système pour toutes les requêtes SELECT en lecture seule :

SET GLOBAL max_execution_time = 2000;

Ceci définit la limite à 2 000 millisecondes (2 secondes)

N'oubliez pas que ce paramètre ne s'applique pas aux procédures stockées, aux fonctions ou aux déclencheurs et est réinitialisé par défaut au redémarrage du serveur, sauf s'il est ajouté à votre fichier de configuration MySQL :

[mysqld]
max_execution_time = 2000

4. Configurer la durée maximale de l'instruction dans MariaDB

MariaDB, bien que dérivée de MySQL, propose une approche similaire mais distincte de la gestion des temps d'exécution des requêtes. À partir de MariaDB 10.1.1, vous pouvez utiliser la variable système max_statement_time à cet effet :

SET GLOBAL max_statement_time = 2;

Cela limite le temps d'exécution à 2 secondes pour toutes les requêtes.

Pour une configuration persistante via les redémarrages du serveur, ajoutez cette ligne à votre fichier de configuration MariaDB :

[mysqld]
max_statement_time = 2

Identifiez et corrigez les requêtes de longue durée avec l'analyse des requêtes de Releem

L'outil d'analyse de requêtes de Releem révolutionne la façon dont vous surveillez et optimisez les performances de votre base de données. Il rassemble automatiquement des informations détaillées sur les 100 principales requêtes, fournissant des indicateurs clés tels que le temps d'exécution moyen et l'impact global de chaque requête sur l'efficacité opérationnelle de votre base de données.

Avec Releem, il n'est pas nécessaire de parcourir manuellement la sortie PROCESSLIST ou de consulter le journal des requêtes lentes pour identifier les requêtes sous-performantes. L'outil dispose d'un tableau de bord intuitif qui vous permet de trier et de repérer sans effort les requêtes qui sont en retard ou qui prennent trop de temps. Cet aperçu immédiat vous aide à identifier et à résoudre les goulots d'étranglement en un rien de temps.