рд▓реЗрдЦрдХреЛ рдирд┐рд░рдиреНрддрд░рддрд╛ "
рд▓реЗрдЦрд▓реЗ рд╡рд┐рд╢реЗрд╖ рдкреНрд░рд╢реНрдирд╣рд░реВ рд░ рдЙрджрд╛рд╣рд░рдгрд╣рд░реВ рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░, 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рдпрд╣рд╛рдБ рдердк рд╡рд┐рд╡рд░рдгрдорд╛ рд╡рд░реНрдгрди рдЧрд░рд┐рдПрдХреЛ рдЫ -
рд╕реНрддрдореНрдн рднрд░реНрди queryid рдкреНрд░рдХрд╛рд░реНрдп рдкреНрд░рдпреЛрдЧ рдЧрд░рд┐рдиреНрдЫ
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 рддрд╛рд▓рд┐рдХрд╛рдорд╛ рдЕрджреНрдпрд╛рд╡рдзрд┐рдХ рд╣реБрдиреНрдЫ, рд░ рддреНрдпрд╕рдкрдЫрд┐ рдЕрднрд┐рд▓реЗрдЦ_рд▓рдХрд┐рдЩ рддрд╛рд▓рд┐рдХрд╛рдХреЛ рд▓рд╛рдЧрд┐ рдирдпрд╛рдБ рд╡рд┐рднрд╛рдЬрди рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрджрд╛, рдорд╛рди рдРрддрд┐рд╣рд╛рд╕рд┐рдХ рдорд╛рдирд╣рд░реВрдорд╛ рднрдгреНрдбрд╛рд░ рдЧрд░рд┐рдиреЗрдЫред
рдирд┐рд░реНрдЧрдд
рд╕рд╛рдорд╛рдиреНрдп рдорд╛ рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реБ рдорд╛ рд╕рд╛рдорд╛рдиреНрдп рдЬрд╛рдирдХрд╛рд░реАред
рд▓рдХрдЯрд╛рдЗрдкрд╣рд░реВрджреНрд╡рд╛рд░рд╛ рд▓рдХрд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ рдкрд░реНрдЦрдБрджреИ
рдЕрдиреБрд░реЛрдз
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| рдореЛрдб | рдЕрд╡рдзрд┐ +-------------------------------------------- --+--------- | рд▓реЗрдирджреЗрди ShareLock | резреп:рейреп:реирем | рдЯрдкрд▓ | 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
рдЙрджрд╛рд╣рд░рдг:
| рд▓рдХрдЯрд╛рдЗрдкрд╣рд░реВрджреНрд╡рд╛рд░рд╛ рддрд╛рд▓рд╛рд╣рд░реВ рд▓рд┐рдиреЗ +----------------------+---------------------- --------+-------------------------------- | locktype| рдореЛрдб | рдЕрд╡рдзрд┐ +-------------------------------------------- --+--------- | рд╕рдореНрдмрдиреНрдз | RowExclusiveLock| релрез:резрез:резреж | virtualxid| ExclusiveLock | рекрео:резреж:рекрей | рд▓реЗрдирджреЗрди ExclusiveLock | рекрек:реирек:релрей | рд╕рдореНрдмрдиреНрдз | AccessShareLock | реиреж:режрем:резрей | рдЯрдкрд▓ | AccessExclusiveLock| резрен:релрео:рекрен | рдЯрдкрд▓ | ExclusiveLock | режрез:рекреж:рекрез | рд╕рдореНрдмрдиреНрдз | ShareUpdateExclusiveLock| 51:11:10 | рд╡рд╕реНрддреБ | RowExclusiveLock| 48:10:43 | рд▓реЗрдирджреЗрди ShareLock | 44:24:53 | рд╡рд┐рд╕реНрддрд╛рд░ | ExclusiveLock | 20:06:13 +-------------------------------------------- --------+---------
рд╡рд┐рд╢рд┐рд╖реНрдЯ queryid рдЕрдиреБрд░реЛрдзрд╣рд░реВрдорд╛ рд╡рд┐рд╕реНрддреГрдд рдЬрд╛рдирдХрд╛рд░реА
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 | рдЕрд╡рдзрд┐ +--------- +---------------------------- +--------- ---------+-----------------------------------------+--------- ------------------------------------------------------------------------------- - | резрезреиреорео | рд▓реЗрдирджреЗрди ShareLock | 11288-2019-09 17:10:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00.302936}| 389015618226997618:11092:00 | резрезремреирем | рд▓реЗрдирджреЗрди ShareLock | 03-34-11626 2019:09:17 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {резреирейреореж}| режреж:режреж:реиреп | резрезремреирем | рд▓реЗрдирджреЗрди ShareLock | 10-00-21.380921 389015618226997618:12380:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00}| 29:11626:2019 | резрезремреирем | рд▓реЗрдирджреЗрди ShareLock | 09-17-10 00:21.380921:389015618226997618 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {резреиреирезрей}| 11092:00:03 | резрезремреирем | рд▓реЗрдирджреЗрди ShareLock | 25-11626-2019 09:17:10 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00}| режреж:режреж:режрез | резрезремреиреп | рд▓реЗрдирджреЗрди ShareLock | 21.380921-389015618226997618-12213 00:01:55 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {11626}| 2019:09:17 | резрезремреиреп | рд▓реЗрдирджреЗрди ShareLock | 10-00-21.380921 389015618226997618:12751:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00} | режреж:режреж:режрез | 01 | рд▓реЗрдирджреЗрди ShareLock | 11629-2019-09 17:10:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {резрезремреиреп}| режреж:режреж:резрей | 24.331935 | рд▓реЗрдирджреЗрди ShareLock | 389015618226997618-11092-00 03:22:11629 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {2019}| 09:17:10 | 00 | рд▓реЗрдирджреЗрди ShareLock | 24.331935-389015618226997618-12007 00:00:01 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {12007}| режреж:режреж:режрел | резреиреирезрей | рд▓реЗрдирджреЗрди ShareLock | 2019-09-17 10:05:03.327933 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {389015618226997618} | режреж:режреж:резреж
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
рдЙрджрд╛рд╣рд░рдг:
| QUERYID рджреНрд╡рд╛рд░рд╛ рд▓рдХрдЯрд╛рдЗрдкрд╣рд░реВрджреНрд╡рд╛рд░рд╛ рддрд╛рд▓рд╛рд╣рд░реВ рд▓рд┐рдБрджреИ +------------+------------+------------------+ ----------------------------------------- +------------ --------------------+---------------------------------- | pid | locktype| рдореЛрдб | рд╕реБрд░реБ рднрдпреЛ | queryid | blocking_pids | рдЕрд╡рдзрд┐ +--------- +---------------------------- +--------- ---------+----------------------------------------- +--------- ------------------------- +-------------------------------- +---------------- --- | резрезреиреорео | рд╕рдореНрдмрдиреНрдз | RowExclusiveLock| 11288-2019-09 17:10:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00.302936}| 389015618226997618:11092:00 | резрезрежрепреи | рд▓реЗрдирджреЗрди ExclusiveLock | 03-34-11092 2019:09:17 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {} | 10:00:00.302936 | резрезреиреорео | рд╕рдореНрдмрдиреНрдз | RowExclusiveLock| 389015618226997618-00-03 34:11288:2019 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {} | 09:17:10 | резрезрежрепреи | рд╕рдореНрдмрдиреНрдз | RowExclusiveLock| 00-00.302936-389015618226997618 00:00:10 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {} | 11092:2019:09 | резрезрежрепреи | virtualxid| ExclusiveLock | 17-10-00 00.302936:389015618226997618:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {} | 03:34:11092 | резрезреиреорео | virtualxid| ExclusiveLock | 2019-09-17 10:00:00.302936 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {389015618226997618}| 00:03:34 | резрезреиреорео | рд▓реЗрдирджреЗрди ExclusiveLock | 11288-2019-09 17:10:00 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {00.302936}| 389015618226997618:11092:00 | резрезреиреорео | рдЯрдкрд▓ | AccessExclusiveLock| 03-34-11288 2019:09:17 | рейреорепрежрезрелремрезреореиреиремрепрепренремрезрео| {10}| режреж:режрей:рейрек
рдкреНрд░рджрд░реНрд╢рди рдШрдЯрдирд╛рд╣рд░реВ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдЧрд░реНрди рд▓рдХ рдЗрддрд┐рд╣рд╛рд╕ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрджреИред
- queryid=389015618226997618 pid=11288 рдХреЛ рд╕рд╛рде рдПрдХ рдкреНрд░рдХреНрд░рд┐рдпрд╛ рджреНрд╡рд╛рд░рд╛ рдирд┐рд╖реНрдкрд╛рджрд┐рдд рднрдПрдХреЛ рдЕрдиреБрд░реЛрдз 2019-09-17 10:00:00 рджреЗрдЦрд┐ 3 рдорд┐рдиреЗрдЯрдХреЛ рд▓рд╛рдЧрд┐ рдЕрд╡рд░реБрджреНрдзрдХреЛ рд▓рд╛рдЧрд┐ рдкрд░реНрдЦрд┐рд░рд╣реЗрдХреЛ рдерд┐рдпреЛред
- рд▓рдХ pid=11092 рдХреЛ рд╕рд╛рде рдПрдХ рдкреНрд░рдХреНрд░рд┐рдпрд╛ рджреНрд╡рд╛рд░рд╛ рд░рд╛рдЦрд┐рдПрдХреЛ рдерд┐рдпреЛ
- pid=11092 queryid=389015618226997618 рдмрд╛рдЯ 2019-09-17 10:00:00 рджреЗрдЦрд┐ рд╕реБрд░реБ рд╣реБрдиреЗ рдЧрд░реА рдЕрдиреБрд░реЛрдз рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рдЧрд░реНрдиреЗ рдкреНрд░рдХреНрд░рд┐рдпрд╛рд▓реЗ 3 рдорд┐рдиреЗрдЯрдХреЛ рд▓рд╛рдЧрд┐ рд▓рдХ рд░рд╛рдЦреНрдпреЛред
рдкрд░рд┐рдгрд╛рдо
рдЕрдм, рдорд▓рд╛рдИ рдЖрд╢рд╛ рдЫ, рд╕рдмреИрднрдиреНрджрд╛ рдЪрд╛рдЦрд▓рд╛рдЧреНрджреЛ рд░ рдЙрдкрдпреЛрдЧреА рднрд╛рдЧ рд╕реБрд░реБ рд╣реБрдиреЗрдЫ - рддрдереНрдпрд╛рдЩреНрдХрд╣рд░реВ рд╕рдЩреНрдХрд▓рди рд░ рдкрд░реНрдЦрд╛рдЗ рд░ рдЕрд╡рд░реЛрдзрд╣рд░реВрдХреЛ рдЗрддрд┐рд╣рд╛рд╕рдорд╛ рдХреЗрд╕рд╣рд░реВрдХреЛ рд╡рд┐рд╢реНрд▓реЗрд╖рдгред
рднрд╡рд┐рд╖реНрдпрдорд╛, рдо рд╡рд┐рд╢реНрд╡рд╛рд╕ рдЧрд░реНрди рдЪрд╛рд╣рдиреНрдЫреБ, рд╣рд╛рдореАрд▓реЗ рдХреЗрд╣рд┐ рдиреЛрдЯрд╣рд░реВрдХреЛ рд╕реЗрдЯ рдкрд╛рдЙрдиреЗрдЫреМрдВ (рдУрд░реЗрдХрд▓рдХреЛ рдореЗрдЯрд╛рд▓рд┐рдВрдХ рдЬрд╕реНрддреИ)ред
рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛, рдпреЛ рдпрд╕ рдХрд╛рд░рдгрдХреЛ рд▓рд╛рдЧрд┐ рд╣реЛ рдХрд┐ рдкреНрд░рдпреЛрдЧ рдЧрд░рд┐рдПрдХреЛ рдкрджреНрдзрддрд┐ рд╕рдмреИрд▓рд╛рдИ рд╕рдХреЗрд╕рдореНрдо рдЪрд╛рдБрдбреЛ рдЙрдкрд▓рдмреНрдз рдЧрд░рд╛рдЗрдиреНрдЫред
рдо рдирд┐рдХрдЯ рднрд╡рд┐рд╖реНрдпрдорд╛ github рдорд╛ рдкрд░рд┐рдпреЛрдЬрдирд╛ рдкреЛрд╕реНрдЯ рдЧрд░реНрдиреЗ рдкреНрд░рдпрд╛рд╕ рдЧрд░реНрдиреЗрдЫреБред
рд╕реНрд░реЛрдд: www.habr.com