最近的一套PostgreSQL（12.6）數據庫在進行UAT測試的時候，發現服務器空間爆滿，導致了PostgreSQL異常關閉。問題發生后，現場的DBA讓我配合做了如下的一些分析，這里去除敏感信息，分享給大家。

一、問題分析

問題發生的時候，現場的DBA其實經過檢查發現了暴漲的源頭是這個pgsql_tmp目錄,

而且現場的DBA在PostgreSQL異常關閉前，用strings去看pgsql_temp里面的文件，確認是對應的數據，而且根據文件的命名方式，通過pid查詢到了產生臨時文件的SQL，SQL是一個select語句，并且涉及到fdw遠程表，這方便了我們后續對問題的分析。

 select * from pg_stat_activity where pid='24518';

通過查看這個SQL的執行計劃，我們可以看到有兩個sort環節，而且都是在PostgreSQL端執行的。

然后看一下涉及的sort列的表的情況，發現這幾列都是字符類型的。要知道oracle_fdw的排序字段是字符類型的時候是不會不下推到oracle端的，但是數字和時間類型都是可以的，因此這個SQL的排序他會先把數據拿到PostgreSQL的內存里，即work_mem里去做這個sort操作。

下推：FDW采用一種稱為 pushdown 的機制，它允許遠程端執行 WHERE、ORDER BY 和 JOIN 子句。下推 WHERE 和 JOIN 減少了本地和遠程服務器之間傳輸的數據量，避免了網絡通信瓶頸。

到這里，我和現場DBA確認了下他們work_mem的大小，經過確認，他們work_mem的值為4MB,也就是默認值，對于這個SQL來說，work_mem明顯太小了，這個參數是寫入臨時文件之前內部排序操作和散列表使用的內存量，超出后，就會溢出到磁盤寫到pgsql_tmp下的臨時文件里，當開始將臨時文件寫入磁盤時，顯然會比內存慢得多，而且臨時文件的太大也可能把空間填滿。

其實可以在啟用log_temp_files后，在PostgreSQL日志中搜索temporary file來查看是否溢出到了磁盤。如果看到了temporary file，就意味著可能需要增加work_mem了。
而且不容忽視的一點是：work_mem這個參數和max_connection是有聯系的，假設你設置為10MB，則500個用戶同時執行查詢排序，很快就會使用5GB的實際內存。或者涉及復雜計算，涉及幾張表的合并，就要用到幾倍的work_mem。因此work_mem是不適宜設置的很大的，如果設置超出256MB，其實很容易因為瞬間的大并發操作導致OOM問題

此外，其實可以通過使用explain analyze 這個SQL去查看這個它執行所需要的內存大小，但生產的環境基本不允許我們隨意explain analyze，因為它會實際執行SQL，如果是問題SQL，不僅可能影響數據庫性能、嚴重甚至造成宕機。而且explain analyze命令跟insert、update、delete的操作更是會修改數據，這是我們絕對不能做的。

下邊我在自己的測試環境給大家演示一下，work_mem對于執行計劃的影響，我原本的work_mem值也是4MB沒有修改：

可以看到，這個SQL的執行計劃里，Sort Method部分是external merge Disk，代表了外部合并磁盤，也就是溢出到了磁盤，生成了臨時文件。可以看到這個SQL的執行計劃里Disk：25864KB，大概有25MB，那我work_mem的值改成128MB，會怎樣呢 ,如下所示，相同的SQL，Sort Method部分變成了quicksort Memory ，此時sort的數據都是放在內存里的，而沒有溢出磁盤。

二、結論

通過上面的分析，我們知道這次的問題，主要是出在了oracle_fdw處理SQL的sort操作的的時候字符類型不能下推，而且在PostgreSQL本地進行sort的時候，超出了work_mem的設置，導致溢出到磁盤，我們可以把work_mem的值稍微提升一點，但一定不要過大，否則業務的瞬間高并發很可能導致OOM。而且對于work_mem的大小我們肯定是要根據平時的業務以及并發量來綜合評估的，基本確定了一個值不會去輕易改動，不能對于個別SQL進行調整，這樣也不利于數據庫的穩定性。因此，最好的方式還是結合業務看是否這條SQL能進行調整，修改sort的列的類型，或者調整寫法讓其能下推到oracle端執行，減少傳輸到PostgreSQL本地的數據量。除此之外我們也可以使用temp_file_limit這個參數對此類臨時文件的大小做一個限制，避免數據目錄的爆滿。