共用方式為

針對適用於 PostgreSQL 的 Azure 資料庫中的高 IOPS 使用率進行疑難排解

本文說明如何快速識別高 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 使用率偏高的時間相關資訊。 比較寫入 IOPS、讀取 IOPS、讀取輸送量和寫入輸送量的圖表，以找出工作負載造成高 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 計劃。

終止長時間執行的交易

您可以選擇終止長時間執行的交易。

若要終止工作階段流程的 ID (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。 例如，checkpoint_timeout 為 5 分鐘的 0.9 值表示完成檢查點的目標為 270 秒 (0.9*300 秒)。 值 0.9 可提供相當一致的 I/O 負載。 checkpoint_completion_target 的積極值可能會導致伺服器上的 I/O 負載增加。

  • checkpoint_timeout：您可以從伺服器上設定的預設值提高 checkpoint_timeout 值。 當您提高值時，請將以下事項納入考量：將值提高也會延長損毀復原的時間。

調整自動資料清理以減少中斷

如需在自動資料清理過於混亂的情況下進行監視和微調的詳細資訊，請檢閱自動資料清理微調。

增加儲存體

當您將更多 IOPS 新增至伺服器時，增加儲存體會很有幫助。 如需儲存體和相關聯 IOPS 的詳細資訊，請檢閱計算和儲存體選項。

相關內容

  • Last updated on