En av metoderna för att få lås historik i PostgreSQL

Fortsättningen av artikeln "Ett försök att skapa en analog till ASH för PostgreSQL «.

Artikeln kommer att granska och visa med konkreta fraser och exempel vilken användbar information som kan erhållas genom visningen av pg_locks.

Varning.
På grund av ämnets nyhet och det ofullständiga testperioden kan artikeln innehålla fel. Kritik och kommentarer välkomnas och förväntas.

Indata

Historik över visningen pg_locks

archive_locking

CREATE TABLE archive_locking 
(       timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean ,
        queryid bigint 
);

I grunden är tabellen liknande tabellen archive_pg_stat_activity, som beskrivs mer detaljerat här — pg_stat_statements + pg_stat_activity + loq_query = pg_ash? och här — Ett försök att skapa en analog till ASH för PostgreSQL.

För att fylla i kolumnen queryid används funktionen

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;

Förklaring: Värdet i kolumnen queryid uppdateras i tabellen history_locking, och när en ny sektion för tabellen archive_locking skapas, kommer värdet att sparas i de historiska värdena.

Utdata

Översikt över processerna som helhet.

VÄNTAR PÅ LÅSNINGAR ENLIGA LÅSTYPER

Fråga

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 

Exempel

| VÄNTAR PÅ LÅS PER LÅSTYP
+--------------------+------------------------------+--------------------
|            låstyp|                          läge|            varaktighet
+--------------------+------------------------------+--------------------
|       transaktionsid|                     DelaLås|            19:39:26
|               tuple|           ÅtkomstExklusivLås|            00:03:35
+--------------------+------------------------------+--------------------

TAGNINGAR AV LÅS PER LÅSTYP

Fråga

MED
t AS
(
	VÄLJ 
		låstyp  ,
		läge ,
		antal(*) som totalt 
	FRÅN 
		activity_hist.archive_locking
	DÄR 
		tidpunkt mellan pg_stat_history_begin+(current_hour_diff * intervall '1 timme') OCH pg_stat_history_end+(current_hour_diff * intervall '1 timme') OCH 
		tilldelat
	GRUPPERA EFTER 
		låstyp  ,
		läge  
)
VÄLJ 
	låstyp  ,
	läge ,
	totalt * intervall '1 sekund' som varaktighet			
FRÅN t 		
ORDNA EFTER 3 DESC 

Exempel

| TAGNINGAR AV LÅS PER LÅSTYP
+--------------------+------------------------------+--------------------
|            låstyp|                          läge|            varaktighet
+--------------------+------------------------------+--------------------
|            relation|              RadExklusivLås|            51:11:10
|          virtualxid|                 ExklusivLås|            48:10:43
|       transaktionsid|                 ExklusivLås|            44:24:53
|            relation|               ÅtkomstDelaLås|            20:06:13
|               tuple|           ÅtkomstExklusivLås|            17:58:47
|               tuple|                 ExklusivLås|            01:40:41
|            relation|      DelaUppdateraExklusivLås|            00:26:41
|              objekt|              RadExklusivLås|            00:00:01
|       transaktionsid|                     DelaLås|            00:00:01
|              extend|                 ExklusivLås|            00:00:01
+--------------------+------------------------------+--------------------

Detaljerad information om specifika queryid-frågor

VÄNTAR PÅ LÅS PER LÅSTYP PER QUERYID

Fråga

MED
lt AS
(
	VÄLJ
		pid , 
		låstyp  ,
		läge ,
		tidpunkt , 
		queryid , 
		blockerande_pid ,
                MIN ( tidpunkt ) ÖVER (PARTITION BY pid , låstyp  ,läge ) som startad  
	FRÅN 
		activity_hist.archive_locking
	DÄR 
		tidpunkt mellan pg_stat_history_begin+(current_hour_diff * intervall '1 timme') OCH 
			                  pg_stat_history_end+(current_hour_diff * intervall '1 timme') OCH 
		IKKE tilldelat OCH
	       queryid ÄR INTE NULL 
	GRUPPERA EFTER 
	        pid , 
		låstyp  ,
		läge ,
		tidpunkt ,
		queryid ,
		blockerande_pid 
)
VÄLJ 
        lt.pid , 
	lt.låstyp  ,
	lt.läge ,			
        lt.startad ,
	lt.queryid  ,
	lt.blockerande_pid ,
	ANTAL(*)  * intervall '1 sekund'	 som varaktighet		
FRÅN lt 	
GRUPPERA EFTER 
	lt.pid , 
        lt.låstyp  ,
	lt.läge ,			
        lt.startad ,
        lt.queryid ,
	lt.blockerande_pid 
ORDNA EFTER 4

Exempel

| VÄNTAR PÅ LÅS EFTER LÅSTYPER EFTER FRÅGE-ID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 låstyp|                läge|                       startad|             frågaid|       blockering_pid|            varaktighet
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|            transaktionsid|           ShareLock|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11626|            transaktionsid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12380}|            00:00:29
|     11626|            transaktionsid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {11092}|            00:03:25
|     11626|            transaktionsid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12213}|            00:01:55
|     11626|            transaktionsid|           ShareLock|    2019-09-17 10:00:21.380921|  389015618226997618|             {12751}|            00:00:01
|     11629|            transaktionsid|           ShareLock|    2019-09-17 10:00:24.331935|  389015618226997618|             {11092}|            00:03:22
|     11629|            transaktionsid|           ShareLock|    2019-09-17 10:00:24.331935|  389015618226997618|             {12007}|            00:00:01
|     12007|            transaktionsid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11629}|            00:00:13
|     12007|            transaktionsid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11092}|            00:01:10
|     12007|            transaktionsid|           ShareLock|    2019-09-17 10:05:03.327933|  389015618226997618|             {11288}|            00:00:05
|     12213|            transaktionsid|           ShareLock|    2019-09-17 10:06:07.328019|  389015618226997618|             {12007}|            00:00:10

TAR LÅSAR EFTER LÅSTYPER EFTER FRÅGE-ID

Fråga

MED
lt SOM
(
	VÄLJ
		pid , 
		låstyp  ,
		läge ,
		tidpunkt , 
		fråge-id , 
		blockering_pid ,
                MIN ( tidpunkt ) ÖVER (PARTITION BY pid , låstyp  ,läge ) som startad  
	FRÅN 
		aktivitet_hist.archive_locking
	DÄR 
		tidpunkt mellan pg_stat_history_begin+(current_hour_diff * intervall '1 timme') OCH 
			                  pg_stat_history_end+(current_hour_diff * intervall '1 timme') OCH 
		beviljad OCH
		fråge-id ÄR INTE NULL 
	GROUP BY 
	        pid , 
		låstyp  ,
		läge ,
		tidpunkt ,
		fråge-id ,
		blockering_pid 
)
VÄLJ 
        lt.pid , 
	lt.låstyp  ,
	lt.läge ,			
        lt.startad ,
	lt.fråge-id  ,
	lt.blockering_pid ,
	COUNT(*)  * intervall '1 sekund'	 som varaktighet			
FRÅN lt 	
GROUP BY 
	lt.pid , 
	lt.låstyp  ,
	lt.läge ,			
        lt.startad ,
	lt.fråge-id ,
	lt.blockering_pid 
ORDER BY 4

Exempel

| TAGNING AV LÅS GENOM LÅSTYPER GENOM QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 låstyp|                läge|                       startad|             queryid|       blockerande_pids|            varaktighet
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|                 relation|    RadExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11092|            transaktionsid|       ExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|                 relation|    RadExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:00:10
|     11092|                 relation|    RadExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11092|               virtualxid|       ExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:03:34
|     11288|               virtualxid|       ExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11288|            transaktionsid|       ExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34
|     11288|                    tuple| ÅtkomstExklusivtLås|    2019-09-17 10:00:00.302936|  389015618226997618|             {11092}|            00:03:34

Användning av låshistorik vid analys av prestandaincidenter.

1. Förfrågan med queryid=389015618226997618 utförd av processen med pid=11288 väntade på lås sedan 2019-09-17 10:00:00 i 3 minuter.
2. Låset hölls av processen med pid=11092.
3. Processen med pid=11092, som utförde förfrågan med queryid=389015618226997618 sedan 2019-09-17 10:00:00, höll låset i 3 minuter.

Resultat

Nu hoppas jag att det mest intressanta och nyttiga börjar - insamlingen av statistik och analys av fall baserade på väntetider och låshistorik.

I framtiden hoppas jag att det blir en uppsättning av något som en note (i likhet med metallänken i Oracle).

Det är egentligen av denna anledning som den använda metoden snabbt görs tillgänglig för allmänheten.

Så snart som möjligt ska jag försöka lägga upp projektet på GitHub.

Källa: habr.com