Continuazione dell'articolo ««.
In questo articolo verrà esaminato e mostrato, attraverso query specifiche ed esempi, quale utile informazione si può ottenere grazie alla cronologia della vista pg_locks.
Attenzione.
A causa della novità dell'argomento e del periodo di test incompleto, l'articolo potrebbe contenere errori. Critiche e osservazioni sono benvenute e attese.
Dati di input
Cronologia della vista 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
);In sostanza, la tabella è analoga alla tabella archive_pg_stat_activity, descritta più dettagliatamente qui — e qui —
Per popolare la colonna queryid si utilizza la funzione
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 'L'ora attuale è inferiore a 5 minuti.';
start_period = date_trunc('hour',now()) + (current_hour_diff * interval '1 hour');
finish_period = start_period - interval '5 minutes' ;
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;Spiegazione: il valore della colonna queryid viene aggiornato nella tabella history_locking e successivamente, quando viene creata una nuova sezione per la tabella archive_locking, il valore verrà salvato nei valori storici.
Output
Informazioni generali sui processi nel loro complesso.
IN ATTESA DI LOCK PER TIPI DI LOCK
Richiesta
CON
t COME
(
SELEZIONA
locktype ,
mode ,
count(*) as total
DA
activity_hist.archive_locking
DOVE
timepoint tra pg_stat_history_begin+(current_hour_diff * interval '1 ora') E pg_stat_history_end+(current_hour_diff * interval '1 ora') E
NON granted
RAGGRUPPA PER
locktype ,
mode
)
SELEZIONA
locktype ,
mode ,
total * interval '1 secondo' come durata
DA t
ORDINA PER 3 DESC Esempio
| IN ATTESA DI LOCK PER TIPI DI LOCK +--------------------+------------------------------+-------------------- | locktype| mode| durata +--------------------+------------------------------+-------------------- | transactionid| ShareLock| 19:39:26 | tuple| AccessExclusiveLock| 00:03:35 +--------------------+------------------------------+--------------------
PRELIEVI DI LOCK PER TIPI DI LOCK
Richiesta
CON
t COME
(
SELEZIONA
locktype ,
mode ,
count(*) as total
DA
activity_hist.archive_locking
DOVE
timepoint tra pg_stat_history_begin+(current_hour_diff * interval '1 ora') E pg_stat_history_end+(current_hour_diff * interval '1 ora') E
granted
RAGGRUPPA PER
locktype ,
mode
)
SELEZIONA
locktype ,
mode ,
total * interval '1 secondo' come durata
DA t
ORDINA PER 3 DESC Esempio
| PRESA DE LOCK DA LOCKTYPE +--------------------+------------------------------+-------------------- | locktype| mode| durata +--------------------+------------------------------+-------------------- | relation| RowExclusiveLock| 51:11:10 | virtualxid| ExclusiveLock| 48:10:43 | transactionid| ExclusiveLock| 44:24:53 | relation| AccessShareLock| 20:06:13 | tuple| AccessExclusiveLock| 17:58:47 | tuple| ExclusiveLock| 01:40:41 | relation| ShareUpdateExclusiveLock| 00:26:41 | object| RowExclusiveLock| 00:00:01 | transactionid| ShareLock| 00:00:01 | extend| ExclusiveLock| 00:00:01 +--------------------+------------------------------+--------------------
Informazioni dettagliate sui queryid specifici delle richieste
IN ATTESA DI LOCK DA LOCKTYPE PER QUERYID
Richiesta
CON
lt COME
(
SELEZIONA
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids ,
MIN ( timepoint ) OVER (PARTIZIONE PER pid , locktype ,mode ) as iniziato
DA
activity_hist.archive_locking
DOVE
timepoint tra pg_stat_history_begin+(current_hour_diff * intervallo '1 ora') E
pg_stat_history_end+(current_hour_diff * intervallo '1 ora') E
NON concesso E
queryid È NON NULL
RAGGRUPPA PER
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids
)
SELEZIONA
lt.pid ,
lt.locktype ,
lt.mode ,
lt.iniziato ,
lt.queryid ,
lt.blocking_pids ,
COUNT(*) * intervallo '1 secondo' come durata
DA lt
RAGGRUPPA PER
lt.pid ,
lt.locktype ,
lt.mode ,
lt.iniziato ,
lt.queryid ,
lt.blocking_pids
ORDINA PER 4Esempio
| IN ATTESA DI BLOCCO PER TIPO DI BLOCCO PER ID QUERY
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| started| queryid| blocking_pids| duration
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 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:10PRENDERE LOCK DA LOCKTYPE PER QUERYID
Richiesta
CON
lt COME
(
SELEZIONA
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids ,
MIN ( timepoint ) OVER (PARTITION BY pid , locktype ,mode ) come iniziato
DA
activity_hist.archive_locking
DOVE
timepoint tra pg_stat_history_begin+(current_hour_diff * interval '1 hour') E
pg_stat_history_end+(current_hour_diff * interval '1 hour') E
concesso E
queryid È NON NULL
GROUP BY
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids
)
SELEZIONA
lt.pid ,
lt.locktype ,
lt.mode ,
lt.iniziato ,
lt.queryid ,
lt.blocking_pids ,
COUNT(*) * interval '1 second' come durata
DA lt
GROUP BY
lt.pid ,
lt.locktype ,
lt.mode ,
lt.iniziato ,
lt.queryid ,
lt.blocking_pids
ORDINA PER 4Esempio
| RICERCA LOCKS PER TIPO DI LOCKS PER QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| started| queryid| blocking_pids| duration
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 11288| relation| 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| relation| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10
| 11092| relation| 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:34Utilizzo della cronologia delle blocchi nell'analisi degli incidenti di prestazioni.
- La richiesta con queryid=389015618226997618 eseguita dal processo con pid=11288 ha atteso un blocco a partire dal 2019-09-17 10:00:00 per 3 minuti.
- Il blocco è stato mantenuto dal processo con pid=11092.
- Il processo con pid=11092, eseguendo la richiesta con queryid=389015618226997618 a partire dal 2019-09-17 10:00:00, ha mantenuto il blocco per 3 minuti.
Risultato
Ora, spero, inizia la parte più interessante e utile: la raccolta di statistiche e l'analisi dei casi sulla storia delle attese e dei blocchi.
In prospettiva, voglio credere che si riuscirà a ottenere un insieme di alcune note simili a quelle del metalink di Oracle.
In effetti, proprio per questo motivo la metodologia utilizzata viene presentata il più rapidamente possibile per la conoscenza generale.
Cercherò di pubblicare il progetto su github nel più breve tempo possibile.
Fonte: habr.com
