View statistics information-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL (PolarFlex) provides a rich set of system views and system tables for viewing statistics information collected from monitoring.

System views

View name	Description
pg_stat_activity	Displays one row for each server process. The row shows information about the current activity of the process, such as its status and current query.
pg_stat_replication	Displays one row for each Write-Ahead Logging (WAL) sender process. The row shows statistics about replication to the standby server connected to that sender.
pg_stat_wal_receiver	Displays only one row. The row shows statistics about the receiver from the server to which the WAL receiver is connected.
pg_stat_subscription	Displays at least one row for each subscription. The row shows information about the workers for that subscription.
pg_stat_ssl	Displays one row for each connection, regular or replication. The row shows information about the SSL used on the connection.
pg_stat_progress_vacuum	Displays one row for each running VACUUM backend process, including `autovacuum` worker processes. The row shows the current progress.

Statistics collection results views

View name	Description
pg_stat_archiver	Displays only one row. The row shows statistics about the activity of the WAL archiver process.
pg_stat_bgwriter	Displays only one row. The row shows statistics about the activity of the background writer process.
pg_stat_database	Displays one row for each database. The row shows database-wide statistics.
pg_stat_database_conflicts	Displays one row for each database. The row shows database-wide statistics about queries that were canceled because of conflicts with recovery on standby nodes.
pg_stat_all_tables	Displays one row for each table in the current database. The row shows statistics about access to the specified table.
pg_stat_sys_tables	Shows statistics about access to system tables.
pg_stat_user_tables	Shows statistics about access to user tables.
pg_stat_xact_all_tables	Similar to `pg_stat_all_tables`, but counts actions that occur only within the current transaction. These actions are not yet included in `pg_stat_all_tables` and related views. The view does not display columns for the number of live and dead rows, or for vacuum and analyze actions.
pg_stat_xact_sys_tables	Same as `pg_stat_xact_all_tables`, but shows only system tables.
pg_stat_xact_user_tables	Same as `pg_stat_xact_all_tables`, but shows only user tables.
pg_stat_all_indexes	Displays one row for each index in the current database. The row shows statistics about access to the specified index.
pg_stat_sys_indexes	Same as `pg_stat_all_indexes`, but shows only indexes on system tables.
pg_stat_user_indexes	Same as `pg_stat_all_indexes`, but shows only indexes on user tables.
pg_statio_all_tables	Displays one row for each table in the current database, including TOAST tables. The row shows statistics about I/O on the specified table.
pg_statio_sys_tables	Same as `pg_statio_all_tables`, but shows only system tables.
pg_statio_user_tables	Same as `pg_statio_all_tables`, but shows only user tables.
pg_statio_all_indexes	Displays one row for each index in the current database. The row shows statistics about I/O on the specified index.
pg_statio_sys_indexes	Same as `pg_statio_all_indexes`, but shows only indexes on system tables.
pg_statio_user_indexes	Same as `pg_statio_all_indexes`, but shows only indexes on user tables.
pg_statio_all_sequences	Displays one row for each sequence in the current database. The row shows statistics about I/O on the specified sequence.
pg_statio_sys_sequences	Same as `pg_statio_all_sequences`, but shows only system sequences. Note No system sequences are currently defined, so this view is empty.
pg_statio_user_sequences	Same as `pg_statio_all_sequences`, but shows only user sequences.
pg_stat_user_functions	Displays one row for each tracked function. The row shows statistics about the execution of that function. For all tracked functions, the following fields are returned: function OID, schema, name, number of calls, total time, and self time. Self time is the amount of time spent in the function itself. Total time includes the time spent in functions that it calls. Time values are in milliseconds.
pg_stat_xact_user_functions	Similar to `pg_stat_user_functions`, but counts only calls during the current transaction. These calls are not yet included in `pg_stat_user_functions`.

The pg_statio_ family of views is primarily used to assess the effectiveness of the buffer. When the number of actual disk reads is much smaller than the number of buffer hits, the buffer can satisfy most read requests without making kernel calls. However, these statistics do not provide a complete picture. Because of how PolarDB handles disk I/O, data blocks that are not in the buffer may still reside in the kernel's I/O cache. Therefore, they can be read again without a physical disk read. To understand the I/O behavior of PolarDB in more detail, use the PolarDB statistics collector together with operating system tools that let you observe how the kernel handles I/O.

System tables

PolarDB for PostgreSQL (PolarFlex) provides the pg_locks system table to monitor database activity. This table allows database administrators to view information about unresolved locks in the lock manager.

This table can be used in the following common scenarios:

View all current unresolved locks, all locks on relations in a specific database, all locks on a specific relation, or all locks held by a specific PostgreSQL session.
Identify the relations in the current database that have the most ungranted locks. These relations are likely sources of contention for database clients.
Determine the impact of lock contention on overall database performance and how lock contention changes with the overall database traffic.

Appendix

pg_stat_activity

This section provides detailed explanations of the fields returned by this view.

Column name	Data type	Description
datid	OID	The OID of the database that this session is connected to.
datname	NAME	The name of the database that this session is connected to.
pid	INTEGER	The process ID of this session. This is a unique identifier for the current session.
usesysid	OID	The OID of the user connected to this session.
usename	NAME	The name of the user connected to this session.
application_name	TEXT	The name of the application connected to this session.
client_addr	INET	The IP address of the client. If this field is empty, it indicates that the client is connected through a Unix socket on the server machine or it is an internal process, such as autovacuum.
client_hostname	TEXT	The hostname of the client, as reported by a reverse DNS lookup of `client_addr`.
client_port	INTEGER	The port number of the client. The value is -1 if a Unix socket is used.
backend_start	TIMESTAMP WITH TIME ZONE	The time when this session process started. This is the timestamp when the session connected to the database.
xact_start	TIMESTAMP WITH TIME ZONE	The time when the current transaction started. This is NULL if no transaction is active. If the current query is the first in its transaction, this column is equal to `query_start`.
query_start	TIMESTAMP WITH TIME ZONE	The time when the currently running query started. If the `state` is not `active`, this is the start time of the last query.
state_change	TIMESTAMP WITH TIME ZONE	The time when the session state was last changed.
wait_event_type	TEXT	The type of event the session is currently waiting for. This is NULL if the session is not waiting. Possible values include the following: LWLock: The session is waiting for a lightweight lock. Each such lock protects a specific data structure in shared memory. `wait_event` contains a name that identifies the purpose of the lightweight lock. Some locks have specific names, while others are part of a set of locks with similar purposes. Lock: The session is waiting for a heavyweight lock. Heavyweight locks, also known as lock manager locks or simply locks, primarily protect SQL-visible objects, such as tables. However, they are also used to ensure mutual exclusion for certain internal operations, such as relationship expansion. `wait_event` identifies the type of lock being waited for. BufferPin: The server process is waiting to access a data buffer while no other process is examining that buffer. A buffer `pin` wait can be prolonged if another process holds an open cursor that will eventually read data from the buffer being accessed. Activity: The server process is idle. This is used for system processes that are waiting for activity in their main processing loop. `wait_event` identifies the specific wait point. Extension: The server process is waiting for activity in an extension module. This classification is used for modules that track custom wait points. Client: The server process is waiting for some activity on a socket from the client application, and the server expects something to happen that is unrelated to its internal processing. `wait_event` identifies the specific wait point. IPC: The server process is waiting for some activity from another process in the server. `wait_event` identifies the specific wait point. Timeout: The server process is waiting for a timeout to occur. `wait_event` identifies the specific wait point. IO: The server process is waiting for an I/O operation to complete. `wait_event` identifies the specific wait point.
wait_event	TEXT	The specific event the session is currently waiting for. This is NULL if the session is not waiting.
state	TEXT	The current status of the session. Possible values include the following: `active`: The session is currently executing a query. `idle`: The session is currently waiting for a new client command. `idle in transaction`: The session is in a transaction, but is not currently executing any task. `idle in transaction (aborted)`: The session is in an aborted transaction that failed to complete, usually because of a statement error. `fastpath function call`: The session is executing a `fast-path` function, such as a `PL/pgSQL` function. `disabled`: The session is disabled, usually due to session limits, such as connection limits.
backend_xid	XID	The top-level transaction identifier of this session, if any.
backend_xmin	XID	The `xmin` horizon of this session.
query	TEXT	The text of the most recent query for this session. If `state` is `active`, this field shows the currently executing query. For other states, it shows the last query that was executed. By default, the query text is truncated to 1024 characters. You can change this using the track_activity_query_size parameter.
backend_type	TEXT	The type of this session. Possible values include the following: `autovacuum launcher`, `autovacuum worker`, `logical replication launcher`, `logical replication worker`, `parallel worker`, `background writer`, `client backend`, `checkpointer`, `startup`, `walreceiver`, `walsender`, and `walwriter`. In addition, background workers registered by plugins may have additional types.

上一篇: Cluster maintenance tasks 下一篇: Monitor disk usage