本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
IO:BufFileRead 和 IO:BufFileWrite
IO:BufFileRead
和 IO:BufFileWrite
事件表示 RDS for PostgreSQL 建立暫存檔。如果操作需要的記憶體超過工作記憶體參數目前的定義,則會將暫存資料寫入永久性儲存。此操作有時稱為「溢出到磁碟」。
支援的引擎版本
所有 RDS for PostgreSQL 版本都支援此等待事件資訊。
Context
IO:BufFileRead
和 IO:BufFileWrite
與工作記憶體區域和維護工作記憶體區域有關。如需這些本機記憶體區域的詳細資訊,請參閱 PostgreSQL 文件中的資源耗用
work_mem
的預設值為 4 MB。如果一個工作階段平行執行操作,則負責平行處理的每個工作者會使用 4 MB 的記憶體。因此,請小心設定 work_mem
。如果將此值設得太大,則執行許多工作階段的資料庫可能會耗用太多記憶體。如果將此值設得太低,RDS for PostgreSQL 會在本機儲存體中建立暫存檔。這些暫存檔的磁碟輸入/輸出可能造成效能降低。
如果您看到下列事件序列,表示資料庫可能正在產生暫存檔:
-
可用性突然遽降
-
可用空間迅速復原
您也可能看到「鏈鋸」模式。此模式可能表示資料庫不斷建立小檔案。
等待變多的可能原因
一般而言,這些等待事件起因於操作耗用超過 work_mem
或 maintenance_work_mem
參數所配置的記憶體。為了補償,操作寫入暫存檔。IO:BufFileRead
和 IO:BufFileWrite
事件的常見原因包括:
- 查詢需要比工作記憶體區域中更多的記憶體
-
具有下列特性的查詢使用工作記憶體區域:
-
雜湊聯結。
-
ORDER BY
子句 -
GROUP BY
子句 -
DISTINCT
-
範圍函數
-
CREATE TABLE AS SELECT
-
具體化檢視表重新整理
-
- 陳述式需要比維護工作記憶體區域中更多的記憶體
-
下列陳述式使用維護工作記憶體區域:
-
CREATE INDEX
-
CLUSTER
-
動作
根據等待事件的原因,我們會建議不同的動作。
主題
識別問題
假設績效詳情未開啟,而您懷疑 IO:BufFileRead
和 IO:BufFileWrite
比平時更常發生。若要識別問題的來源,您可以設定 log_temp_files
參數來記錄所有產生超過指定閾值 KB 暫存檔的查詢。根據預設,log_temp_files
會設為 -1
,這會關閉此記錄功能。如果您將此參數設為 0
,RDS for PostgreSQL 會記錄所有暫存檔。如果您將其設為 1024
,RDS for 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
,請考慮建立複合索引。將多個資料列組合成一個索引,可縮短相異資料列的評估時間。此外,如果您使用 RDS for 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);
根據預設,RDS for PostgreSQL 執行規劃工具會合併相似的節點,以免重複操作。不過,使用視窗區塊的明確宣告可以更輕鬆維護查詢。防止重複也可以改善效能。
調查具體化檢視表和 CTAS 陳述式
具體化檢視表重新整理時會執行查詢。此查詢可以包含 GROUP BY
、ORDER BY
或 DISTINCT
等操作。在重新整理期間,您可能看到大量暫存檔及等待事件 IO:BufFileWrite
和 IO:BufFileRead
。同樣地,當您根據 SELECT
陳述式建立資料表時,CREATE TABLE
陳述式會執行查詢。若要減少所需的暫存檔,請最佳化查詢。
重建索引時使用 pg_repack
當您建立索引時,引擎會排序結果集。隨著資料表變大,以及索引資料欄的值變得更多樣化,暫存檔需要更多空間。在大多數情況下,除非修改維護工作記憶體區域,否則無法阻止為大型資料表建立暫存檔。如需 maintenance_work_mem
的詳細資訊,請參閱 PostgreSQL 文件中的 https://www.postgresql.org/docs/current/runtime-config-resource.html
重新建立大型索引時,可能的解決方法是使用 pg_repack 延伸模組。如需詳細資訊,請參閱 pg_repack 文件中的以最少鎖定重組 PostgreSQL 資料庫中的資料表
叢集化資料表時提高 maintenance_work_mem
CLUSTER
命令根據 index_name 指定的現有索引,以叢集化 table_name 指定的資料表。RDS for PostgreSQL 實際上會重新建立資料表,以符合特定索引的順序。
在磁帶儲存盛行的年代,因為儲存輸送量有限,叢集很普遍。如今 SSD 型儲存很普遍,較不流行叢集。不過,如果將資料表叢集化,還是可稍微提高效能,視資料表大小、索引、查詢等而定。
如果您執行 CLUSTER
命令,然後看到等待事件 IO:BufFileWrite
和 IO:BufFileRead
,請調校 maintenance_work_mem
。請將記憶體調到很大。較大的值表示引擎可以使用更多記憶體執行叢集操作。
調整記憶體以防止 IO:BufFileRead 和 IO:BufFileWrite
在某些情況下,您需要調整記憶體。您的目標是使用適當的參數來平衡下列消用區域的記憶體,如下所示。
-
work_mem
值。 -
折除
shared_buffers
值之後剩餘的記憶體 -
已開啟和使用中的連線數上限,受限於
max_connections
如需如何調整記憶體的詳細資訊,請參閱 PostgreSQL 文件中的資源耗用
增加工作記憶體區域的大小
在某些情況下,您只能選擇增加工作階段所使用的記憶體。如果查詢撰寫無誤,且使用正確的索引鍵來聯結,請考慮提高 work_mem
值。
若要了解查詢產生多少暫存檔,請將 log_temp_files
設定為 0
。如果將 work_mem
值提高到日誌中指出的最大值,就可以防止查詢產生暫存檔。然而,work_mem
會針對每個連線或平行工作者,設定每個計劃節點的最大值。如果資料庫有 5,000 個連線,且每個連線各使用 256 MiB 的記憶體,則引擎需要 1.2 TiB 的 RAM。因此,執行個體可能記憶體不足。
為共用緩衝集區保留足夠記憶體
資料庫不只使用工作記憶體區域,還使用共用緩衝集區之類的記憶體區域。提高 work_mem
之前,請考慮這些額外記憶體區域的需求。
例如,假設您的 RDS for PostgreSQL 執行個體類別為 db.r5.2xlarge。此類別有 64 GiB 的記憶體。預設會保留 25% 的記憶體給共用緩衝集區。減去配置給共用記憶體區域的數量後,剩下 16,384 MB。請勿將剩餘的記憶體全部配置給工作記憶體區域,因為作業系統和引擎也需要記憶體。
可配置給 work_mem
的記憶體取決於執行個體類別。使用越大的執行個體類別,可用的記憶體越多。不過,在上述範例中,最多只能使用 16 GiB。否則,當記憶體不足時,就無法使用執行個體。為了讓執行個體從無法使用狀態中復原,RDS for PostgreSQL 自動化服務會自動重新啟動。
管理連線數目
假設您的資料庫執行個體有 5,000 個同時連線。每個連線至少使用 4 MiB 的 work_mem
。連線耗用大量記憶體可能導致效能降低。因應之道如下:
-
提升為更大的執行個體類別。
-
使用連線代理或集區來減少同時的資料庫連線數。
關於代理,根據您的應用程式而定,請考慮 Amazon RDS Proxy、pgBuncer 或連線集區。此解決方案可減輕 CPU 負載。面臨所有連線都需要工作記憶體區域時,也能降低風險。在只有少數資料庫連線時,您可以提高 work_mem
的值。如此就能減少 IO:BufFileRead
和 IO:BufFileWrite
等待事件。等待工作記憶體區域的查詢也會大幅加速。