從零開始學(xué)PostgreSQL：監(jiān)控數(shù)據(jù)庫動態(tài)
統(tǒng)計信息概述
PostgreSQL 提供了一套豐富的統(tǒng)計信息收集和報告機制，用于監(jiān)控數(shù)據(jù)庫的運行狀況和性能。以下是這些機制的一些關(guān)鍵要點：
統(tǒng)計信息收集配置
- track_activities: 控制對服務(wù)器進程當(dāng)前活動的監(jiān)控。
- track_counts: 決定是否收集表和索引訪問的累積統(tǒng)計信息。
- track_functions: 跟蹤用戶定義函數(shù)的調(diào)用和執(zhí)行時間。
- track_io_timing: 監(jiān)測塊讀寫時間。
- track_wal_io_timing: 開啟對WAL寫入時間的監(jiān)控。
這些參數(shù)通常在postgresql.conf文件中設(shè)置，但超級用戶也可以在會話中動態(tài)調(diào)整它們。
統(tǒng)計信息存儲與持久化
- 統(tǒng)計信息在共享內(nèi)存中累積，并在適當(dāng)時間間隔刷新。
- 服務(wù)器關(guān)閉時，統(tǒng)計信息會保存到pg_stat目錄下的子目錄，以便重啟時恢復(fù)。
- 不干凈的關(guān)閉或特殊啟動場景（如立即關(guān)閉、服務(wù)器崩潰、從基礎(chǔ)備份啟動或時間點恢復(fù)）會導(dǎo)致統(tǒng)計信息計數(shù)器重置。
統(tǒng)計信息查看
- 多個預(yù)定義視圖（如pg_stat_activity、pg_stat_database等）顯示當(dāng)前系統(tǒng)狀態(tài)。
- 累積統(tǒng)計信息通過其他視圖（如pg_stat_user_tables、pg_stat_user_functions等）展示。
- 統(tǒng)計信息可能不是實時更新的，而是以固定頻率刷新，這可能導(dǎo)致信息滯后。
- 統(tǒng)計信息在被訪問時會被緩存，直到當(dāng)前事務(wù)結(jié)束，以保持一致性。
- 特殊權(quán)限（如pg_read_all_stats）允許用戶查看更全面的統(tǒng)計信息。
動態(tài)統(tǒng)計信息與安全限制
統(tǒng)計信息視圖
視圖名稱 | 描述 |
pg_stat_activity | 顯示每個會話的實時活動，包括正在執(zhí)行的查詢、等待狀態(tài)、會話狀態(tài)等，是監(jiān)控和調(diào)試運行中的查詢的常用工具。 |
pg_stat_replication | 列出所有正在運行的WAL發(fā)送者進程，提供流式復(fù)制到備用服務(wù)器的統(tǒng)計信息，如發(fā)送位置、寫入位置、復(fù)制延遲等。 |
pg_stat_wal_receiver | 顯示W(wǎng)AL接收者進程的狀態(tài)，用于監(jiān)控從主服務(wù)器接收WAL文件的情況。 |
pg_stat_recovery_prefetch | 展示在恢復(fù)過程中預(yù)取塊的統(tǒng)計信息，用于了解恢復(fù)期間的預(yù)取效率。 |
pg_stat_subscription | 列出所有訂閱的統(tǒng)計信息，包括訂閱的狀態(tài)、進度等，對于監(jiān)控邏輯復(fù)制訂閱的健康狀態(tài)至關(guān)重要。 |
pg_stat_ssl | 顯示每個使用SSL連接的統(tǒng)計信息，如會話狀態(tài)、SSL版本等，用于監(jiān)控安全連接的使用情況。 |
pg_stat_gssapi | 提供每個使用GSSAPI進行身份驗證和加密的連接的統(tǒng)計信息，用于監(jiān)控安全連接的健康狀態(tài)。 |
pg_stat_progress_analyze | 顯示正在進行的ANALYZE操作的進度，用于監(jiān)控索引統(tǒng)計信息的更新過程。 |
pg_stat_progress_create_index | 顯示正在進行的CREATE INDEX操作的進度，用于監(jiān)控索引創(chuàng)建任務(wù)的狀態(tài)。 |
pg_stat_progress_vacuum | 顯示正在進行的VACUUM操作的進度，用于監(jiān)控清理和優(yōu)化表的進程。 |
pg_stat_progress_cluster | 顯示正在進行的CLUSTER操作的進度，用于監(jiān)控基于索引排序表的進程。 |
pg_stat_progress_basebackup | 顯示正在進行的基本備份操作的進度，用于監(jiān)控流式備份的狀態(tài)。 |
pg_stat_progress_copy | 顯示正在進行的COPY操作的進度，用于監(jiān)控數(shù)據(jù)導(dǎo)入導(dǎo)出任務(wù)的狀態(tài)。 |
pg_stat_archiver | 顯示W(wǎng)AL歸檔器進程的統(tǒng)計信息，用于監(jiān)控WAL文件的歸檔狀態(tài)。 |
pg_stat_bgwriter | 顯示后臺寫入器進程的統(tǒng)計信息，用于監(jiān)控緩沖區(qū)的清理和寫入磁盤的活動。 |
pg_stat_database | 顯示每個數(shù)據(jù)庫的統(tǒng)計信息，包括事務(wù)數(shù)、回滾數(shù)、臨時文件使用情況等，用于監(jiān)控數(shù)據(jù)庫級別的活動。 |
pg_stat_database_conflicts | 顯示因恢復(fù)沖突而被取消的查詢的統(tǒng)計信息，用于監(jiān)控數(shù)據(jù)恢復(fù)過程中可能遇到的問題。 |
pg_stat_io | 顯示I/O操作的統(tǒng)計信息，包括讀寫次數(shù)、時間等，用于監(jiān)控磁盤I/O的效率。 |
pg_stat_replication_slots | 顯示每個復(fù)制槽的統(tǒng)計信息，用于監(jiān)控復(fù)制槽的使用情況。 |
pg_stat_slru | 顯示每個簡單LRU內(nèi)存管理器的統(tǒng)計信息，用于監(jiān)控內(nèi)存管理的效率。 |
pg_stat_subscription_stats | 顯示每個訂閱的統(tǒng)計信息，包括錯誤和警告的計數(shù)，用于監(jiān)控訂閱的健康狀態(tài)。 |
pg_stat_wal | 顯示W(wǎng)AL操作的統(tǒng)計信息，包括記錄數(shù)、字節(jié)數(shù)、寫入時間等，用于監(jiān)控WAL日志的生成和寫入活動。 |
pg_stat_all_tables | 顯示所有表的統(tǒng)計信息，包括掃描次數(shù)、行數(shù)、更新次數(shù)等，用于監(jiān)控表級別的活動。 |
pg_stat_sys_tables | 顯示系統(tǒng)表的統(tǒng)計信息，與pg_stat_all_tables類似，但僅限于系統(tǒng)表。 |
pg_stat_user_tables | 顯示用戶表的統(tǒng)計信息，與pg_stat_all_tables類似，但僅限于用戶表。 |
pg_stat_xact_all_tables | 顯示所有表在當(dāng)前事務(wù)中的統(tǒng)計信息，與pg_stat_all_tables類似，但反映的是事務(wù)內(nèi)的操作。 |
pg_stat_xact_sys_tables | 顯示系統(tǒng)表在當(dāng)前事務(wù)中的統(tǒng)計信息，與pg_stat_xact_all_tables類似，但僅限于系統(tǒng)表。 |
pg_stat_xact_user_tables | 顯示用戶表在當(dāng)前事務(wù)中的統(tǒng)計信息，與pg_stat_xact_all_tables類似，但僅限于用戶表。 |
pg_stat_all_indexes | 顯示所有索引的統(tǒng)計信息，包括掃描次數(shù)、使用次數(shù)等，用于監(jiān)控索引的使用情況。 |
pg_stat_sys_indexes | 顯示系統(tǒng)索引的統(tǒng)計信息，與pg_stat_all_indexes類似，但僅限于系統(tǒng)索引。 |
pg_stat_user_indexes | 顯示用戶索引的統(tǒng)計信息，與pg_stat_all_indexes類似，但僅限于用戶索引。 |
pg_stat_user_functions | 顯示用戶定義函數(shù)的統(tǒng)計信息，包括調(diào)用次數(shù)、執(zhí)行時間等，用于監(jiān)控函數(shù)的使用情況。 |
pg_stat_xact_user_functions | 顯示用戶定義函數(shù)在當(dāng)前事務(wù)中的統(tǒng)計信息，與pg_stat_user_functions類似，但反映的是事務(wù)內(nèi)的函數(shù)調(diào)用。 |
pg_statio_all_tables | 顯示所有表的I/O統(tǒng)計信息，包括讀寫次數(shù)、字節(jié)數(shù)等，用于監(jiān)控表級別的磁盤I/O活動。 |
pg_statio_sys_tables | 顯示系統(tǒng)表的I/O統(tǒng)計信息，與pg_statio_all_tables類似，但僅限于系統(tǒng)表。 |
pg_statio_user_tables | 顯示用戶表的I/O統(tǒng)計信息，與pg_statio_all_tables類似，但僅限于用戶表。 |
pg_statio_all_indexes | 顯示所有索引的I/O統(tǒng)計信息，用于監(jiān)控索引的磁盤I/O活動。 |
pg_statio_sys_indexes | 顯示系統(tǒng)索引的I/O統(tǒng)計信息，與pg_statio_all_indexes類似，但僅限于系統(tǒng)索引。 |
pg_statio_user_indexes | 顯示用戶索引的I/O統(tǒng)計信息，與pg_statio_all_indexes類似，但僅限于用戶索引。 |
pg_statio_all_sequences | 顯示所有序列的I/O統(tǒng)計信息，用于監(jiān)控序列的磁盤I/O活動。 |
pg_statio_sys_sequences | 顯示系統(tǒng)序列的I/O統(tǒng)計信息，與pg_statio_all_sequences類似，但僅限于系統(tǒng)序列。 |
pg_statio_user_sequences | 顯示用戶序列的I/O統(tǒng)計信息，與pg_statio_all_sequences類似，但僅限于用戶序列。 |
查看鎖
在 PostgreSQL 中，pg_locks 系統(tǒng)表是一個非常有用的工具，用于監(jiān)視數(shù)據(jù)庫活動中的鎖管理情況。下面是對 pg_locks 的總結(jié)：
用途
pg_locks 表提供了關(guān)于當(dāng)前活躍鎖的信息，這對于數(shù)據(jù)庫管理員來說是非常有價值的，因為它可以幫助識別和解決鎖相關(guān)的性能問題。以下是一些常見的用途：
- 查看當(dāng)前未完成的所有鎖：這可以幫助你了解哪些類型的鎖正在被持有，以及它們的狀態(tài)（如鎖定模式、鎖定對象等）。
- 特定數(shù)據(jù)庫中關(guān)系上的所有鎖：這有助于確定哪些表或索引正受到鎖爭用的影響。
- 特定關(guān)系上的所有鎖：這可以讓你深入了解特定表上的鎖爭用情況。
- 特定 PostgreSQL 會話持有的所有鎖：這有助于找出哪些會話正在持有鎖，以及它們是否是造成性能瓶頸的原因。
如何使用 pg_locks
你可以通過查詢 pg_locks 表來獲取所需的信息。以下是一些基本的查詢示例：
- 查看所有未完成的鎖：
SELECT * FROM pg_locks;- 查看特定數(shù)據(jù)庫中關(guān)系上的所有鎖：
SELECT * FROM pg_locks WHERE database = <database_oid>;- 查看特定關(guān)系上的所有鎖：
SELECT * FROM pg_locks WHERE relation = <relation_oid>;- 查看特定會話持有的所有鎖：
SELECT * FROM pg_locks WHERE pid = <session_pid>;5.找出正在執(zhí)行的事務(wù)（等待事務(wù)添加 WHERE NOT l.granted;）
SELECT
l.locktype,
l.mode,
l.transactionid,
l.classid,
l.relation,
l.page,
l.tuple,
l.virtualxid,
l.database,
l.pid AS locked_pid,
p.query,
p.usename
FROM
pg_locks l
JOIN
pg_stat_activity p ON l.pid = p.pid終止鎖定事務(wù)：
- 一旦你找到了需要終止的事務(wù)的PID，你可以使用以下函數(shù)來取消或終止該事務(wù)：
- pg_cancel_backend(pid)：嘗試優(yōu)雅地取消事務(wù)。如果事務(wù)正在進行查詢，查詢將被中斷，事務(wù)將被回滾。
- pg_terminate_backend(pid)：直接終止事務(wù)的后端進程。這將導(dǎo)致事務(wù)立即終止，無論它正在進行什么操作
SELECT pg_cancel_backend(542768);
SELECT pg_terminate_backend(542768);
