GaussDB 在實際生產環境中,性能瓶頸與數據庫鎖等待問題常常是影響業務的核心因素。本文整理了一套 可直接用于排查性能問題的 SQL & 實戰案例,涵蓋:
-
TOP SQL 耗時分析
-
會話等鎖 & 持鎖分析
-
長事務排查
-
常規鎖 / 輕量級鎖(lwlock)案例
-
SubPlan / 子查詢引發的性能問題
適合運維工程師、數據庫管理員(DBA)一線使用。
1. TOP SQL 排查(耗時高、調用高)
在 GaussDB 中,可通過 dbe_perf.summary_statement 快速定位耗時最久的 SQL。
?? 查詢耗時最高的 TOP SQL
SELECT
n_calls,
unique_sql_id,
substr(query, 1, 50) AS query,
total_elapse_time / n_calls / 1000 AS avg_time,
total_elapse_time / 1000 AS totaltime
FROM
dbe_perf.summary_statement
WHERE
user_name = 'root'
AND n_calls > 1
ORDER BY
totaltime DESC;可用于快速識別:
-
平均耗時最高的 SQL
-
調用次數高但效率差的 SQL
-
業務熱點 SQL 性能瓶頸
2. 等鎖 & 持鎖會話排查(阻塞分析)
當業務出現“卡頓”“SQL 無響應”時,首要處理方向就是鎖等待。
?? 查找等待鎖的會話與持鎖會話
SELECT
a.query_id,
a.query AS waiting_sql,
b.wait_status,
b.wait_event,
b.block_sessionid,
c.pid AS block_id,
c.query AS block_query
FROM
pg_stat_activity a,
pg_thread_wait_status b,
pg_stat_activity c
WHERE
a.query_id = b.query_id
AND b.block_sessionid = c.sessionid
AND a.state != 'idle'
AND a.datname = 'postgres';可直接判斷:
-
哪條 SQL 在等鎖
-
哪個會話造成阻塞
-
等待事件類型(鎖 / IO / LWLock / 其他)
3. 長事務排查(經典導致鎖問題的根源)
長事務可能導致:
-
鎖長時間不釋放
-
膨脹(dead tuple)無法清理
-
真正的性能瓶頸
?? 查找持續時間最長的事務
SELECT
(now() - xact_start) AS diff_time,
pid,
sessionid,
query
FROM
pg_stat_activity
WHERE
state = 'active'
ORDER BY
diff_time DESC;案例:長事務造成阻塞分析
步驟 1:先查看阻塞關系(等鎖 SQL)
使用第 2 節的等鎖 SQL,發現會話 2 被會話 1 阻塞:
-
會話 2:執行 UPDATE → 需要 RowExclusiveLock
-
會話 1:執行 LOCK TABLE → 持有 AccessExclusiveLock
二者鎖模式不兼容 → 形成鎖等待。
鎖模式知識點(關鍵)
|
鎖級別 |
鎖模式 |
說明 |
|---|---|---|
|
3 |
RowExclusiveLock |
INSERT/UPDATE/DELETE 獲得,DML 必然申請 |
|
8 |
AccessExclusiveLock |
DDL 獲得,與所有鎖均不兼容 |
后臺日志也能看到沖突
路徑:
$GAUSSLOG/pg_log/dn_6001日志信息會明確提示:
-
會話 2 請求 RowExclusiveLock
-
會話 1 持有 AccessExclusiveLock
-
鎖沖突產生阻塞
4. 常規鎖分析(pg_locks + wait event)
該 SQL 適用于排查阻塞鏈路+鎖類型+等待事件。
?? 常規鎖排查 SQL
SELECT
(now() - s.xact_start) diff_time,
s.pid,
s.sessionid,
s.query,
locktag_decode(l.locktag) AS locktag,
t.node_name,
t.db_name,
t.thread_name,
t.wait_status,
t.wait_event
FROM
pg_thread_wait_status t,
pg_locks l,
pg_stat_activity s
WHERE
t.locktag = l.locktag
AND l.granted = 't'
AND s.pid = t.tid;?? 示例分析
情況 1:
idle in transaction
說明:
-
手動開啟事務但未提交
-
或發生 CN 剔除 / 主備切換導致事務殘留
→ 這種事務不提交就會一直占鎖。
情況 2:等待事件為
acquire lock
說明:
-
該線程等待另一個事務釋放鎖
-
繼續溯源即可找到真正的阻塞點
情況 3:等待事件為
acquire lwlock
說明:
-
正在申請輕量級鎖(LWLock)
-
特別是 "LockMgrLock" 說明主鎖表競爭高
-
多發生在并發較大系統中
5. 輕量級鎖(LWLock)分析
LWLock 是 GaussDB 內部的輕量級鎖,用于:
-
保護共享內存結構
-
維持鎖管理器、buffer、wal buffer 的線程安全
?? 查詢 LWLock 情況
SELECT
(now() - s.xact_start) diff_time,
s.pid,
s.sessionid,
s.query,
t.*,
lw.*
FROM
pg_stat_activity s,
pg_thread_wait_status t,
gs_lwlock_status() lw
WHERE
s.state = 'active'
AND s.pid = t.tid
AND t.sessionid = lw.sessionid
AND lw.granted = 't'
ORDER BY
diff_time DESC;備注:如遇 "LockMgrLock" 則表示主鎖表競爭嚴重,常見于熱點表、超高并發場景。
6. SubPlan / 子查詢導致性能問題
什么是 SubQuery / SubLink?
-
SubQuery:SQL 中的子查詢語法
-
SubLink:執行計劃中子查詢的表達方式
在執行計劃中看到:
SubPlan通常意味著性能隱患,例如:
-
子查詢被重復執行
-
觸發 NESTED LOOP + 子查詢組合
-
無法使用索引回表
常用優化方式
|
技術 |
優點 |
|---|---|
|
將 SubPlan 改寫為 JOIN |
消除重復執行,提高效率 |
|
改寫為 CTE |
提高可讀性,減少多次掃描 |
|
用 EXISTS 替換 IN |
避免大量回表 |
總結
本文整理 GaussDB 中與性能瓶頸和鎖等待排查最相關的 SQL,包括:
-
TOP SQL 分析
-
等鎖 & 持鎖會話排查
-
長事務定位
-
常規鎖 / 輕量級鎖(LWLock)分析
-
SubPlan 性能問題識別
如果你正在做生產排障或穩定性優化,這套 SQL 可以直接作為“隨手工具箱”。




