本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用 PostgreSQL 管理暫存檔案 在 PostgreSQL 中，複雜查詢可能會同時執行數個排序或雜湊操作，每個操作都使用執行個體記憶體來儲存結果，直到 [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 參數中指定的值為止。當執行個體記憶體不足時，會建立暫存檔來儲存結果。這些檔案會寫入磁碟以完成查詢執行。稍後，這些檔案會在查詢完成後自動移除。在 RDS for PostgreSQL 中，這些檔案會儲存在資料磁碟區上的 Amazon EBS 中。如需詳細資訊，請參閱 [Amazon RDS 資料庫執行個體儲存體](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html)。您可以監控在 CloudWatch​ 中發佈的 `FreeStorageSpace`​ 指標，藉此確保資料庫執行個體有充足的可用儲存空間。如需詳細資訊，請參閱 [https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm](https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm)。 我們建議針對涉及多個並行查詢的工作負載使用 Amazon RDS Optimized Read 執行個體，以增加暫存檔案的使用量。這些執行個體會使用本機非揮發性記憶體快速 (NVMe) 為主的固態硬碟 (SSD) 區塊層級儲存體來放置暫存檔案。如需詳細資訊，請參閱 [使用 Amazon RDS Optimized Reads 改善 RDS for PostgreSQL 的查詢效能](USER_PostgreSQL.optimizedreads.md)。 您可以使用以下參數和函數來管理執行個體中的暫存檔案。 + **[https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK](https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK)** – 此參數會取消任何超過 temp\$1file 大小的查詢 (以 KB 為單位)。此限制可防止任何查詢無休止地執行，並消耗含有暫存檔的磁碟空間。您可以使用 `log_temp_files` 參數的結果來估計值。最佳實務是檢查工作負載行為並根據估計值設定限制。以下範例顯示查詢超過限制時的取消方式。 ``` postgres=>select * from pgbench_accounts, pg_class, big_table; ``` ``` ERROR: temporary file size exceeds temp_file_limit (64kB) ``` + **[https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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; ``` + **[https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE)** – 此功能可從 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\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html)`** – 如果您啟動 pg\$1stat\$1statements 參數，則可以檢視每次呼叫的平均暫存檔案使用量。您可以識別查詢的 query\$1id，並使用它來檢查暫存檔的使用情況，如以下範例所示。 ``` 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](https://aws.amazon.com/rds/performance-insights/)`** – 在績效詳情儀表板中，您可以透過開啟指標 **temp\$1bytes** 和 **temp\$1files** 來檢視暫存檔的使用情況。然後，您可以查看這兩個指標的平均值，並查看它們如何對應至查詢工作負載。績效詳情中的檢視不會明確顯示產生暫存檔的查詢。不過，當您將績效詳情與為 `pg_ls_tmpdir` 顯示的查詢結合使用時，您可以疑難排解、分析及判斷查詢工作負載中的變更。 如需如何使用 Performance Insights 來分析指標和查詢的詳細資訊，請參閱 [使用績效詳情儀表板來分析指標](USER_PerfInsights.UsingDashboard.md)。 如需使用 Performance Insights 檢視暫存檔案使用量的範例，請參閱 [使用 Performance Insights 來檢視暫存檔使用情況](PostgreSQL.ManagingTempFiles.Example.md) # 使用 Performance Insights 來檢視暫存檔使用情況 您可以使用 Performance Insights，透過開啟指標 **temp\$1bytes** 和 **temp\$1files** 來檢視暫存檔的使用情況。Performance Insights 中的檢視不會顯示產生暫存檔的特定查詢，但是，當您將 Performance Insights 與針對 `pg_ls_tmpdir` 顯示的查詢結合時，您可以疑難排解、分析和判斷查詢工作負載中的變更。 1. 在 [績效詳情] 儀表板中選擇**管理指標**。 1. 選擇**資料庫指標**，並選取 **temp\$1bytes** 和 **temp\$1files** 指標，如下方影像所示。 ![\[指標會顯示在圖形中。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_metrics.png) 1. 在**最高 SQL**索引標籤中，選擇**偏好設定**圖示。 1. 在**偏好設定**視窗中，開啟**最高 SQL**索引標籤中顯示的下列統計資料，然後選擇**繼續**。 + Temp writes/sec + Temp reads/sec + Tmp blk write/call + Tmp blk read/call 1. 暫存檔在與針對 `pg_ls_tmpdir` 顯示的查詢組合時會被劃分，如以下範例所示。 ![\[顯示暫存檔使用情況的查詢。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_query.png) 當工作負載中最常用的查詢經常建立暫存檔案時，就會發生 `IO:BufFileRead` 和 `IO:BufFileWrite` 事件。您可以使用 Performance Insights，透過檢閱「資料庫負載」和「最高 SQL」區段中的「平均作用中工作階段」(AAS)，找出最常在 `IO:BufFileRead` 和 `IO:BufFileWrite` 上等待的查詢。 ![\[圖中的 IO:BufFileRead 和 IO:BufFileWrite。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/images/perfinsights_IOBufFile.png) 如需如何使用 Performance Insights 來分析各等待事件的最常用查詢和負載的詳細資訊，請參閱 [最高 SQL 索引標籤概觀](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL)。您應找出並調整造成暫存檔案使用量及相關等待事件增加的查詢。如需這些等待事件和修補的詳細資訊，請參閱 [IO:BufFileRead 和 IO:BufFileWrite](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.iobuffile.html)。 **注意** [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 參數可控制排序操作何時用完記憶體，以及將結果寫入暫存檔。我們建議您不要將此參數的設定變更為高於預設值，因為它會允許每個資料庫工作階段耗用更多記憶體。此外，執行複雜聯結和排序的單一工作階段可以執行平行操作，其中每個操作都會耗用記憶體。 最佳實務是，當您有具有多個聯結和排序的大型報表時，請使用 `SET work_mem` 命令在工作階段層級設定此參數。然後，變更僅套用於目前工作階段，不會全域變更該值。