作者
digoal
日期
2021-02-07
标签
PostgreSQL , pg_locks , 等待开始时间
背景
PostgreSQL 14 将增加pg_locks视图wait_start字段, 用于查询锁等待的开始时间, 便于排查等待耗时和先后顺序.
https://www.postgresql.org/message-id/flat/a96013dc51cdc56b2a2b84fa8a16a993@oss.nttdata.com
```
adding wait_start column to pg_locks
Lists: pgsql-hackers
From: torikoshia
To: Pgsql Hackers
Subject: adding wait_start column to pg_locks
Date: 2020-12-15 02:47:23
Message-ID: a96013dc51cdc56b2a2b84fa8a16a993@oss.nttdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers
Hi,
When examining the duration of locks, we often do join on pg_locks
and pg_stat_activity and use columns such as query_start or
state_change.
However, since these columns are the moment when queries have
started or their state has changed, we cannot get the exact lock
duration in this way.
So I'm now thinking about adding a new column in pg_locks which
keeps the time at which locks started waiting.
One problem with this idea would be the performance impact of
calling gettimeofday repeatedly.
To avoid it, I reused the result of the gettimeofday which was
called for deadlock_timeout timer start as suggested in the
previous discussion[1].
Attached a patch.
BTW in this patch, for fast path locks, wait_start is set to
zero because it seems the lock will not be waited for.
If my understanding is wrong, I would appreciate it if
someone could point out.
Any thoughts?
[1]
https://www.postgresql.org/message-id/28804.1407907184%40sss.pgh.pa.us
Regards,
--
Atsushi Torikoshi
NTT DATA CORPORATION
```
《PostgreSQL 等待事件 及 等待采样统计 (pg_wait_sampling) 发布新版本 1.1.2》
《PostgreSQL 14 preview - wal接收优化,无需等待startup process replay结束. Unnecessary delay in streaming replication due to replay lag》
《PostgreSQL pro - pg_stat_statements 扩展插件 pgpro_stats - 等待事件统计、采样配置、自动化监控metric配置等》
《PostgreSQL 14 引入WaitLatch(), 避免每次等待时需要 epoll/kqueue 系统调用.》
《PostgreSQL 多副本的等待行为和事务快照, 以及数据可见性》
《PostgreSQL 13 preview - wait event sample - 等待事件统计(插件,hook) - ASH - performance insight》
《PostgreSQL 全库一致性逻辑备份，大表逻辑备份问题以及推荐解决方案 - 锁等待隐患，膨胀隐患》
《powa4 PostreSQL Workload Analyzer - PostgreSQL监控工具、带WEB展示 - 索引推荐,等待事件分析,命中率,配置变更跟踪等》
《PostgreSQL 10 CLogControlLock 等待事件分析与优化 - hint bit, freeze, autovacuum, 风暴》
《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids》
《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》
《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
《PostgreSQL skip locked与CTE妙用 - 解决并发批量更新锁冲突带来的锁等待，提高处理吞吐》
《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) NUMA 架构spin锁等待优化》
《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
《PostgreSQL 10.0 preview 功能增强 - 增加ProcArrayGroupUpdate等待事件》
《PostgreSQL 10.0 preview 功能增强 - 新增数十个IO等待事件监控》
《Oracle log file parallel write 等待事件分析 - PostgreSQL的WAL异曲同工》
《PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)》
《为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题》
《PostgreSQL 9.6 支持等待事件统计了》
《PostgreSQL 锁等待跟踪》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
