Traqueur, problème de verrouillage et parallélisme

Le gang des bloqueurs

Téléchargement du traqueur pour PostgreSQL 12, 13, 14, 15, 16, 17
Téléchargement du traqueur pour PostgreSQL 11
Téléchargement du traqueur pour PostgreSQL 10
Téléchargement du traqueur pour PostgreSQL 9.6
Téléchargement du traqueur pour PostgreSQL 9.5
Téléchargement du traqueur pour PostgreSQL 9.4
Téléchargement du traqueur pour PostgreSQL 9.3
Journal des changements
Signalement de bugs via le forum
Licence identique à celle de PostgreSQL, open source type BSD
Dossier des versions

      Cet article vient en complément de celui sur la traque d’une situation de verrouillage. Le parallélisme complique-t-il le diagnostic ?
      Les ressources manquent sur le serveur, un truncate est bloqué ... il est temp d’analyser avec le traqueur :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-19) 6.3.0 20170618, 64-bit \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 1740 MB | (1 ligne) -- SESSION A explain select count(*) from t1 a, t1 b; QUERY PLAN ------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=22002655250913.34..22002655250913.35 rows=1 width=8) -> Gather (cost=22002655250913.02..22002655250913.33 rows=3 width=8) Workers Planned: 3 -> Partial Aggregate (cost=22002655249913.02..22002655249913.03 rows=1 width=8) -> Nested Loop (cost=0.00..19959680668765.71 rows=817189832458921 width=0) -> Parallel Seq Scan on t1 a (cost=0.00..385067.59 rows=16236059 width=0) -> Seq Scan on t1 b (cost=0.00..726024.84 rows=50331784 width=0) (7 lignes) select count(*) from t1 a, t1 b; -- OUILLE ... -- SESSION B start transaction; START TRANSACTION truncate table t1; -- EN ATTENTE ./traqueur.sh -d 1 -p traqueur 0.08.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers | non_cpu_pc | mem_en_mo | swapped_en_mo ---------+--------------+----------+------+----------+-----------+-------------+------------------------------------------+-----------------+-----------------------+------------+-----------+--------------- 100 | 1 / 10 | postgres | 2838 | postgres | | psql | select count(*) from t1 a, t1 b; | | | 1 | 2 | 0 100 | 1 / 10 | postgres | 2855 | postgres | | psql | truncate table t1; | Lock | {2838,2860,2861,2862} | 100 | 0 | 0 100 | 1 / 10 | postgres | 2861 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 0 | 0 | 0 100 | 1 / 10 | postgres | 2862 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 0 | 0 | 0 100 | 1 / 10 | traqueur | 2691 | postgres | | traqueur | execute traqueur_sleep; | Timeout | | 100 | 11 | 0 80 | 1 / 8 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 2 | 0 | 0 10 | 1 / 1 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | IO | | 2 | 0 | 0 10 | 1 / 1 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | LWLock | | 1 | 0 | 0 (8 lignes) Charge CPU moyennne globale ----------------------------- 100 (1 ligne) temps_cpu_user_en_s | temps_cpu_systeme_en_s | temps_cpu_idle_en_s | temps_io_en_s ---------------------+------------------------+---------------------+--------------- 4 | 0 | 0 | 0 (1 ligne) ./traqueur.sh -d 1 -p -o "pid, query, backend_type" traqueur 0.08.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | pid | query | backend_type | non_cpu_pc | mem_en_mo | swapped_en_mo ---------+--------------+------+------------------------------------------+-------------------+------------+-----------+--------------- ... 100 | 1 / 10 | 2838 | select count(*) from t1 a, t1 b; | client backend | 0 | 2 | 0 100 | 1 / 10 | 2860 | select count(*) from t1 a, t1 b;postgres | background worker | 2 | 0 | 0 ...

      Le serveur est très sollicité, la charge CPU est de 100%. Mais ce n’est pas la raison du blocage du truncate qui attend sur un Lock.
      Les process bloquants le truncate sont les process 2838, 2860, 2861, 2862. Il y a un problème sur la requête "select count(*) from t1 a, t1 b". Elle réalise un produit cartésien et va mettre un peu temps avant d’aboutir !
      Le process principal responsable de ce produit cartésien est le 2838. Les process 2860, 2861, 2862 sont en fait des travailleurs appelés à la rescousse pour aider le process principal. Un indice : le suffixe "postgres" est ajouté à la colonne query dans le cas de 2860, 2861, 2862 et pas pour 2838. En cas de doute, vous pouvez utiliser l’option -o et inclure la colonne backend_type, disponible depuis la version 10 de PostgreSQL, qui indiquera "background worker" pour les travailleurs supplémentaires, "client backend" pour le process principal. Il serait aussi possible d’exclure les background workers du champ de l’analyse avec l’option -w.
      Le parallélisme ne change pas fondamentalement la manière de traiter un problème de verrouillage. Vous pouvez tuer le process principal mais tuer un des process parallèle arrêtera aussi la requête, sans d’ailleurs terminer la session :

select pg_terminate_backend(2861); pg_terminate_backend ---------------------- t -- SESSION A select count(*) from t1 a, t1 b; ERREUR: arrêt des connexions suite à la demande de l'administrateur CONTEXTE : processus parallèle select 1; ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction rollback; ROLLBACK select 1; ?column? ---------- 1 -- SESSION B TRUNCATE TABLE ...

      Le truncate a été libéré et a pu achever son travail, mission accomplie.

Conclusion

      Le parallélisme ne complique pas fondamentalement le diagnostic concernant les bloqueurs finaux. Tuer le chef du gang ou même l’un des comparses mettra fin aux agissements de toute la bande.

Mise à jour : 11/07/2017