共用方式為

針對適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器中的高 IOPS 使用率進行疑難排解

  • 發行項

適用於： 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

本文說明如何快速識別高 IOPS（每秒輸入/輸出作業數）使用率的根本原因，並提供補救動作，以在您使用 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器時控制 IOPS 使用率。

在本文中，您將學會如何：

  • 關於疑難解答指南，以找出並取得減輕根本原因的建議。
  • 使用工具來識別高輸入/輸出 （I/O） 使用率，例如 Azure 計量、查詢存放區 和pg_stat_statements。
  • 找出根本原因，例如長時間執行的查詢、檢查點計時、干擾性自動數據清理精靈進程，以及高記憶體使用率。
  • 使用說明分析、微調檢查點相關伺服器參數，以及微調自動數據清理精靈來解決高 I/O 使用率。

疑難排解指南

使用 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器入口網站上可用的功能疑難解答指南，即可找到降低高 IOPS 使用率案例的可能根本原因和建議。 如何設定疑難解答指南以使用它們，請遵循 設定疑難解答指南。

識別高 I/O 使用率的工具

請考慮下列工具來識別高 I/O 使用率。

Azure 計量

Azure 計量是檢查所定義日期和期間 I/O 使用率的良好起點。 計量會提供 I/O 使用率高的時間相關信息。 比較寫入 IOP、讀取 IOP、讀取輸送量和寫入輸送量的圖表，以找出工作負載造成高 I/O 使用率的時間。 針對主動式監視，您可以設定計量的警示。 如需逐步指引，請參閱 Azure 計量。

查詢存放區

查詢存放區 功能會自動擷取查詢和運行時間統計數據的歷程記錄，並保留它們以供檢閱。 它會依時間分割數據，以查看時態使用模式。 所有使用者、資料庫和查詢的數據都會儲存在 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例中名為 azure_sys 的資料庫。 如需逐步指引，請參閱使用 查詢存放區 監視效能。

使用下列語句來檢視取用 I/O 的前五個 SQL 語句：

select * from query_store.qs_view qv where is_system_query is FALSE
order by blk_read_time + blk_write_time  desc limit 5;

pg_stat_statements延伸模組

延伸 pg_stat_statements 模組可協助識別在伺服器上取用 I/O 的查詢。

使用下列語句來檢視取用 I/O 的前五個 SQL 語句：

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;

注意

使用查詢存放區或pg_stat_statements來填入資料行blk_read_time和blk_write_time時，您必須啟用伺服器參數 track_io_timing。 如需 的詳細資訊 track_io_timing，請檢閱 伺服器參數。

識別根本原因

如果 I/O 耗用量層級一般很高，可能是根本原因：

長時間執行的交易

長時間執行的交易可以取用 I/O，這可能會導致高 I/O 使用率。

下列查詢可協助識別長時間執行的連線：

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

檢查點計時

在檢查點發生太頻繁的情況中，也可以看到高 I/O。 其中一個識別方式是檢查 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器記錄檔中是否有下列記錄文字：「LOG：檢查點發生太頻繁」。

您也可以使用具有時間戳的定期快照 pg_stat_bgwriter 集儲存的方法進行調查。 藉由使用已儲存的快照集，您可以計算平均檢查點間隔、要求的檢查點數目，以及計時的檢查點數目。

干擾性自動數據清理精靈程式

執行下列查詢來監視自動資料清理：

SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE n_live_tup > 0;

此查詢可用來檢查資料庫中數據表的清理頻率。

  • last_autovacuum：上次自動數據清理在數據表上執行的日期和時間。
  • autovacuum_count：清理數據表的次數。
  • autoanalyze_count：分析數據表的次數。

解析高 I/O 使用率

若要解決高 I/O 使用率，您可以使用下列三種方法中的任何一種。

EXPLAIN ANALYZE 命令

識別耗用高 I/O 的查詢之後，請使用 EXPLAIN ANALYZE 來進一步調查查詢並調整查詢。 如需命令的詳細資訊 EXPLAIN ANALYZE ，請檢閱 EXPLAIN 方案。

終止長時間執行的交易

您可以考慮終止長時間執行的交易作為選項。

若要終止工作階段的行程識別碼 （PID），您必須使用下列查詢來偵測 PID：

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

您也可以依其他屬性進行篩選，例如 usename （用戶名稱）或 datname （資料庫名稱）。

擁有工作階段的 PID 之後，您可以使用下列查詢來終止它：

SELECT pg_terminate_backend(pid);

微調伺服器參數

如果您發現檢查點發生頻率太高，請增加 max_wal_size 伺服器參數，直到大部分檢查點都是時間驅動，而不是要求。 最後，90% 以上的時間應該以時間為基礎，而且兩個檢查點之間的間隔應該接近 checkpoint_timeout 伺服器上設定的值。

  • max_wal_size：尖峰上班時間是到達 max_wal_size 值的好時機。 若要到達值，請執行下列動作：

    1. 執行下列查詢以取得目前的WAL LSN，然後記下結果：

      select pg_current_wal_lsn();

    2. 等候秒 checkpoint_timeout 數。 執行下列查詢以取得目前的WAL LSN，然後記下結果：

      select pg_current_wal_lsn();

    3. 執行下列查詢，其使用這兩個結果，以 GB 為單位來檢查差異：

      select round (pg_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • checkpoint_completion_target：最佳做法是將值設定為0.9。 例如，5 分鐘 0.9 checkpoint_timeout 的值表示完成檢查點的目標為 270 秒（0.9*300 秒）。 值 0.9 提供相當一致的 I/O 負載。 的積極值 checkpoint_completion_target 可能會導致伺服器上的 I/O 負載增加。

  • checkpoint_timeout：您可以從伺服器上設定的預設值增加 checkpoint_timeout 值。 當您增加值時，請考慮增加它也會增加當機復原的時間。

調整自動數據清理以減少中斷

如需在自動數據清理太干擾的情況下監視和微調的詳細資訊，請檢閱 自動數據清理微調。

增加儲存體

當您將更多 IOPS 新增至伺服器時，增加記憶體有助於。 如需記憶體和相關聯 IOPS 的詳細資訊，請參閱 計算和記憶體選項。

相關內容