本章介绍查看数据库在干什么的方法。
27.1 标准Unix工具
ps:
格式:postgres: user database host activity
$ ps auxww | grep ^postgres
postgres 15551 0.0 0.1 57536 7132 pts/0 S 18:02 0:00 postgres -i
postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: background writer
postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer
postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: walwriter
postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher
postgres 15558 0.0 0.0 17512 1068 ? Ss 18:02 0:00 postgres: stats collector
postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle
postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting
postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction
# 上例中,15606等待15610释放锁(因为没有其他活动会话),复杂的锁定信息,可查看pg_locks系统视图。
27.2 统计信息收集进程
PostgreSQL的统计信息收集进程(statistics collector)为收集和报告数据库活动信息的进程。当前,该进程可以统计表和索引的访问信息。还可跟踪每个表的总函数及对表的vacuum和analyze操作。还可以统计对用户自定义函数的调用及其耗时。
PostgreSQL还提供关于当前数据库状态的动态信息,例如当前执行的命令、系统当前连接会话等。不过独立于收集器进程。
27.2.1 统计信息收集配置
可配置是否收集统计信息。相关参数:track_activities、track_counts、track_functions、track_io_timing。
27.2.2 查看统计信息
表27.1列出了查看数据库系统的当前状态的视图;表27.2列出了查看统计信息收集结果的视图。
27.2.3. pg_stat_activity
简单示例:
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 | ProcArray
(2 rows)
27.2.4. pg_stat_replication
pg_stat_replication每行为一个WAL发送进程。
27.2.5. pg_stat_wal_receiver
该视图仅一行,展示WAL接收进程的状态。
27.2.6. pg_stat_subscription
该视图为每个主订阅进程存储一行,以及处理初始数据的进程行。
27.2.7. pg_stat_ssl
该视图每个后台进程或WAL发送进程一行,展示当前连接的SSL使用统计。
27.2.8. pg_stat_gssapi
该视图每个后台进程一行,展示连接中GSSAPI的使用情况。
27.2.9. pg_stat_archiver
该视图仅一行,用以展示归档进程信息。
27.2.10. pg_stat_bgwriter
该视图仅一行,含群集的全局数据。
27.2.11. pg_stat_database
该视图中,群集中每个数据库有一行以及记录共享对象,展示数据库级别的统计信息。
27.2.12. pg_stat_database_conflicts
该视图展示数据库级别因备库恢复冲突而导致的查询失败或取消。该视图仅包含备库信息,因为主节点不会发生此类问题。
27.2.13. pg_stat_all_tables
该视图包含当前数据库中每个表的访问统计信息(含TOAST表)。pg_stat_user_tables和pg_stat_sys_tables与该视图包含的信息相同,不过一个限定到用户表,另一个限定系统表。
27.2.14. pg_stat_all_indexes
该视图包含当前数据库中的索引访问统计信息。pg_stat_user_indexes和pg_stat_sys_indexes视图包含相同信息,不过一个限定到用户索引,另一个限定到系统索引。
27.2.15. pg_statio_all_tables
该视图中当前数据库每个表一行(含TOAST表),展示指定表的I/O统计信息。pg_statio_user_tables和pg_statio_sys_tables含相同信息,不过一个限定于用户表,一个限定于系统表。
27.2.16. pg_statio_all_indexes
该视图为当前数据库每个索引一行,展示指定索引的I/O统计信息。pg_statio_user_indexes和pg_statio_sys_indexes视图含相同信息,不过限定于用户索引和系统索引。
27.2.17. pg_statio_all_sequences
该视图含当前数据库每个序列一行,展示指定序列的I/O统计信息。
27.2.18. pg_stat_user_functions
该视图包含每个跟踪函数一行,展示函数的执行统计信息。track_functions参数控制跟踪的特定函数。
27.2.19. pg_stat_slru
该视图包含每个跟踪的SLRU(simple least-recently-used)缓存一行,展示访问缓存页的统计信息。
27.2.20 统计信息函数
可使用底层函数自定义对统计信息的查看。可使用\d+查看具体使用的底层函数名。
postgres=# \d+ pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------+--------------------------+-----------+----------+---------+----------+-------------
datid | oid | | | | plain |
datname | name | | | | plain |
pid | integer | | | | plain |
leader_pid | integer | | | | plain |
usesysid | oid | | | | plain |
usename | name | | | | plain |
application_name | text | | | | extended |
client_addr | inet | | | | main |
client_hostname | text | | | | extended |
client_port | integer | | | | plain |
backend_start | timestamp with time zone | | | | plain |
xact_start | timestamp with time zone | | | | plain |
query_start | timestamp with time zone | | | | plain |
state_change | timestamp with time zone | | | | plain |
wait_event_type | text | | | | extended |
wait_event | text | | | | extended |
state | text | | | | extended |
backend_xid | xid | | | | plain |
backend_xmin | xid | | | | plain |
query | text | | | | extended |
backend_type | text | | | | extended |
View definition:
SELECT s.datid,
d.datname,
s.pid,
s.leader_pid,
s.usesysid,
u.rolname AS usename,
s.application_name,
s.client_addr,
s.client_hostname,
s.client_port,
s.backend_start,
s.xact_start,
s.query_start,
s.state_change,
s.wait_event_type,
s.wait_event,
s.state,
s.backend_xid,
s.backend_xmin,
s.query,
s.backend_type
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, leader_pid)
LEFT JOIN pg_database d ON s.datid = d.oid
LEFT JOIN pg_authid u ON s.usesysid = u.oid;
postgres=#
27.3 查看锁
pg_locks查看锁信息。
27.4 进度报告
PostgreSQL可以提供命令执行期间进程的报告。当前,仅支持ANALYZE,CLUSTER,CREATE INDEX,VACUUM和BASE_BACKUP。未来会扩展。
27.4.1 ANALYZE进度报告
执行ANALYZE的时候,pg_stat_progress_analyze视图会展示当前执行该命令的后台进程信息。
27.4.2 CREATE INDEX进度报告
可通过pg_stat_progress_icreate_index视图查看后台执行的CREATE INDEX或REINDEX命令。
27.4.3 VACUUM进度报告
VACUUM运行的时候,可通过pg_stat_progress_vacuum视图查看后台执行的进程。VACUUM FULL通过pg_stat_progress_cluster查看。
27.4.4 CLUSTER进度报告
CLUSTER或VACUUM FULL运行时,可通过查询pg_stat_progress_cluster视图查看进度。
27.4.5 Base Backup进度报告
在进行基础备份时(例如使用pg_basebackup),可检索pg_stat_progress_basebackup查看当前运行BASE_BACKUP复制命令和流备份的WAL发送进程。
27.5 动态跟踪
PostgreSQL提供对数据库服务的动态跟踪。支持DTrace。
27.5.1 编译以支持动态跟踪
默认,探针不可用,需要在编译时指定。比如通过在配置时指定configure --enable-dtrace以包含DTrace支持。
27.5.2 内置探针
源码中包含了多种探针,查看表27.42;表27.43展示了探针中使用的类型。
27.5.3 使用探针
以下示例使用DTrace分析系统中的事务数:
#!/usr/sbin/dtrace -qs
postgresql$1:::transaction-start
{
@start["Start"] = count();
self->ts = timestamp;
}
postgresql$1:::transaction-abort
{
@abort["Abort"] = count();
}
postgresql$1:::transaction-commit
/self->ts/
{
@commit["Commit"] = count();
@time["Total time (ns)"] = sum(timestamp - self->ts);
self->ts=0;
}
输出示例:
# ./txn_count.d `pgrep -n postgres` or ./txn_count.d <PID>
^C
Start 71
Commit 70
Total time (ns) 2312105013
27.5.4 定义新的探针
可在源码定义新的探针,不过需要重新编译。插入新探针的步骤:
-
定义探针名及使用的数据;
-
添加探针定义至src/backend/utils/probes.d;
-
如果pg_trace.h在包含探测点的模块中尚不存在,则将其包含在内,并在源代码中的所需位置插入TRACE_POSTGRESQL探测宏;
-
重新编译并确认新探针可用;