ProHoster > Blogi > Haldamine > Üks meetod PostgreSQL-is blokeeringute ajaloo saamiseks

Üks meetod PostgreSQL-is blokeeringute ajaloo saamiseks

Artikli jÀtk «Katse luua PostgreSQL-i jaoks ASH-i analoog «.

Artiklis kÀsitletakse ja nÀidatakse konkreetsete pÀringute ja nÀidete kaudu, millist kasulikku teavet saab pg_locks esituse ajaloo abil saada.

Hoiatus.
Kuna teema on uus ja katseaeg on lÔpetamata, vÔib artikkel sisaldada vigu. Kritika ja mÀrkused on teretulnud ja oodatud.

Sisendandmed

pg_locks esituse ajalugu

archive_locking

LOO TABEL archive_locking 
(       timepoint timestamp ilma ajavööndita ,
	locktype tekst ,
	relation oid ,
	mode tekst ,
	tid xid ,
	vtid tekst ,
	pid terviklik ,
	blocking_pids terviklik[] ,
	granted boolean ,
        queryid bigint 
);

Sisuliselt on tabel sarnane tabelile archive_pg_stat_activity, mis on siin pĂ”hjalikumalt kirjeldatud — pg_stat_statements + pg_stat_activity + loq_query = pg_ash? ja siin — Üritus luua PostgreSQL-i sarnane ASH.

Veeru tÀitmiseks queryid kasutatakse funktsiooni

update_history_locking_by_queryid

--update_history_locking_by_queryid.sql
LOO VÕI ASENDAGE FUNKTIOON update_history_locking_by_queryid() TAGASTAB boolean AS $$
DEKLAREERIDA
  tulemus boolean ;
  praegune_minuut topelt tÀpsus ; 
  
  algus_minuut tÀisarv ;
  lÔpp_minuut tÀisarv ;
  
  algus_periood timestamp ilma ajavööndita ;
  lÔpp_periood timestamp ilma ajavööndita ;
  
  lukustus_rec salvestus ; 
  lÔpp_rec salvestus ;
  
  praegune_tund_erinevus topelt tÀpsus ;
ALUSTA
  tÔsta teadet '***update_history_locking_by_queryid';
  
  tulemus = TÕSI ;
  
  praegune_minuut = extract ( minut nĂŒĂŒd() );

  VALI * ENDPOINTIST KUS on_need_monitorimine
  INTO lÔpp_rec ;
  
  praegune_tund_erinevus = lÔpp_rec.tund_erinevus ;
  
  KUI praegune_minuut < 5 
  SIIS
	TÕSTA TEADE 'Praegune aeg on vĂ€hem kui 5 minutit.';
	
	algus_periood = date_trunc('tund',nĂŒĂŒd()) + (praegune_tund_erinevus * intervall '1 tund');
    lÔpp_periood = algus_periood - intervall '5 minutit' ;
  MUUL JUHUL 
    lĂ”pp_minuut =  extract ( minut nĂŒĂŒd() ) / 5 ;
    algus_minuut =  lÔpp_minuut - 1 ;
  
    algus_periood = date_trunc('tund',nĂŒĂŒd()) + intervall '1 minut'*algus_minuut*5+(praegune_tund_erinevus * intervall '1 tund');
    lĂ”pp_periood = date_trunc('tund',nĂŒĂŒd()) + intervall '1 minut'*lĂ”pp_minuut*5+(praegune_tund_erinevus * intervall '1 tund') ;
    
  KATE LÕPP ;  
  
  TÕSTA TEADE 'algus_periood = %', algus_periood;
  TÕSTA TEADE 'lĂ”pp_periood = %', lĂ”pp_periood;

	KUI lukustus_rec IN   
	KOOS act_queryid KUI
	 (
		VALI 
				pid , 
				timepoint ,
				query_start NAGU alanud ,			
				MAX(timepoint) OVER (PARTITSEERI PID ,	query_start   ) NAGU lÔpetatud ,			
				queryid 
		FROM 
				activity_hist.history_pg_stat_activity 			
		KUS 			
				timepoint ON algus_periood ja 
								  lÔpp_periood
		GRUPPI KÜSITUD 
				pid , 
				timepoint ,  
				query_start ,
				queryid 
	 ),
	 lukustus_pids KUID
		(
			VALI
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint , 
				MIN ( timepoint ) OVER (PARTITSEERI pid , locktype  ,mode ) NAGU alanud 
			FROM 
				activity_hist.history_locking hl
			KUS 
				hl.timepoint vaheline algus_periood ja 
								     lÔpp_periood
			GRUPPI KÜSITUD 
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint 
		)
	VALI 
		lp.pid , 
		lp.locktype  ,
		lp.mode ,
		lp.timepoint ,     
		aq.queryid 
	FROM lukustus_pids 	lp VASAKU VÄLJAS OLEKUD act_queryid aq ON ( lp.pid = aq.pid JA lp.started VAHELINE aq.started JA aq.finished )
	KUS aq.queryid EI OLE NULL 
	GRUPPI KÜSITUD  
		lp.pid , 
		lp.locktype  ,
		lp.mode ,
		lp.timepoint , 
		aq.queryid
	SILMA
		UURISE activity_hist.history_locking SET queryid = lock_rec.queryid 
		KUS pid = lock_rec.pid JA locktype = lock_rec.locktype JA mode = lock_rec.mode JA timepoint = lock_rec.timepoint ;	
	LÕPE LOOP;    
  
  TAGASTA tulemus ;
LÕPP
$$ KEEL plpgsql;

Explanation: Veeru queryid vÀÀrtus uuendatakse tabelis history_locking ja seejÀrel, kui luuakse uus sektsioon tabelis archive_locking, vÀÀrtus salvestatakse ajaloolistesse vÀÀrtustesse.

VĂ€ljundandmed

Üldine teave protsesside kohta.

OOTEOLLOCKID TYPIDE KAUDE

PĂ€ring

WITH
t AS
(
	SELECT 
		locktype  ,
		mode ,
		count(*) as total 
	FROM 
		activity_hist.archive_locking
	WHERE 
		timepoint between pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
		NOT granted
	GROUP BY 
		locktype  ,
		mode  
)
SELECT 
	locktype  ,
	mode ,
	total * interval '1 second' as duration			
FROM t 		
ORDER BY 3 DESC

NĂ€ide

| OOTEOLLOCKID TYPIDE KAUDE
+--------------------+------------------------------+--------------------
|            locktype|                          mode|            duration
+--------------------+------------------------------+--------------------
|       transactionid|                     ShareLock|            19:39:26
|               tuple|           AccessExclusiveLock|            00:03:35
+--------------------+------------------------------+--------------------

LOCKIDE OLEKUTESSE KAUDE

PĂ€ring

WITH
t AS
(
	SELECT 
		locktype  ,
		mode ,
		count(*) as total 
	FROM 
		activity_hist.archive_locking
	WHERE 
		timepoint between pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
		granted
	GROUP BY 
		locktype  ,
		mode  
)
SELECT 
	locktype  ,
	mode ,
	total * interval '1 second' as duration			
FROM t 		
ORDER BY 3 DESC

NĂ€ide

| LOCKIDE OLEKUTESSE KAUDE
+--------------------+------------------------------+--------------------
|            locktype|                          mode|            duration
+--------------------+------------------------------+--------------------
|            relation|              RowExclusiveLock|            51:11:10
|          virtualxid|                 ExclusiveLock|            48:10:43
|       transactionid|                 ExclusiveLock|            44:24:53
|            relation|               AccessShareLock|            20:06:13
|               tuple|           AccessExclusiveLock|            17:58:47
|               tuple|                 ExclusiveLock|            01:40:41
|            relation|      ShareUpdateExclusiveLock|            00:26:41
|              object|              RowExclusiveLock|            00:00:01
|       transactionid|                     ShareLock|            00:00:01
|              extend|                 ExclusiveLock|            00:00:01
+--------------------+------------------------------+--------------------

Detailne teave konkreetsete queryid pÀringute kohta.

OOTEOLLOCKID TYPIDE KAUDE QUERYID ALUSEL

PĂ€ring

WITH
lt AS
(
	SELECT
		pid , 
		locktype  ,
		mode ,
		timepoint , 
		queryid , 
		blocking_pids ,
                MIN ( timepoint ) OVER (PARTITION BY pid , locktype  ,mode ) as started  
	FROM 
		activity_hist.archive_locking
	WHERE 
		timepoint between pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND 
			                  pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
		NOT granted AND
	       queryid IS NOT NULL 
	GROUP BY 
	        pid , 
		locktype  ,
		mode ,
		timepoint ,
		queryid ,
		blocking_pids 
)
SELECT 
        lt.pid , 
	lt.locktype  ,
	lt.mode ,			
        lt.started ,
	lt.queryid  , 
	lt.blocking_pids ,
	COUNT(*)  * interval '1 second'	 as duration		
FROM lt 	
GROUP BY 
	lt.pid , 
        lt.locktype  ,
	lt.mode ,			
        lt.started ,
        lt.queryid ,
	lt.blocking_pids 
ORDER BY 4

NĂ€ide

| OOTE LOCKID TYPIDE ALUSE QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 locktype|                mode|                       started|             queryid|       blocking_pids|            duration
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|            transactionid|           ShareLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11626|            transactionid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12380}|            00:00:29
|     11626|            transactionid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {11092}|            00:03:25
|     11626|            transactionid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12213}|            00:01:55
|     11626|            transactionid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12751}|            00:00:01
|     11629|            transactionid|           ShareLock|    2019-09-17 10:00:24.331935|  389015618226997618|             {11092}|            00:03:22
|     11629|            transactionid|           ShareLock|    2019-09-17 10:00:24.331935|  389015618226997618|             {12007}|            00:00:01
|     12007|            transactionid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11629}|            00:00:13
|     12007|            transactionid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11092}|            00:01:10
|     12007|            transactionid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11288}|            00:00:05
|     12213|            transactionid|           ShareLock|    2019-09-17 10:06:07.328019|  389015618226997618|             {12007}|            00:00:10

LOCKID TYPIDE ALUSE QUERYID VÕTMINE

PĂ€ring

KOOS
lt KUI
(
	VALI
		pid , 
		locktype  ,
		mode ,
		timepoint , 
		queryid , 
		blocking_pids ,
                MIN ( timepoint ) OVER (PARTITION BY pid , locktype  ,mode ) as started  
	FROM 
		activity_hist.archive_locking
	KUS
		timepoint between pg_stat_history_begin+(current_hour_diff * interval '1 hour') JA 
			                  pg_stat_history_end+(current_hour_diff * interval '1 hour') JA 
		grantitud JA
		queryid EI OLE NULL 
	GROUP BY 
	        pid , 
		locktype  ,
		mode ,
		timepoint ,
		queryid ,
		blocking_pids 
)
VALI 
        lt.pid , 
	lt.locktype  ,
	lt.mode ,			
        lt.started ,
	lt.queryid  ,
	lt.blocking_pids ,
	COUNT(*)  * interval '1 second'	 as duration			
FROM lt 	
GROUP BY 
	lt.pid , 
	lt.locktype  ,
	lt.mode ,			
        lt.started ,
	lt.queryid ,
	lt.blocking_pids 
ORDER BY 4

NĂ€ide

| VÕTME TÜÜBID QUERYID ALOOMISEGA
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 lukketĂŒĂŒp|                reĆŸiim|                       algas|             queryid|       blokeerimise_pid|            kestus
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|                 suhe|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11092|            transactionid|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|                 suhe|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:00:10
|     11092|                 suhe|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11092|               virtualxid|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|               virtualxid|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11288|            transactionid|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11288|                    tuple| AccessExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34

Lukustuse ajaloo kasutamine jĂ”udluse hĂ€irete analĂŒĂŒsimisel.

  1. Queryid=389015618226997618, mida tÀitv protsess pid=11288, ootas lukustust alates 2019-09-17 10:00:00 kolm minutit.
  2. Lukustust hoidis protsess pid=11092.
  3. Protsess pid=11092, tÀites pÀringu queryid=389015618226997618 alates 2019-09-17 10:00:00, hoidis lukustust kolm minutit.

KokkuvÔte

NĂŒĂŒd, loodan, et hakkab toimuma kĂ”ik huvitav ja kasulik - statistika kogumine ja ootuste ning lukustuste ajaloo analĂŒĂŒs.

Tulevikus, tahaks loota, et suudan luua mingisuguse mÀrkmete kogumi (analoooge Oracle'i metalinkiga).

Üldiselt, just selle pĂ”hjusel kasutatakse meetodit, mis vĂ”imalikult kiiresti esitatakse kĂ”igile tutvumiseks.

Esimesel vĂ”imalusel pĂŒĂŒan projekti githubi ĂŒles laadida.

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster