本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
使用 PostgreSQL 管理暫存檔案
在 PostgreSQL 中,執行排序和雜湊操作的查詢會使用執行個體記憶體來儲存結果,直到 work_mem
FreeStorageSpace
指標, CloudWatch 以確保資料庫執行個體具有足夠的可用儲存空間。如需詳細資訊,請參閱 FreeStorageSpace
我們建議針對涉及多個並行查詢的工作負載使用 Amazon RDS Optimized Reads 執行個體,以增加暫存檔案的使用量。這些執行個體會使用本機非揮發性記憶體快速 (NVMe) 為主的固態硬碟 (SSD) 區塊層級儲存體來放置暫存檔案。如需詳細資訊,請參閱 Amazon RDS Optimized Reads。
您可以使用以下參數和函數來管理執行個體中的暫存檔案。
-
temp_file_limit
– 此參數會取消任何超過 temp_file 大小的查詢 (以 KB 為單位)。此限制可防止任何查詢無休止地執行,並消耗含有暫存檔的磁碟空間。您可以使用 log_temp_files
參數的結果來估計值。最佳實務是檢查工作負載行為並根據估計值設定限制。以下範例顯示查詢超過限制時的取消方式。postgres=>
select * from pgbench_accounts, pg_class, big_table;
ERROR: temporary file size exceeds temp_file_limit (64kB)
log_temp_files
– 此參數會在移除工作階段的暫存檔案時,將訊息傳送至 postgresql.log。這個參數會在查詢順利完成後產生日誌。因此,它可能無助於疑難排解使用中、長時間執行的查詢。 下列範例顯示,當查詢順利完成時,這些項目會在清理暫存檔時記錄在 postgresql.log 檔案中。
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
pg_ls_tmpdir
– 此功能可從 RDS for PostgreSQL 13 及以上版本使用,提供目前暫存檔案使用情況的可見性。完成的查詢不會出現在函數的結果中。在下列範例中,您可以檢視此函數的結果。 postgres=>
select * from pg_ls_tmpdir();
name | size | modification -----------------+------------+------------------------ pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00 pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00 (7 rows)
postgres=>
select query from pg_stat_activity where pid = 8355;
query ---------------------------------------------------------------------------------------- select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid (1 row)
檔案名稱包含產生暫存檔之工作階段的處理 ID (PID)。較進階的查詢 (例如下列範例) 會針對每個 PID 執行暫存檔案的總和。
postgres=>
select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;
pid | count | sum ------+------------------- 8355 | 2 | 2144501760 8351 | 2 | 2090770432 8327 | 1 | 1072250880 8328 | 2 | 2144501760 (4 rows)
-
pg_stat_statements
– 如果您啟動 pg_stat_statements 參數,則可以檢視每次呼叫的平均暫存檔案使用量。您可以識別查詢的 query_id,並使用它來檢查暫存檔的使用情況,如以下範例所示。postgres=>
select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';
queryid ---------------------- -7170349228837045701 (1 row)
postgres=>
select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;
queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call ----------------------+---------------------------+-------+-------------------------+---------------------------- -7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678 (1 row)
-
Performance Insights
– 在績效詳情儀表板中,您可以透過開啟指標 temp_bytes 和 temp_files 來檢視暫存檔的使用情況。然後,您可以查看這兩個指標的平均值,並查看它們如何對應至查詢工作負載。績效詳情中的檢視不會明確顯示產生暫存檔的查詢。不過,當您將績效詳情與為pg_ls_tmpdir
顯示的查詢結合使用時,您可以疑難排解、分析及判斷查詢工作負載中的變更。如需如何使用績效詳情來分析指標和查詢的詳細資訊,請參閱 使用績效詳情儀表板來分析指標
使用績效詳情來檢視暫存檔使用情況
-
在 [績效詳情] 儀表板中選擇管理指標。
-
選擇資料庫指標,並選取 temp_bytes 和 temp_files 指標,如下方影像所示。
-
在最高 SQL索引標籤中,選擇偏好設定圖示。
-
在偏好設定視窗中,開啟最高 SQL索引標籤中顯示的下列統計資料,然後選擇繼續。
-
Temp writes/sec
-
Temp reads/sec
-
Tmp blk write/call
-
Tmp blk read/call
-
-
暫存檔在與針對
pg_ls_tmpdir
顯示的查詢組合時會被劃分,如以下範例所示。
-
當工作負載中最常用的查詢經常建立暫存檔案時,就會發生 IO:BufFileRead
和 IO:BufFileWrite
事件。您可以使用 Performance Insights,透過檢閱「資料庫負載」和「最高 SQL」區段中的「平均作用中工作階段」(AAS),找出最常在 IO:BufFileRead
和 IO:BufFileWrite
上等待的查詢。
如需如何使用 Performance Insights 來分析各等待事件的最常用查詢和負載的詳細資訊,請參閱 最高 SQL 索引標籤概觀。您應找出並調整造成暫存檔案使用量及相關等待事件增加的查詢。如需這些等待事件和修正的詳細資訊,請參閱 IO: BufFileRead 和 BufFileWrite 。
注意
work_mem
最佳實務是,當您有具有多個聯結和排序的大型報表時,請使用 SET work_mem
命令在工作階段層級設定此參數。然後,變更僅套用於目前工作階段,不會全域變更該值。