分析具有高磁碟 IO 的查詢

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 個呼叫消耗的記憶體量嗎？420G好像挺大的
2. 如果我將 (1-hit_percent) 乘以 total_memory_read，我會得到它從磁碟中獲取的 GB 數（並最終獲得 ~231 的磁碟 IO）嗎？
3. 關於如何跟踪高 IO 豬還有其他建議嗎？

記憶體（shared_buffer 或作業系統核心）與總數據

在社區 PostgreSQL 中，這是錯誤的。來自核心記憶體的數據算作已讀，而不是命中。PostgreSQL 只知道請求的數據，而不知道核心必須做什麼來傳遞它。因此，某些（可能很大）百分比的讀取可能真的會命中核心。出於這個原因，通過打開 track_io_timing 然後查看 blk_read_time，而不是查看 shared_blks_read，查看等待數據所花費的實際時間可能是一個好主意。

但是有傳言說 Aurora 所做的一件事就是在 PostgreSQL 中實現直接 IO 或類似的東西。如果情況確實如此，那麼核心記憶體命中出現在等式中的哪個位置並不重要，因為它們將為零。

total_memory_read 真的是這 8 個呼叫消耗的記憶體量嗎？420G好像挺大的

這似乎是正確的。是的，這很大，但是編寫一個檢查完整數據表的查詢很容易，因為它需要全部數據，或者因為沒有有用的索引。你有一個 50 GB 的表嗎？

如果我將 (1-hit_percent) 乘以 total_memory_read，我會得到它從磁碟中獲取的 GB 數（並最終獲得 ~231 的磁碟 IO）嗎？

我猜。但是，推導出一個數字，然後推導出另一個數字，然後從其他推導出的一個數字中推導出第三個數字，然後將其中一些數字相乘以抵消您的推導並返回原始數字之一，這似乎很愚蠢。為什麼不直接看 shared_blks_read 呢？

關於如何跟踪高 IO 豬還有其他建議嗎？

如果您只知道“我使用了太多 IO”，那麼請查看由 shared_blks_read desc 或 blk_read_time desc 排序的 pg_stat_statements 表。甚至計算命中率都沒有意義，更不用說按它進行排序了。我也不會強加 5 的限制，這是相當低的。您可能希望對整個表的列求和，這樣您就知道頂部覆蓋了多少行，無論多少行。您可以使用視窗函式來執行此操作，或者您可以執行一次總和，然後在心理上應用它。

我也會刪除calls > 1. 如果一個只執行一次的巨型查詢用盡了你的大部分 IO，你會想知道這一點，而不是隱藏它。同時刪除角色過濾器。也許您對這些角色的查詢無能為力，但如果它們消耗大量 IO，您至少應該被告知。

引用自：https://dba.stackexchange.com/questions/306947