Geekly articles weekly

Salah satu cara untuk mendapatkan riwayat kunci di PostgreSQL

Kelanjutan artikel " Mencoba membuat analog ASH untuk PostgreSQL ".

Artikel akan diperiksa dan ditampilkan pada pertanyaan dan contoh spesifik - informasi apa yang berguna yang dapat diperoleh dengan menggunakan riwayat tampilan pg_locks.
Peringatan
Karena kebaruan topik dan periode pengujian yang tidak lengkap, artikel mungkin mengandung kesalahan. Kritik dan komentar sangat didorong dan diharapkan.

Masukkan data


Pg_locks riwayat pengiriman


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

Bahkan, tabel ini mirip dengan tabel archive_pg_stat_activity dijelaskan lebih terinci di sini - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? dan di sini - Upaya untuk membuat analog ASH untuk PostgreSQL.

Untuk mengisi kolom queryid, gunakan fungsinya

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

Penjelasan: nilai kolom kueriid diperbarui di tabel history_locking, dan kemudian ketika membuat bagian baru untuk tabel archive_locking, nilai akan disimpan dalam nilai historis.

Jejak


Informasi umum tentang proses secara keseluruhan.

MENUNGGU LOCKS OLEH LOCKTYPES


Minta
 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

Contoh
  |  MENUNGGU LOCKS OLEH LOCKTYPES
 + -------------------- + ---------------------------- - + --------------------
 |  jenis kunci |  mode |  durasi
 + -------------------- + ---------------------------- - + --------------------
 |  transactionid |  Sharelock |  19:39:26
 |  tuple |  AccessExclusiveLock |  00:03:35
 + -------------------- + ---------------------------- - + --------------------

MENGAMBIL KUNCI OLEH LOCKTYPES


Minta
 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

Contoh
  |  MENGAMBIL KUNCI OLEH LOCKTYPES
 + -------------------- + ---------------------------- - + --------------------
 |  jenis kunci |  mode |  durasi
 + -------------------- + ---------------------------- - + --------------------
 |  hubungan |  RowExclusiveLock |  51:11:10
 |  virtualxid |  ExclusiveLock |  48:10:43
 |  transactionid |  ExclusiveLock |  44:24:53
 |  hubungan |  AccessShareLock |  20:06:13
 |  tuple |  AccessExclusiveLock |  17:58:47
 |  tuple |  ExclusiveLock |  01:40:41
 |  hubungan |  ShareUpdateExclusiveLock |  00:26:41
 |  objek |  RowExclusiveLock |  00:00:01
 |  transactionid |  Sharelock |  00:00:01
 |  memperpanjang |  ExclusiveLock |  00:00:01
 + -------------------- + ---------------------------- - + --------------------

Informasi terperinci tentang permintaan kueri tertentu

MENUNGGU KUNCI OLEH LOCKTYPES OLEH QUERYID


Minta
 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

Contoh
  |  MENUNGGU KUNCI OLEH LOCKTYPES OLEH QUERYID
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  pid |  jenis kunci |  mode |  mulai |  queryid |  blocking_pids |  durasi
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  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

MENGAMBIL LOCKS DENGAN LOCKTYPES BY QUERYID


Minta
 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

Contoh
  |  MENGAMBIL LOCKS DENGAN LOCKTYPES OLEH QUERYID
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  pid |  jenis kunci |  mode |  mulai |  queryid |  blocking_pids |  durasi
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ -
 |  11288 |  hubungan |  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 |  hubungan |  RowExclusiveLock |  2019-09-17 10: 00: 00.302936 |  389015618226997618 |  {} |  00:00:10
 |  11092 |  hubungan |  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

Menggunakan riwayat kunci saat menganalisis insiden kinerja.

  1. Permintaan dengan queryid = 389015618226997618 dieksekusi oleh sebuah proses dengan pid = 11288 diharapkan memblokir dari 2019-09-17 10:00:00 selama 3 menit.
  2. Kunci dipegang oleh suatu proses dengan pid = 11092
  3. Sebuah proses dengan pid = 11092 mengeksekusi kueri dengan queryid = 389015618226997618 mulai dari 2019-09-17 10:00:00 memegang kunci selama 3 menit.

Ringkasan


Sekarang, saya harap, hal yang paling menarik dan berguna dimulai - mengumpulkan statistik dan menganalisis kasus-kasus tentang sejarah harapan dan kunci.

Di masa depan, saya ingin percaya, itu akan berubah menjadi satu set semacam catatan (mirip dengan Oracle metalink).

Secara umum, untuk alasan inilah teknik yang digunakan ditampilkan secepat mungkin untuk pengenalan umum.

Dalam waktu dekat saya akan mencoba menempatkan proyek di github.

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

More articles:


All Articles