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