Geekly articles weekly

Eine Möglichkeit, den Sperrverlauf in PostgreSQL abzurufen

Fortsetzung des Artikels " Versuch, ein Analogon von ASH für PostgreSQL zu erstellen ".

Der Artikel wird anhand spezifischer Abfragen und Beispiele untersucht und angezeigt. Welche nützlichen Informationen können mithilfe des Verlaufs der Ansicht pg_locks abgerufen werden?
Warnung
Aufgrund der Neuheit des Themas und des unvollständigen Testzeitraums kann der Artikel Fehler enthalten. Kritik und Kommentare werden nachdrücklich ermutigt und erwartet.

Daten eingeben


Pg_locks Übermittlungsverlauf


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 );

Tatsächlich ähnelt die Tabelle der hier ausführlicher beschriebenen Tabelle archive_pg_stat_activity - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? und hier - Ein Versuch, ein Analogon von ASH für PostgreSQL zu erstellen.

Verwenden Sie die Funktion, um die Abfrage-ID- Spalte zu füllen

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;

Erläuterung: Der Wert der Spalte queryid wird in der Tabelle history_locking aktualisiert. Wenn Sie dann einen neuen Abschnitt für die Tabelle archive_locking erstellen, wird der Wert in historischen Werten gespeichert.

Impressum


Allgemeine Informationen zu den gesamten Prozessen.

WARTEN AUF SCHLÖSSER NACH LOCKTYPEN


Anfrage
 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

Beispiel
  |  WARTEN AUF SCHLÖSSER NACH LOCKTYPEN
 + -------------------- + ---------------------------- - + --------------------
 |  locktype |  Modus |  Dauer
 + -------------------- + ---------------------------- - + --------------------
 |  Transaktions-ID |  Sharelock |  19:39:26
 |  Tupel |  AccessExclusiveLock |  00:03:35
 + -------------------- + ---------------------------- - + --------------------

ENTNAHMEN VON SCHLÖSSERN NACH LOCKTYPEN


Anfrage
 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

Beispiel
  |  ENTNAHMEN VON SCHLÖSSERN NACH LOCKTYPEN
 + -------------------- + ---------------------------- - + --------------------
 |  locktype |  Modus |  Dauer
 + -------------------- + ---------------------------- - + --------------------
 |  Beziehung |  RowExclusiveLock |  51:11:10
 |  virtualxid |  ExclusiveLock |  48:10:43
 |  Transaktions-ID |  ExclusiveLock |  44:24:53
 |  Beziehung |  AccessShareLock |  20:06:13
 |  Tupel |  AccessExclusiveLock |  17:58:47
 |  Tupel |  ExclusiveLock |  01:40:41
 |  Beziehung |  ShareUpdateExclusiveLock |  00:26:41
 |  Objekt |  RowExclusiveLock |  00:00:01
 |  Transaktions-ID |  Sharelock |  00:00:01
 |  verlängern |  ExclusiveLock |  00:00:01
 + -------------------- + ---------------------------- - + --------------------

Detaillierte Informationen zu bestimmten Abfragen von Abfrage-IDs

WARTEN AUF SCHLÖSSER NACH LOCKTYPEN NACH QUERYID


Anfrage
 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

Beispiel
  |  WARTEN AUF SCHLÖSSER NACH LOCKTYPEN NACH QUERYID
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ - -
 |  pid |  locktype |  Modus |  gestartet |  queryid |  blockierende_pids |  Dauer
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ - -
 |  11288 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {11092} |  00:03:34
 |  11626 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 21.380921 |  389015618226997618 |  {12380} |  00:00:29
 |  11626 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 21.380921 |  389015618226997618 |  {11092} |  00:03:25
 |  11626 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 21.380921 |  389015618226997618 |  {12213} |  00:01:55
 |  11626 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 21.380921 |  389015618226997618 |  {12751} |  00:00:01
 |  11629 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 24.331935 |  389015618226997618 |  {11092} |  00:03:22
 |  11629 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 00: 24.331935 |  389015618226997618 |  {12007} |  00:00:01
 |  12007 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 05: 03.327933 |  389015618226997618 |  {11629} |  00:00:13
 |  12007 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 05: 03.327933 |  389015618226997618 |  {11092} |  00:01:10
 |  12007 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 05: 03.327933 |  389015618226997618 |  {11288} |  00:00:05
 |  12213 |  Transaktions-ID |  Sharelock |  2019-09-17 10: 06: 07.328019 |  389015618226997618 |  {12007} |  00:00:10

LOCKS NACH LOCKTYPEN NACH QUERYID NEHMEN


Anfrage
 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

Beispiel
  |  LOCKS NACH LOCKTYPEN NACH QUERYID NEHMEN
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ - -
 |  pid |  locktype |  Modus |  gestartet |  queryid |  blockierende_pids |  Dauer
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ - -
 |  11288 |  Beziehung |  RowExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {11092} |  00:03:34
 |  11092 |  Transaktions-ID |  ExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {} |  00:03:34
 |  11288 |  Beziehung |  RowExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {} |  00:00:10
 |  11092 |  Beziehung |  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 |  Transaktions-ID |  ExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {11092} |  00:03:34
 |  11288 |  Tupel |  AccessExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {11092} |  00:03:34

Verwenden des Sperrverlaufs bei der Analyse von Leistungsvorfällen.

  1. Eine Anforderung mit queryid = 389015618226997618, die von einem Prozess mit pid = 11288 ausgeführt wird, wird voraussichtlich 3 Minuten lang vom 17.09.2019 um 10:00:00 Uhr blockiert.
  2. Die Sperre wurde durch einen Prozess mit pid = 11092 gehalten
  3. Ein Prozess mit pid = 11092, der eine Abfrage mit queryid = 389015618226997618 ab dem 17.09.2019 um 10:00:00 Uhr ausführte, hielt die Sperre 3 Minuten lang.

Zusammenfassung


Nun hoffe ich, dass das Interessanteste und Nützlichste beginnt - das Sammeln von Statistiken und das Analysieren von Fällen zur Geschichte von Erwartungen und Sperren.

Ich möchte glauben, dass es sich in Zukunft als eine Art Notiz herausstellen wird (ähnlich wie beim Oracle Metalink).

Aus diesem Grund wird aus diesem Grund die verwendete Technik zur allgemeinen Einarbeitung so schnell wie möglich angezeigt.

In naher Zukunft werde ich versuchen, das Projekt auf Github zu bringen.

Source: https://habr.com/ru/post/de467719/

More articles:


All Articles