Geekly articles weekly

Une façon d'obtenir l'historique des verrous dans PostgreSQL

Suite de l'article " Essayer de créer un analogue d'ASH pour PostgreSQL ".

L'article sera examiné et affiché sur des requêtes et des exemples spécifiques - quelles informations utiles peuvent être obtenues en utilisant l'historique de la vue pg_locks.
Avertissement
En raison de la nouveauté du sujet et de la période de test incomplète, l'article peut contenir des erreurs. La critique et les commentaires sont fortement encouragés et attendus.

Entrer les données


Historique des soumissions de 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 );

En fait, la table est similaire à la table archive_pg_stat_activity décrite plus en détail ici - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? et ici - Une tentative de créer un analogue d'ASH pour PostgreSQL.

Pour remplir la colonne queryid, utilisez la fonction

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;

Explication: la valeur de la colonne queryid est mise à jour dans la table history_locking, puis lors de la création d'une nouvelle section pour la table archive_locking, la valeur sera enregistrée dans les valeurs historiques.

Mentions légales


Informations générales sur l'ensemble des processus.

EN ATTENTE DES SERRURES PAR LOCKTYPES


Demande
 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

Exemple
  |  EN ATTENTE DES SERRURES PAR LOCKTYPES
 + -------------------- + ---------------------------- - + --------------------
 |  locktype |  mode |  la durée
 + -------------------- + ---------------------------- - + --------------------
 |  transactionid |  Sharelock |  19:39:26
 |  tuple |  AccessExclusiveLock |  00:03:35
 + -------------------- + ---------------------------- - + --------------------

PRISES DE SERRURES PAR LOCKTYPES


Demande
 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

Exemple
  |  PRISES DE SERRURES PAR LOCKTYPES
 + -------------------- + ---------------------------- - + --------------------
 |  locktype |  mode |  la durée
 + -------------------- + ---------------------------- - + --------------------
 |  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
 |  objet |  RowExclusiveLock |  00:00:01
 |  transactionid |  Sharelock |  00:00:01
 |  étendre |  ExclusiveLock |  00:00:01
 + -------------------- + ---------------------------- - + --------------------

Informations détaillées sur les requêtes spécifiques aux requêtes

EN ATTENTE DES SERRURES PAR LOCKTYPES PAR QUERYID


Demande
 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

Exemple
  |  EN ATTENTE DES SERRURES PAR LOCKTYPES PAR QUERYID
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  pid |  locktype |  mode |  commencé |  queryid |  blocking_pids |  la durée
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  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

PRISE DE SERRURES PAR LOCKTYPES PAR QUERYID


Demande
 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

Exemple
  |  PRISE DE SERRURES PAR LOCKTYPES PAR QUERYID
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  pid |  locktype |  mode |  commencé |  queryid |  blocking_pids |  la durée
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  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:34

Utilisation de l'historique des verrous lors de l'analyse des incidents de performances.

  1. Une demande avec queryid = 389015618226997618 exécutée par un processus avec pid = 11288 blocage attendu du 2019-09-17 10:00:00 pendant 3 minutes.
  2. Le verrou a été maintenu par un processus avec pid = 11092
  3. Un processus avec pid = 11092 exécutant une requête avec queryid = 389015618226997618 à partir du 2019-09-17 10:00:00 a maintenu le verrou pendant 3 minutes.

Résumé


Maintenant, j'espère, la chose la plus intéressante et utile commence - la collecte de statistiques et l'analyse des cas sur l'histoire des attentes et des verrous.

À l'avenir, je veux le croire, il se révélera être un ensemble d'une sorte de note (similaire à Oracle Metalink).

En général, c'est pour cette raison que la technique utilisée est affichée le plus rapidement possible pour une familiarisation générale.

Dans un avenir très proche, je vais essayer de mettre le projet sur github.

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

More articles:


All Articles