Një nga metodat për të marrë historinë e bllokimeve në PostgreSQL

Vazhdimi i artikullit «Përpjekje për të krijuar një analog ASH për PostgreSQL «.

Artikulli do të shqyrtojë dhe do të tregojë në kërkesa dhe shembuj specifikë - çfarë informacioni të dobishëm mund të merrni me ndihmën e historisë së paraqitjes pg_locks.

Kujdes.
Për shkak të risisë së temës dhe përfundimit të periudhës së testimit, artikulli mund të përmbajë gabime. Kërkesa dhe vërejtje janë gjithmonë të mirëpritura dhe priten.

Të dhënat hyrëse

Historia e paraqitjes pg_locks

archive_locking

KRIJONI TABELËN archive_locking 
(       timepoint timestamp pa zonë kohe ,
	locktype tekst ,
	relation oid ,
	mode tekst ,
	tid xid ,
	vtid tekst ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean ,
        queryid bigint 
);

Në thelb, tabela është e ngjashme me tabelën archive_pg_stat_activity, e përshkruar më hollësisht këtu - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? dhe këtu — Përpjekja për të krijuar një ngjashmëri ASH për PostgreSQL.

Për të mbushur kolonën queryid përdoret funksioni

update_history_locking_by_queryid

--update_history_locking_by_queryid.sql
KRIJONI O PËRMBAJ ME FUNKSION update_history_locking_by_queryid() KTHE NJË boolean AS $$
SHKRUANI
  rezultati boolean ;
  current_minute double precision ; 
  
  start_minute integer ;
  finish_minute integer ;
  
  start_period timestamp pa zonë kohe ;
  finish_period timestamp pa zonë kohe ;
  
  lock_rec rekord ; 
  endpoint_rec rekord ;
  
  current_hour_diff double precision ;
KRIJONI
  RAISE NOTICE '***update_history_locking_by_queryid';
  
  rezultati = TRUE ;
  
  current_minute = extract ( minute nga tani() );

  ZGJIDHNI * NGA endpoint KU është_nevojë_monitorimi
  NË endpoint_rec ;
  
  current_hour_diff = endpoint_rec.hour_diff ;
  
  NËSE current_minute < 5 
  ATËHERË
	RAISE NOTICE 'Koha aktuale është më e vogël se 5 minuta.';
	
	start_period = date_trunc('orë', tani()) + (current_hour_diff * interval '1 orë');
    finish_period = start_period - interval '5 minuta' ;
  NDËRKOHE 
    finish_minute = extract ( minute nga tani() ) / 5 ;
    start_minute =  finish_minute - 1 ;
  
    start_period = date_trunc('orë', tani()) + interval '1 minutë'*start_minute*5+(current_hour_diff * interval '1 orë');
    finish_period = date_trunc('orë', tani()) + interval '1 minutë'*finish_minute*5+(current_hour_diff * interval '1 orë') ;
    
  FUNDIF 
  
  RAISE NOTICE 'start_period = %', start_period;
  RAISE NOTICE 'finish_period = %', finish_period;

	PËR lock_rec NË   
	ME act_queryid SI
	 (
		ZGJIDHNI 
				pid , 
				imepoint ,
				query_start SI filluar ,			
				MAX(timepoint) OVER (PARTICIPIM NGA pid ,	query_start   ) SI përfunduar ,			
				queryid 
		MARR NGA 
				activity_hist.history_pg_stat_activity 			
		KU 			
				timepoint MIDIS start_period dhe 
								 finish_period
		GRUPI NGA 
				pid , 
				imepoint ,  
				query_start ,
				queryid 
	 ),
	 lock_pids SI
		(
			ZGJIDHNI
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint , 
				MIN ( timepoint ) OVER (PARTICIPIM NGA pid , locktype  ,mode ) si filluar 
			MARR NGA 
				activity_hist.history_locking hl
			KU 
				hl.timepoint midis start_period dhe 
								     finish_period
			GRUPI NGA 
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint 
		)
	ZGJIDHNI 
		lp.pid , 
		lp.locktype  ,
		lp.mode ,
		lp.timepoint ,     
		aq.queryid 
	FROM lock_pids 	lp LEFT OUTER JOIN act_queryid aq ON ( lp.pid = aq.pid AND lp.filluar MIDIS aq.filluar DHE aq.përfunduar )
	KU aq.queryid ËSHTE E SAKTË 
	GRUPI NGA  
		lp.pid , 
		lp.locktype  ,
		lp.mode ,
		lp.timepoint , 
		aq.queryid
	LOOP
		UPDATE activity_hist.history_locking SET queryid = lock_rec.queryid 
		KU pid = lock_rec.pid DHE locktype = lock_rec.locktype DHE mode = lock_rec.mode DHE timepoint = lock_rec.timepoint ;	
	FUNDO LOOP;    
  
  KTHE rezultatin ;
FUND
$$ GJUHA plpgsql;

Shpjegimi: Vlera e kolonës queryid përditësohet në tabelën history_locking, dhe më pas kur krijohet një seksion i ri për tabelën archive_locking, vlera do të ruhet në vlerat historike.

Të dhënat dalëse

Informacione të përgjithshme për proceset në tërësi.

PRITJA E Bllokimeve NGA LLOJET E BLLOKIMEVE

Pyetje

ME
t SI
(
	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 

Shembulli

| PRITJA E Bllokimeve NGA LLOJET E BLLOKIMEVE
+--------------------+------------------------------+--------------------
|            locktype|                          mode|            duration
+--------------------+------------------------------+--------------------
|       transactionid|                     ShareLock|            19:39:26
|               tuple|           AccessExclusiveLock|            00:03:35
+--------------------+------------------------------+--------------------

Marrjet e Bllokimeve NGA LLOJET E BLLOKIMEVE

Pyetje

ME
t SI
(
	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 

Shembulli

| MARRJET E Bllokimeve NGA LLOJET E BLLOKIMEVE
+--------------------+------------------------------+--------------------
|            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
+--------------------+------------------------------+--------------------

Informacione të detajuara për kërkesat e caktuara me queryid

PRITJA E Bllokimeve NGA LLOJET E BLLOKIMEVE NGA QUERYID

Pyetje

ME
lt SI
(
	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

Shembulli

| PËR PRITJE TË Bllokimeve NGA LLOJET E Bllokimit NGA 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

MARRJA E Bllokimeve NGA LLOJET E Bllokimit NGA QUERYID

Pyetje

ME
lt SI
(
	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 
		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

Shembulli

| MARRJA E BLOKIMEVE NGA LLOJE TË BLOKIMEVE NGA QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 lloji i bllokimit|                moda|                       filluar|             queryid|       bllokimi_pids|            koha
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|                 marrëdhënie|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11092|            identifikimi i transaksionit|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|                 marrëdhënie|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:00:10
|     11092|                 marrëdhënie|    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|            identifikimi i transaksionit|       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

Përdorimi i historisë së bllokimit për analizën e incidenteve të performancës.

1. Kërkesa me queryid=389015618226997618 e cila po ekzekutohet nga procesi me pid=11288 ka pritur bllokimin që nga 2019-09-17 10:00:00 për 3 minuta.
2. Bllokimin e mbante procesi me pid=11092
3. Procesi me pid=11092 duke ekzekutuar kërkesën me queryid=389015618226997618 që nga 2019-09-17 10:00:00 mbante bllokimin për 3 minuta.

Përfundimi

Tani, shpresoj, do të fillojë gjëja më interesante dhe të dobishme - mbledhja e statistikave dhe analiza e rasteve të historisë së pritjeve dhe bllokimeve.

Në perspektivë, shpresoj, do të arrijmë një grup disa si nota (në analogji me metalinkun e Oraklit).

Kështu që pikërisht nga kjo arsye, metodika e përdorur, nxirret sa më shpejt për njohjen e përgjithshme.

Në kohët më të afërta do të përpiqem të publikoj projektin në github.

Burimi: habr.com