PostgreSQL troubleshooting系列之六-监控
前言
本文的内容来源于电子书《Troubleshooting PostgreSQL》。那本书虽老，但是里边的内容依然很有参考价值。俺这里尝试对其简译，对某些地方进行修正和补充，希望对学习和深入了解使用PG有帮助。
本文来自原书第7章。
7、PostgreSQL的监控
如果您没有实际跟踪问题所需的信息，则几乎不可能进行故障排除。在进行故障排除时，数据就是一切。没有数据，没有能力看到正在发生的事情，情况实际上是无望的。
本章中主要涉及到下边几方面的内容：
检查数据库总体的行为
检查冲突
寻找IO瓶颈
观察系统内部
找到慢查询
外部工具
7.1 检查数据库的总体行为
访问系统的第一件事就是检查系统的总体健康状态。系统是否使用过多的CPU? 或者说，是否使用了过多的内存？磁盘I/O是否正常？
7.1.1 检查pg_stat_activity
最好是从pg_stat_activity开始，这个系统视图会列出所有的数据库连接。自9.2开始，pg_stat_statement开始一点点扩展，关于数据库连接越来越多的信息可以被看到：
postgres=# \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
usesysid | oid | | |
usename | name | | |
application_name | text | | |
client_addr | inet | | |
client_hostname | text | | |
client_port | integer | | |
backend_start | timestamp with time zone | | |
xact_start | timestamp with time zone | | |
query_start | timestamp with time zone | | |
state_change | timestamp with time zone | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
我们来看看这些列。头两列告诉我们连接的数据库。第一列是数据库的对象ID。第二列是数据库的名字。紧接着是连接的用户的信息。
然后是应用的名字。应用名可以被终端用户或应用程序自由设定。这个方便于应用进行调试。考虑下边的例子：
test=# SET application_name TO 'script.pl:456';
SET
test=# SHOW application_name;
application_name
------------------
script.pl:456
(1 row)
您可以看到，这只是简单的纯文本名，可以包含任意字符串。
后边的三个列（client_*)会告诉您，该连接来源于哪儿。可以检查对应的hostname， port， 等等。
Backend_start和fact_start则告诉您数据库连接或者事务是从什么时候开始的。
注意：关注非常老的和长时间运行的事务。它们会对您的VACUUM政策产生严重影响。请记住，只有当单个事务不再看到一行时，才能清除该行。因此，长时间运行的事务可能会产生不希望看到的副作用。
后边重要的字段是query_start, state, state_change以及query。这几个字段要组合起来看。query_start表示最后一次查询开始的时间。当状态是active时，则到了那个时刻仍然是active的。当状态为idle时，查询将不再active，当state_change设置时，将被终止。所以，query_start和state_change之间的差值是前一个查询的运行时间。
在列waiting之后，是前边讨论过的backend_xid和backend_xmin。这两列可以反映系统的传统行为（事务ID以及其他）。
为避免问题，通常要检查下边的几点：
有没有等待的查询？
有没有造成VACUUM问题的长事务？
是根本没有连接还是有多余的连接？
是否列出了花费(或正在花费)太多时间的查询？
有了这些有价值的探索，您就可以解决一些非常常见的问题。
7.1.2 检索数据库级别的信息
一旦检查完pg_stat_activity, 我们就需要转而注意视图：pg_stat_database。系统的每个数据库它都有一行。
test=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
这里有几个重要的字段。首先是对象ID以及数据库的名字。该视图也提示了当前打开的数据库连接数。同时，也有一些更隐含的信息，使用的并不是很广泛，比如字段：xact_commit以及xact_rollback。它们分别表示启动数据库以来，成功和放弃的事务数。为何这些字段如此重要？在典型的应用中，提交与回滚的比例应该是很稳定的。一个应用程序可能有5%的比例是回滚的命令。如果这个比例突然升至35%或者更高，那意味着什么？极有可能的情况是，某些软件更新在什么地方引起语法错误，进而产生大量回滚。只是那个问题还没有被发现。因此，使用恰当的话，pg_stat_database可能会提问哪个地方出问题了。
接着后边的一些字段告诉我们有多少数据被读取、添加、更新、删除，等等。这些可以看一看，但是通常，它们并不揭示深层的问题。它们通常是通用指南，告诉你系统进展到什么程度。
下一个字段是有关冲突的信息。我们可以在下一节专门介绍。
Temp_files和temp_bytes是两个非常重要的字段。它们告诉我们系统通过临时文件进行了大量的I/O操作。什么时候发生这样操作？如果有大量的数据进行排序之类的操作的时候，就会有临时文件相关的操作。例如：ORDER BY子句、CREATE INDEX或者一些OVER子句引起很重的排序操作。如果work_mem或者maintenance_work_mem指的值不合适，就会导致大量的与临时文件有关的I/O操作。与大量临时间文件I/O操作有关的另一个原因，还有可能是一些简单的 比较糟糕的SQL语句（例如，在结果中返回太多的数据）。
后边还有两个字段提供I/O相关的线索：blk_write_time, blk_read_time。这两个字段的目的，主要用于度量往操作系统读和写数据的时间。缺省情况下，这两个字段并没有被激活。
test=# SHOW track_io_timing;
track_io_timing
-----------------
off
(1 row)
除非track_io_timing被激活，这两个字段不会有任何数据。如果您遇到了I/O问题或者磁盘等待问题，那建议您打开这个选项，用于发现是什么地方造成I/O问题。
请注意，在某些罕见的情况下，计时可能会使性能降低一小部分。然而，在大多数情况下，这种开销是不相关的。
最后，stats_reset字段的值会告诉我们统计数据什么时候会被重置。
7.2 发现I/O瓶颈
关于监控I/O，除了前边说到的track_io_timing命令，可以通过数据库统计视图提供相关线索。我们通过pg_stat_bgwriter视图还可以找到更多有关系统I/O的信息：
test=> \d pg_stat_bgwriter
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
checkpoints_timed | bigint | | |
checkpoints_req | bigint | | |
checkpoint_write_time | double precision | | |
checkpoint_sync_time | double precision | | |
buffers_checkpoint | bigint | | |
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_backend | bigint | | |
buffers_backend_fsync | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
理解I/O系统正在干什么是很重要的。其中就包含了检查点。在PG里头，引发检查点有两个原因：要么是系统的WAL段满了，要么是系统到了checkpoint轮询超时了。pg_stat_writer会告诉你是什么导致检查点被执行。如果所有的检查点都能及时完成，意味着增加checkpoint_timeout没有意义。
checkpoint_write_time用于表示执行检查点时将文件写入磁盘(写入)的时间，而checkpoint_sync_time则是最后刷盘的时间。buffers_checkpoint是在执行检查点时，有多少块被写。而buffers_clean则表示有多少块被bg writer进程写。当bg writer从共享缓冲区里往操作系统写入块时，可能要多个来回。当它因为每轮已经写了太多块而停止时，我们可以在maxwritten_clean中看到。如果bg writer不能跟上写负载，后端(数据库连接)也被允许执行I/O。后端写入的块数量在buffers_backend中计算。有时，后端甚至必须调用fsync来刷新磁盘。这些刷新调用计数在buffers_backend_fsync中。最后是buffers_alloc，它告诉我们分配的缓冲区数量。
使用pg_stat_bgwriter的关键是找出哪个组件在做I/O。一般来说，bg writer应该自己完成大部分的I/O操作。但是，在高负载下，普通数据库连接可能需要自己完成一些工作。在PostgreSQL 9.2中，增加了checkpoint进程，以减少常规bg writer进程的一些工作。checkpoint和bg writer进程都写入相同的文件。然而，checkpoint会在某一时刻(检查点开始时)写入所有脏的数据，而不管它在变脏之后被使用了多少次。另一方面，后台写入器写入最近没有使用过的数据，而不管它是什么时候第一次被污染的。两者都不知道或不关心正在写入的数据是否已提交或回滚，或者是否仍在进行中。
7.3 检查冲突
一旦正确地检查了I/O系统，就有必要停下来一分钟，看看系统中是否存在主要冲突:
test=# \d pg_stat_database_conflicts
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Collation | Nullable | Default
------------------+--------+-----------+----------+---------
datid | oid | | |
datname | name | | |
confl_tablespace | bigint | | |
confl_lock | bigint | | |
confl_snapshot | bigint | | |
confl_bufferpin | bigint | | |
confl_deadlock | bigint | | |
pg_stat_database_conflicts视图告诉我们系统中发生的冲突。该视图实际上列出了许多不同的冲突。然而，并不是所有这些都是相关的。其中最重要的是锁冲突、快照冲突和死锁。如果一个事务正在等待另一个事务完成，就会发生锁定冲突。当事务依赖于一个太旧的快照时，可能会发生快照冲突(这种情况发生在高隔离级别中)。最后是死锁，本书已经对其进行了广泛的介绍。
7.4 寻找慢查询
在检查活动查询、检查I/O问题和锁定之后，看看哪些查询实际上导致了大多数问题可能会很有趣。如果不知道实际的耗时查询，从长远来看很难改进。幸运的是，PostgreSQL提供了一个名为pg_stat_statements的模块，它能够跟踪查询并向管理员提供有关这些查询的统计信息。
要使用这个扩展，首先要启用它：
test=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
注意，该模块必须在数据库中启用。这个模块创建的系统视图目前只存在于这个数据库中。仅仅这样还不行。
test=# SELECT * FROM pg_stat_statements;
ERROR: pg_stat_statements must be loaded via
shared_preload_libraries
该模块实际上是在postmaster启动时加载的。它不能动态加载，因为创建的信息必须在断开连接后仍然存在。
要在启动时加载模块，在postgresql中修改shared_preload_libraries：
shared_preload_libraries = 'pg_stat_statements'
然后必须重新启动数据库。现在系统将自动收集所有信息，并在系统视图中呈现:
test=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
toplevel | boolean | | |
queryid | bigint | | |
query | text | | |
plans | bigint | | |
total_plan_time | double precision | | |
min_plan_time | double precision | | |
max_plan_time | double precision | | |
mean_plan_time | double precision | | |
stddev_plan_time | double precision | | |
calls | bigint | | |
total_exec_time | double precision | | |
min_exec_time | double precision | | |
max_exec_time | double precision | | |
mean_exec_time | double precision | | |
stddev_exec_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
随着版本失衡，这个视图的列也越来越多。
对于每个数据库(dbid)，我们将看到查询及其被调用的次数(调用)。除此之外，还有每个查询所消耗的总时间，以及返回的总行数。在许多情况下，这些信息就足够了:
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY 2 DESC;
这个简单的查询将显示最昂贵的查询及其总运行时间，但还有更多。接下来的两列将告诉我们将要检查的查询的I/O行为。可以检测到与共享缓冲区相关的问题，以及后端本身分配的本地缓冲区的行为。
然后是temp_blks_read和temp_blks_written。这两个字段将告诉我们是否必须将临时数据写入磁盘。
最后，还有关于I/O定时的信息。同样，必须打开track_io_timing以收集此信息。可以检查每个查询在输入和输出上花费了多少时间。
7.4.1 注意总体开销
许多人询问这个模块的开销。事实证明，这很难衡量。实际上是零。在使用该模块时，无需担心严重的性能下降。通常，总是启用pg_stat_statements来跟踪慢速查询是很有用的。这使您能够深入了解服务器上正在发生的事情。
7.4.2 重置数据
要重置pg_stat_statements收集的数据，可以调用pg_stat_ statements_reset():
SELECT pg_stat_statements_reset();
PostgreSQL将重置与pg_stat_statements相关的统计信息。其余的统计数据将保持不变。
7.5 探索内部信息
一旦完成了基本的检查(锁定、慢速查询等)，我们的注意力就可以转向更复杂的检查。
7.5.1 深入检查表相关的信息
正如本书中已经描述的，PostgreSQL使用了一种称为多版本并发控制(MVCC)的机制。这个系统的美妙之处在于，一行的多个版本可以同时存在，这是扩展系统的完美方式。但是，这也意味着如果存在太多行的太多版本，则可能会浪费磁盘上的空间。这个问题通常被称为表膨胀。如果表变得太大，效率太低，就可能出现性能问题。
PG已经创建了pgstattuple模块来检测这些问题。在使用模块之前，它必须被加载:
test=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
基本上，pgstattuple很容易使用。您所要做的就是调用pgstattuple函数并将表名作为参数传递。不返回任何列:
test=# \x
Expanded display is on.
test=# SELECT * FROM pgstattuple('pg_class');
-[ RECORD 1 ]------+-------
table_len | 106496
tuple_count | 400
tuple_len | 77701
tuple_percent | 72.96
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 23632
free_percent | 22.19
我们在这里看到的是pgstattuple返回表的总大小、表内的行数以及它们的大小。然后过两个块; dead_*列为我们提供了关于表中死行的数量和大小的信息。通过运行VACUUM可以很容易地将死行变为空闲空间。
使用pgstattuple的主要挑战是同时为许多表运行它。每次都可以为每个表运行一些东西，但这可能太耗时了。一次对所有表运行它肯定更方便。
为了实现这一点，我们可以使用复合类型。pgstattuple实际上是重载的，不仅可以用表名调用它，还可以使用对象id。现在的主要挑战是获取表列表。这里可以使用一个名为pg_class的系统表。在relkind列中，所有表都标识为r(关系)。然而，最重要的部分是分解pgstattuple返回的数据类型。简单地使用括号和星号就可以了:
SELECT relname, (pgstattuple(oid)).*
FROM pg_class
WHERE relkind = 'r'
ORDER BY table_len DESC;
....
-[ RECORD 2 ]------+------------------------
relname | pg_proc
table_len | 778240
tuple_count | 3214
tuple_len | 722076
tuple_percent | 92.78
dead_tuple_count | 26
dead_tuple_len | 10875
dead_tuple_percent | 1.4
free_space | 18056
free_percent | 2.32
-[ RECORD 3 ]------+------------------------
relname | pg_depend
table_len | 540672
tuple_count | 8860
tuple_len | 434140
tuple_percent | 80.3
这样会把系统中的每张表的信息都会展示出来。
pgstattuple模块是检测表膨胀的完美工具。但是，要小心使用。它必须完全读取表格。因此，过于频繁地使用它并不是一个好主意，因为它会导致繁重的I/O(类似于备份)。
7.5.2 探测I/O缓存
除了查看表内部，还可以检查PostgreSQL I/O缓存(共享缓冲区区域)，以查看缓存的内容和未缓存的内容。在许多情况下，了解I/O缓存的内容可以揭示与磁盘活动过多导致的性能低下相关的问题。
pg_buffercache视图可以被激活来检查缓存:
test=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
就像之前所做的那样，模块必须被激活才能为我们提供信息。同样，提供了系统视图。它包含了所有需要的信息:
test=# \d pg_buffercache
View "public.pg_buffercache"
Column | Type | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
bufferid | integer | | |
relfilenode | oid | | |
reltablespace | oid | | |
reldatabase | oid | | |
relforknumber | smallint | | |
relblocknumber | bigint | | |
isdirty | boolean | | |
usagecount | smallint | | |
pinning_backends | integer | | |
这个特殊视图的主要挑战是，它有点难以阅读。大多数字段的表示方式使得普通用户很难理解实际显示的内容。下面的列表概述了如何将这些字段转换为更可读的内容:
relfilenode： 磁盘上的数据文件的名字。 (
SELECT relname FROM pg_ class WHERE relfilenode = 'this oid';
)reltablespace: 表空间的oid (
SELECT spcname FROM pg_tablespace WHERE oid = 'this oid';
)reldatabase: 数据库的oid (
SELECT datname FROM pg_ database WHERE oid = 'this oid';
)
使用这些SELECT语句，可以将这些数字转换为可读的名称。
关于ID与对象名称的转换，我还专门写了一篇短文，可能 参考：
test=# select bufferid, relfilenode::regclass as relname, reltablespace, reldatabase, usagecount from pg_buffercache where relfilenode::regclass::varchar='pg_class' limit 2;
-[ RECORD 1 ]-+---------
bufferid | 3
relname | pg_class
reltablespace | 1663
reldatabase | 16554
usagecount | 5
-[ RECORD 2 ]-+---------
bufferid | 4
relname | pg_class
reltablespace | 1663
reldatabase | 16554
usagecount | 5
下一列是relforknumber(它是数据文件、可见性映射或自由空间映射的缓存块)，最后是缓存表中块的编号。这个字段允许您查看表的哪些区域在RAM中，哪些不在RAM中。
最后两列告诉我们该块是否脏，以及它被固定的频率。给定所有这些字段，您可以自己创建一个分析，返回您所需要的数据。
网络上还有很多有用的模块。然而，根据我的判断，这一章中的模块是最重要的模块。它们也随PostgreSQL核心发行版一起发布。
7.6 与外部工具集成
一般来说，当想到监视时，大多数人会立即想到一些监视工具，如Nagios、Zabbix等。虽然监控工具很重要，但我希望本章已经证明，监控不仅仅是监控工具和漂亮的图表。
尽管如此，在本节中，我们的目标是在工具的帮助下指导您了解一些通常用于监视的模块。本文并不是对Nagios和其他工具的介绍，而是一个指南，告诉您可以使用哪些工具。
7.6.1 使用Nagios插件
可以与Nagios结合使用的最广泛使用的插件是Burcado项目开发人员提供的插件: http://bucardo.org/wiki/Check_postgres
也可以使用这些插件与Shinken和Tanto。
它提供了适合监视不同类型实例的各种检查。这个插件的美妙之处在于，它不仅可以生成Nagios的监控数据，还可以生成MRTG(多路由器流量绘图器:http://oss)所需的输出。oetiker.ch/mrtg/doc/mrtg.en.html)以及Cacti (http://www.cacti.net/)。Cacti是一个很好的简单的图形工具，人们可以用它来显示系统中正在发生的事情。如果您不愿意深入研究Nagios, Cacti是一个不错的选择，它以一种简单的方式为您提供所需的内容。
Nagios插件提供哪些检查在一定程度上取决于所运行的服务器类型。这里有一些我最喜欢的检查，它们几乎可以应用于任何类型的设置:
后端:这告诉你打开的数据库连接的数量
commitratio:检查所有数据库的提交比率和抱怨太低的时候
custom_query:这是最强大的检查，因为它允许您运行能够返回任何所需输出的通用查询
database_size:返回数据库大小
dbstats:返回关于数据库活动的高级信息
locks:检查一个或多个数据库上的锁总数
query_time:检查在一个或多个数据库上运行查询的长度
wal_files:检查pg_wal目录中存在多少个WAL文件
当然，根据您的应用程序，可能需要不同或额外的检查。然而，这个选择是一个很好的开始。
7.7 其它可用工具
如果您更喜欢其他工具而不是Nagios或Cacti，那么也有合适的插件。根据您喜欢的工具，您甚至可能需要使用自己的插件来完成您的设置工作。
7.7.1 Zabbix 插件
Zabbix插件名为pg_monz。关于这个插件的更多信息可以在http://pg-monz.github.io/pg_monz/index-en.html上找到。它基本上提供了将Zabbix与PostgreSQL集成并获取所有重要信息所需的一切。
7.7.2 pgznalhyze-collector
pganalysis -collector是一个命令行工具，用于收集有关Postgres数据库的信息以及在这些数据库上执行的查询。所有数据都被转换为JSON (JavaScript Object Notation)，可以由您选择的工具用于处理。
该模块创建有关CPU、内存、RAM、表、模式、数据库、查询等的信息。这个模块可以从https://github.com/pganalyze/pganalyze-collector下载。
7.7.3 pg_view
pg_view是另一个可以用来监视PostreSQL的工具。pg_view是由德国一家名为Zalando的大型零售公司开发的，它可以免费使用。它本质上是一个命令行工具，在一个简单的基于命令行的GUI上显示简明的信息。
有关此工具的更多信息可在https://github.com/zalando/pg_视图中找到。
[笔者注: ]
上边这些插件都是差不多10年以前的情形了。经过这么多年发展，可用的监控插件就太丰富了。PowA， grafana+prometheus+pg_exporter, ….. 我们完全可以根据自己的需要，选择合适易用的插件去搭建相应的监控平台。
7.8 总结
本章介绍了监控。介绍了一些板载工具，如pgstattuple、pg_buffercache和pg_stat_activity。除此之外，还介绍了一些可以与PostgreSQL一起使用的外部工具和插件。
参考：
《Troubleshooting PostgreSQL》第7章
