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