IO:BufFileRead 和 IO:BufFileWrite

IO:BufFileRead 和 IO:BufFileWrite 事件表示 Aurora PostgreSQL 建立暫存檔。如果操作需要的記憶體超過工作記憶體參數目前的定義，則會將暫存資料寫入永久性儲存。此操作有時稱為「溢出到磁碟」。

支援的引擎版本

所有版本的 Aurora PostgreSQL 都支援此等待事件資訊。

Context

IO:BufFileRead 和 IO:BufFileWrite 與工作記憶體區域和維護工作記憶體區域有關。如需這些本機記憶體區域的詳細資訊，請參閱工作記憶體區域和維護工作記憶體區域。

work_mem 的預設值為 4 MB。如果一個工作階段平行執行操作，則負責平行處理的每個工作者會使用 4 MB 的記憶體。因此，請小心設定 work_mem。如果將此值設得太大，則執行許多工作階段的資料庫可能會耗用太多記憶體。如果將此值設得太小，Aurora PostgreSQL 會在本機儲存中建立暫存檔。這些暫存檔的磁碟輸入/輸出可能造成效能降低。

如果您看到下列事件序列，表示資料庫可能正在產生暫存檔：

1. 可用性突然遽降

2. 可用空間迅速復原

您也可能看到「鏈鋸」模式。此模式可能表示資料庫不斷建立小檔案。

等待變多的可能原因

一般而言，這些等待事件起因於操作耗用超過 work_mem 或 maintenance_work_mem參數所配置的記憶體。為了補償，操作寫入暫存檔。IO:BufFileRead 和 IO:BufFileWrite 事件的常見原因包括：

查詢需要比工作記憶體區域中更多的記憶體

具有下列特性的查詢使用工作記憶體區域：

• 雜湊聯結。

• ORDER BY 子句

• GROUP BY 子句

• DISTINCT

• 視窗函數

• CREATE TABLE AS SELECT

• 具體化檢視表重新整理

陳述式需要比維護工作記憶體區域中更多的記憶體

下列陳述式使用維護工作記憶體區域：

• CREATE INDEX

• CLUSTER

動作

根據等待事件的原因，我們會建議不同的動作。

主題

• 識別問題
• 檢查聯結查詢
• 檢查 ORDER BY 和 GROUP BY 查詢
• 避免使用 DISTINCT 操作
• 考慮使用視窗函數代替 GROUP BY 函數
• 調查具體化檢視表和 CTAS 陳述式
• 建立索引時使用 pg_repack
• 叢集化資料表時提高 maintenance_work_mem
• 調整記憶體以防止 IO:BufFileRead 和 IO:BufFileWrite

識別問題

假設績效詳情未開啟，而您懷疑 IO:BufFileRead 和 IO:BufFileWrite 比平時更常發生。請執行下列動作：

1. 在 Amazon CloudWatch 中檢查 FreeLocalStorage 指標。

2. 尋找鏈鋸模式，即一連串鋸齒尖。

鏈鋸模式表示快速耗用和釋放儲存，通常與暫存檔有關。如果您看到此模式，請開啟績效詳情。使用績效詳情時，您可以識別等待事件何時發生及其相關聯的查詢。您的解決方案取決於導致事件的特定查詢。

或設定 log_temp_files 參數。此參數會記錄產生超過閾值 KB 暫存檔的所有查詢。如果值為 0，Aurora PostgreSQL 會記錄所有暫存檔。如果值為 1024，Aurora PostgreSQL 會記錄產生大於 1 MB 暫存檔的所有查詢。如需 log_temp_files 的詳細資訊，請參閱 PostgreSQL 文件中的錯誤報告和日誌記錄。

檢查聯結查詢

您的應用程式可能使用聯結。例如，下列查詢會聯結四個資料表。

SELECT * 
       FROM order 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

暫存檔使用量激增的可能原因在於查詢本身有問題。例如，不標準的子句可能無法正確篩選聯結。請看下列範例中的第二個內部聯結。

SELECT * 
       FROM order
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

上述查詢誤將 customer.id 聯結至 customer.id，導致在每個客戶與每筆訂單之間產生笛卡爾乘積。這種意外聯結會產生大型暫存檔。根據資料表的大小，笛卡爾查詢甚至可能導致填滿儲存。有下列情況時，表示應用程式可能有笛卡爾聯結：

• 您看到儲存可用性大幅遽降，接著迅速復原。

• 未建立索引。

• 未發出 CREATE TABLE FROM SELECT 陳述式。

• 未重新整理具體化檢視表。

若要檢查是否以適當索引鍵聯結資料表，請檢驗查詢和物件關聯式映射指令。切記，不一定會呼叫應用程式的某些查詢，有些查詢是動態產生。

檢查 ORDER BY 和 GROUP BY 查詢

在某些情況下，ORDER BY 子句可能導致產生過多暫存檔。請考量下列準則：

• 只將需要排序的資料欄放入 ORDER BY 子句中。如果查詢傳回數千個資料列，並在 ORDER BY 子句中指定許多資料欄，此準則尤其重要。

• 當 ORDER BY 子句比對的資料欄有相同遞增或遞減順序時，請考慮建立索引以加速執行。最好是局部索引，因為較小。讀取和周遊較小的索引比較快。

• 如果您為可接受空值的資料欄建立索引，請決定要將空值存放在索引結尾還是開頭。

  可能的話，請篩選結果集，以減少需要排序的資料列數。如果您使用 WITH 子句陳述式或子查詢，請記住，內部查詢會產生結果集並傳給外部查詢。查詢篩選掉越多資料列，查詢就越不需要排序。

• 如果不需要取得完整結果集，請使用 LIMIT 子句。例如，如果您只想要前五個資料列，則在查詢中使用 LIMIT 子句就不會一直產生結果。如此，查詢只需要較少的記憶體和暫存檔。

使用 GROUP BY 子句的查詢可能也需要暫存檔。GROUP BY 查詢使用如下函數來彙總值：

• COUNT

• AVG

• MIN

• MAX

• SUM

• STDDEV

若要調校 GROUP BY 查詢，請遵循 ORDER BY 查詢的建議。

避免使用 DISTINCT 操作

可能的話，請避免使用 DISTINCT 操作來移除重複的資料列。查詢傳回不必要和重複的資料列越多，DISTINCT 操作的成本越高。可能的話，請在 WHERE 子句中增加篩選條件，即使不同的資料表使用相同的篩選器也無妨。篩選查詢並正確聯結可改善效能和減少使用資源。還可防止不正確的報告和結果。

如果需要對同一個資料表的多個資料列使用 DISTINCT，請考慮建立複合索引。將多個資料列組合成一個索引，可縮短相異資料列的評估時間。此外，如果您使用 Amazon Aurora PostgreSQL 第 10 版或更新版本，則可以使用 CREATE STATISTICS 命令，將多個資料欄之間的統計數字相互關聯。

考慮使用視窗函數代替 GROUP BY 函數

使用 GROUP BY 時，您變更結果集，然後擷取彙總結果。使用視窗函數時，您彙總資料而不變更結果集。視窗函數使用 OVER 子句來跨查詢所定義的集執行計算，使資料列彼此相互關聯。您在視窗函數中可以使用所有 GROUP BY 函數，但也可使用如下函數：

• RANK

• ARRAY_AGG

• ROW_NUMBER

• LAG

• LEAD

若要盡量減少視窗函數產生的暫存檔，當需要兩個相異彙總時，請移除相同結果集的重複部分。請看下列查詢。

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

您可以使用 WINDOW 子句重寫查詢，如下所示。

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

根據預設，Aurora PostgreSQL 執行規劃工具會合併相似的節點，以免重複操作。不過，使用視窗區塊的明確宣告可以更輕鬆維護查詢。防止重複也可以改善效能。

調查具體化檢視表和 CTAS 陳述式

具體化檢視表重新整理時會執行查詢。此查詢可以包含 GROUP BY、ORDER BY 或 DISTINCT 等操作。在重新整理期間，您可能看到大量暫存檔及等待事件 IO:BufFileWrite 和 IO:BufFileRead。同樣地，當您根據 SELECT 陳述式建立資料表時，CREATE TABLE 陳述式會執行查詢。若要減少所需的暫存檔，請最佳化查詢。

建立索引時使用 pg_repack

當您建立索引時，引擎會排序結果集。隨著資料表變大，以及索引資料欄的值變得更多樣化，暫存檔需要更多空間。在大多數情況下，除非修改維護工作記憶體區域，否則無法阻止為大型資料表建立暫存檔。如需詳細資訊，請參閱維護工作記憶體區域。

重新建立大型索引時，可能的解決方法是使用 pg_repack 工具。如需詳細資訊，請參閱 pg_repack 文件中的以最少鎖定重組 PostgreSQL 資料庫中的資料表。

叢集化資料表時提高 maintenance_work_mem

CLUSTER 命令根據 index_name 指定的現有索引，以叢集化 table_name 指定的資料表。Aurora PostgreSQL 實際上會重新建立資料表，以符合特定索引的順序。

在磁帶儲存盛行的年代，因為儲存輸送量有限，叢集很普遍。如今 SSD 型儲存很普遍，較不流行叢集。不過，如果將資料表叢集化，還是可稍微提高效能，視資料表大小、索引、查詢等而定。

如果您執行 CLUSTER 命令，然後看到等待事件 IO:BufFileWrite 和 IO:BufFileRead,請調校 maintenance_work_mem。請將記憶體調到很大。較大的值表示引擎可以使用更多記憶體執行叢集操作。

調整記憶體以防止 IO:BufFileRead 和 IO:BufFileWrite

在某些情況下，您需要調整記憶體。目標是平衡下列需求：

• work_mem 值 (請參閱工作記憶體區域)

• 折除 shared_buffers 值之後剩餘的記憶體 (請參閱緩衝集區)

• 已開啟和使用中的連線數上限，受限於 max_connections

增加工作記憶體區域的大小

在某些情況下，您只能選擇增加工作階段所使用的記憶體。如果查詢撰寫無誤，且使用正確的索引鍵來聯結，請考慮提高 work_mem 值。如需詳細資訊，請參閱 工作記憶體區域。

若要了解查詢產生多少暫存檔，請將 log_temp_files 設定為 0。如果將 work_mem 值提高到日誌中指出的最大值，就可以防止查詢產生暫存檔。然而，work_mem 會針對每個連線或平行工作者，設定每個計劃節點的最大值。如果資料庫有 5,000 個連線，且每個連線各使用 256 MiB 的記憶體，則引擎需要 1.2 TiB 的 RAM。因此，執行個體可能記憶體不足。

為共用緩衝集區保留足夠記憶體

資料庫不只使用工作記憶體區域，還使用共用緩衝集區之類的記憶體區域。提高 work_mem 之前，請考慮這些額外記憶體區域的需求。如需緩衝集區的詳細資訊，請參閱緩衝集區。

例如，假設您的 Aurora PostgreSQL 執行個體類別為 db.r5.2xlarge。此類別有 64 GiB 的記憶體。預設會保留 75% 的記憶體給共用緩衝集區。減去配置給共用記憶體區域的數量後，剩下 16,384 MB。請勿將剩餘的記憶體全部配置給工作記憶體區域，因為作業系統和引擎也需要記憶體。

可配置給 work_mem 的記憶體取決於執行個體類別。使用越大的執行個體類別，可用的記憶體越多。不過，在上述範例中，最多只能使用 16 GiB。否則，當記憶體不足時，就無法使用執行個體。為了讓執行個體從無法使用狀態中復原，Aurora PostgreSQL 自動化服務會自動重新啟動。

管理連線數目

假設您的資料庫執行個體有 5,000 個同時連線。每個連線至少使用 4 MiB 的 work_mem。連線耗用大量記憶體可能導致效能降低。因應之道如下：

• 提升為更大的執行個體類別。

• 使用連線代理或集區來減少同時的資料庫連線數。

關於代理，根據您的應用程式而定，請考慮 Amazon RDS Proxy、pgBuncer 或連線集區。此解決方案可減輕 CPU 負載。面臨所有連線都需要工作記憶體區域時，也能降低風險。在只有少數資料庫連線時，您可以提高 work_mem 的值。如此就能減少 IO:BufFileRead 和 IO:BufFileWrite 等待事件。等待工作記憶體區域的查詢也會大幅加速。