1 of 1

28.2. 統計資訊收集器

版本：11

PostgreSQL 的統計資訊收集器是一個子系統，支援收集和回報有關伺服器活動的訊息。目前，收集器可以計算對磁盤區塊和單個資料列中資料表和索引的存取。它還追踪每個資料表中的總資料列數量，以及有關每個資料表的清理和分析操作的資訊。它還可以統計對使用者定義函數的呼叫以及每個函數所花費的總時間。

PostgreSQL 也支援回報有關系統當下正在發生什麼的動態訊息，例如目前正由其他伺服器程序所執行的明確指令，以及系統中正在進行的其他連線。此功能獨立於收集器程序。

28.2.1. 統計收集設定

由於統計信息的收集會增加查詢執行的成本，因此系統可以設定收集或不收集訊息。這由通常在 postgresql.conf 中的參數控制。（有關設定參數的詳細訊息，請參閱第 19 章。）

參數 track_activities 啓用監控任何伺服器程序正在執行的指令。

參數 track_counts 控制是否收集有關資料表和索引存取的統計資訊。

參數 track_functions 可以追踪使用者自訂函數的使用情況。

參數 track_io_timing 啓用監控磁碟區塊讀取和寫入時間。

通常這些參數會在 postgresql.conf 中設定，以便它們適用於所有伺服器程序，但也可以使用 SET 指令在各個連線中啓用或停用它們。（為了防止普通使用者將其活動隱藏於管理員之外，只允許超級使用者使用 SET 變更這些參數。）

統計資訊收集器透過臨時檔案將收集的資訊傳輸到其他 PostgreSQL 程序。這些檔案儲存在參數 stats_temp_directory 所指定的目錄中，預設為 pg_stat_tmp。為了獲得更好的效能，可以將 stats_temp_directory 指向記憶體的檔案系統，從而降低物理 I/O 需求。當伺服器完全地關閉時，統計資料的永久副本將儲存在 pg_stat 子目錄中，以便可以跨伺服器重新啟動並保留統計資訊。在伺服器啟動時進行回復（例如，在立即關閉，伺服器當機和實時回復之後），將重置所有統計計數器。

28.2.2. Viewing Statistics

Table 28.1 中列出了的幾個預先定義好的檢視表，可用於顯示系統的當下的狀態。Table 28.2 中還列出了其他幾個檢視表，可用於顯示統計訊息收集的結果。進一步可以使用基礎統計函數建構自訂的檢視表，如第 28.2.3 節中所述。

使用統計訊息監控收集的資料時，重要的是要瞭解到訊息並不會立即更新。每個伺服器程序在回到閒置狀態之前會向收集器發送新的統計計數；因此，仍在進行中的查詢或事務不會對顯示的數據產生影響。此外，收集器本身每 PGSTAT_STAT_INTERVAL 毫秒最多回報一次（500毫秒，除非在編譯伺服器時更改）。因此顯示的數據是落後於實際活動的。但是，track_activities 所收集的目前查詢資訊是最新的。

另一個要點是，當要求伺服器程序顯示任何這些統計訊息時，它首先獲取收集器程序發出的最新報告，然後繼續將此快照結果用於所有統計檢視表和函數，直到其當下該事務結束。因此，只要您繼續該事務，統計數據就會顯示靜態結果。同樣地，當在事務中首次請求任何此類資訊時，收集關於所有連線的當下查詢資訊，將會在整個事務中顯示相同的資訊。這是一個正常的功能，而不是一個錯誤，因為它允許您對統計信息執行多個查詢並且關聯結果，而不必擔心數字在您交易當下發生變化。但是，如果要查看每個查詢的新結果，請確保在任何事務區塊之外執行查詢。或者，您可以呼叫 pg_stat_clear_snapshot()，它將丟棄當下事務的統計資訊快照（如果有的話）。下次使用統計資訊時將會去獲取新的快照。

事務還可以在檢視圖 pg_stat_xact_all_tables，pg_stat_xact_sys_tables，pg_stat_xact_user_tables 和pg_stat_xact_user_functions 中查看自己的統計訊息（尚未傳遞給收集器的）。這些數字不符合上述原則；相反地，他們在整個交易過程中不斷更新。

Table 28.1. Dynamic Statistics Views

Table 28.2. Collected Statistics Views

The per-index statistics are particularly useful to determine which indexes are being used and how effective they are.

The pg_statio_ views are primarily useful to determine the effectiveness of the buffer cache. When the number of actual disk reads is much smaller than the number of buffer hits, then the cache is satisfying most read requests without invoking a kernel call. However, these statistics do not give the entire story: due to the way in which PostgreSQL handles disk I/O, data that is not in the PostgreSQL buffer cache might still reside in the kernel's I/O cache, and might therefore still be fetched without requiring a physical read. Users interested in obtaining more detailed information on PostgreSQL I/O behavior are advised to use the PostgreSQL statistics collector in combination with operating system utilities that allow insight into the kernel's handling of I/O.

Table 28.3. `pg_stat_activity` View

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

Note

The wait_event and state columns are independent. If a backend is in the active state, it may or may not be waiting on some event. If the state is active and wait_event is non-null, it means that a query is being executed, but is being blocked somewhere in the system.

Table 28.4. wait_event Description

Note

For tranches registered by extensions, the name is specified by extension and this will be displayed as wait_event. It is quite possible that user has registered the tranche in one of the backends (by having allocation in dynamic shared memory) in which case other backends won't have that information, so we display extension for such cases.

Here is an example of how wait events can be viewed

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
 pid  | wait_event_type |  wait_event
------+-----------------+---------------
 2540 | Lock            | relation
 6644 | LWLock          | ProcArrayLock
(2 rows)

Table 28.5. pg_stat_replication View

The pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers.

The lag times reported in the pg_stat_replication view are measurements of the time taken for recent WAL to be written, flushed and replayed and for the sender to know about it. These times represent the commit delay that was (or would have been) introduced by each synchronous commit level, if the remote server was configured as a synchronous standby. For an asynchronous standby, the replay_lag column approximates the delay before recent transactions became visible to queries. If the standby server has entirely caught up with the sending server and there is no more WAL activity, the most recently measured lag times will continue to be displayed for a short time and then show NULL.

Lag times work automatically for physical replication. Logical decoding plugins may optionally emit tracking messages; if they do not, the tracking mechanism will simply display NULL lag.

Note

The reported lag times are not predictions of how long it will take for the standby to catch up with the sending server assuming the current rate of replay. Such a system would show similar times while new WAL is being generated, but would differ when the sender becomes idle. In particular, when the standby has caught up completely, pg_stat_replication shows the time taken to write, flush and replay the most recent reported WAL location rather than zero as some users might expect. This is consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions. To reduce confusion for users expecting a different model of lag, the lag columns revert to NULL after a short time on a fully replayed idle system. Monitoring systems should choose whether to represent this as missing data, zero or continue to display the last known value.

Table 28.6. pg_stat_wal_receiver View

The pg_stat_wal_receiver view will contain only one row, showing statistics about the WAL receiver from that receiver's connected server.

Table 28.7. pg_stat_subscription View

The pg_stat_subscription view will contain one row per subscription for main worker (with null PID if the worker is not running), and additional rows for workers handling the initial data copy of the subscribed tables.

Table 28.8. pg_stat_ssl View

The pg_stat_ssl view will contain one row per backend or WAL sender process, showing statistics about SSL usage on this connection. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection.

Table 28.9. pg_stat_archiver View

The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster.

Table 28.10. pg_stat_bgwriter View

The pg_stat_bgwriter view will always have a single row, containing global data for the cluster.

Table 28.11. pg_stat_database View

The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.

Table 28.12. pg_stat_database_conflicts View

The pg_stat_database_conflicts view will contain one row per database, showing database-wide statistics about query cancels occurring due to conflicts with recovery on standby servers. This view will only contain information on standby servers, since conflicts do not occur on master servers.

Table 28.13. pg_stat_all_tables View

The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.

Table 28.14. pg_stat_all_indexes View

The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.

Indexes can be used by simple index scans, “bitmap” index scans, and the optimizer. In a bitmap scan the output of several indexes can be combined via AND or OR rules, so it is difficult to associate individual heap row fetches with specific indexes when a bitmap scan is used. Therefore, a bitmap scan increments the pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses, and it increments the pg_stat_all_tables.idx_tup_fetch count for the table, but it does not affect pg_stat_all_indexes.idx_tup_fetch. The optimizer also accesses indexes to check for supplied constants whose values are outside the recorded range of the optimizer statistics because the optimizer statistics might be stale.

Note

The idx_tup_read and idx_tup_fetch counts can be different even without any use of bitmap scans, because idx_tup_read counts index entries retrieved from the index while idx_tup_fetch counts live rows fetched from the table. The latter will be less if any dead or not-yet-committed rows are fetched using the index, or if any heap fetches are avoided by means of an index-only scan.

Table 28.15. pg_statio_all_tables View

The pg_statio_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about I/O on that specific table. The pg_statio_user_tables and pg_statio_sys_tables views contain the same information, but filtered to only show user and system tables respectively.

Table 28.16. pg_statio_all_indexes View

The pg_statio_all_indexes view will contain one row for each index in the current database, showing statistics about I/O on that specific index. The pg_statio_user_indexes and pg_statio_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.

Table 28.17. pg_statio_all_sequences View

The pg_statio_all_sequences view will contain one row for each sequence in the current database, showing statistics about I/O on that specific sequence.

Table 28.18. pg_stat_user_functions View

The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of that function. The track_functions parameter controls exactly which functions are tracked.

28.2.3. Statistics Functions

Other ways of looking at the statistics can be set up by writing queries that use the same underlying statistics access functions used by the standard views shown above. For details such as the functions' names, consult the definitions of the standard views. (For example, in psql you could issue \d+ pg_stat_activity.) The access functions for per-database statistics take a database OID as an argument to identify which database to report on. The per-table and per-index functions take a table or index OID. The functions for per-function statistics take a function OID. Note that only tables, indexes, and functions in the current database can be seen with these functions.

Additional functions related to statistics collection are listed in Table 28.19.

Table 28.19. Additional Statistics Functions

pg_stat_get_activity, the underlying function of the pg_stat_activity view, returns a set of records containing all the available information about each backend process. Sometimes it may be more convenient to obtain just a subset of this information. In such cases, an older set of per-backend statistics access functions can be used; these are shown in Table 28.20. These access functions use a backend ID number, which ranges from one to the number of currently active backends. The function pg_stat_get_backend_idset provides a convenient way to generate one row for each active backend for invoking these functions. For example, to show the PIDs and current queries of all backends:

SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       pg_stat_get_backend_activity(s.backendid) AS query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Table 28.20. Per-Backend Statistics Functions

28.2. 統計資訊收集器

版本：11

28.2.1. 統計收集設定

參數 track_activities 啓用監控任何伺服器程序正在執行的指令。

參數 track_counts 控制是否收集有關資料表和索引存取的統計資訊。

參數 track_functions 可以追踪使用者自訂函數的使用情況。

參數 track_io_timing 啓用監控磁碟區塊讀取和寫入時間。

28.2.2. Viewing Statistics

Table 28.1. Dynamic Statistics Views

Table 28.2. Collected Statistics Views

The per-index statistics are particularly useful to determine which indexes are being used and how effective they are.

Table 28.3. `pg_stat_activity` View

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

Note

Table 28.4. wait_event Description

Note

Here is an example of how wait events can be viewed

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
 pid  | wait_event_type |  wait_event
------+-----------------+---------------
 2540 | Lock            | relation
 6644 | LWLock          | ProcArrayLock
(2 rows)

Table 28.5. pg_stat_replication View

Lag times work automatically for physical replication. Logical decoding plugins may optionally emit tracking messages; if they do not, the tracking mechanism will simply display NULL lag.

Note

Table 28.6. pg_stat_wal_receiver View

The pg_stat_wal_receiver view will contain only one row, showing statistics about the WAL receiver from that receiver's connected server.

Table 28.7. pg_stat_subscription View

Table 28.8. pg_stat_ssl View

Table 28.9. pg_stat_archiver View

The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster.

Table 28.10. pg_stat_bgwriter View

The pg_stat_bgwriter view will always have a single row, containing global data for the cluster.

Table 28.11. pg_stat_database View

The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.

Table 28.12. pg_stat_database_conflicts View

Table 28.13. pg_stat_all_tables View

Table 28.14. pg_stat_all_indexes View

Note

Table 28.15. pg_statio_all_tables View

Table 28.16. pg_statio_all_indexes View

Table 28.17. pg_statio_all_sequences View

The pg_statio_all_sequences view will contain one row for each sequence in the current database, showing statistics about I/O on that specific sequence.

Table 28.18. pg_stat_user_functions View

28.2.3. Statistics Functions

Additional functions related to statistics collection are listed in Table 28.19.

Table 28.19. Additional Statistics Functions

SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       pg_stat_get_backend_activity(s.backendid) AS query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Table 28.20. Per-Backend Statistics Functions

28.2. 統計資訊收集器

28.2.1. 統計收集設定

28.2.2. Viewing Statistics

Table 28.1. Dynamic Statistics Views

Table 28.2. Collected Statistics Views

Table 28.3. pg_stat_activity View

Note

Note

Note

Note

28.2.3. Statistics Functions

28.2. 統計資訊收集器

28.2.1. 統計收集設定

28.2.2. Viewing Statistics

Table 28.1. Dynamic Statistics Views

Table 28.2. Collected Statistics Views

Table 28.3. pg_stat_activity View

Note

Note

Note

Note

28.2.3. Statistics Functions

Table 28.3. `pg_stat_activity` View

Table 28.3. `pg_stat_activity` View