Page 1 sur 1

Traqueur, verrouillage

Posté : dim. 9 juil. 2017 15:37
par Phil
Un cas d'utilisation avancé du traqueur : http://pgphil.ovh/traqueur_10_06.php
Analyser une situation de blocage et tuer uniquement ce qui doit l'être.

Traqueur, verrouillage

Posté : mar. 11 juil. 2017 19:47
par Phil
Le parallélisme chante-t-il l'analyse lors d'un problème lié au verrouillage ? Réponse ici : http://pgphil.ovh/traqueur_10_08.php

Traqueur, verrouillage

Posté : mar. 29 août 2017 18:56
par Phil
Les transactions préparées peuvent aussi bloquer d'autres transactions : http://pgphil.ovh/traqueur_10_10.php

Traqueur, verrouillage

Posté : dim. 1 oct. 2017 13:47
par Phil
Diagnostic sur un problème de verrouillage massif impliquant de nombreuses sessions : http://pgphil.ovh/traqueur_10_11.php

Traqueur, verrouillage

Posté : lun. 2 oct. 2017 16:31
par Phil
Merci à un utilisateur pour sa question sur http://pgphil.ovh/traqueur_10_11.php :

"Oui mais si on double le nombre de sessions et si varie le style de verrous c'est toujours viable ?"

Le mieux c'est encore de tester. 10 sessions vont bloquer 990 sessions avec au programme de l'update, du delete, du truncate et du select :

Code : Tout sélectionner

-- PREPARATION ENVIRONNEMENT

select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.19 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

create table t1(c1 int2);
insert into t1 select generate_series(1, 20, 1);

-- SESSION 1 BLOQUANTE
start transaction; update t1 set c1 = 10 where c1 = 1;
START TRANSACTION
UPDATE 1

-- SESSION 2 BLOQUANTE
start transaction; update t1 set c1 = 20 where c1 = 2;
START TRANSACTION
UPDATE 1

-- SESSION 3 BLOQUANTE
start transaction; update t1 set c1 = 30 where c1 = 3;
START TRANSACTION
UPDATE 1

-- SESSION 4 BLOQUANTE
start transaction; update t1 set c1 = 40 where c1 = 4;
START TRANSACTION
UPDATE 1

-- SESSION 5 BLOQUANTE
start transaction; update t1 set c1 = 50 where c1 = 5;
START TRANSACTION
UPDATE 1

-- SESSION 6 BLOQUANTE
start transaction; update t1 set c1 = 60 where c1 = 6;
START TRANSACTION
UPDATE 1

-- SESSION 7 BLOQUANTE
start transaction; update t1 set c1 = 70 where c1 = 7;
START TRANSACTION
UPDATE 1

-- SESSION 8 BLOQUANTE
start transaction; update t1 set c1 = 80 where c1 = 8;
START TRANSACTION
UPDATE 1

-- SESSION 9 BLOQUANTE
start transaction; update t1 set c1 = 90 where c1 = 9;
START TRANSACTION
UPDATE 1

-- SESSION 10 BLOQUANTE
start transaction; update t1 set c1 = 100 where c1 = 10;
START TRANSACTION
UPDATE 1

-- SESSIONS 11 à 20 BLOQUEES
for i in {11..20}; do psql -c "start transaction; update t1 set c1 = $i+1 where c1 = $i; update t1 set c1 = $i+10 where c1 = $i-10;" & done

-- SESSIONS 21 à 30 BLOQUEES
for i in {21..30}; do psql -c "start transaction; update t1 set c1 = $i+2 where c1 = $i-10;" & done

-- SESSIONS 31 à 100 BLOQUEES
for i in {31..100}; do psql -c "start transaction; delete from t1;" & done

-- SESSIONS 101 à 200 BLOQUEES
for i in {101..200}; do psql -c "start transaction; truncate table t1;" & done

-- SESSIONS 201 à 1000 BLOQUEES
for i in {201..300}; do psql -c "start transaction; select * from t1;" & done
for i in {301..400}; do psql -c "start transaction; select * from t1;" & done
for i in {401..500}; do psql -c "start transaction; select * from t1;" & done
for i in {501..600}; do psql -c "start transaction; select * from t1;" & done
for i in {601..700}; do psql -c "start transaction; select * from t1;" & done
for i in {701..800}; do psql -c "start transaction; select * from t1;" & done
for i in {801..900}; do psql -c "start transaction; select * from t1;" & done
for i in {901..1000}; do psql -c "start transaction; select * from t1;" & done

 ./traqueur.sh -d 0 -t -n -o "itquery, tquery, blockers"
traqueur 0.12.07beta - performance tool for PostgreSQL 9.3 => 10
INFORMATION, no connection parameters provided, connecting to traqueur database ...
INFORMATION, connected to traqueur database
INFORMATION, PostgreSQL version : 90319
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
Time: 0.106 ms
Time: 7.622 ms
Time: 5.921 ms
Time: 2.542 ms
Time: 0.946 ms
Time: 5.117 ms
Time: 0.550 ms
Time: 345.470 ms
Time: 0.241 ms
Time: 20633.749 ms (00:20.634)
Time: 0.652 ms
Time: 0.953 ms
 busy_pc | distinct_exe |  itquery   |                                             tquery                                             |                      blockers
---------+--------------+------------+------------------------------------------------------------------------------------------------+-----------------------------------------------------
   80000 | 799 / 800    | 1211598098 | start transaction; select * from t1;                                                           | {1019,1070,1116,1223,1232,1299,1364,1419,1479,1526}
   10000 | 100 / 100    | 1321634223 | start transaction; truncate table t1;                                                          | {1019,1070,1116,1223,1232,1299,1364,1419,1479,1526}
    7000 | 70 / 70      | 1095940764 | start transaction; delete from t1;                                                             | {1019}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1116}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1223}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1232}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1299}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1364}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1419}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1479}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1526}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1070}
     100 | 1 / 1        | 1181196098 | start transaction; update t1 set c1 = 0+0 where c1 = 0; update t1 set c1 = 0+0 where c1 = 0-0; | {1019}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1019}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1070}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1116}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1223}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1232}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1299}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1364}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1419}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1479}
     100 | 1 / 1        | 1440820938 | start transaction; update t1 set c1 = 0+0 where c1 = 0-0;                                      | {1526}
(23 rows)


L'analyse dure 21 secondes et donne bien les 10 bloqueurs finaux, mission accomplie !