PostgreSQL 效能調優：work_mem 的力量
簡報
幾年前，我的任務是解決我工作的公司的一個關鍵系統的效能問題。這是一個艱鉅的挑戰，廢寢忘食，更掉頭髮，後端使用了PostgreSQL，經過一番努力和挖掘，解決方案原來就這麼簡單：
ALTER USER foo SET work_mem='32MB';
現在，說實話，這可能會也可能不會立即解決您的效能問題。這在很大程度上取決於您的查詢模式和系統的工作負載。但是，如果您是後端開發人員，我希望這篇文章能為您解決問題的工具庫添加另一個工具，尤其是 PostgreSQL ？
在這篇文章中，我們將創建一個場景來模擬效能下降，並探索一些工具來調查問題，例如 EXPLAIN、用於負載測試的 k6，甚至深入研究 PostgreSQL 的原始程式碼。我也會分享一些文章來幫助大家解決相關問題。
- ➡️ 具有完整實現的 github 儲存庫
案例研究
讓我們創造一個簡單的系統來分析足球員的表現。目前，唯一的業務規則是回答這個問題：
- 參與得分最多的前 N 位選手是誰？
以下 SQL 建立我們的資料模型並填入它：
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
初始化和填充資料庫的腳本可在 github 儲存庫中找到。
是的，我們可以設計資料庫來提高系統效能，但這裡的主要目標是探索未最佳化的場景。相信我，您可能會遇到這樣的系統，其中要么是糟糕的初始設計選擇，要么是意外的增長需要付出巨大的努力來提高性能。
偵錯問題
為了模擬與 work_mem 配置相關的問題，讓我們創建一個查詢來回答這個問題：誰是對進球貢獻最大的前 2000 名球員？
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
好吧，但是我們要如何辨識這個查詢中的瓶頸呢？與其他 DBMS 一樣，PostgreSQL 支援 EXPLAIN 指令，這有助於我們了解查詢規劃器執行的每個步驟（最佳化與否）。
我們可以分析以下細節：
- 使用了什麼類型的掃描？索引掃描、僅索引掃描、順序掃描等
- 使用了哪個索引，在什麼條件下使用？
- 如果涉及排序，使用什麼類型的演算法？是完全依賴內存，還是需要使用磁碟？
- 共享緩衝區的使用。
- 執行時間估計。
您可以在這裡了解有關 PostgreSQL 規劃器/最佳化器的更多資訊：
- 官方文件
- pganalyze - postgres 查詢規劃的基礎
- cybertec - 如何解釋 postgresql 解釋
說話很便宜
光說不練，所以讓我們深入研究一個實際的例子。首先，我們將 work_mem 減少到其可能的最小值，即 64kB，如原始碼中所定義：
ALTER USER foo SET work_mem='32MB';
接下來我們來分析EXPLAIN指令的輸出：
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
我們可以看到執行時間為82.718ms，並且使用的排序演算法是外部合併。該演算法依賴磁碟而不是內存，因為資料超出了 64kB work_mem 限制。
供您參考，tuplesort.c 模組透過在此行將狀態設為 SORTEDONTAPE 來標記排序演算法何時使用磁碟。磁碟交互由 logtape.c 模組處理。
如果您是視覺型的人（像我一樣），有一些工具可以幫助您理解 EXPLAIN 輸出，例如 https://explain.dalibo.com/。以下是顯示具有排序步驟的節點的範例，包括排序方法：外部合併和已使用排序空間等詳細資訊：2.2MB：
「統計」部分對於分析更複雜的查詢特別有用，因為它提供了每個查詢節點的執行時間詳細資訊。在我們的範例中，它突出顯示了排序節點之一中可疑的高執行時間（接近 42 毫秒）：
- 您可以在此處視覺化和分析此查詢計劃：https://explain.dalibo.com/plan/2gd0a8c8fab6a532#stats
如 EXPLAIN 輸出所示，效能問題的主要原因之一是使用磁碟的 Sort 節點。此問題的一個副作用是寫入 I/O 指標出現峰值，尤其是在工作負載較高的系統中（我希望您正在監視這些指標；如果沒有，祝您在需要時好運！）。是的，即使是唯讀查詢也會導致寫入峰值，因為排序演算法會將資料寫入暫存檔案。
解決方案
當我們使用 work_mem=4MB（PostgreSQL 中的預設值）執行相同的查詢時，執行時間減少了 50% 以上。
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
- 要進行視覺化分析，請查看此連結：https://explain.dalibo.com/plan/b094ec2f1cfg44f6#
在此 EXPLAIN 輸出中，其中一個排序節點現在使用記憶體演算法堆排序。就上下文而言，只有當堆排序比快速排序執行成本更低時，規劃器才會選擇堆排序。您可以在 PostgreSQL 原始碼中更深入地了解決策過程。
此外，先前佔用近 40 毫秒執行時間的第二個 Sort 節點完全從執行計畫中消失。發生此更改是因為規劃器現在選擇 HashJoin 而不是 MergeJoin，因為哈希操作適合內存，消耗大約 480kB。
有關連接演算法的更多詳細信息，請查看以下文章：
- HashJoin 演算法
- MergeJoin 演算法
對 API 的影響
預設的 work_mem 並不總是足以處理系統的工作負載。您可以使用以下方法在使用者層級調整此值：
ALTER USER foo SET work_mem='32MB';
注意：如果您使用連接池或連接池程序，回收舊會話以使新配置生效非常重要。
您也可以在資料庫事務層級控制此配置。讓我們執行一個簡單的 API，透過 k6 的負載測試來了解和測量 work_mem 變更的影響：
-
k6-test.js
CREATE TABLE players ( player_id SERIAL PRIMARY KEY, nationality TEXT, age INT, position TEXT ); CREATE TABLE matches ( match_id SERIAL PRIMARY KEY, match_date DATE, home_team TEXT, away_team TEXT ); CREATE TABLE player_stats ( player_stat_id SERIAL PRIMARY KEY, player_id INT REFERENCES players(player_id), match_id INT REFERENCES matches(match_id), goals INT, assists INT, minutes_played INT ); -- Populate players with a range of nationalities, ages, and positions INSERT INTO players (nationality, age, position) SELECT ('Country' || (1 + random()*100)::int), -- 100 different nationalities (18 + random()*20)::int, -- Ages between 18 and 38 (ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int] FROM generate_series(1, 10000);登入後複製登入後複製登入後複製
API 是在 Go 中實現的，並公開了兩個使用不同 work_mem 配置執行查詢的端點：
-
main.go
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
登入後複製登入後複製登入後複製
下面是 docker-compose 文件，其中包含運行負載測試所需的所有依賴項：
-
docker-compose.yaml
/* * workMem is forced to be at least 64KB, the current minimum valid value * for the work_mem GUC. This is a defense against parallel sort callers * that divide out memory among many workers in a way that leaves each * with very little memory. */ state->allowedMem = Max(workMem, 64) * (int64) 1024;登入後複製
我們可以設定 ENDPOINT 環境變數來定義要測試的場景：/low-work-mem 或 /optimized-work-mem。使用：docker compose up --abort-on-container-exit 執行測試。在本範例中，我使用了 Docker 版本 20.10.22。
-
測試端點：/low-work-mem - work_mem=64kB
BEGIN; -- 1. Initialize a transaction. SET LOCAL work_mem = '64kB'; -- 2. Change work_mem at transaction level, another running transactions at the same session will have the default value(4MB). SHOW work_mem; -- 3. Check the modified work_mem value. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -- 4. Run explain with options that help us to analyses and indetifies bottlenecks. SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps INNER JOIN players p ON p.player_id = ps.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000; -- QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Limit (cost=18978.96..18983.96 rows=2000 width=12) (actual time=81.589..81.840 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Buffers: shared hit=667, temp read=860 written=977 | -> Sort (cost=18978.96..19003.96 rows=10000 width=12) (actual time=81.587..81.724 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Sort Key: (sum((ps.goals + ps.assists))) DESC | Sort Method: external merge Disk: 280kB | Buffers: shared hit=667, temp read=860 written=977 | -> GroupAggregate (cost=15076.66..17971.58 rows=10000 width=12) (actual time=40.293..79.264 rows=9998 loops=1) | Output: p.player_id, sum((ps.goals + ps.assists)) | Group Key: p.player_id | Buffers: shared hit=667, temp read=816 written=900 | -> Merge Join (cost=15076.66..17121.58 rows=100000 width=12) (actual time=40.281..71.313 rows=100000 loops=1) | Output: p.player_id, ps.goals, ps.assists | Merge Cond: (p.player_id = ps.player_id) | Buffers: shared hit=667, temp read=816 written=900 | -> Index Only Scan using players_pkey on public.players p (cost=0.29..270.29 rows=10000 width=4) (actual time=0.025..1.014 rows=10000 loops=1)| Output: p.player_id | Heap Fetches: 0 | Buffers: shared hit=30 | -> Materialize (cost=15076.32..15576.32 rows=100000 width=12) (actual time=40.250..57.942 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Buffers: shared hit=637, temp read=816 written=900 | -> Sort (cost=15076.32..15326.32 rows=100000 width=12) (actual time=40.247..49.339 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Sort Key: ps.player_id | Sort Method: external merge Disk: 2208kB | Buffers: shared hit=637, temp read=816 written=900 | -> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.011..8.378 rows=100000 loops=1) | Output: ps.goals, ps.assists, ps.player_id | Buffers: shared hit=637 | Planning: | Buffers: shared hit=6 | Planning Time: 0.309 ms | Execution Time: 82.718 ms | COMMIT; -- 5. You can also execute a ROLLBACK, in case you want to analyze queries like INSERT, UPDATE and DELETE.登入後複製 -
測試端點：/optimized-work-mem - work_mem=4MB
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps INNER JOIN players p ON p.player_id = ps.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000; -- QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------------------------+ Limit (cost=3646.90..3651.90 rows=2000 width=12) (actual time=41.672..41.871 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Buffers: shared hit=711 | -> Sort (cost=3646.90..3671.90 rows=10000 width=12) (actual time=41.670..41.758 rows=2000 loops=1) | Output: p.player_id, (sum((ps.goals + ps.assists))) | Sort Key: (sum((ps.goals + ps.assists))) DESC | Sort Method: top-N heapsort Memory: 227kB | Buffers: shared hit=711 | -> HashAggregate (cost=2948.61..3048.61 rows=10000 width=12) (actual time=38.760..40.073 rows=9998 loops=1) | Output: p.player_id, sum((ps.goals + ps.assists)) | Group Key: p.player_id | Batches: 1 Memory Usage: 1169kB | Buffers: shared hit=711 | -> Hash Join (cost=299.00..2198.61 rows=100000 width=12) (actual time=2.322..24.273 rows=100000 loops=1) | Output: p.player_id, ps.goals, ps.assists | Inner Unique: true | Hash Cond: (ps.player_id = p.player_id) | Buffers: shared hit=711 | -> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.008..4.831 rows=100000 loops=1)| Output: ps.player_stat_id, ps.player_id, ps.match_id, ps.goals, ps.assists, ps.minutes_played | Buffers: shared hit=637 | -> Hash (cost=174.00..174.00 rows=10000 width=4) (actual time=2.298..2.299 rows=10000 loops=1) | Output: p.player_id | Buckets: 16384 Batches: 1 Memory Usage: 480kB | Buffers: shared hit=74 | -> Seq Scan on public.players p (cost=0.00..174.00 rows=10000 width=4) (actual time=0.004..0.944 rows=10000 loops=1) | Output: p.player_id | Buffers: shared hit=74 | Planning: | Buffers: shared hit=6 | Planning Time: 0.236 ms | Execution Time: 41.998 ms |登入後複製
結果表明，具有較高 work_mem 的端點的效能優於具有較低配置的端點。在測試負載下，p90 延遲下降了超過 43ms，吞吐量顯著提高。
如果百分位指標對您來說是新的，我建議您學習並理解它們。這些指標對於指導效能分析非常有幫助。以下是一些可幫助您入門的資源：
- k6 回應時間
- p90 與 p99
結論
在夢到這個問題、多次醒來嘗試新的解決方案並最終發現 work_mem 可以提供幫助之後，下一個挑戰是找出此配置的正確值。 ？
work_mem 的預設值 4MB，與許多其他 PostgreSQL 設定一樣，是保守的。這使得 PostgreSQL 可以在運算能力有限的小型機器上運作。但是，我們必須小心，不要因記憶體不足錯誤而導致 PostgreSQL 實例崩潰。 單一查詢，如果足夠複雜，可能會消耗work_mem 指定記憶體的數倍，取決於排序、合併聯接、雜湊聯接等操作的數量（受hash_mem_multiplier 影響），等等。如官方文件所述：
選擇值時必須牢記這一事實。排序操作用於 ORDER BY、DISTINCT 和合併連線。哈希表用於哈希連接、基於哈希的聚合、記憶節點和 IN 子查詢的基於哈希的處理。
不幸的是，沒有設定 work_mem 的神奇公式。這取決於系統的可用記憶體、工作負載和查詢模式。 TimescaleDB 團隊有一個自動調整工具，並且這個主題得到了廣泛討論。以下是一些可以指導您的優秀資源：
- 你所知道的關於work_mem的一切都是錯的
- 我應該如何針對給定係統調整 work_mem
但歸根結底，恕我直言，答案是：測驗。今天測試。明天測試。 永遠測試。繼續測試，直到找到適合您的用例的可接受的值，該值可以增強查詢效能而不破壞資料庫。 ？
以上是PostgreSQL 效能調優：work_mem 的力量的詳細內容。更多資訊請關注PHP中文網其他相關文章！
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
Debian OpenSSL有哪些漏洞
Apr 02, 2025 am 07:30 AM
OpenSSL，作為廣泛應用於安全通信的開源庫，提供了加密算法、密鑰和證書管理等功能。然而，其歷史版本中存在一些已知安全漏洞，其中一些危害極大。本文將重點介紹Debian系統中OpenSSL的常見漏洞及應對措施。 DebianOpenSSL已知漏洞：OpenSSL曾出現過多個嚴重漏洞，例如：心臟出血漏洞(CVE-2014-0160):該漏洞影響OpenSSL1.0.1至1.0.1f以及1.0.2至1.0.2beta版本。攻擊者可利用此漏洞未經授權讀取服務器上的敏感信息，包括加密密鑰等。
Beego ORM中如何指定模型關聯的數據庫？
Apr 02, 2025 pm 03:54 PM
在BeegoORM框架下，如何指定模型關聯的數據庫？許多Beego項目需要同時操作多個數據庫。當使用Beego...
從前端轉型後端開發，學習Java還是Golang更有前景？
Apr 02, 2025 am 09:12 AM
後端學習路徑：從前端轉型到後端的探索之旅作為一名從前端開發轉型的後端初學者，你已經有了nodejs的基礎，...
在Go語言中使用Redis Stream實現消息隊列時，如何解決user_id類型轉換問題？
Apr 02, 2025 pm 04:54 PM
Go語言中使用RedisStream實現消息隊列時類型轉換問題在使用Go語言與Redis...
GoLand中自定義結構體標籤不顯示怎麼辦？
Apr 02, 2025 pm 05:09 PM
GoLand中自定義結構體標籤不顯示怎麼辦？在使用GoLand進行Go語言開發時，很多開發者會遇到自定義結構體標籤在�...
Go語言中用於浮點數運算的庫有哪些？
Apr 02, 2025 pm 02:06 PM
Go語言中用於浮點數運算的庫介紹在Go語言（也稱為Golang）中，進行浮點數的加減乘除運算時，如何確保精度是�...
Go的爬蟲Colly中Queue線程的問題是什麼？
Apr 02, 2025 pm 02:09 PM
Go爬蟲Colly中的Queue線程問題探討在使用Go語言的Colly爬蟲庫時，開發者常常會遇到關於線程和請求隊列的問題。 �...
如何在Debian上配置MongoDB自動擴容
Apr 02, 2025 am 07:36 AM
本文介紹如何在Debian系統上配置MongoDB實現自動擴容，主要步驟包括MongoDB副本集的設置和磁盤空間監控。一、MongoDB安裝首先，確保已在Debian系統上安裝MongoDB。使用以下命令安裝：sudoaptupdatesudoaptinstall-ymongodb-org二、配置MongoDB副本集MongoDB副本集確保高可用性和數據冗餘，是實現自動擴容的基礎。啟動MongoDB服務:sudosystemctlstartmongodsudosys
