Fortsetzung des Artikels „«.
Der Artikel wird anhand konkreter Anfragen und Beispiele aufzeigen, welche nützlichen Informationen mit der Darstellungsgeschichte von pg_locks gewonnen werden können.
Warnung.
Aufgrund der Neuheit des Themas und des unvollendeten Testzeitraums kann der Artikel Fehler enthalten. Kritik und Anmerkungen sind jederzeit willkommen und werden erwartet.
Eingabedaten
Darstellungsgeschichte von pg_locks
archive_locking
CREATE TABLE archive_locking
( timepoint timestamp ohne Zeitzone ,
locktype text ,
relation oid ,
mode text ,
tid xid ,
vtid text ,
pid integer ,
blocking_pids integer[] ,
granted boolean ,
queryid bigint
);Im Grunde ist die Tabelle analog zur Tabelle archive_pg_stat_activity, die hier ausführlicher beschrieben wird — und hier —
Zur Befüllung der Spalte queryid wird die Funktion
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 ohne Zeitzone ;
finish_period timestamp ohne Zeitzone ;
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 'Aktuelle Zeit beträgt weniger als 5 Minuten.';
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;Erläuterung: Der Wert der queryid-Spalte wird in der Tabelle history_locking aktualisiert, und anschließend wird beim Erstellen eines neuen Abschnitts für die Tabelle archive_locking der Wert in den historischen Werten gespeichert.
Ausgabe
Allgemeine Informationen zu den Prozessen insgesamt.
WARTEN AUF LOCKS NACH LOCKTYPEN
Abfrage
MIT
t ALS
(
SELECT
locktype ,
mode ,
count(*) as total
FROM
activity_hist.archive_locking
WHERE
timepoint zwischen pg_stat_history_begin+(current_hour_diff * interval '1 hour') UND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
NICHT gewährt
GROUP BY
locktype ,
mode
)
SELECT
locktype ,
mode ,
total * interval '1 second' as duration
FROM t
ORDER BY 3 DESC Beispiel
| WARTEN AUF LOCKS NACH LOCKTYPEN +--------------------+------------------------------+-------------------- | locktype| mode| duration +--------------------+------------------------------+-------------------- | transactionid| ShareLock| 19:39:26 | tuple| AccessExclusiveLock| 00:03:35 +--------------------+------------------------------+--------------------
NIMMUNGEN VON LOCKS NACH LOCKTYPEN
Abfrage
MIT
t ALS
(
SELECT
locktype ,
mode ,
count(*) as total
FROM
activity_hist.archive_locking
WHERE
timepoint zwischen pg_stat_history_begin+(current_hour_diff * interval '1 hour') UND
pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
gewährt
GROUP BY
locktype ,
mode
)
SELECT
locktype ,
mode ,
total * interval '1 second' as duration
FROM t
ORDER BY 3 DESC Beispiel
| NIMMUNGEN VON LOCKS NACH LOCKTYPEN +--------------------+------------------------------+-------------------- | locktype| mode| duration +--------------------+------------------------------+-------------------- | 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 +--------------------+------------------------------+--------------------
Detaillierte Informationen zu spezifischen queryid-Anfragen
WARTEN AUF LOCKS NACH LOCKTYPEN NACH QUERYID
Abfrage
MIT
lt ALS
(
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 zwischen pg_stat_history_begin+(current_hour_diff * interval '1 hour') UND
pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
NICHT gewährt UND
queryid IST NICHT 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 4Beispiel
| WARTEN AUF LOCKS NACH LOCKARTEN NACH QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| started| queryid| blocking_pids| dauer
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 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:10SCHLIESSEN VON LOCKS NACH LOCKARTEN NACH QUERYID
Abfrage
MIT
lt ALS
(
SELECT
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids ,
MIN ( timepoint ) ÜBER (PARTITION BY pid , locktype ,mode ) als started
FROM
activity_hist.archive_locking
WHERE
timepoint zwischen pg_stat_history_begin+(current_hour_diff * interval '1 hour') UND
pg_stat_history_end+(current_hour_diff * interval '1 hour') UND
gewährt UND
queryid IST NICHT 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' als dauer
FROM lt
GROUP BY
lt.pid ,
lt.locktype ,
lt.mode ,
lt.started ,
lt.queryid ,
lt.blocking_pids
ORDER BY 4Beispiel
| LOCKS NACH LOCKTYPE UND QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| gestartet| queryid| blocking_pids| dauer
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 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:34Nutzung der Lock-Geschichte bei der Analyse von Leistungsereignissen.
- Der Prozess mit queryid=389015618226997618 und pid=11288 wartete seit dem 17.09.2019 um 10:00:00 für eine Sperre und zwar für 3 Minuten.
- Die Sperre wurde vom Prozess mit pid=11092 gehalten.
- Der Prozess mit pid=11092 hielt die Sperre seit dem 17.09.2019 um 10:00:00 für 3 Minuten, während er die Anfrage mit queryid=389015618226997618 ausführte.
Fazit
Jetzt, hoffe ich, beginnt das spannendste und nützlichste: die Sammlung von Statistiken und die Analyse von Fällen bezüglich der Geschichte von Wartezeiten und Sperren.
In der Zukunft hoffe ich, ein Set von Notizen zu erstellen (ähnlich wie bei den Metadaten von Oracle).
Genau aus diesem Grund benutzen wir die Methodik, die so schnell wie möglich für die Allgemeinheit ausgegeben wird.
In naher Zukunft werde ich versuchen, das Projekt auf GitHub zu veröffentlichen.
Quelle: habr.com
