MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人，通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ～ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標，基本紀錄登入使用的範例：

2019年8月1日 星期四

找出 PGSQL 裡面被綁太久的 Table Lock

在 Postgres 裡面，Lock 是一套狀態標記系統，資料庫裡面每個動作，一定有一個 Lock 等級標誌著；這個標記表現行為的優先順序，有點像是階級一樣。。。。因此，在 PGSQL 裡面看到 Lock 的話，千萬不要馬上套用其他資料庫的經驗，馬上把 Session 砍掉。
然而，實際使用上，資料庫常常會因為 Lock 遇到一些狀況。這邊用一個模擬狀況，示範日常需要的 Lock 監控要怎麼在 PGSQL 進行。
本處所示範的情況
 - 是習慣其他關聯式資料庫的人所熟知的日常監控與剔除的目標
 - 但是在 PGSQL 裡面，不叫做 Dead Lock！！！

通常，可能因為程式漏寫或是設計不佳，或是人為疏失，可能會有一些 Sessions 忘記關掉交易，結果讓資料庫的 Session 一直卡住，造成其他 Session 要進行需要更重 Table Lock 的作業時，被這些「被遺忘的 Session」 卡住。

重新聲明（因為很重要所以再講一次～）：
1. 這不是在處理 Deadlock！！Deadlock 是專有名詞～
2. 有了這篇筆記之後，不要以後在 PGSQL 看到「有 Lock」就砍掉～

以下就是這個筆記的情境。


某 A 執行一個作業，但還沒 Commit
edb=# -- session A
edb=# set application_name='Session A';
SET
edb=# begin;
BEGIN
edb=# select * from test;
 id 
----
  1
  2
(2 rows)

edb=#

然後橘色的人就跑去喝下午茶了～

然後另外有一個 Session 
edb=# -- session B
edb=# set application_name='Session B';
SET
edb=# 
edb=# alter table test alter COLUMN id type numeric;
卡住。。。

結果被卡住了。。。接著這個人就去客訴。。


Q. 被客訴的資料庫管理者怎麼查知？
直接想到的是，把 pg_stat_activity 跟 pg_locks 作 Join
edb=# select a.pid, a.application_name,
 a.wait_event, a.wait_event_type, a.state,
 l.locktype, l.relation::regclass,
 l.mode, l.granted 
from pg_stat_activity a, 
 pg_locks l 
where a.backend_type = 'client backend' 
  and a.wait_event is not null 
  and l.locktype='relation' 
  and a.pid=l.pid ;
 pid  | application_name | wait_event | wait_event_type |        state        | locktype | relation  |        mode         | granted 
------+------------------+------------+-----------------+---------------------+----------+-----------+---------------------+---------
 3504 | Session A        | ClientRead | Client          | idle in transaction | relation | test_pkey | AccessShareLock     | t
 3504 | Session A        | ClientRead | Client          | idle in transaction | relation | test      | AccessShareLock     | t
 4002 | Session B        | relation   | Lock            | active              | relation | test      | AccessExclusiveLock | f
(4 rows)

edb=#

這邊標示一個特殊的 f 值：這表示他想要獲取作業權限，但是求不得。。然後就引發煩惱惹～

在正常的環境下，這樣子查的內容會看到眼花。。。
所以要湊一句，可以顯示成：左邊是被擋的，右邊是肇事者的 SQL
努力完畢後，有下面這一句～ 
edb=# with victim as(
  select a.pid, a.application_name,
         a.wait_event, a.wait_event_type, a.state,
         (now() - a.query_start) as elapsed_time,
         l.locktype, l.relation::regclass,
         l.mode, l.granted 
  from pg_stat_activity a, 
       pg_locks l 
  where a.backend_type = 'client backend' 
    and a.wait_event is not null 
    and l.locktype='relation' 
    and a.pid=l.pid
    and a.wait_event_type = 'Lock' 
 and l.granted = 'f'
),
culprit as(
  select a.pid, a.application_name,
         a.wait_event, a.wait_event_type, a.state,
         l.locktype, l.relation::regclass,
         l.mode, l.granted 
  from pg_stat_activity a, 
       pg_locks l 
  where a.backend_type = 'client backend' 
    and a.wait_event is not null 
    and l.locktype='relation' 
    and a.pid=l.pid
 and l.granted = 't'
)
select v.pid as victim_pid,v.application_name as victim,v.state as victim_state, v.elapsed_time as waiting_time,
 null as separtor,
 c.pid as culprit_pid,c.application_name as culprit,c.state as culprit_state
from
victim v,
culprit c
where v.relation = c.relation;
 victim_pid |  victim   | victim_state |  waiting_time  | separtor | culprit_pid |  culprit  |    culprit_state    
------------+-----------+--------------+----------------+----------+-------------+-----------+---------------------
       4002 | Session B | active       | 00:10:11.36389 |          |        3504 | Session A | idle in transaction
(1 row)

edb=#

這一句使用到 PGSQL 9.6 之後才在 pg_stat_activity 引進的 wait_event 資訊，因此這一句需要 Postgres 9.6 版才能使用～

在兜出這一句 SQL 之後，發現其實官方教學有一個現成的內容，比較通用到舊版 PGSQL，不過寫得很複雜。。。
查詢結果如下： 
edb=# SELECT blocked_locks.pid     AS blocked_pid,                                                                                                  blocked_activity.usename  AS blocked_user,
      blocking_locks.pid     AS blocking_pid,
      blocking_activity.usename AS blocking_user,
      blocked_activity.query    AS blocked_statement,
      blocking_activity.query   AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks         blocked_locks
 JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
 JOIN pg_catalog.pg_locks         blocking_locks 
     ON blocking_locks.locktype = blocked_locks.locktype
     AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
     AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
     AND blocking_locks.pid != blocked_locks.pid
 JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
 blocked_pid | blocked_user | blocking_pid | blocking_user |               blocked_statement                | current_statement_in_blocking_process 
-------------+--------------+--------------+---------------+------------------------------------------------+---------------------------------------
        4002 | enterprisedb |         3504 | enterprisedb  | alter table test alter COLUMN id type numeric; | insert into test values (3);
        4002 | enterprisedb |         3504 | enterprisedb  | alter table test alter COLUMN id type numeric; | insert into test values (3);
(2 rows)

edb=#

以及有一篇比較新的，湊起來也比我的簡潔～ 
edb=# SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid,
  blockinga.query as blocking_query, blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;
 locked_item | blocked_pid |                 blocked_query                  |    blocked_mode     | blocking_pid |   blocking_query    |  blocking_mode  
-------------+-------------+------------------------------------------------+---------------------+--------------+---------------------+-----------------
 test        |        4002 | alter table test alter COLUMN id type numeric; | AccessExclusiveLock |         3504 | select * from test; | AccessShareLock
(1 row)

edb=#

[20230605 更新] 強者我同事ㄉ改良版
SELECT
  COALESCE(blockingl.relation::regclASs::text,blockingl.locktype) AS locked_item,
  blockinga.pid AS blocking_pid,
  blockinga.query AS blocking_query, 
  blockingl.mode AS blocking_mode,
  CASE 
    WHEN blockinga.wait_event_type = 'Lock' THEN 'False'
    ELSE 'True'
  END  AS leading_lock,
  blockeda.pid AS blocked_pid, 
  blockeda.query AS blocked_query,
  blockedl.mode AS blocked_mode,
  now() - blockeda.query_start AS blocked_time
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
order by leading_lock desc;



參考資料
Postgres: Idle queries and pg_locks – Hacker Noon
Lock Monitoring - PostgreSQL wiki
Monitor PostgreSQL locks (Example) - Coderwall
Exploring Query Locks in Postgres - Big elephants
MyOdoo2014 - High Performance Odoo - Olivier Dony
akhdaniel/PG Odoo Analysis · GitHub
pg_blocking_xid() 函數
關於 PGSQL 的 Lock 的其他參考資料

於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)