ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Ib txoj hauv kev kom tau txais keeb kwm kaw hauv PostgreSQL

Ib txoj hauv kev kom tau txais keeb kwm kaw hauv PostgreSQL

Txuas ntxiv ntawm tsab xov xwm "Kev sim los tsim ASH analogue rau PostgreSQL ".

Cov kab lus yuav tshuaj xyuas thiab qhia, siv cov lus nug tshwj xeeb thiab cov piv txwv, cov ntaub ntawv tseem ceeb twg tuaj yeem tau siv keeb kwm ntawm pg_locks saib.

Ceebtoom
Vim qhov tshiab ntawm cov ncauj lus thiab lub sijhawm xeem tsis tiav, tsab xov xwm yuav muaj qhov yuam kev. Kev thuam thiab cov lus pom zoo txais tos thiab xav tau.

Cov ntaub ntawv nkag

Keeb kwm ntawm pg_locks sawv cev

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

Qhov tseem ceeb, lub rooj zoo ib yam li lub rooj archive_pg_stat_activity, piav qhia ntau ntxiv ntawm no - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? thiab ntawm no - Kev sim los tsim ib qho analogue ntawm ASH rau PostgreSQL.

Sau ib kab lus nug muaj nuj nqi yog siv

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;

Cov lus piav qhia: tus nqi ntawm queryid kem yog hloov kho nyob rau hauv lub history_locking rooj, thiab ces thaum ib tug tshiab muab faib yog tsim rau lub archive_locking rooj, tus nqi yuav muab khaws cia rau hauv cov keeb kwm qhov tseem ceeb.

Tsim tawm

Cov lus qhia dav dav ntawm cov txheej txheem dav dav.

TAM SIM NO LOCKTYPES

Thov

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

Piv Txwv:

| TXOJ KEV HLUB NTAWM LOCKTYPES --------------------+------------------------------------+ -------------------- | xauv hom | hom | lub sij hawm --------------------------------------------------------- --+-------------------------------- | kev lag luam | ShareLock | 19:39:26 | tug| AccessExclusiveLock | 00:03:35 +--------------------+-------------------------------- -------- +----------------

TXOJ CAI LOS NTAWM LOCKTYPES

Thov

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

Piv Txwv:

| TXOJ CAI LOS NTAWM LOCKTYPES -------------------------------------------------------------- --------+-------------------- | xauv hom | hom | lub sij hawm --------------------------------------------------------- --+-------------------------------- | kev sib raug zoo| RowExclusiveLock | 51:11:10 | virtualxid| ExclusiveLock | 48:10:43 | kev lag luam | ExclusiveLock | 44:24:53 | kev sib raug zoo| AccessShareLock| 20:06:13 | tug| AccessExclusiveLock | 17:58:47 | tug| ExclusiveLock | 01:40:41 | kev sib raug zoo| ShareUpdateExclusiveLock| 00:26:41 | khoom | RowExclusiveLock | 00:00:01 | kev lag luam | ShareLock | 00:00:01 | ncua | ExclusiveLock | 00:00:01 +--------------------+-------------------------------- -------- +----------------

Cov ncauj lus kom ntxaws txog cov lus nug tshwj xeeb

TAM SIM NO LOCKTYPES NTAWM QUERYID

Thov

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

Piv Txwv:

| TSO RAU LOCKS LOS NTAWM LOCKTYPES LOS NTAWM QUERYID ----------+----------------+--------------------- -------------------------------------------------------------------- ----------------------+------------------------------------- | pid | xauv hom | hom | pib| queryid | thaiv_pids| lub sij hawm -------------------------------------------------------------- ----------------------+-------------------- -------------------------------------------------------------------- --- | 11288 | kev lag luam | ShareLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {11092}| 00:03:34 | 11626 | kev lag luam | ShareLock | 2019-09-17 10:00:21.380921 | 389015618226997618 | {12380}| 00:00:29 | 11626 | kev lag luam | ShareLock | 2019-09-17 10:00:21.380921 | 389015618226997618 | {11092}| 00:03:25 | 11626 | kev lag luam | ShareLock | 2019-09-17 10:00:21.380921 | 389015618226997618 | {12213}| 00:01:55 | 11626 | kev lag luam | ShareLock | 2019-09-17 10:00:21.380921 | 389015618226997618 | {12751}| 00:00:01 | 11629 | kev lag luam | ShareLock | 2019-09-17 10:00:24.331935 | 389015618226997618 | {11092}| 00:03:22 | 11629 | kev lag luam | ShareLock | 2019-09-17 10:00:24.331935 | 389015618226997618 | {12007}| 00:00:01 | 12007 | kev lag luam | ShareLock | 2019-09-17 10:05:03.327933 | 389015618226997618 | {11629}| 00:00:13 | 12007 | kev lag luam | ShareLock | 2019-09-17 10:05:03.327933 | 389015618226997618 | {11092}| 00:01:10 | 12007 | kev lag luam | ShareLock | 2019-09-17 10:05:03.327933 | 389015618226997618 | {11288}| 00:00:05 | 12213 | kev lag luam | ShareLock | 2019-09-17 10:06:07.328019 | 389015618226997618 | {12007}| 00:00:10 Nws

TXOJ CAI LOS NTAWM LOCKTYPES LOS NTAWM QUERYID

Thov

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

Piv Txwv:

| TXOJ CAI LOS NTAWM LOCKTYPES LOS NTAWM QUERYID +----------------+----------+--------------------------------------+ -------------------------------------------------------------------- + --------------------+------------------------------------- | pid | xauv hom | hom | pib| queryid | thaiv_pids| lub sij hawm -------------------------------------------------------------- ----------------------+------------------------------------ ------------+------------------------------------ - | 11288 | kev sib raug zoo| RowExclusiveLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {11092}| 00:03:34 | 11092 | kev lag luam | ExclusiveLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {}| 00:03:34 | 11288 | kev sib raug zoo| RowExclusiveLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {}| 00:00:10 | 11092 | kev sib raug zoo| 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 | kev lag luam | ExclusiveLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {11092}| 00:03:34 | 11288 | tug| AccessExclusiveLock | 2019-09-17 10:00:00.302936 | 389015618226997618 | {11092}| 00:03:34 ib

Siv lub xauv keeb kwm los txheeb xyuas qhov xwm txheej ua haujlwm.

  1. Ib qho kev thov nrog queryid=389015618226997618 ua tiav los ntawm cov txheej txheem nrog pid=11288 tau tos kev thaiv pib txij 2019-09-17 10:00:00 rau 3 feeb.
  2. Lub xauv tau tuav los ntawm tus txheej txheem nrog pid = 11092
  3. Cov txheej txheem nrog pid = 11092 ua tiav qhov kev thov nrog queryid=389015618226997618 pib txij 2019-09-17 10:00:00 tuav lub xauv rau 3 feeb.

Qhov no

Tam sim no, kuv vam tias, qhov nthuav tshaj plaws thiab muaj txiaj ntsig zoo yuav pib - sau cov txheeb cais thiab txheeb xyuas cov xwm txheej ntawm keeb kwm ntawm kev tos thiab thaiv.

Nyob rau hauv lub neej yav tom ntej, kuv xav ntseeg, peb yuav tau txais ib co sau ntawv (zoo ib yam li Oracle's metalink).

Feem ntau, nws yog vim li no tias cov txheej txheem siv tau tsim muaj rau txhua tus kom sai li sai tau.

Kuv yuav sim tshaj tawm qhov project ntawm github yav tom ntej.

Tau qhov twg los: www.hab.com

Ntxiv ib saib