Alexey Lesovsky 2015 年报告的转录“深入了解 PostgreSQL 内部统计数据”
报告作者的免责声明： 我注意到这份报告的日期是 2015 年 4 月——9.4 年多过去了，时间已经过去了很多。 不再支持报告中讨论的版本 4。 在过去的 5 年中，发布了 XNUMX 个新版本，其中出现了许多关于统计的创新、改进和变化，并且一些材料已经过时且不相关。 在我回顾时，我试图标记这些地方，以免误导读者。 我没有改写这些地方，有很多，结果会变成完全不同的报告。
PostgreSQL DBMS是一个庞大的机制，这个机制由许多子系统组成，它们的协调工作直接影响到DBMS的性能。 在运行过程中，收集有关组件运行的统计信息和信息，这使您可以评估 PostgreSQL 的有效性并采取措施来提高性能。 但是，此类信息很多，并且以相当简化的形式呈现。 处理和解释这些信息有时是一项非常重要的任务，工具和实用程序的“动物园”甚至很容易让高级 DBA 感到困惑。
下午好我的名字是阿列克谢。 正如 Ilya 所说，我将讨论 PostgreSQL 统计信息。
PostgreSQL 活动统计。 PostgreSQL 有两个统计数据。 将讨论的活动统计信息。 以及有关数据分布的调度程序统计信息。 我将专门讨论 PostgreSQL 活动统计信息，它使我们能够判断性能并以某种方式改进它。
我将告诉您如何有效地使用统计数据来解决您遇到或可能遇到的各种问题。
什么不会出现在报告中？ 在报告中，我不会触及调度程序的统计信息，因为。 这是一个单独的主题，用于单独报告数据如何存储在数据库中以及查询规划器如何了解此数据的定性和定量特征。
而且不会有工具评论，我不会将一种产品与另一种产品进行比较。 不会有广告。 让我们放下这个。
我想告诉你使用统计数据是有用的。 有必要。 无所畏惧地使用它。 我们所需要的只是简单的 SQL 和 SQL 的基本知识。
我们将讨论选择哪些统计数据来解决问题。
如果我们查看 PostgreSQL 并在操作系统上运行命令来查看进程，我们将看到一个“黑框”。 我们会看到一些进程在做一些事情，通过名字我们可以大致想象出它们在那里做什么，它们在做什么。 但是，实际上，这是一个黑盒子，我们无法查看内部。
我们可以查看 CPU 负载 top，我们可以看到一些系统实用程序的内存利用率，但我们无法查看 PostgreSQL 内部。 为此，我们需要其他工具。
继续下去，我会告诉你时间花在了哪里。 如果我们把PostgreSQL用这样一个scheme的形式来表示，那么就可以回答时间花在了哪里。 这是两件事：它是对来自应用程序的客户端请求的处理，以及 PostgreSQL 为保持其运行而执行的后台任务。
如果我们开始查看左上角，我们可以看到如何处理客户端请求。 该请求来自应用程序，并打开一个客户端会话以进行进一步的工作。 请求被传递给调度程序。 规划器构建查询计划。 进一步发送执行。 有某种与表和索引相关联的块 I/O 数据。 必要的数据从磁盘读取到称为“共享缓冲区”的特殊区域的内存中。 查询结果，如果是updates，deletes，都会记录在WAL中的事务日志中。 一些统计信息进入日志或统计收集器。 并将请求的结果返回给客户端。 之后，客户端可以用新请求重复所有操作。
我们有什么后台任务和后台进程？ 我们有几个进程可以保持数据库正常运行。 这些进程也将在报告中涵盖：这些是 autovacuum、检查点、与复制相关的进程、后台编写器。 我将在报告时逐一提及。
统计学存在哪些问题？
- 很多信息。 PostgreSQL 9.4 提供了 109 个指标用于查看统计数据。 但是，如果数据库存储了很多表、模式、数据库，那么所有这些指标都必须乘以相应的表、数据库数量。 也就是说，还有更多的信息。 而且很容易淹死在里面。
- 下一个问题是统计数据由计数器表示。 如果我们查看这些统计数据，我们将看到不断增加的计数器。 如果自重置统计数据以来已经过去了很长时间，我们将看到数十亿个值。 他们什么也没告诉我们。
- 没有历史。 如果您遇到某种故障，例如 15-30 分钟前有东西掉落，您将无法使用统计信息查看 15-30 分钟前发生了什么。 这是问题。
- 缺少内置于 PostgreSQL 中的工具是一个问题。 内核开发人员不提供任何实用程序。 他们没有那样的东西。 他们只是在数据库中提供统计数据。 使用它，向它提出请求，无论你想要什么，然后去做。
- 由于 PostgreSQL 中没有内置工具，这会导致另一个问题。 很多第三方工具。 每家或多或少有直接人手的公司都在尝试编写自己的程序。 因此，社区有很多工具可以用来处理统计数据。 在某些工具中有一些功能，在其他工具中没有其他功能，或者有一些新功能。 并且出现一种情况，您需要使用两个、三个或四个相互重叠且功能不同的工具。 这很烦人。
随之而来的是什么？ 重要的是能够直接进行统计以便不依赖于程序，或者自己以某种方式改进这些程序：添加一些功能以获得您的好处。
并且您需要 SQL 的基本知识。 要从统计中获取一些数据，您需要进行 SQL 查询，即您需要知道如何进行 select、join。
统计数据告诉我们几件事。 它们可以分为几类。
- 第一类是数据库中发生的事件。 这是在数据库中发生某些事件时：查询、表访问、autovacuum、提交，然后这些都是事件。 与这些事件对应的计数器递增。 我们可以跟踪这些事件。
- 第二类是表、数据库等对象的属性。 他们有财产。 这是表格的大小。 我们可以跟踪表的增长，索引的增长。 我们可以看到动态变化。
- 第三类是花在事件上的时间。 请求是一个事件。 它有自己特定的持续时间度量。 在这里开始，在这里结束。 我们可以跟踪它。 从磁盘读取块或写入的时间。 这些东西也被跟踪。
统计来源如下：
- 在共享内存（shared buffer）中有一段用于放置静态数据的段，还有那些在某些事件发生时不断递增的计数器，或者在数据库运行的某些时刻出现的计数器。
- 所有这些计数器都对用户不可用，甚至对管理员也不可用。 这些都是低级的东西。 为了访问它们，PostgreSQL 提供了一个 SQL 函数形式的接口。 我们可以使用这些函数进行选择并获得某种指标（或指标集）。
- 然而，使用这些函数并不总是很方便，所以函数是视图（VIEWs）的基础。 这些是提供有关特定子系统或数据库中某些事件集的统计信息的虚拟表。
- 这些内置视图 (VIEW) 是处理统计数据的主要用户界面。 它们默认可用，无需任何额外设置，您可以立即使用它们、观看、从那里获取信息。 还有贡献。 贡献是官方的。 你可以安装postgresql-contrib包（例如postgresql94-contrib），在配置中加载必要的模块，为其指定参数，重启PostgreSQL就可以使用了。 （笔记。 根据发行版，在最新版本的 contrib 中，该包是主包的一部分).
- 还有非官方的贡献。 它们不随标准 PostgreSQL 发行版一起提供。 它们必须作为库进行编译或安装。 选项可能非常不同，具体取决于此非官方贡献的开发人员提出的内容。
这张幻灯片显示了所有这些视图 (VIEW) 以及 PostgreSQL 9.4 中可用的一些函数。 正如我们所见，它们有很多。 如果您是第一次体验，很容易感到困惑。
但是，如果我们拿之前的照片 Как тратится время на PostgreSQL 并与此列表兼容，我们得到这张照片。 当我们运行 PostgreSQL 时，每个视图 (VIEW) 或每个函数都可以用于一个或另一个目的以获得适当的统计信息。 并且我们已经可以得到一些关于子系统运行的信息。
我们首先要看的是 pg_stat_database. 正如我们所看到的，这是一个表示。 它包含很多信息。 最多样化的信息。 它提供了关于我们在数据库中发生的事情的非常有用的知识。
我们能从那里得到什么？ 让我们从最简单的事情开始。
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;我们首先要看的是缓存命中率。 缓存命中率是一个有用的指标。 它允许您估计从共享缓冲区缓存中获取了多少数据，以及从磁盘读取了多少数据。
很清楚 我们的缓存命中率越高越好. 我们将此指标评估为百分比。 并且，例如，如果我们的这些缓存命中率大于 90%，那么这很好。 如果它下降到 90% 以下，那么我们就没有足够的内存来将数据的热头保存在内存中。 为了使用这些数据，PostgreSQL 被迫访问磁盘，这比从内存中读取数据要慢。 你需要考虑增加内存：要么增加共享缓冲区，要么增加铁内存（RAM）。
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;从这个演示文稿中还能得到什么？ 您可以看到数据库中发生的异常。 这里显示了什么？ 有提交、回滚、临时文件的创建、它们的大小、死锁和冲突。
我们可以使用这个请求。 这个 SQL 非常简单。 我们可以亲眼看到这些数据。
这是阈值。 我们查看提交和回滚的比率。 Commits是交易成功的确认。 Rollbacks 是回滚，即事务做了一些工作，拉紧了数据库，考虑了一些事情，然后失败了，事务的结果被丢弃了。 IE。 回滚的数量不断增加是不好的。 你应该以某种方式避免它们，并编辑代码以防止这种情况发生。
冲突与复制有关。 他们也应该避免。 如果您有一些查询在副本上执行并且出现冲突，那么您需要分析这些冲突并查看会发生什么。 可以在日志中找到详细信息。 并解决冲突，使应用程序请求无误地工作。
死锁也是一种糟糕的情况。 当请求竞争资源时，一个请求访问了一个资源并获得了锁，第二个请求访问了第二个资源也获得了锁，然后两个请求都访问了对方的资源并阻塞等待邻居释放锁。 这也是一个有问题的情况。 它们需要在重写应用程序和序列化资源访问级别上解决。 而如果你看到你的死锁在不断增加，你需要查看日志中的详细信息，分析出现的情况，看看问题出在哪里。
临时文件 (temp_files) 也不好。 当用户请求没有足够的内存来容纳操作的临时数据时，它会在磁盘上创建一个文件。 他可以在内存中的临时缓冲区中执行的所有操作已经开始在磁盘上执行。 它很慢。 这会增加查询执行时间。 向 PostgreSQL 发送请求的客户端稍后会收到响应。 如果所有这些操作都在内存中执行，Postgres 的响应速度会更快，客户端等待的时间也会更少。
pg_stat_bgwriter - 这个视图描述了两个 PostgreSQL 后台子系统的操作： checkpointer и background writer.
首先，让我们分析所谓的控制点。 checkpoints. 什么是检查点？ 检查点是事务日志中的一个位置，指示日志中提交的所有数据更改已成功与磁盘上的数据同步。 根据工作负载和设置，该过程可能会很长，并且主要包括将共享缓冲区中的脏页与磁盘上的数据文件同步。 它是做什么用的？ 如果 PostgreSQL 一直访问磁盘并从那里获取数据，并在每次访问时写入数据，它会很慢。 因此，PostgreSQL 有一个内存段，内存段的大小取决于配置中的参数。 Postgres 在这块内存中分配操作数据以供进一步处理或查询。 在数据更改请求的情况下，它们会被更改。 我们得到了两个版本的数据。 一个在内存中，另一个在磁盘上。 并且您需要定期同步此数据。 我们需要将内存中的更改同步到磁盘。 这需要一个检查点。
检查点遍历共享缓冲区，标记检查点所需的脏页。 然后它开始第二次通过共享缓冲区。 而标记为检查点的页面，他已经同步了。 因此，数据已经与磁盘同步。
有两种类型的控制点。 一个检查点在超时时执行。 这个检查点很有用，很好—— checkpoint_timed. 并且有按需检查站 - checkpoint required. 当我们有一个非常大的数据记录时，就会出现这样的检查点。 我们记录了很多交易日志。 而 PostgreSQL 认为它需要尽快同步这一切，做一个检查点并继续前进。
如果你查看统计数据 pg_stat_bgwriter 看看你有什么 checkpoint_req 比 checkpoint_timed 大很多，那就不好了。 为什么不好？ 这意味着 PostgreSQL 在需要将数据写入磁盘时一直处于压力之下。 超时检查点压力较小，并且根据内部计划执行，并且随着时间的推移而延长。 PostgreSQL 有能力暂停工作而不会使磁盘子系统紧张。 这对 PostgreSQL 很有用。 并且在检查点期间执行的请求不会因为磁盘子系统繁忙而受到压力。
调整checkpoint的参数有XNUMX个：
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
它们允许您控制控制点的操作。 但我不会详述它们。 他们的影响是一个单独的问题。
注意事项： 报告中考虑的版本 9.4 不再相关。 在现代版本的 PostgreSQL 中，参数 checkpoint_segments 由参数替换 min_wal_size и max_wal_size.
下一个子系统是后台编写器 - background writer. 他在做什么？ 它在无限循环中不断运行。 它将页面扫描到共享缓冲区并将找到的脏页面刷新到磁盘。 这样，它有助于检查点在检查点期间做更少的工作。
他还需要什么？ 如果突然（大量且立即）需要共享缓冲区来容纳数据，它会提供对共享缓冲区中干净页面的需求。 假设当请求需要干净的页面并且它们已经在共享缓冲区中时出现这种情况。 Postgres backend 他只是拿走它们并使用它们，他不需要自己清理任何东西。 但是如果突然没有这样的页面，后端会暂停并开始搜索页面以将它们刷新到磁盘并根据自己的需要使用它们 - 这会对当前执行请求的时间产生负面影响。 如果你看到你有一个参数 maxwritten_clean 大，这意味着后台编写器没有完成它的工作，你需要增加参数 bgwriter_lru_maxpages这样他就可以在一个周期内做更多的工作，清理更多的页面。
另一个非常有用的指标是 buffers_backend_fsync. 后端不执行 fsync 因为它很慢。 他们将 fsync 向上传递到 IO 堆栈检查点。 检查点有自己的队列，它定期处理 fsync 并将内存中的页面与磁盘上的文件同步。 如果检查点队列又大又满，则后端被迫自己执行 fsync，这会减慢后端速度，即客户端将比它可以收到的响应晚。 如果您发现此值大于零，那么这已经是一个问题并且 您需要注意后台写入器的设置，并评估磁盘子系统的性能。
注意事项： _以下文本描述了与复制相关的统计视图。 大多数视图和函数名称已在 Postgres 10 中重命名。重命名的本质是替换 xlog 上 wal и location 上 lsn 在函数/视图名称等中具体例子，函数 pg_xlog_location_diff() 被重命名为 pg_wal_lsn_diff()._
我们这里也有很多。 但我们只需要与位置相关的项目。
如果我们看到所有的值都相等，那么这是理想的，副本不会落后于主人。
这里的这个十六进制位置就是事务日志中的位置。 如果数据库中有一些活动，它会不断增加：插入、删除等。
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());如果这些东西不同，那么就会有某种滞后。 滞后是副本与主服务器的滞后，即服务器之间的数据不同。
延迟的原因有以下三个：
- 无法处理文件同步写入的是磁盘子系统。
- 当数据没有时间到达副本并且无法复制时，这些可能是网络错误或网络过载。
- 还有处理器。 处理器是一个非常罕见的案例。 我已经见过两三次，但这也可能发生。
这是三个允许我们使用统计信息的查询。 我们可以估计在我们的事务日志中记录了多少。 有这么一个功能 pg_xlog_location_diff 我们可以估计以字节和秒为单位的复制延迟。 为此，我们还使用来自该视图 (VIEW) 的值。
注： _代替 pg_xlog_locationdiff() 函数，您可以使用减法运算符并将一个位置从另一个位置减去。 舒服的。
有一个延迟，以秒为单位，有一个时刻。 如果主服务器上没有活动，事务在大约 15 分钟前就在那里并且没有活动，如果我们查看副本上的延迟，我们会看到 15 分钟的延迟。 这是值得记住的。 当您看到这种滞后时，它可能会导致昏迷。
pg_stat_all_tables 是另一个有用的视图。 它显示表的统计信息。 当我们在数据库中有表时，它有一些活动，一些动作，我们可以从这个视图中获取这些信息。
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;我们首先要看的是顺序表扫描。 这些段落之后的数字本身不一定是坏的，并不表示我们已经需要做一些事情。
然而，还有第二个指标——seq_tup_read。 这是从顺序扫描返回的行数。 如果平均数超过 1、000、10、000，那么这已经表明您可能需要在某处建立索引以便按索引进行访问，或者可以优化使用此类顺序扫描的查询，以便这不会发生。
一个简单的例子 - 假设一个具有大 OFFSET 和 LIMIT 的请求是值得的。 例如，扫描一个表中的 100 行，然后获取 000 行所需的行，并丢弃之前扫描的行。 这也是一个糟糕的案例。 并且需要优化此类请求。 这是一个如此简单的 SQL 查询，您可以在其中查看它并评估接收到的数字。
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;也可以使用此表并使用其他功能获得表大小 pg_total_relation_size(), pg_relation_size().
一般来说，有元命令 dt и di，您可以在 PSQL 中使用它，还可以查看表和索引大小。
但是，函数的使用帮助我们看表的大小，甚至考虑索引，或者不考虑索引，并且已经根据数据库的增长做出一些估计，即它如何与我们一起增长，与什么强度，并且已经得出一些关于尺寸优化的结论。
写活动。 什么是记录？ 来看看操作 UPDATE – 更新表中行的操作。 实际上，更新是两个操作（甚至更多）。 这是插入一个新的行版本并将旧的行版本标记为过时。 稍后，autovacuum 会来清理这些过时版本的行，将这个地方标记为可重用。
此外，更新不仅仅是更新表。 它仍然是索引更新。 如果你的表有很多索引，那么用update的话，查询中更新的字段参与的所有索引也需要更新。 这些索引还将包含需要清理的过时行版本。
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;由于其设计，UPDATE 是一个重量级的操作。 但它们可以变得更容易。 吃 hot updates. 它们出现在 PostgreSQL 8.3 版中。 这是什么？ 这是一个轻量级更新，不会导致重建索引。 也就是说，我们更新了记录，但只更新了页中的记录（属于表），索引仍然指向页中的同一条记录。 有一点这样有趣的工作逻辑，当真空来临时，它就会有这些链条 hot 重建并且一切都在不更新索引的情况下继续工作，并且一切都在减少资源浪费的情况下发生。
当你有 n_tup_hot_upd 很大，很好。 这意味着轻量级更新盛行，这对我们来说在资源方面更便宜，一切都很好。
ALTER TABLE table_name SET (fillfactor = 70);如何增加音量 hot update欧？ 我们可以用 fillfactor. 当使用 INSERT 填充表中的页面时，它确定保留的可用空间的大小。 当插入到表中时，它们会完全填满页面，不要在其中留下空白空间。 然后突出显示一个新页面。 数据被重新填入。 这是默认行为，fillfactor = 100%。
我们可以将填充因子设置为 70%。 也就是说，通过插入，分配了一个新页面，但仅填充了该页面的 70%。 我们还有 30% 的储备。 当您需要进行更新时，它很可能会发生在同一页上，并且该行的新版本将适合同一页。 hot_update 将完成。 这使得在表格上书写更容易。
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));自动清理队列。 Autovacuum 就是这样一个子系统，在 PostgreSQL 中对它的统计非常少。 我们只能在 pg_stat_activity 中的表中看到目前有多少真空。 但是，很难了解他在旅途中排队的桌子数。
注： _从 Postgres 10 开始，跟踪 vacuum vacuum 的情况有了很大的改善——出现了 pg_stat_progress 视图vacuum，这大大简化了 autovacuum 监控的问题。
我们可以使用这个简化的查询。 我们可以看到何时应该抽真空。 但是，真空应该如何以及何时开始？ 这些是我之前谈到的字符串的旧版本。 已发生更新，已插入新版本的行。 已出现字符串的过时版本。 桌子 pg_stat_user_tables 有这样一个参数 n_dead_tup. 它显示“死”行的数量。 一旦死行的数量超过某个阈值，自动清理就会出现在桌面上。
这个阈值是如何计算的？ 这是表中总行数的一个非常具体的百分比。 有个参数 autovacuum_vacuum_scale_factor. 它定义了百分比。 假设 10% + 有一个额外的 50 行基本阈值。 会发生什么？ 当我们的死行多于表中所有行的“10% + 50”时，我们将表置于 autovacuum 上。
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));但是，有一点。 参数的基本阈值 av_base_thresh и av_scale_factor 可以单独分配。 因此，阈值将不是全局的，而是针对表的。 因此，要进行计算，您需要使用技巧和技巧。 如果您有兴趣，可以看看我们来自 Avito 的同事的经验（幻灯片上的链接无效，已在文中更新）。
他们为 考虑到这些事情。 两张床单上有一块脚布。 但他正确且相当有效地考虑让我们能够评估我们需要为桌子提供大量真空的地方，以及几乎没有的地方。
我们对于它可以做些什么呢？ 如果队列很长而 autovacuum 无法应对，那么我们可以增加 vacuum worker 的数量，或者简单地让 vacuum 更积极以便它更早触发，以小块的形式处理表格。 因此队列将减少。 - 这里的主要事情是监控磁盘上的负载，因为。 真空不是免费的，尽管随着 SSD / NVMe 设备的出现，这个问题变得不那么明显了。
pg_stat_all_indexes 是索引的统计信息。 她并不大。 并且我们可以从中获取索引的使用信息。 例如，我们可以确定我们有哪些索引是额外的。
正如我已经说过的， update 不仅更新表，还更新索引。 相应地，如果我们的表有很多索引，那么在更新表中的行时，索引字段的索引也需要更新，并且 如果我们有没有索引扫描的未使用索引，那么它们就会作为镇流器挂在我们身边。 你需要摆脱它们。 为此，我们需要一个字段 idx_scan. 我们只看索引扫描的次数。 如果索引在相对较长的统计存储期间（至少 2-3 周）零扫描，那么这些索引很可能是坏索引，我们需要摆脱它们。
注： 在流复制集群的情况下查找未使用的索引时，需要检查集群的所有节点，因为统计信息不是全局的，如果索引不在主服务器上使用，那么它可以在副本上使用（如果有负载）。
两个链接：
这些是有关如何查找未使用索引的更高级查询示例。
第二个链接是一个相当有趣的查询。 其中有一个非常重要的逻辑。 我推荐它进行审查。
指标还应该总结什么？
-
未使用的索引是不好的。
-
它们占用空间。
-
减慢更新操作。
-
真空的额外工作。
如果我们删除未使用的索引，那么我们只会让数据库变得更好。
下一个视图是 pg_stat_activity. 这是实用程序的模拟 ps，仅在 PostgreSQL 中。 如果 ps'哦，你观察操作系统中的进程，然后 pg_stat_activity 将向您展示 PostgreSQL 内部的活动。
我们能从那里得到什么？
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;我们可以看到数据库中发生的整体活动。 我们可以进行新的部署。 一切都在那里爆炸，不接受新连接，应用程序中不断出现错误。
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;我们可以运行这样的查询并查看相对于最大连接限制的总连接百分比，并查看我们拥有最多连接的人。 在这种情况下，我们看到那个用户 cron_role 打开了 508 个连接。 他发生了一些事情。 你需要处理它然后看看。 这很可能是某种异常数量的连接。
如果我们有一个 OLTP 负载，查询应该很快，非常快，并且不应该有很长的查询。 但是，如果有长期请求，那么短期内没有什么可担心的，但是 从长远来看，长查询会损害数据库，当出现表碎片时，它们会增加表的膨胀效果。 膨胀和长查询都需要处理。
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;请注意：有了这样的请求，我们可以定义长请求和事务。 我们使用函数 clock_timestamp() 来确定工作时间。 我们发现的长请求，我们可以记住它们，执行它们 explain，查看计划并以某种方式进行优化。 我们拍摄当前的长期请求并继续存在。
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';坏事务在事务中空闲，在事务中空闲（中止）事务。
这是什么意思？ 事务有多个状态。 这些状态之一可以随时采取。 有一个字段来定义状态 state 在这个视图中。 我们用它来确定状态。
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';而且，正如我上面所说，这两个状态 idle in transaction 和 idle in transaction (aborted) 是不好的。 这是什么？ 这是应用程序打开事务、执行某些操作并开始其业务的时候。 交易保持开放。 由于 Postrges 事务引擎的架构，它挂起，其中什么也没有发生，它需要一个连接，锁定更改的行并且可能仍然增加其他表的膨胀。 并且此类交易也应该被枪杀，因为它们在任何情况下通常都是有害的。
如果您发现数据库中有超过 5-10-20 个，那么您需要担心并开始对它们进行处理。
这里我们也用于计算时间 clock_timestamp(). 我们拍摄交易，我们优化应用程序。
上面说了，锁是两个或多个事务竞争一个或一组资源的时候。 为此，我们有一个领域 waiting 布尔值 true или false.
True - 这意味着进程正在等待，需要做一些事情。 当一个进程在等待时，启动该进程的客户端也在等待。 浏览器中的客户端也坐着等待。
注意事项： _从 Postgres 9.6 开始，字段 waiting 删除并替换为两个更具信息性的字段 wait_event_type и wait_event._
怎么办？ 如果你看到 true 很长一段时间，那么你应该摆脱这样的请求。 我们只是拍摄这样的交易。 我们写给开发人员需要以某种方式优化的内容，这样就不会出现资源竞争。 然后开发人员优化应用程序，以免发生这种情况。
极端但同时可能不致命的情况是 死锁的发生。 两个事务更新了两个资源，然后它们再次访问它们，已经访问了相反的资源。 在这种情况下，PostgreSQL 会自行处理事务，以便另一个可以继续工作。 这是死路一条，她自己也不明白。 因此，PostgreSQL 被迫采取极端措施。
这里有两个查询可以让您跟踪锁。 我们使用视图 pg_locks，它允许您跟踪重锁。
第一个链接是请求文本本身。 它很长。
第二个链接是关于锁的文章。 读起来很有用，很有趣。
那么我们看到了什么？ 我们看到两个请求。 与交易 ALTER TABLE 是阻塞事务。 它开始了，但并没有结束，发布这个交易的应用程序正在某处做其他事情。 第二个请求是更新。 它等待 alter table 完成，然后再继续其工作。
这就是我们如何找出谁关押了谁，谁关押了谁，我们可以进一步处理这个问题。
下一个模块是 pg_stat_statements. 就像我说的，它是一个模块。 要使用它，你需要在配置中加载它的库，重启PostgreSQL，安装模块（一个命令），然后我们就会有一个新的视图。
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;我们能从那里得到什么？ 如果我们谈论简单的事情，我们可以取平均查询执行时间。 时间在增长，这意味着 PostgreSQL 响应缓慢，需要做一些事情。
我们可以看到数据库中最活跃的写入事务会更改共享缓冲区中的数据。 查看谁在那里更新或删除数据。
我们可以查看这些请求的不同统计数据。
我们 pg_stat_statements 用于构建报告。 我们每天重置一次统计数据。 让我们积累它。 在下次重置统计数据之前，我们构建一个报告。 这是报告的链接。 你可以看看。
我们在做什么？ 我们计算所有查询的总体统计数据。 然后，对于每个查询，我们计算它对该总体统计数据的单独贡献。
我们能看到什么？ 我们可以在所有其他请求的背景下看到特定类型的所有请求的总执行时间。 我们可以查看与整体情况相关的 CPU 和 I/O 使用情况。 并且已经优化了这些请求。 我们正在根据这份报告构建热门查询，并且已经开始思考要优化的内容。
我们在幕后有什么？ 还有几个投稿我没有考虑，因为时间有限。
有 pgstattuple 也是来自标准 contribs 包的附加模块。 它允许您评估 bloat 表，所谓的。 表碎片。 如果碎片很大，则需要将其删除，使用不同的工具。 和功能 pgstattuple 工作了很长时间。 桌子越多，它工作的时间就越长。
下一个贡献是 pg_buffercache. 它允许您检查共享缓冲区：缓冲区页面的使用强度和使用情况。 它只允许您查看共享缓冲区并评估那里发生的事情。
下一个模块是 pgfincore. 它允许您通过系统调用执行低级表操作 mincore()，即它允许您将表加载到共享缓冲区，或卸载它。 除其他外，它还允许检查操作系统的页面缓存，即表在页面缓存、共享缓冲区中占用了多少，并允许您评估表上的负载。
下一个模块是 pg_stat_kcache. 它还使用系统调用 getrusage(). 它在执行请求之前和之后执行它。 在获得的统计数据中，它允许我们估计我们的请求在磁盘 I/O 上花费了多少，即文件系统的操作并查看处理器使用情况。 但是，该模块还很年轻 (khe-khe)，它的工作需要 PostgreSQL 9.4 和我之前提到的 pg_stat_statements。
-
使用统计数据的能力很有用。 您不需要第三方软件。 你可以看、看、做某事、表演。
-
使用统计很容易，它是纯 SQL。 你收集了一个请求，编译它，发送它，查看它。
-
统计数据有助于回答问题。 如果您有疑问，您可以求助于统计数据——查看、得出结论、分析结果。
-
和实验。 很多请求，很多数据。 您始终可以优化某些现有查询。 您可以制作比原始版本更适合您的请求版本并使用它。
引用
报告中基于文章中找到的有效链接。
作者写得更多
（英语）
统计收集器
系统管理功能
贡献模块
SQL 实用程序和 SQL 代码示例
谢谢大家的关注！
来源： habr.com