Skip to content

Files

Latest commit

Dec 15, 2023
f400df2 · Dec 15, 2023

History

History
94 lines (38 loc) · 3.86 KB

20201110_04.md

File metadata and controls

94 lines (38 loc) · 3.86 KB

PostgreSQL 14 preview - 支持 lwlock blocking 诊断 - 增加 pg_lwlock_blocking_pid

作者

digoal

日期

2020-11-10

标签

PostgreSQL , pg_lwlock_blocking_pid , lwlock 堵塞诊断


背景

当前lw lock这种轻量锁的等待没有跟踪数据, 所以无法知道谁堵塞了谁, 只知道在等待lw事件.

PG 14可能引入支持lw lock的等待跟踪, 需要改lw锁结构, 锁的存储可能变得更重, 具体实现PG社区还在讨论中.

https://www.postgresql.org/message-id/flat/71779d05-3a9b-b897-f54d-16ffedcc896b@amazon.com

https://commitfest.postgresql.org/30/2576/

# select query,pid,state,wait_event,wait_event_type,pg_lwlock_blocking_pid(pid),pg_blocking_pids(pid) from pg_stat_activity where state='active' and pid != pg_backend_pid();    
    
    
    
              query              |  pid  | state  |  wait_event   | wait_event_type |          pg_lwlock_blocking_pid           | pg_blocking_pids    
    
    
    
--------------------------------+-------+--------+---------------+-----------------+-------------------------------------------+------------------    
    
    
    
  insert into bdtlwa values (1); | 10232 | active |               |                 | (,,,)                                     | {}    
    
    
    
  insert into bdtlwb values (1); | 10254 | active | WALInsert     | LWLock          | (LW_WAIT_UNTIL_FREE,10232,LW_EXCLUSIVE,1) | {}    
    
    
    
  create table bdtwt (a int);    | 10256 | active | WALInsert     | LWLock          | (LW_WAIT_UNTIL_FREE,10232,LW_EXCLUSIVE,1) | {}    
    
    
    
  insert into bdtlwa values (2); | 10259 | active | BufferContent | LWLock          | (LW_EXCLUSIVE,10232,LW_EXCLUSIVE,1)       | {}    
    
    
    
  drop table bdtlwd;             | 10261 | active | WALInsert     | LWLock          | (LW_WAIT_UNTIL_FREE,10232,LW_EXCLUSIVE,1) | {}    
    
    
    
(5 rows)    
    
    
    
So, should a PID being blocked on a LWLock we could see:    
    
    
    
  * in which mode request it is waiting    
  * the last pid holding the lock    
  * the mode of the last PID holding the lock    
  * the number of PID(s) holding the lock    

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

digoal's wechat