ProHoster > Blog > Beheer > Een van de methoden om het blokkeringsoverzicht in PostgreSQL te verkrijgen

Een van de methoden om het blokkeringsoverzicht in PostgreSQL te verkrijgen

Vervolg op het artikel Ā«Poging om een analoog van ASH voor PostgreSQL te creĆ«ren Ā«.

In dit artikel wordt besproken en getoond aan de hand van specifieke queries en voorbeelden ā€” welke nuttige informatie men kan verkrijgen met behulp van het pg_locks overzicht.

Waarschuwing.
Vanwege de nieuwheid van het onderwerp en het onvoltooide testperiode, kan het artikel fouten bevatten. Kritiek en opmerkingen zijn van harte welkom en worden verwacht.

Invoergegevens

Het pg_locks overzicht

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 
);

In wezen is de tabel vergelijkbaar met de tabel archive_pg_stat_activity, die hier uitgebreider wordt beschreven ā€” pg_stat_statements + pg_stat_activity + loq_query = pg_ash? en hier ā€” Poging om een analoog van ASH voor PostgreSQL te creĆ«ren.

Voor het invullen van de kolom queryid wordt de functie

update_history_locking_by_queryid gebruikt.

--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 'Huidige tijd is minder dan 5 minuten.';
	
	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;

Uitleg: De waarde van de kolom queryid wordt bijgewerkt in de tabel history_locking, en zal vervolgens worden bewaard in de historische waarden bij het aanmaken van een nieuwe sectie voor de tabel archive_locking.

Uitvoer

Algemene informatie over de processen als geheel.

WACHTEN OP LOCKS PER LOCKTYPE

Verzoek

MET
t AS
(
	SELECT 
		locktype  ,
		mode ,
		count(*) as totaal 
	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 ,
	totaal * interval '1 second' as duur			
FROM t 		
ORDER BY 3 DESC

Voorbeeld

| WACHTEN OP SLOTEN VOLGENS SLOTYPES
+--------------------+------------------------------+--------------------
|            slottype|                          modus|            duur
+--------------------+------------------------------+--------------------
|       transactionid|                     ShareLock|            19:39:26
|               tuple|           AccessExclusiveLock|            00:03:35
+--------------------+------------------------------+--------------------

INNAME VAN SLOTEN VOLGENS SLOTYPES

Verzoek

MET
t AS
(
	SELECT 
		slottype  ,
		modus ,
		count(*) als totaal 
	FROM 
		activity_hist.archive_locking
	WHERE 
		tijdstip tussen pg_stat_history_begin+(current_hour_diff * interval '1 uur') EN pg_stat_history_end+(current_hour_diff * interval '1 uur') EN 
		verleend
	GROUP BY 
		slottype  ,
		modus  
)
SELECT 
	slottype  ,
	modus ,
	totaal * interval '1 seconde' als duur			
FROM t 		
ORDER BY 3 DESC

Voorbeeld

| INNAME VAN SLOTEN VOLGENS SLOTYPES
+--------------------+------------------------------+--------------------
|            slottype|                          modus|            duur
+--------------------+------------------------------+--------------------
|            relatie|              RowExclusiveLock|            51:11:10
|          virtualxid|                 ExclusiveLock|            48:10:43
|       transactionid|                 ExclusiveLock|            44:24:53
|            relatie|               AccessShareLock|            20:06:13
|               tuple|           AccessExclusiveLock|            17:58:47
|               tuple|                 ExclusiveLock|            01:40:41
|            relatie|      ShareUpdateExclusiveLock|            00:26:41
|              object|              RowExclusiveLock|            00:00:01
|       transactionid|                     ShareLock|            00:00:01
|              extend|                 ExclusiveLock|            00:00:01
+--------------------+------------------------------+--------------------

Gedetailleerde informatie over specifieke queryid-aanvragen

WACHTEN OP SLOTEN VOLGENS SLOTYPES PER QUERYID

Verzoek

MET
lt AS
(
	SELECT
		pid , 
		slottype  ,
		modus ,
		tijdstip , 
		queryid , 
		blocking_pids ,
                MIN ( tijdstip ) OVER (PARTITION BY pid , slottype  ,modus ) als begonnen  
	FROM 
		activity_hist.archive_locking
	WHERE 
		tijdstip tussen pg_stat_history_begin+(current_hour_diff * interval '1 uur') EN 
			                  pg_stat_history_end+(current_hour_diff * interval '1 uur') EN 
		NIET verleend EN
	       queryid IS NIET NULL 
	GROUP BY 
	        pid , 
		slottype  ,
		modus ,
		tijdstip ,
		queryid ,
		blocking_pids 
)
SELECT 
        lt.pid , 
	lt.slottype  ,
	lt.modus ,			
        lt.beginnen ,
	lt.queryid  ,
	lt.blocking_pids ,
	COUNT(*)  * interval '1 seconde'	 als duur		
FROM lt 	
GROUP BY 
	lt.pid , 
        lt.slottype  ,
	lt.modus ,			
        lt.beginnen ,
        lt.queryid ,
	lt.blocking_pids 
ORDER BY 4

Voorbeeld

| WACHTEN OP VERGRENDELINGEN OP BASIS VAN VERGRENSDINGSTYPEN PER QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 vergrendeltype|                modus|                       gestart|             queryid|       blokkering_pids|            duur
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     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

VERGRENDELING NEMEN OP BASIS VAN VERGRENSDINGSTYPEN PER QUERYID

Verzoek

MET
lt ALS
(
	SELECT
		pid , 
		vergrendeltype  ,
		modus ,
		tijdstip , 
		queryid , 
		blokkering_pids ,
                MIN ( tijdstip ) OVER (PARTITION BY pid , vergrendeltype  ,modus ) als gestart  
	FROM 
		activity_hist.archive_locking
	WHERE 
		tijdstip tussen pg_stat_history_begin+(current_hour_diff * interval '1 hour') EN 
			                  pg_stat_history_end+(current_hour_diff * interval '1 hour') EN 
		toegekend EN
		queryid IS NIET NULL 
	GROUP BY 
	        pid , 
		vergrendeltype  ,
		modus ,
		tijdstip ,
		queryid ,
		blokkering_pids 
)
SELECT 
        lt.pid , 
	lt.vergrendeltype  ,
	lt.modus ,
        lt.gestart ,
	lt.queryid  ,
	lt.blokkering_pids ,
	COUNT(*)  * interval '1 second'	 als duur			
FROM lt 	
GROUP BY 
	lt.pid , 
	lt.vergrendeltype  ,
	lt.modus ,
        lt.gestart ,
	lt.queryid ,
	lt.blokkering_pids 
ORDER BY 4

Voorbeeld

| LOCKS OPHALEN PER LOCKTYPE PER QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|       pid|                 locktype|                mode|                       gestart|             queryid|       blocking_pids|            duur
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
|     11288|                 relatie|    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|                 relatie|    RowExclusiveLock|    2019-09-17 10:00:00.302936|  389015618226997618|                  {}|            00:00:10
|     11092|                 relatie|    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

Het gebruik van de lockgeschiedenis bij het analyseren van prestatie-incidenten.

  1. De aanvraag met queryid=389015618226997618, uitgevoerd door proces met pid=11288, wachtte op een lock sinds 2019-09-17 10:00:00 gedurende 3 minuten.
  2. De lock werd vastgehouden door proces met pid=11092.
  3. Proces met pid=11092 hield de lock vast gedurende 3 minuten terwijl het aanvraag met queryid=389015618226997618 uitvoerde, beginnend vanaf 2019-09-17 10:00:00.

Conclusie

Nu hoop ik dat het interessante en nuttige beginnen - het verzamelen van statistieken en het analyseren van cases op basis van de geschiedenis van wachten en locks.

In de toekomst hoop ik een soort set van notities te kunnen creƫren zoals die van de metalink van Oracle.

Eigenlijk is dat de reden waarom de gebruikte methode zo snel mogelijk wordt gepresenteerd voor algemene kennismaking.

Zodra mogelijk zal ik proberen het project op GitHub te plaatsen.

Bron: habr.com

Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers šŸ”„ Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers | ProHoster