使用 PostgreSQL 管理暫存檔案

在 PostgreSQL 中，執行排序和雜湊操作的查詢會使用執行個體記憶體來儲存結果，直到 work_mem 參數中指定的值為止。當執行個體記憶體不足時，會建立暫存檔來儲存結果。這些檔案會寫入磁碟以完成查詢執行。稍後，這些檔案會在查詢完成後自動移除。在 Amazon PostgreSQL 中，這些檔案與其他日誌檔案共用本機儲存體。您可以透過監看 FreeLocalStorage 的 Amazon CloudWatch 指標，來監控 Aurora PostgreSQL 資料庫叢集的本機儲存空間。如需詳細資訊，請參閱疑難排解本機儲存問題。

您可以使用以下參數和函數來管理執行個體中的暫存檔案。

• 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 顯示的查詢結合使用時，您可以疑難排解、分析及判斷查詢工作負載中的變更。

  如需如何使用績效詳情來分析指標和查詢的詳細資訊，請參閱 使用績效詳情儀表板來分析指標

  使用績效詳情來檢視暫存檔使用情況

  1. 在 [績效詳情] 儀表板中選擇管理指標。

  2. 選擇資料庫指標，並選取 temp_bytes 和 temp_files 指標，如下方影像所示。

     

  3. 在最高 SQL索引標籤中，選擇偏好設定圖示。

  4. 在偏好設定視窗中，開啟最高 SQL索引標籤中顯示的下列統計資料，然後選擇繼續。

     • Temp writes/sec

     • Temp reads/sec

     • Tmp blk write/call

     • Tmp blk read/call

  5. 暫存檔在與針對 pg_ls_tmpdir 顯示的查詢組合時會被劃分，如以下範例所示。

     

當工作負載中最常用的查詢經常建立暫存檔案時，就會發生 IO:BufFileRead 和 IO:BufFileWrite 事件。您可以使用 Performance Insights，透過檢閱「資料庫負載」和「最高 SQL」區段中的「平均作用中工作階段」(AAS)，找出最常在 IO:BufFileRead 和 IO:BufFileWrite 上等待的查詢。

如需如何使用 Performance Insights 來分析各等待事件的最常用查詢和負載的詳細資訊，請參閱 最高 SQL 索引標籤概觀。您應找出並調整造成暫存檔案使用量及相關等待事件增加的查詢。如需這些等待事件和修補的詳細資訊，請參閱 IO:BufFileRead 和 IO:BufFileWrite。

注意

work_mem 參數可控制排序操作何時用完記憶體，以及將結果寫入暫存檔。我們建議您不要將此參數的設定變更為高於預設值，因為它會允許每個資料庫工作階段耗用更多記憶體。此外，執行複雜聯結和排序的單一工作階段可以執行平行操作，其中每個操作都會耗用記憶體。

最佳實務是，當您有具有多個聯結和排序的大型報表時，請使用 SET work_mem 命令在工作階段層級設定此參數。然後，變更僅套用於目前工作階段，不會全域變更該值。