Pagpadayon sa artikulo "
Susihon ug ipakita sa artikulo, gamit ang piho nga mga pangutana ug pananglitan, kung unsang mapuslanon nga kasayuran ang makuha gamit ang kasaysayan sa pagtan-aw sa pg_locks.
Pasidaan
Tungod sa kabag-ohan sa hilisgutan ug sa wala pa nahuman nga panahon sa pagsulay, ang artikulo mahimong adunay mga sayup. Ang pagsaway ug mga komento kusganong gidawat ug gipaabot.
Input data
Kasaysayan sa representasyon sa 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
);
Sa tinuud, ang lamesa parehas sa lamesa archive_pg_stat_activity, gihulagway sa mas detalyado dinhi -
Aron pun-on ang usa ka kolum pangutana gigamit ang function
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;
Pagpatin-aw: ang bili sa kolum sa queryid gi-update sa history_locking table, ug unya sa diha nga ang usa ka bag-ong partition gihimo para sa archive_locking table, ang bili itago sa historical values.
Paggawas
Kinatibuk-ang impormasyon sa mga proseso sa kinatibuk-an.
NAGHULAT SA MGA LOCK PINAAGI SA MGA LOCKTYPES
Paghangyo
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
Pananglitan:
| NAGHULAT SA MGA LOCKS BY LOCKTYPES +------------+---------- ---------+ ------------------- | locktype| mode| gidugayon +---------------------+-------------------------------------- - ----------------------------------- | transactionid| ShareLock| 19:39:26 | tupol| AccessExclusiveLock| 00:03:35 +----------------------+----------------------- - -------+----------
PAGKUHA SA MGA LOCK PINAAGI SA LOCKTYPES
Paghangyo
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
Pananglitan:
| PAGKUHA SA MGA LOCKS PINAAGI SA LOCKTYPES +----------------------------------------------- --------------------------------------------- | locktype| mode| gidugayon +---------------------+-------------------------------------- - ----------------------------------- | relasyon| RowExclusiveLock| 51:11:10 | virtualxid| ExclusiveLock| 48:10:43 | transactionid| ExclusiveLock| 44:24:53 | relasyon| AccessShareLock| 20:06:13 | tupol| AccessExclusiveLock| 17:58:47 | tupol| ExclusiveLock| 01:40:41 | relasyon| ShareUpdateExclusiveLock| 00:26:41 | butang| RowExclusiveLock| 00:00:01 | transactionid| ShareLock| 00:00:01 | ipalawig| ExclusiveLock| 00:00:01 +----------------------+---------------------- - -------+----------
Detalyadong impormasyon sa piho nga mga hangyo sa queryid
NAGHULAT SA MGA LOCK PINAAGI SA MGA LOCKTYPES NI QUERYID
Paghangyo
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
Pananglitan:
| NAGHULAT SA MGA LOCK PINAAGI SA MGA LOCKTYPES NI QUERYID +----------+----------+----- --------------- +----------------------------+-------------------- -----------------------+----------- --------- | pid| locktype| mode| nagsugod| queryid| blocking_pids| gidugayon +-----------+------------------------+----------- ----------+--------------------------------+--------- --------------------------+---------------------+----------------- --- | 11288| transactionid| ShareLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12380}| 00:00:29 | 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {11092}| 00:03:25 | 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12213}| 00:01:55 | 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12751}| 00:00:01 | 11629| transactionid| ShareLock| 2019-09-17 10:00:24.331935| 389015618226997618| {11092}| 00:03:22 | 11629| transactionid| ShareLock| 2019-09-17 10:00:24.331935| 389015618226997618| {12007}| 00:00:01 | 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11629}| 00:00:13 | 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11092}| 00:01:10 | 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11288}| 00:00:05 | 12213| transactionid| ShareLock| 2019-09-17 10:06:07.328019| 389015618226997618| {12007}| 00:00:10
PAGKUHA SA MGA LOCK PINAAGI SA LOCKTYPES PINAAGI SA QUERYID
Paghangyo
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
Pananglitan:
| PAGKUHA SA MGA LOCK PINAAGI SA LOCKTYPES PINAAGI SA QUERYID +----------+-----------+------ --------------+ ------------------------------------- ---------------------------------- --------- | pid| locktype| mode| nagsugod| queryid| blocking_pids| gidugayon +-----------+------------------------+----------- ----------+--------------------------------+--------- --------------------------+---------------------+----------------- --- | 11288| relasyon| 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| relasyon| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10 | 11092| relasyon| 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| tupol| AccessExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
Paggamit sa kasaysayan sa lock aron pag-analisar sa mga insidente sa pasundayag.
- Usa ka hangyo nga adunay queryid=389015618226997618 nga gipatuman sa usa ka proseso nga adunay pid=11288 naghulat alang sa pag-block sugod sa 2019-09-17 10:00:00 sa 3 minuto.
- Ang kandado gihuptan sa usa ka proseso nga adunay pid=11092
- Usa ka proseso nga adunay pid=11092 nga nagpatuman sa usa ka hangyo nga adunay queryid=389015618226997618 sugod sa 2019-09-17 10:00:00 naggunit sa lock sa 3 minuto.
Ang resulta
Karon, nanghinaut ko, magsugod ang labing makapaikag ug mapuslanon nga bahin - pagkolekta sa mga estadistika ug pag-analisar sa mga kaso sa kasaysayan sa paghulat ug pag-block.
Sa umaabot, gusto ko nga motuo, makakuha kami usa ka set sa pipila nga mga nota (sama sa metalink sa Oracle).
Sa kinatibuk-an, tungod niini nga hinungdan nga ang pamaagi nga gigamit magamit sa tanan sa labing madali nga panahon.
Akong sulayan nga i-post ang proyekto sa github sa umaabot nga umaabot.
Source: www.habr.com