Voortsetting van die artikel "
Die artikel sal ondersoek en wys, met behulp van spesifieke navrae en voorbeelde, watter nuttige inligting verkry kan word deur die geskiedenis van die pg_locks-aansig te gebruik.
Waarskuwing.
As gevolg van die nuutheid van die onderwerp en die onvoltooide toetsperiode, kan die artikel foute bevat. Kritiek en kommentaar word sterk verwelkom en verwag.
Voer data in
Geskiedenis van pg_locks voorstelling
argief_sluiting
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
);
In wese is die tabel soortgelyk aan die tabel argief_pg_stat_aktiwiteit, hier in meer besonderhede beskryf -
Om 'n kolom te vul queryid funksie gebruik word
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;
Verduideliking: die waarde van die queryid-kolom word opgedateer in die history_locking-tabel, en wanneer 'n nuwe partisie vir die archive_locking-tabel geskep word, sal die waarde in die historiese waardes gestoor word.
Uitset
Algemene inligting oor prosesse in die algemeen.
WAG VIR SLUITE DEUR SLOTTIPE
Navraag
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
Voorbeeld
| WAG VIR SLOTE DEUR SLOTTIPE +-----------+------------------------------------------+ ------------------ | slottipe| modus| duur +---------------------+-------------------------------- - --+---------------------------- | transaksieid| DeelSlot| 19:39:26 | tupel| AccessExclusive Lock| 00:03:35 +--------------------+-------------------------------- - -------+----------
OPNAME VAN SLUITE PER SLOTTIPE
Navraag
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
Voorbeeld
| OPNAME VAN SLOTE PER SLOTTIPE +-------------------+------------------------------ ----+------------------------- | slottipe| modus| duur +---------------------+-------------------------------- - --+---------------------------- | verhouding| RowExclusiveLock| 51:11:10 | virtualxid| EksklusieweSlot| 48:10:43 | transaksieid| EksklusieweSlot| 44:24:53 | verhouding| AccessShareLock| 20:06:13 | tupel| AccessExclusive Lock| 17:58:47 | tupel| EksklusieweSlot| 01:40:41 | verhouding| DeelUpdateExclusiveLock| 00:26:41 | voorwerp| RowExclusiveLock| 00:00:01 | transaksieid| DeelSlot| 00:00:01 | verleng| EksklusieweSlot| 00:00:01 +--------------------+----------------------------- - -------+----------
Gedetailleerde inligting oor spesifieke queryid-versoeke
WAG VIR SLUITE DEUR SLOTTIPE DEUR QUERYID
Navraag
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
Voorbeeld
| WAG VIR SLUITE DEUR SLOTTIPE DEUR QUERYID +--------------------------- +----------------------------+--- ------------------ --+--------------------+---------------- ---------- | pid| slottipe| modus| begin| navraag| blocking_pids| duur +----------+--------------------------------+---------------- ----------+-------------------------------------+--------- ----------------------------------+ --- | 11288| transaksieid| DeelSlot| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11626| transaksieid| DeelSlot| 2019-09-17 10:00:21.380921| 389015618226997618| {12380}| 00:00:29 | 11626| transaksieid| DeelSlot| 2019-09-17 10:00:21.380921| 389015618226997618| {11092}| 00:03:25 | 11626| transaksieid| DeelSlot| 2019-09-17 10:00:21.380921| 389015618226997618| {12213}| 00:01:55 | 11626| transaksieid| DeelSlot| 2019-09-17 10:00:21.380921| 389015618226997618| {12751}| 00:00:01 | 11629| transaksieid| DeelSlot| 2019-09-17 10:00:24.331935| 389015618226997618| {11092}| 00:03:22 | 11629| transaksieid| DeelSlot| 2019-09-17 10:00:24.331935| 389015618226997618| {12007}| 00:00:01 | 12007| transaksieid| DeelSlot| 2019-09-17 10:05:03.327933| 389015618226997618| {11629}| 00:00:13 | 12007| transaksieid| DeelSlot| 2019-09-17 10:05:03.327933| 389015618226997618| {11092}| 00:01:10 | 12007| transaksieid| DeelSlot| 2019-09-17 10:05:03.327933| 389015618226997618| {11288}| 00:00:05 | 12213| transaksieid| DeelSlot| 2019-09-17 10:06:07.328019| 389015618226997618| {12007}| 00:00:10
NEEM SLOTE DEUR SLOTTIPE DEUR QUERYID
Navraag
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
Voorbeeld
| NEEM SLOTE DEUR SLOTTIPE DEUR QUERYID +-----------------------------------+ ----------------------------+--------------------+ --------------------+---------------------------- | pid| slottipe| modus| begin| navraag| blocking_pids| duur +----------+--------------------------------+---------------- ----------+-------------------------------------+--------- ----------------------------------+ --- | 11288| verhouding| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11092| transaksieid| EksklusieweSlot| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11288| verhouding| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10 | 11092| verhouding| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11092| virtualxid| EksklusieweSlot| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34 | 11288| virtualxid| EksklusieweSlot| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11288| transaksieid| EksklusieweSlot| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34 | 11288| tupel| AccessExclusive Lock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
Gebruik slotgeskiedenis om prestasie-insidente te ontleed.
- 'n Versoek met queryid=389015618226997618 uitgevoer deur 'n proses met pid=11288 het vir 2019 minute gewag vir blokkering vanaf 09-17-10 00:00:3.
- Die slot is gehou deur 'n proses met pid=11092
- 'n Proses met pid=11092 wat 'n versoek uitvoer met queryid=389015618226997618 vanaf 2019-09-17 10:00:00 het die slot vir 3 minute gehou.
Totale
Nou, hoop ek, sal die interessantste en nuttigste deel begin - die versameling van statistieke en die ontleding van gevalle oor die geskiedenis van wagte en blokkasies.
In die toekoms, wil ek glo, sal ons 'n stel van 'n paar notas kry (soortgelyk aan Oracle se metalink).
Oor die algemeen is dit om hierdie rede dat die metodologie wat gebruik word so vinnig as moontlik aan almal beskikbaar gestel word.
Ek sal probeer om die projek in die nabye toekoms op github te plaas.
Bron: will.com