Postgres : automatiser le kill des requêtes longues

Quelquefois, il peut y avoir une requête qui dure plus longtemps que prévu, et qui bloque les requêtes suivantes. Cela peut entraîner des locks en cascade, et mettre à genoux un serveur ou un projet. Voici comment détecter ces anomalies, et éventuellement, les corriger automatiquement.

Les prérequis

Le premier prérequis est le plus important. Il peut paraître évident, et pourtant… il faut déjà savoir ce que vous faites. Tuer une requête, même longue, ne doit pas être fait à la légère. Nous allons détailler le fonctionnement dans ce billet, et il vaut mieux analyser point par point ce qui se passe avant d’en arriver là. Normalement, postgres gère très bien les transactions, et vous ne devriez pas avoir des locks parallèlles (une requête A qui bloque une requête B, qui bloque elle même la requête A). De même, si une requête prend trop de temps, il faut avant tout analyser le pourquoi, en utilisant l’explain plan fourni avec postgres. Il faut peut être rajouter des index, peut être revoir la requête, peut être même si elle est trop complexe la décomposer pour la mettre dans une fonction… cela fait partie du travail de dba.
Une fois que vous savez tout ça, et si vous voulez toujours killer des requêtes, il vous faut un utilisateur qui soit administrateur de la base de données (par exemple l’utilisateur postgres, mais vous pouvez bien sur en créer un autre.

Le fonctionnement

pg_stat_activity

Il existe une table système qui recense toutes les requêtes en cous dans la base. C’est la table pg_stat_ctivity. Une fois votre client postgres préféré lancé (psql ou pgadmin3 par exemple), vous pouvez lancer la requête

 select * from pg_stat_activity;

Cette requête va nous donner quelques informations précieuses :

  • procpid : c’est le numéro de process, il nous sera utile plus tard
  • usename : le nom de l’utilisateur qui a lancé la requête
  • current_query : elle donne la requête courante. Si vous voyez <command string not enabled> il faut éditer le fichier postgresql.conf (soit dans /etc/postgres pour les debian-like, soit dans /var/lib/pgsql/data pour les autres) pour mettre la valeur stats_command_string à on, et redémarrer postgres. Si vous voyez IDLE, c’est que le client est connecté, mais aucune requête n’est en cours (en attente)
  • waiting : Devrait toujours être à f (false). Si c’est à t (true) c’est que la requête attend de pouvoir accéder aux données. On est dans un lock wait, et cet état ne devrait pas durer.
  • query_start : c’est un timestamp qui contient l’heure précise de démarrage de la requête

Éventuellement, on peut auss noter :

  • client_addr et client_port pour les connexions distantes (rien n’est indiqué si c’est une connexion locale).

Nous avons déjà une bonne piste. Quand votre base de données rame, vous pouvez toujours lancer cette requête, et voir s’il ne reste pas une requête qui traine depuis un bon moment grace au query_start.

pg_cancel_backend

C’est une fonction qui permet de tuer une requête. Dans notre exemple précédent, supposons que

 select * from pg_stat_activity;

nous donne une requête qui tourne depuis plus de 5 minutes, qu’il y a plein de requêtes en attente (waiting à t), il va falloir intervenir. La première chose à faire est de noter le current_query pour analyser la requête, et comprendre d’où vient l’anomalie. Ensuite, pour permettre le bon fonctionnement des autres requêtes, nous allons pouvoir la tuer. Il faut noter pour ça le procpid (par exemple 16452) et lancer la commande suivante :

 select pg_cancel_backend(16452)

Cette commande devrait retourner t (true). Si c’est bien le cas, dans quelques secondes, si vous relancez le select * from pg_stat_activity;, elle devrait avoir disparu (quelques secondes pour la transaction, la base de données annule tout ce qui a pu être fait par cette requête). Si ce n’est pas le cas, c’est peut être parce qu’elle est déjà terminée, parce qu’elle a déjà été tuée par ailleurs, ou que vous n’avez pas les droits pour terminer une requête

Automatisation du kill des requêtes longues

Nous pouvons maintenant coupler ces deux requêtes. Imaginons quelque chose du style :

select procpid,usename,current_query 
from pg_stat_activity 
where query_start <= now() - '2 minutes'::interval 
and current_query != '<IDLE>';

Cette requête va nous donner la liste de toutes les requêtes qui sont en cours depuis plus de deux minutes (ce qui est déjà un temps très long pour un fonctionnement normal). Nous pouvons maintenant mettre tout ça dans un shell, et rediriger ces informations dans un fichier texte. Dans l’exemple suivant, on se connecte avec l’utilisateur postgres, sur la database production, et on crée un fichier analyse_requête.log, que l’on va envoyer par mail à l’adresse exploitation@monentreprise.fr

psql -U postgres -d production << EOF > analyse_requetes.log
select procpid,usename,current_query 
from pg_stat_activity 
where query_start <= now() - '2 minutes'::interval 
and current_query != '<IDLE>'; \q 
EOF 
mailx -s 'Analyse des requêtes longues' exploitation@monentreprise.fr < analyse_requetes.log

On peut maintenant lancer ce shell à intervalles régulier pour recevoir une liste des requêtes longues (il existe d’autres moyens plus simples pour cela, mais ce n’est qu’une étape pour la suite)

Maintenant, on a analysé les requêtes. On sait qu’il faut tuer les requêtes de plus de 2 minutes lancées par l’utilisateur toto, et dont le corps de la requête contient le mot nettoyage. On est bien d’accord, ceci n’est qu’une solution d’attente, le principe est de savoir pourquoi ces requêtes durent, et de faire une correction en amont. faire un kill automatique n’est là que pour permettre aux autres requêtes d’aboutir sans pénaliser la production, et ne doit être lancé que sur des requêtes où on est sur que l’intégrité de la base ne sera pas impactée. La requête à lancer est

select pg_cancel_backend(procpid) 
from pg_stat_activity 
where usename = 'toto' 
and query_start <= now() - '2 minutes'::interval 
and current_query like '%nettoyage%';

Si on rajoute ça dans le shell précédent (avant la ligne \q), on va tuer toutes les requêtes ciblées.

Laisser un commentaireAnnuler la réponse.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.