第99期吐槽:SQL hang住锁阻塞性能暴跌!抓不到捣蛋SQL的DBA很尴尬。
文中参考文档点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
第99期吐槽:锁等待场景,无法定位事务内捣蛋SQL
问题: 当一个事务中早期SQL堵塞了未来的其他会话时, 无法定位到事务中早期SQL.
session a:
sqla1 -- blocking session b sqlb2.
sqla2
sqla3 -- last query
session b:
sqlb1
sqlb2 -- wait
假设sqla1堵塞了sqlb2, 如何定位到SQLA1? 目前只能定位到session a, 从PG_stat_activity 只能看到last query ：sqla3.
这个问题对开发调试不友好. 因为无法知道造成堵塞的源头SQL是什么?
PG 提供的一些最常用锁分析手段如下, 通过以下方法, 目前只能定位到session a, 从PG_stat_activity 只能看到last query ：sqla3.
202403/20240304_01.md 《PostgreSQL 锁跟踪分析工具 pg_lock_tracer》
201903/20190304_01.md 《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids》
201902/20190201_02.md 《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》
201806/20180622_02.md 《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
201705/20170521_01.md 《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
解法
如果想了解堵塞sqlb2的sqla1, 需要满足这些前提: 有地方存储了sqla1, 以及其对应的锁信息, 以及sqlb2请求的锁信息.
1、哪里能存储sqla1呢?
SQL审计日志
2、其对应的锁信息呢?
开启trace_locks. 编译前配置 src/include/pg_config_manual.h
Details of the structure being dumped may be found in src/include/storage/lock.h.
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
trace_locks参数使用方法
https://www.postgresql.org/docs/devel/runtime-config-developer.html
trace_locks (boolean)
If on, emit information about lock usage. Information dumped includes the type of lock operation, the type of lock and the unique identifier of the object being locked or unlocked. Also included are bit masks for the lock types already granted on this object as well as for the lock types awaited on this object. For each lock type a count of the number of granted locks and waiting locks is also dumped as well as the totals. An example of the log file output is shown here:
LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(AccessShareLock)
LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
wait(0) type(AccessShareLock)
LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(AccessShareLock)
LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(INVALID)
3、sqlb2请求的sql及锁信息呢?
log_statement审计日志打开, 同时打开trace_locks 或者 使用log_lock_waits来记录等待时间超过某个时间窗口的信息.
#log_lock_waits = off # log lock waits >= deadlock_timeout
#lock_timeout = 0 # in milliseconds, 0 is disabled
4、分析等待, 使用以下方法找到堵塞session, 然后在审计日志中通过session找到对应SQL以及锁信息, 最终定位是事务中哪些SQL堵塞了xxx.
201903/20190304_01.md 《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids》
201902/20190201_02.md 《PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》
201806/20180622_02.md 《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
201705/20170521_01.md 《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
小结
这种开启trace_locks和sql审计日志的方法, 不适合高qps的业务. 会带来较大性能影响.
有兴趣可以看看海信聚好看dbdoctor产品, 该产品使用ebpf采样, 对数据库高并发业务性能影响较小.
本期彩蛋-招商中,有需要的小伙伴可联系嵌入...
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: