Geekly articles weekly

在PostgreSQL中获取锁定历史记录的一种方法

继续文章“ 尝试为PostgreSQL创建ASH的类似物 ”。

本文将进行检查并显示在特定的查询和示例上-使用pg_locks视图的历史记录可以获得哪些有用的信息。
警告
由于该主题的新颖性和不完整的测试期限，因此该文章可能包含错误。 强烈鼓励和期望批评和评论。

输入数据


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

实际上，该表类似于此处更详细描述的archive_pg_stat_activity表-pg_stat_statements + pg_stat_activity + loq_query = pg_ash？ 在这里- 尝试为PostgreSQL创建ASH的类似物。

要填充queryid列，请使用函数

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;

说明： queryid列的值在history_locking表中更新，然后在为archive_locking表创建新节时，该值将保存在历史值中。

印记


有关整个过程的一般信息。

等待按锁类型的锁


索取
 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

例子
  | 等待按锁类型的锁
 + -------------------- + ---------------------------- -+ --------------------
 | 锁类型| 模式 持续时间
 + -------------------- + ---------------------------- -+ --------------------
 |  transactionid | 共享锁|  19:39:26
 | 元组|  AccessExclusiveLock |  00:03:35
 + -------------------- + ---------------------------- -+ --------------------

按锁类型进行锁取


索取
 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

例子
  | 按锁类型进行锁取
 + -------------------- + ---------------------------- -+ --------------------
 | 锁类型| 模式 持续时间
 + -------------------- + ---------------------------- -+ --------------------
 | 关系|  RowExclusiveLock |  51:11:10
 |  virtualxid |  ExclusiveLock |  48:10:43
 |  transactionid |  ExclusiveLock |  44:24:53
 | 关系|  AccessShareLock |  20:06:13
 | 元组|  AccessExclusiveLock |  17:58:47
 | 元组|  ExclusiveLock |  01:40:41
 | 关系|  ShareUpdateExclusiveLock |  00:26:41
 | 对象  RowExclusiveLock |  00:00:01
 |  transactionid | 共享锁|  00:00:01
 | 扩展|  ExclusiveLock |  00:00:01
 + -------------------- + ---------------------------- -+ --------------------

有关特定queryid查询的详细信息

等待按QUERYID按锁类型进行的锁


索取
 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

例子
  | 等待按QUERYID按锁类型进行的锁
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ --
 |  pid | 锁类型| 模式 开始  queryid |  block_pids | 持续时间
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ --
 |  11288 |  transactionid | 共享锁|  2019-09-17 10：00：00.302936 |  389015618226997618 |  {11092} |  00:03:34
 |  11626 |  transactionid | 共享锁|  2019-09-17 10:00：21.380921 |  389015618226997618 |  {12380} |  00:00:29
 |  11626 |  transactionid | 共享锁|  2019-09-17 10：00：21.380921 |  389015618226997618 |  {11092} |  00:03:25
 |  11626 |  transactionid | 共享锁|  2019-09-17 10：00：21.380921 |  389015618226997618 |  {12213} |  00:01:55
 |  11626 |  transactionid | 共享锁|  2019-09-17 10：00：21.380921 |  389015618226997618 |  {12751} |  00:00:01
 |  11629 |  transactionid | 共享锁|  2019-09-17 10：00：24.331935 |  389015618226997618 |  {11092} |  00:03:22
 |  11629 |  transactionid | 共享锁|  2019-09-17 10：00：24.331935 |  389015618226997618 |  {12007} |  00:00:01
 |  12007 |  transactionid | 共享锁|  2019-09-17 10：05：03.327933 |  389015618226997618 |  {11629} |  00:00:13
 |  12007 |  transactionid | 共享锁|  2019-09-17 10：05：03.327933 |  389015618226997618 |  {11092} |  00:01:10
 |  12007 |  transactionid | 共享锁|  2019-09-17 10：05：03.327933 |  389015618226997618 |  {11288} |  00:00:05
 |  12213 |  transactionid | 共享锁|  2019-09-17 10：06：07.328019 |  389015618226997618 |  {12007} |  00:00:10

按QUERYID按锁类型进行锁


索取
 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

例子
  | 按QUERYID的锁类型进行锁
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ --
 |  pid | 锁类型| 模式 开始  queryid |  block_pids | 持续时间
 + ---------- + ------------------------- + ------------ -------- + ------------------------------ + ---------- ---------- + -------------------- + ------------------ --
 |  11288 | 关系|  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 | 关系|  RowExclusiveLock |  2019-09-17 10：00：00.302936 |  389015618226997618 |  {} |  00:00:10
 |  11092 | 关系|  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 | 元组|  AccessExclusiveLock |  2019-09-17 10：00：00.302936 |  389015618226997618 |  {11092} |  00:03:34

分析性能事件时使用锁定历史记录。

  1. pid = 11288的进程执行的queryid = 389015618226997618的请求预计将在2019-09-17 10:00:00阻止3分钟。
  2. 锁由pid = 11092的进程持有
  3. 从2019-09-17 10:00:00开始，具有pid = 11092的进程执行带有queryid = 389015618226997618的查询的过程将锁定保持3分钟。

总结


现在，我希望最有趣和有用的事情开始了-收集统计数据并分析有关期望和锁定历史的案例。

我想相信，将来，它将成为某种形式的笔记（类似于Oracle metalink）。

通常，由于这个原因，所使用的技术会尽可能快地显示出来，以进行一般性的熟悉。

在不久的将来，我将尝试将该项目放在github上。

Source: https://habr.com/ru/post/zh-CN467719/

More articles:


All Articles