背景

我每天攝取總計約 1000 萬行的時間序列數據，其中每一行都有一個時間戳、一個玩家 ID 和一些其他列。

然後通過 API 查詢特定播放器和時間範圍的數據（例如，播放器的最後 90 天xxx），大多數情況需要超過 1 個並發請求（通常執行大約 10 個並發請求）

我正在執行 postgres 9.6.17，機器有 500GB 硬碟空間（始終有大約 15% 的可用空間）、8 個核心和 16GB 記憶體。work_mem設置為 2GB、cache_size12GB、最大連接數設置為 100 等。

API 是 20 個 python gunicorn 工作者在單獨的機器上執行 flask 和 sqlalchemy+psycopg2。每個工作人員都有一個到數據庫的 2 個連接池，允許溢出 5 個。池設置曾經更高，但事實證明使用池幾乎沒有任何好處，因此數量很少。

天真的方法

最初，我將所有數據放入一個表中，並在時間戳和播放器上建立索引。這種方法執行良好，直到數據量開始使其變得越來越慢（出於明顯的原因）。這導致 API 工作人員超時並返回 500。返回的典型查詢（超過 6 個月的單人數據）的成本explain約為 1M，範例如下

Bitmap Heap Scan on player_data  (cost=515553.98..585514.47 rows=80037 width=32)
  Recheck Cond: (((player_id)::text = 'xxx'::text) AND (ts >= 1572566400) AND (ts < 1574899199))
  ->  BitmapAnd  (cost=515553.98..515553.98 rows=62819 width=0)
        ->  Bitmap Index Scan on idx_player_id  (cost=0.00..12749.35 rows=962837 width=0)
              Index Cond: ((player_id)::text = 'xxx'::text)
        ->  Bitmap Index Scan on idx_ts  (cost=0.00..502778.48 rows=37691480 width=0)
              Index Cond: ((ts >= 1572566400) AND (ts < 1574899199))

更好的分區方法

作為一項改進，我開始將數據儲存在分區中，每天一個，然後在每個分區上創建玩家 ID 和時間戳索引。這顯著減少了查詢時間，改進也可見explain：

Append  (cost=0.00..85192.02 rows=80037 width=32)
  ->  Seq Scan on player_data  (cost=0.00..0.00 rows=1 width=85)
        Filter: ((ts >= 1572566400) AND (ts < 1574899199) AND ((player_id)::text = 'xxx'::text)
  ->  Index Scan using idx_player_id_20191104 on player_data_20191104  (cost=0.43..317.61 rows=280 width=32)
        Index Cond: ((player_id)::text = 'xxx'::text)
        Filter: ((ts >= 1572566400) AND (ts < 1574899199)
........<continued for a while>................

成本幾乎降低了一個數量級，並且一切進展順利，尤其是對於時間粒度較小的查詢。

問題

一段時間後，使用者開始抱怨查詢不起作用，這意味著 API 開始返回 500 錯誤。檢查日誌後，我注意到很多以下錯誤：

sqlalchemy.exc.OperationalError: (psycopg2.errors.OutOfMemory) out of memory
DETAIL:  Failed on request of size NNNN.

請注意，NNNN它幾乎可以是 1 到 10000 之間的任何整數。一旦出現錯誤，它就會發生在所有同時執行的查詢上，從而導致數據庫在一段時間（幾分鐘）內無響應。通常，重新啟動 API 服務會恢復正常。當數據庫無響應時，任何類型的查詢和訪問都會返回相同的錯誤，包括psql來自我的機器的連接嘗試等。

我還應該注意並行執行的查詢大多命中相同的索引和分區，例如在同一時間範圍內的不同玩家 ID，有時查詢是相同的，除了偏移量/限制值。

另一個注意事項 - 無論是通過 API 還是直接出現並發，都會出現相同的錯誤psql。

調試和診斷

我在查詢時檢查了數據庫機器上的資源（因為它應該耗盡記憶體）：

• 可用 RAM 永遠不會低於 60%
• 沒有使用交換，沒有任何東西被寫入磁碟（儘管如果 postgres 用完可用的記憶體，它應該）
• 測試期間與數據庫的並發連接數最高為 100，空閒為 40
• 單分區索引大小約5MB，老表的索引大小幾乎1GB

另外，我嘗試了對數據庫的以下更改：

• 從 20MB增加到work_mem2GB，沒有改善
• 將記憶體從 8GB 增加到 12GB，最後增加到 16GB，沒有改善

我還比較了舊查詢和新查詢，結果如下：

• 超過 10 個對分區表的並發查詢導致錯誤出現
• 對舊的單表的 200 個並發查詢不會導致任何問題

問題）

所以我猜的問題是：

• 分區的使用是否會產生一些記憶體成本，導致查詢由於記憶體分配失敗而失敗？
• 是索引嗎？使用多個索引是否比使用一個大索引更糟糕？
• 這個理論是否有意義，還是我錯過了一些明顯的東西？

因為有點道理，所以回答我自己的問題：

• 沒有 9.6 的配置使問題消失
• PSQL12 中也出現了同樣的問題，儘管分區確實是本地的
• PSQL12 的配置沒有任何區別

所以最後，為了讓系統保持工作狀態，我選擇了（多）更少的分區和更長的查詢時間。

後來我遷移到自定義編碼和聚合解決方案。

（通常執行大約 10 個並發請求）

… 16GB 記憶體。work_mem 設置為 2GB，

你的 work_mem 設置對我來說似乎很瘋狂。有 10 個並發查詢，每個查詢都可能使用多個 work_mem 實例（在分區中尤其常見——所以回答你的標題問題，是的），記憶體不足也就不足為奇了。

我在查詢時檢查了數據庫機器上的資源（因為它應該耗盡記憶體）：

你是怎麼做的？來自不同工具的數據需要以不同的方式解釋。

另外，我嘗試了對數據庫的以下更改：

將 work_mem 從 20MB 增加到 2GB，沒有改善

是的，增加您使用的記憶體量不太可能解決記憶體不足問題。“毆打將繼續，直到士氣提高。”

但是您是說當 work_mem 設置為僅 20MB 時您遇到了完全相同的問題？您是否查看了數據庫日誌文件以直接查看它對問題的描述（而不是 python 傳遞給您的關於它的內容？）

根據您的描述，聽起來您可能根本不需要分區。只需一個多列索引就(player_id, ts)可以解決您的問題，而無需承擔分區的負擔和成本。

您是否計劃刪除/存檔舊數據，或者將無限期地累積？

引用自：https://dba.stackexchange.com/questions/262797