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

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

Artikulli do të shqyrtojë dhe tregojë përmes kërkesave dhe shembujve konkretë — se cilat informacione të dobishme mund të merren nga historia e paraqitjes pg_locks.

Kujdes.
Në lidhje me novitetin e temës dhe përfundimin e periudhës së testimit, artikulli mund të përmbajë gabime. Kritikat dhe vërejtjet janë gjithmonë të mirëpritura dhe të pritura.

Të dhënat hyrëse

Historia e paraqitjes pg_locks

archive_locking

CREATE TABLE archive_locking 
(       timepoint timestamp pa zonë kohore ,
	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ë analog të 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
CREATE OR REPLACE FUNCTION update_history_locking_by_queryid() RETURNS boolean AS $$
DECLARE
  result boolean ;
  current_minute double precision ; 
  
  start_minute integer ;
  finish_minute integer ;
  
  start_period timestamp without time zone ;
  finish_period timestamp without time zone ;
  
  lock_rec record ; 
  endpoint_rec record ;
  
  current_hour_diff double precision ;
BEGIN
  RAISE NOTICE '***update_history_locking_by_queryid';
  
  result = TRUE ;
  
  current_minute = extract ( minute from now() );

  SELECT * FROM endpoint WHERE is_need_monitoring
  INTO endpoint_rec ;
  
  current_hour_diff = endpoint_rec.hour_diff ;
  
  IF current_minute < 5 
  THEN
	RAISE NOTICE 'Current time is less than 5 minute.';
	
	start_period = date_trunc('hour',now()) + (current_hour_diff * interval '1 hour');
    finish_period = start_period - interval '5 minute' ;
  ELSE 
    finish_minute =  extract ( minute from now() ) / 5 ;
    start_minute =  finish_minute - 1 ;
  
    start_period = date_trunc('hour',now()) + interval '1 minute'*start_minute*5+(current_hour_diff * interval '1 hour');
    finish_period = date_trunc('hour',now()) + interval '1 minute'*finish_minute*5+(current_hour_diff * interval '1 hour') ;
    
  END IF ;  
  
  RAISE NOTICE 'start_period = %', start_period;
  RAISE NOTICE 'finish_period = %', finish_period;

	FOR lock_rec IN   
	WITH act_queryid AS
	 (
		SELECT 
				pid , 
				timepoint ,
				query_start AS started ,			
				MAX(timepoint) OVER (PARTITION BY pid ,	query_start   ) AS finished ,			
				queryid 
		FROM 
				activity_hist.history_pg_stat_activity 			
		WHERE 			
				timepoint BETWEEN start_period and 
								  finish_period
		GROUP BY 
				pid , 
				timepoint ,  
				query_start ,
				queryid 
	 ),
	 lock_pids AS
		(
			SELECT
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint , 
				MIN ( timepoint ) OVER (PARTITION BY pid , locktype  ,mode ) as started 
			FROM 
				activity_hist.history_locking hl
			WHERE 
				hl.timepoint between start_period and 
								     finish_period
			GROUP BY 
				hl.pid , 
				hl.locktype  ,
				hl.mode ,
				hl.timepoint 
		)
	SELECT 
		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.started BETWEEN aq.started AND aq.finished )
	WHERE aq.queryid IS NOT NULL 
	GROUP BY  
		lp.pid , 
		lp.locktype  ,
		lp.mode ,
		lp.timepoint , 
		aq.queryid
	LOOP
		UPDATE activity_hist.history_locking SET queryid = lock_rec.queryid 
		WHERE pid = lock_rec.pid AND locktype = lock_rec.locktype AND mode = lock_rec.mode AND timepoint = lock_rec.timepoint ;	
	END LOOP;    
  
  RETURN result ;
END
$$ LANGUAGE plpgsql;

Shpjegim: 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

Informacion i përgjithshëm, për proceset në tërësi.

PRITJA E BLLOKIMEVE NGA LLOJET E BLLOKIMEVE

Kërkesë

ME
t SI
(
	Zgjidhni 
		locktype  ,
		mode ,
		count(*) si total 
	Nga 
		activity_hist.archive_locking
	Ku 
		timepoint midis pg_stat_history_begin+(current_hour_diff * interval '1 hour') DHE pg_stat_history_end+(current_hour_diff * interval '1 hour') DHE 
		Nuk u dha
	Gruponi sipas 
		locktype  ,
		mode  
)
Zgjidhni 
	locktype  ,
	mode ,
	total * interval '1 second' si kohë			
Nga t 		
Rendit sipas 3 ZMADHES 

Shembuj

| 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

Kërkesë

ME
t SI
(
	Zgjidhni 
		locktype  ,
		mode ,
		count(*) si total 
	Nga 
		activity_hist.archive_locking
	Ku 
		timepoint midis pg_stat_history_begin+(current_hour_diff * interval '1 hour') DHE pg_stat_history_end+(current_hour_diff * interval '1 hour') DHE 
		u dha
	Gruponi sipas 
		locktype  ,
		mode  
)
Zgjidhni 
	locktype  ,
	mode ,
	total * interval '1 second' si kohë			
Nga t 		
Rendit sipas 3 ZMADHES 

Shembuj

| MARRJA E KLOKAVE NGA LLOJET E KLOKAVE
+--------------------+------------------------------+--------------------
|            lloji i klokes|                          moda|            kohëzgjatja
+--------------------+------------------------------+--------------------
|            marrëdhënia|              RowExclusiveLock|            51:11:10
|          virtualxid|                 ExclusiveLock|            48:10:43
|       id e transaksionit|                 ExclusiveLock|            44:24:53
|            marrëdhënia|               AccessShareLock|            20:06:13
|               tupel|           AccessExclusiveLock|            17:58:47
|               tupel|                 ExclusiveLock|            01:40:41
|            marrëdhënia|      ShareUpdateExclusiveLock|            00:26:41
|              objekti|              RowExclusiveLock|            00:00:01
|       id e transaksionit|                     ShareLock|            00:00:01
|              zgjatje|                 ExclusiveLock|            00:00:01
+--------------------+------------------------------+--------------------

Informacion i detajuar për kërkesat e veçanta me queryid

PO PRIT KLOKAT NGA LLOJET E KLOKAVE NGA QUERYID

Kërkesë

ME
lt SI
(
	ZBULO
		pid , 
		locktype  ,
		mode ,
		timepoint , 
		queryid , 
		blocking_pids ,
                MIN ( timepoint ) OVER (PARTITION BY pid , locktype  ,mode ) si filluar  
	FROM 
		activity_hist.archive_locking
	KU
		timepoint ndërmjet pg_stat_history_begin+(current_hour_diff * interval '1 hour') DHE 
			                  pg_stat_history_end+(current_hour_diff * interval '1 hour') DHE 
		Nuk u dha DHE
	       queryid NUK është NULL 
	GRUPI NGA 
	        pid , 
		locktype  ,
		mode ,
		timepoint ,
		queryid ,
		blocking_pids 
)
ZBULO
        lt.pid , 
	lt.locktype  ,
	lt.mode ,			
        lt.filluar ,
	lt.queryid  ,
	lt.blocking_pids ,
	COUNT(*)  * interval '1 second'	 si kohëzgjatje		
FROM lt 	
GRUPI NGA 
	lt.pid , 
        lt.locktype  ,
	lt.mode ,			
        lt.filluar ,
        lt.queryid ,
	lt.blocking_pids 
RENDIT DHE 4

Shembuj

| PRITENDA E LOCKAVE NGA LLOJET DHE ID TË KËRKESËS
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       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

MARRËN LOCKASH ME LLOJ LOCKASH ME QUERYID

Kërkesë

ME
lt SI
(
	ZBJEDH
		pid , 
		lloj_locku  ,
		modi ,
		nokta_e_kohës , 
		queryid , 
		pid_blokues ,
                MIN ( nokta_e_kohës ) NË (SHKATHTËSI PËR pid , lloji_locku ,modi ) si filluar  
	FROM 
		activity_hist.archive_locking
	WHERE 
		nokta_e_kohës midis pg_stat_history_begin+(current_hour_diff * interval '1 orë') DHE 
			                  pg_stat_history_end+(current_hour_diff * interval '1 orë') DHE 
		pranuar DHE
		queryid NUK ËSHTË NULL 
	GRUPI NGA 
	        pid , 
		lloj_locku  ,
		modi ,
		nokta_e_kohës ,
		queryid ,
		pid_blokues 
)
ZBJEDH 
        lt.pid , 
	lt.lloj_locku  ,
	lt.modi ,			
        lt.filluar ,
	lt.queryid  ,
	lt.pid_blokues ,
	COUNT(*)  * interval '1 sekond'	 si kohëzgjatje			
FROM lt 	
GRUPI NGA 
	lt.pid , 
	lt.lloj_locku  ,
	lt.modi ,			
        lt.filluar ,
	lt.queryid ,
	lt.pid_blokues 
RUJTO KRENARE 4

Shembuj

| MARRJA E BLLOKIMEVE NGA LLOJET E BLLOKIMEVE ME QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 lloji i bllokimit|                mënyra|                       fillimi|             queryid|       bllokimi_pidve|            kohëzgjatja
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|                 marrëdhënia|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11092|            id transaksioni|       ExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|                 marrëdhënia|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:00:10
|     11092|                 marrëdhënia|    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|            id transaksioni|       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 historikut të bllokimeve për analizën e incidenteve të performancës.

1. Kërkesa me queryid=389015618226997618 e ekzekutuar nga procesi me pid=11288 priste bllokimin që nga 2019-09-17 10:00:00 për gjatë 3 minutash.
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 gjatë 3 minutash.

Përfundimi

Tani, shpresoj që të fillojë më interesanti dhe i dobishmi – mbledhja e statistikave dhe analiza e rasteve nga historiku i pritjeve dhe bllokimeve.

Në perspektivë, shpresoj të krijohet një grup i disa si nota (ngjashëm me metalinkun e Oracle).

Në të vërtetë, pikërisht për këtë arsye metodika e përdorur del sa më shpejt për njohjen e përgjithshme.

Në kohën më të afërt do të përpiqem ta ngarkoj projektin në github.

Burimi: habr.com