使用高磁盘IO分析查询

Question

RDS Aurora PostgreSQL 10.14实例db.r5.4xlarge。

我想在我的机器里找出一些高的RDS IO成本。我正在查看pg_stat_statements，并询问以下查询是否有意义：

SELECT rolname::regrole,
       calls,
       round((total_time / 1000 / 60)::numeric, 3)                             as total_minutes,
       round(((total_time / 1000) / calls)::numeric, 3)                        as average_time_seconds,
       rows,
       userid,
       regexp_replace(query, '[ \t\n]+', ' ', 'g')                             AS query_text,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
       pg_size_pretty((shared_blks_hit + shared_blks_read) * 8192)             AS total_memory_read
FROM pg_stat_statements
         JOIN pg_roles r
              ON r.oid = userid
WHERE calls > 1
  AND rolname NOT LIKE '%backup'
  AND rolname <> 'rdsadmin'
  AND rolname <> 'rdsproxyadmin'
ORDER BY 8 asc nulls last
LIMIT 5;

根据文档hit_percent显示从缓存(shared_buffer或os内核)获取的数据与总数据相比，数据的数量越多越好.

另外，我还有total_memory_read，它是从磁盘和缓存中读取的内存总数。这是我收到的一封信

|rolname   |calls|total_minutes|average_time_seconds|rows|userid|query_text            |hit_percent       |total_memory_read|
+----------+-----+-------------+--------------------+----+------+----------------------+------------------+-----------------+
|XXX       |8    |4.278        |32.085              |256 |20550 |SELECT some_query ... |44.915182913169814|420 GB           |
+----------+-----+-------------+--------------------+----+------+----------------------+------------------+-----------------+

我的问题：

1. total_memory_read真的是这8个调用消耗的内存量吗？它似乎相当庞大，是420 G。
2. 如果我是total_memory_read的倍数(1-hit_%)，那么我是否得到了它从磁盘获取的GB数(最终得到了~231的磁盘IO )？
3. 对于如何追踪高IO猪还有其他建议吗？

Answer 1

缓存(shared_buffer或os内核)与总数据的比较

在社区PostgreSQL中，这是错误的。来自内核缓存的数据是读的，而不是命中的。PostgreSQL只知道请求数据，而不知道内核需要做什么才能传递数据。因此，某些(可能很大)的读取可能真的是内核的命中。因此，查看等待数据的实际时间可能是个好主意，方法是打开track_io_timing然后查看blk_read_time，而不是或者除了查看shared_blks_read之外。

但是有传言说，Aurora所做的事情之一就是在PostgreSQL中实现直接IO或类似的东西。如果真的是这样的话，那么在这个等式中内核缓存的命中位置并不重要，因为它们将为零。

total_memory_read真的是这8个调用消耗的内存量吗？它似乎相当庞大，是420 G。

这似乎是对的。是的，这是很大的，但是编写一个检查完整数据表的查询很容易，因为它需要全部数据，或者因为没有有用的索引。有50 GB的桌子吗？

如果我是total_memory_read的倍数(1-hit_%)，那么我是否得到了它从磁盘获取的GB数(最终得到了~231的磁盘IO )？

我想是的。但是，推导一个数字，然后导出另一个数字，然后从另一个导出的数字中推导出第三个数字，然后将其中一些数字相乘，抵消你的导子，然后回到其中一个原始数字，这似乎很愚蠢。为什么不直接看看shared_blks_read呢？

对于如何追踪高IO猪还有其他建议吗？

如果您只知道“我使用了太多的IO"，那么查看由pg_stat_statements desc或blk_read_time desc排序的blk_read_time表。即使计算命中率也没有意义，更不用说按命中率进行排序了。我也不会施加5的限制，那是相当低的。您可能想要对整个表的列进行求和，这样您就可以知道顶部包含了多少部分的读取，不管有多少行。您可以使用一个窗口函数来完成这个任务，或者您可以运行一次和，然后再从精神上应用它。

我也会删除calls > 1。如果一个只运行过一次的大型查询消耗了您的大部分IO，您可能想知道这一点，而不是隐藏它。还删除角色筛选器。也许你对这些角色的查询无能为力，但是如果它们消耗了大量的IO，你至少应该被告知。