مضمون جو تسلسل "
مضمون جانچيندو ۽ ڏيکاريندو، مخصوص سوالن ۽ مثالن کي استعمال ڪندي، 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هتي وڌيڪ تفصيل سان بيان ڪيو ويو آهي -
ڪالم ڀرڻ لاءِ سوال فنڪشن استعمال ڪيو ويندو آهي
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;
وضاحت: queryid ڪالمن جي قيمت 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
مثال طور
| لاڪ ٽائپس پاران لاڪ جي انتظار ۾ +------------------------- +------------------------------- ------------------- | locktype| موڊ| مدت +-------------------------------------------- --+---------| transactionid| شيئر لاڪ | 19:39:26 | ٽوپل| AccessExclusiveLock| 00:03:35 +--------------------------------------------------------- -------- +------------
لاڪ ٽائيپ ذريعي لاڪ وٺڻ
عرض ڪيو
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
مثال طور
| لاڪ ٽائيپ ذريعي لاڪ وٺڻ +-------------------------------------------- --------+-------------------------------- | locktype| موڊ| مدت +-------------------------------------------- --+---------| تعلق | RowExclusiveLock| 51:11:10 | virtualxid| ExclusiveLock| 48:10:43 | transactionid| ExclusiveLock| 44:24:53 | تعلق | AccessShareLock| 20:06:13 | ٽوپل| AccessExclusiveLock| 17:58:47 | ٽوپل| ExclusiveLock| 01:40:41 | تعلق | ShareUpdateExclusiveLock| 00:26:41 | اعتراض| RowExclusiveLock| 00:00:01 | transactionid| شيئر لاڪ | 00:00:01 | وڌايو| ExclusiveLock| 00:00:01 +--------------------------------------------------------- -------- +------------
مخصوص سوالن جي درخواستن تي تفصيلي ڄاڻ
QUERYID پاران لاڪ ٽائپس پاران لاڪ لاء انتظار ڪريو
عرض ڪيو
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
مثال طور
| QUERYID پاران لاڪ ٽائپس پاران لاڪ جي انتظار ۾ +--------- +------------ +--------------- +------------------------------------------------------------------------- ------------------------- +------------------------------- | pid| locktype| موڊ| شروع ڪيو | queryid| blocking_pids| عرصو +--------- +------------------------- + --------- +---------------------------- +--------- ------------------------- +----------------------------------------- --- | 11288| transactionid| شيئر لاڪ | 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11626| transactionid| شيئر لاڪ | 2019-09-17 10:00:21.380921| 389015618226997618| {12380}| 00:00:29 | 11626| transactionid| شيئر لاڪ | 2019-09-17 10:00:21.380921| 389015618226997618| {11092}| 00:03:25 | 11626| transactionid| شيئر لاڪ | 2019-09-17 10:00:21.380921| 389015618226997618| {12213}| 00:01:55 | 11626| transactionid| شيئر لاڪ | 2019-09-17 10:00:21.380921| 389015618226997618| {12751}| 00:00:01 | 11629| transactionid| شيئر لاڪ | 2019-09-17 10:00:24.331935| 389015618226997618| {11092}| 00:03:22 | 11629| transactionid| شيئر لاڪ | 2019-09-17 10:00:24.331935| 389015618226997618| {12007}| 00:00:01 | 12007| transactionid| شيئر لاڪ | 2019-09-17 10:05:03.327933| 389015618226997618| {11629} 00:00:13 | 12007| transactionid| شيئر لاڪ | 2019-09-17 10:05:03.327933| 389015618226997618| {11092}| 00:01:10 | 12007| transactionid| شيئر لاڪ | 2019-09-17 10:05:03.327933| 389015618226997618| {11288}| 00:00:05 | 12213| transactionid| شيئر لاڪ | 2019-09-17 10:06:07.328019| 389015618226997618| {12007}| 00:00:10
QUERYID پاران لاڪ ٽائپس ذريعي لاڪ کڻڻ
عرض ڪيو
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| locktype| موڊ| شروع ڪيو | queryid| blocking_pids| عرصو +--------- +------------------------- + --------- +---------------------------- +--------- ------------------------- +----------------------------------------- --- | 11288| تعلق | 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| تعلق | RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10 | 11092| تعلق | 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| ٽوپل| AccessExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
ڪارڪردگي جي واقعن جو تجزيو ڪرڻ لاءِ تالا جي تاريخ استعمال ڪندي.
- queryid=389015618226997618 سان گڏ هڪ درخواست pid=11288 سان عمل ڪندي 2019-09-17 10:00:00 کان 3 منٽن تائين بلاڪ ٿيڻ جي انتظار ۾ هئي.
- تالا هڪ عمل سان منعقد ڪيو ويو pid = 11092
- هڪ عمل pid=11092 سان هڪ درخواست تي عمل ڪندي queryid=389015618226997618 2019-09-17 10:00:00 کان شروع ٿيندي 3 منٽن لاءِ تالا رکيا.
نتيجو
هاڻي، مون کي اميد آهي ته، سڀ کان وڌيڪ دلچسپ ۽ مفيد حصو شروع ٿيندو - انگ اکر گڏ ڪرڻ ۽ انتظار ۽ بلاڪ جي تاريخ تي ڪيسن جو تجزيو.
مستقبل ۾، مان يقين ڪرڻ چاهيان ٿو، اسان ڪجهه نوٽس جو هڪ سيٽ حاصل ڪنداسين (Oracle’s metalink وانگر).
عام طور تي، اهو ئي سبب آهي ته اهو طريقو استعمال ڪيو ويو آهي هر ڪنهن کي جلدي ممڪن طور تي دستياب آهي.
آئون ويجهي مستقبل ۾ گٿب تي پروجيڪٽ پوسٽ ڪرڻ جي ڪوشش ڪندس.
جو ذريعو: www.habr.com