Fitohizan'ny lahatsoratra "
Ny lahatsoratra dia handinika sy hampiseho amin'ny fanontaniana manokana sy ohatra izay fampahalalana mahasoa azo amin'ny fampiasana ny tantaran'ny fijerena pg_locks.
Warning.
Noho ny zava-baovao amin'ny lohahevitra sy ny tsy fahafenoan'ny vanim-potoana fitsapana, dia mety misy hadisoana ilay lahatsoratra. Ny fanakianana sy ny fanehoan-kevitra dia raisina ary andrasana.
Ampidiro ny angona
pg_locks tantara fanehoana
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
);
Amin'ny ankapobeny, ny latabatra dia mitovy amin'ny latabatra archive_pg_stat_activityvoafaritra amin'ny antsipiriany eto -
Hameno tsanganana queryid asa no ampiasaina
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;
fanazavana: ny sandan'ny tsanganana queryid dia havaozina ao amin'ny tabilao history_locking, ary rehefa misy fisarahana vaovao noforonina ho an'ny latabatra archive_locking, dia hotehirizina ao anaty sanda ara-tantara ny sanda.
Fivoahana
Fampahalalana ankapobeny momba ny dingana amin'ny ankapobeny.
MIANDRY HIDY AMIN'NY LOCKTYPES
Request
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
ohatra
| MIANDRY HIDY AMIN'NY LOCKTYPES +----------------------------------+----------------------- ----------------------+--------------------- | locktype| fomba| faharetana +--------------------+--------------------- ---+-- ------------------- | transactionid| sharelock| 19:39:26 | tuple| AccessExclusiveLock| 00:03:35 +--------------------+----------------------- -------+--------
Fandraisana HIDY AMIN'NY LOCKTYPES
Request
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
ohatra
| Fandraisana hidin-trano amin'ny alΓ lan'ny LOCKTYPES +----------------------------------+----------------------- ----------------------+--------------------- | locktype| fomba| faharetana +--------------------+--------------------- ---+-- ------------------- | fifandraisana| RowExclusiveLock| 51:11:10 | virtualxid| ExclusiveLock| 48:10:43 | transactionid| ExclusiveLock| 44:24:53 | fifandraisana| AccessShareLock| 20:06:13 | tuple| AccessExclusiveLock| 17:58:47 | tuple| ExclusiveLock| 01:40:41 | fifandraisana| ShareUpdateExclusiveLock| 00:26:41 | object| RowExclusiveLock| 00:00:01 | transactionid| sharelock| 00:00:01 | manitatra| ExclusiveLock| 00:00:01 +--------------------+---------------------- -------+--------
Fampahafantarana amin'ny antsipiriany momba ny fangatahana queryid manokana
MIANDRY HIDY AMIN'NY LOCKTYPES AMIN'NY QUERYID
Request
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
ohatra
| MIANDRY HIDY AMIN'NY LOCKTYPES AMIN'NY QUERYID +----------+-------------------------+------ ---------------+--------------------------------+--- ----------------------------+--------- - --------- | pid| locktype| fomba| nanomboka| queryid| blocking_pids| faharetana +----------+-----------------------+---------- -----------------------+--------------------------------+-------- -------------------------+---------------------+---------------- --- | 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
MANDRAY HIDY AMIN'NY LOCKTYPES AMIN'NY QUERYID
Request
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
ohatra
| MANDRAKIZAY HITONDRA AMIN'NY LOCKTYPES AMIN'NY QUERYID +----------+------------------------+------ --------------+--------------------------------+---- -----------------------+------------ --------- | pid| locktype| fomba| nanomboka| queryid| blocking_pids| faharetana +----------+-----------------------+---------- -----------------------+--------------------------------+-------- ---+--------------------+----------------- --- | 11288| fifandraisana| 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| fifandraisana| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10 | 11092| fifandraisana| 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| tuple| AccessExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
Mampiasa tantara fanakanana rehefa mamakafaka ny zava-nitranga.
- Fanontaniana misy queryid=389015618226997618 notanterahina tamin'ny dingana misy pid=11288 dia niandry hidin-trano nanomboka ny 2019-09-17 10:00:00 nandritra ny 3 minitra.
- Ny hidin-trano dia nohazonina tamin'ny dingana pid=11092
- Ny dingana miaraka amin'ny pid=11092 manatanteraka fanontaniana miaraka amin'ny queryid=389015618226997618 hatramin'ny 2019-09-17 10:00:00 dia nitazona ny hidin-trano nandritra ny 3 minitra.
Ny vokany
Ankehitriny, manantena aho fa hanomboka ny zavatra mahaliana sy mahasoa indrindra - fanangonana antontan'isa sy famakafakana tranga momba ny tantaran'ny andrasana sy fanakanana.
Amin'ny ho avy, tiako ny hino fa hahazo karazana naoty maromaro ianao (mitovy amin'ny metalink Oracle).
Amin'ny ankapobeny, noho izany antony izany no anehoana haingana araka izay azo atao ny fomba fiasa ampiasaina mba hampahafantarana ny besinimaro.
Amin'ny hoavy tsy ho ela dia hiezaka ny hametraka ny tetikasa amin'ny github aho.
Source: www.habr.com