🥇វិធីសាស្រ្តមួយសម្រាប់ការទទួលបានប្រវត្តិចាក់សោនៅក្នុង PostgreSQL

ការបន្តនៃអត្ថបទ "ការប៉ុនប៉ងបង្កើតអាណាឡូក ASH សម្រាប់ PostgreSQL "។

អត្ថបទនឹងពិនិត្យ និងបង្ហាញ ដោយប្រើសំណួរ និងឧទាហរណ៍ជាក់លាក់ តើព័ត៌មានមានប្រយោជន៍អ្វីខ្លះអាចទទួលបានដោយប្រើប្រវត្តិនៃទិដ្ឋភាព pg_locks ។

ព្រមាន
ដោយសារតែភាពថ្មីថ្មោងនៃប្រធានបទ និងរយៈពេលសាកល្បងមិនទាន់បញ្ចប់ អត្ថបទអាចមានកំហុស។ ការរិះគន់ និងមតិត្រូវបានស្វាគមន៍យ៉ាងខ្លាំង និងរំពឹងទុក។

បញ្ចូលទិន្នន័យ

ប្រវត្តិនៃការតំណាង 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 
);

សំខាន់តារាងគឺស្រដៀងនឹងតារាង archive_pg_stat_activityពិពណ៌នាលម្អិតនៅទីនេះ - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? ហើយនៅទីនេះ - ការប៉ុនប៉ងដើម្បីបង្កើត analogue នៃ ASH សម្រាប់ PostgreSQL ។

ដើម្បីបំពេញជួរឈរ សំណួរ មុខងារត្រូវបានប្រើប្រាស់

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;

ការពន្យល់៖ តម្លៃនៃជួរឈរសំណួរត្រូវបានធ្វើបច្ចុប្បន្នភាពនៅក្នុងតារាង history_locking ហើយបន្ទាប់មកនៅពេលដែលភាគថាសថ្មីត្រូវបានបង្កើតសម្រាប់តារាង archive_locking តម្លៃនឹងត្រូវបានរក្សាទុកក្នុងតម្លៃប្រវត្តិសាស្ត្រ។

លទ្ធផល

ព័ត៌មានទូទៅអំពីដំណើរការទូទៅ។

កំពុងរង់ចាំការចាក់សោដោយប្រភេទចាក់សោ

សំណើ

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

ឧទាហរណ៍:

| រង់ចាំការចាក់សោរដោយ LOCKTYPES -----------------------+------------------------------------+ ------------------- | ប្រភេទចាក់សោ| របៀប| រយៈពេល +--------------------+-------------------------------- --+-------------------- | លេខប្រតិបត្តិការ| ShareLock| ១៩:៣៩:២៦ | tuple| AccessExclusiveLock| 19:39:26 ------------------------------+----------------------- --------+----------

ការចាប់យកសោតាមប្រភេទសោ

សំណើ

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

ឧទាហរណ៍:

| ការចាប់យកសោតាមប្រភេទសោ +--------------------------------+-------------------------- --------+-------- | ប្រភេទចាក់សោ| របៀប| រយៈពេល +--------------------+-------------------------------- --+-------------------- | ទំនាក់ទំនង | RowExclusiveLock| 51:11:10 | និម្មិត | ExclusiveLock| ៤៨:១០:៤៣ | លេខប្រតិបត្តិការ| ExclusiveLock| 48:10:43 | ទំនាក់ទំនង | AccessShareLock| 44:24:53 | tuple| AccessExclusiveLock| ១៧:៥៨:៤៧ | tuple| ExclusiveLock| 20:06:13 | ទំនាក់ទំនង | ShareUpdateExclusiveLock| 17:58:47 | វត្ថុ | RowExclusiveLock| 01:40:41 | លេខប្រតិបត្តិការ| ShareLock| 00:26:41 | ពង្រីក| ExclusiveLock| 00:00:01 ------------------------------+----------------------- --------+----------

ព័ត៌មានលម្អិតអំពីសំណើសំណួរជាក់លាក់

កំពុងរង់ចាំការចាក់សោដោយ LOCKTYPES ដោយការស្វែងរក

សំណើ

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

ឧទាហរណ៍:

| រង់ចាំការចាក់សោរដោយ LOCKTYPES ដោយការសាកសួរ +----------+----------------+--------------------- --------------------------------+------------------ -+--------------------+----------------- --------- | pid| ប្រភេទចាក់សោ| របៀប| បានចាប់ផ្តើម| សំណួរ | blocking_pids| រយៈពេល ----------+--------------------------------+----------- --------+--------------------------------+--------- ----------+--------------------------------+---------------- ---| ១១២៨៨| លេខប្រតិបត្តិការ| ShareLock| 11288-2019-09 17:10:00| 00.302936| {389015618226997618}| 11092:00:03 | ១១៦២៦| លេខប្រតិបត្តិការ| ShareLock| 34-11626-2019 09:17:10| 00| {21.380921}| 389015618226997618:12380:00 | ១១៦២៦| លេខប្រតិបត្តិការ| ShareLock| 00-29-11626 2019:09:17| 10| {00}| 21.380921:389015618226997618:11092 | ១១៦២៦| លេខប្រតិបត្តិការ| ShareLock| 00-03-25 11626:2019:09| 17| {10}| 00:21.380921:389015618226997618 | ១១៦២៦| លេខប្រតិបត្តិការ| ShareLock| 12213-00-01 55:11626:2019| 09| {17}| 10:00:21.380921 | ១១៦២៩| លេខប្រតិបត្តិការ| ShareLock| 389015618226997618-12751-00 00:01:11629| 2019| {09}| 17:10:00 | ១១៦២៩| លេខប្រតិបត្តិការ| ShareLock| 24.331935-389015618226997618-11092 00:03:22| 11629| {2019}| 09:17:10 | 00| លេខប្រតិបត្តិការ| ShareLock| 24.331935-389015618226997618-12007 00:00:01| 12007| {2019}| 09:17:10 | 05| លេខប្រតិបត្តិការ| ShareLock| 03.327933-389015618226997618-11629 00:00:13| 12007| {2019}| 09:17:10 | 05| លេខប្រតិបត្តិការ| ShareLock| 03.327933-389015618226997618-11092 00:01:10| 12007| {2019}| 09:17:10 | 05| លេខប្រតិបត្តិការ| ShareLock| 03.327933-389015618226997618-11288 00:00:05| 12213| {2019}| 09:17:10

ចាប់យកសោតាមប្រភេទចាក់សោដោយសំណួរ

សំណើ

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

ឧទាហរណ៍:

| យកសោតាមប្រភេទចាក់សោតាមសំណួរ +----------+----------+------ -------------+ --------------------------------+--------------------+ --------------------+------------------ -------- | pid| ប្រភេទចាក់សោ| របៀប| បានចាប់ផ្តើម| សំណួរ | blocking_pids| រយៈពេល ----------+--------------------------------+----------- --------+--------------------------------+--------- ----------+--------------------------------+---------------- ---| ១១២៨៨| ទំនាក់ទំនង | RowExclusiveLock| 11288-2019-09 17:10:00| 00.302936| {389015618226997618}| 11092:00:03 | ១១០៩២| លេខប្រតិបត្តិការ| ExclusiveLock| 34-11092-2019 09:17:10| 00| {}| 00.302936:389015618226997618:00 | ១១២៨៨| ទំនាក់ទំនង | RowExclusiveLock| 03-34-11288 2019:09:17| 10| {}| 00:00.302936:389015618226997618 | ១១០៩២| ទំនាក់ទំនង | RowExclusiveLock| 00-00-10 11092:2019:09| 17| {}| 10:00:00.302936 | ១១០៩២| និម្មិត | ExclusiveLock| 389015618226997618-00-03 34:11092:2019| 09| {}| 17:10:00 | ១១២៨៨| និម្មិត | ExclusiveLock| 00.302936-389015618226997618-00 03:34:11288| 2019| {09}| 17:10:00 | ១១២៨៨| លេខប្រតិបត្តិការ| ExclusiveLock| 00.302936-389015618226997618-11092 00:03:34| 11288| {2019}| 09:17:10 | ១១២៨៨| tuple| AccessExclusiveLock| 00-00.302936-389015618226997618 11092:00:03| 34| {11288}| 2019:09:17

ការប្រើប្រវត្តិចាក់សោដើម្បីវិភាគឧប្បត្តិហេតុនៃការអនុវត្ត។

សំណើជាមួយ queryid=389015618226997618 ត្រូវបានប្រតិបត្តិដោយដំណើរការជាមួយ pid=11288 កំពុងរង់ចាំការទប់ស្កាត់ដែលចាប់ផ្តើមពី 2019-09-17 10:00:00 សម្រាប់រយៈពេល 3 នាទី។
ការចាក់សោត្រូវបានគ្រប់គ្រងដោយដំណើរការជាមួយ pid=11092
ដំណើរការជាមួយ pid=11092 ប្រតិបត្តិសំណើជាមួយ queryid=389015618226997618 ចាប់ផ្តើមពី 2019-09-17 10:00:00 ចាក់សោរយៈពេល 3 នាទី។

លទ្ធផល

ឥឡូវនេះខ្ញុំសង្ឃឹមថាផ្នែកដែលគួរឱ្យចាប់អារម្មណ៍និងមានប្រយោជន៍បំផុតនឹងចាប់ផ្តើម - ការប្រមូលស្ថិតិនិងការវិភាគករណីលើប្រវត្តិនៃការរង់ចាំនិងការទប់ស្កាត់។

នៅពេលអនាគត ខ្ញុំចង់ជឿថា យើងនឹងទទួលបានកំណត់ចំណាំមួយចំនួន (ស្រដៀងទៅនឹង metalink របស់ Oracle)។

ជាទូទៅ វាគឺសម្រាប់ហេតុផលនេះ ដែលវិធីសាស្រ្តដែលបានប្រើត្រូវបានធ្វើឱ្យមានដល់មនុស្សគ្រប់គ្នាឱ្យបានលឿនតាមដែលអាចធ្វើទៅបាន។

ខ្ញុំនឹងព្យាយាមបង្ហោះគម្រោងនេះនៅលើ github នាពេលខាងមុខនេះ។

ប្រភព: www.habr.com

វិធីសាស្រ្តមួយសម្រាប់ការទទួលបានប្រវត្តិចាក់សោនៅក្នុង PostgreSQL

បញ្ចូល​ទិន្នន័យ

ប្រវត្តិនៃការតំណាង pg_locks

លទ្ធផល

កំពុងរង់ចាំការចាក់សោដោយប្រភេទចាក់សោ

ការចាប់យកសោតាមប្រភេទសោ

កំពុងរង់ចាំការចាក់សោដោយ LOCKTYPES ដោយការស្វែងរក

ចាប់យកសោតាមប្រភេទចាក់សោដោយសំណួរ

លទ្ធផល

បន្ថែមមតិយោបល់ ответОтменить

បញ្ចូលទិន្នន័យ