ProHoster > Pūnaewele > Nā Administration > ʻO kekahi o nā ala no ka loaʻa ʻana o ka mōʻaukala laka ma PostgreSQL

ʻO kekahi o nā ala no ka loaʻa ʻana o ka mōʻaukala laka ma PostgreSQL

Ka hoʻomau ʻana o ka ʻatikala "ʻO kahi hoʻāʻo e hana i kahi analogue o ASH no PostgreSQL ".

E noʻonoʻo a hōʻike ka ʻatikala ma nā nīnau kikoʻī a me nā hiʻohiʻona i ka ʻike pono e hiki ke loaʻa me ka hoʻohana ʻana i ka mōʻaukala ʻike pg_locks.

Pākuʻi
Ma muli o ke ʻano hou o ke kumuhana a me ka piha ʻole o ka manawa hoʻāʻo, aia paha i ka ʻatikala nā hewa. Manaʻo ʻia nā ʻōlelo hoʻohewa a me nā manaʻo.

ʻikepili hoʻokomo

pg_locks hōʻike mōʻaukala

waihona_paʻa

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

Ma ke kumu, ua like ka papaʻaina me ka papaʻaina archive_pg_stat_activitywehewehe ʻia ma nā kikoʻī hou aku ma aneʻi - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? a maanei - ʻO kahi hoʻāʻo e hana i kahi analogue o ASH no PostgreSQL.

E hoʻopiha i kahi kolamu queryid hoʻohana ʻia ka hana

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;

ʻO ka wehewehe: ka waiwai o ka queryid kolamu ua hou i loko o ka history_locking papaʻaina, a laila, i ka wā i hana ʻia ai kahi ʻāpana hou no ka archive_locking table, e mālama ʻia ka waiwai i nā waiwai mōʻaukala.

Huahana

ʻIke nui e pili ana i nā kaʻina hana ma ka laulā.

KE KAALI ANA I NA LAKA E NA LOCKTYPES

Noi

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

Pākuhi:

| KE KAALI ANA NO NA LAKA BY LOCKTYPES +---------------------------------------------- --------+--------------------- | locktype| ano| lōʻihi +--------------------+--------------------- ---+-- ------------------- | transactionid| kaʻana like| 19:39:26 | tuple| AccessExclusiveLock| 00:03:35 +---------------------+---------------------- --------+---------------------

KA LAWE ANA I NA LAKA E NA LOCKTYPES

Noi

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

Pākuhi:

| KA LAWE I NA LAKA E NA LOCKTYPES +----------------------------------------------- --------+--------------------- | locktype| ano| lōʻihi +--------------------+--------------------- ---+-- ------------------- | pili| RowExclusiveLock| 51:11:10 | virtualxid| Loka Kahiko| 48:10:43 | transactionid| Loka Kahiko| 44:24:53 | pili| AccessShareLock| 20:06:13 | tuple| AccessExclusiveLock| 17:58:47 | tuple| Loka Kahiko| 01:40:41 | pili| ShareUpdateExclusiveLock| 00:26:41 | mea| RowExclusiveLock| 00:00:01 | transactionid| kaʻana like| 00:00:01 | hooloihi| Loka Kūʻokoʻa| 00:00:01 +---------------------+---------------------- --------+---------------------

ʻIke kikoʻī no nā nīnau queryid kikoʻī

KAkali ʻana no nā laka ma nā ʻano LOCKTYPES BY QUERYID

Noi

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

Pākuhi:

| KE KAkali ʻana i nā laka e nā locktypes BY QUERYID +----------+------------------------+------ --------------+--------------------------------+--- --------------------+--------------------+---------- - --------- | pid| locktype| ano| hoomaka| queryid| blocking_pids| lōʻihi +----------+------------------------+----------- -----------------------+--------------------------------+--------- ---+--------------------+----------------- --- | 11288| transactionid| kaʻana like| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11626| transactionid| kaʻana like| 2019-09-17 10:00:21.380921| 389015618226997618| {12380}| 00:00:29 | 11626| transactionid| kaʻana like| 2019-09-17 10:00:21.380921| 389015618226997618| {11092}| 00:03:25 | 11626| transactionid| kaʻana like| 2019-09-17 10:00:21.380921| 389015618226997618| {12213}| 00:01:55 | 11626| transactionid| kaʻana like| 2019-09-17 10:00:21.380921| 389015618226997618| {12751}| 00:00:01 | 11629| transactionid| kaʻana like| 2019-09-17 10:00:24.331935| 389015618226997618| {11092}| 00:03:22 | 11629| transactionid| kaʻana like| 2019-09-17 10:00:24.331935| 389015618226997618| {12007}| 00:00:01 | 12007| transactionid| kaʻana like| 2019-09-17 10:05:03.327933| 389015618226997618| {11629}| 00:00:13 | 12007| transactionid| kaʻana like| 2019-09-17 10:05:03.327933| 389015618226997618| {11092}| 00:01:10 | 12007| transactionid| kaʻana like| 2019-09-17 10:05:03.327933| 389015618226997618| {11288}| 00:00:05 | 12213| transactionid| kaʻana like| 2019-09-17 10:06:07.328019| 389015618226997618| {12007}| 00:00:10

LAWE LOCK BY LOCKTYPES BY QUERYID

Noi

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

Pākuhi:

| E LAWE I NA LOCKTYPES MA QUERYID +---------------------------------------+------ --------------+--------------------------------+---- -----------------------+------------ -------- | pid| locktype| ano| hoomaka| queryid| blocking_pids| lōʻihi +----------+------------------------+----------- -----------------------+--------------------------------+--------- ---+--------------------+----------------- --- | 11288| pili| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11092| transactionid| Loka Kūʻokoʻa| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11288| pili| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10 | 11092| pili| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11092| virtualxid| Loka Kūʻokoʻa| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11288| virtualxid| Loka Kūʻokoʻa| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11288| transactionid| Loka Kūʻokoʻa| 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

Ke hoʻohana nei i ka mōʻaukala pale i ka wā e nānā ana i nā hanana hana.

  1. ʻO kahi nīnau me queryid=389015618226997618 i hoʻokō ʻia e kahi kaʻina me pid=11288 ke kali nei i kahi laka mai 2019-09-17 10:00:00 no 3 mau minuke.
  2. Ua paʻa ka laka ma kahi kaʻina me pid=11092
  3. He kaʻina hana me pid=11092 e hoʻokō ana i kahi nīnau me queryid=389015618226997618 mai 2019-09-17 10:00:00 paʻa i ka laka no 3 mau minuke.

ʻO ka hopena

I kēia manawa, manaʻolana wau, e hoʻomaka ka mea hoihoi a me ka pono - e hōʻiliʻili i nā helu helu a me ka nānā ʻana i nā hihia ma ka mōʻaukala o nā manaʻolana a me ka pale ʻana.

I ka wā e hiki mai ana, makemake wau e manaʻoʻiʻo, e loaʻa iā ʻoe kahi hoʻonohonoho o kekahi ʻano memo (e like me ka metalink ʻo Oracle).

Ma ka laulā, ʻo ia ke kumu e hōʻike ʻia ai ke ʻano i hoʻohana ʻia i ka wikiwiki e hiki ai ke kamaʻāina ākea.

I ka wā e hiki mai ana e hoʻāʻo wau e kau i ka papahana ma github.

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka