何年も前、私は働いていた会社の重要なシステムのパフォーマンスの問題を解決する任務を負っていました。それは厳しい挑戦であり、眠れない夜が続き、さらに抜け毛が増えました。バックエンドには PostgreSQL が使用され、多くの努力と調査の結果、解決策は 1 行のシンプルなものであることが判明しました。
ALTER USER foo SET work_mem='32MB';
正直に言うと、これでパフォーマンスの問題がすぐに解決されるかどうかはわかりません。それはクエリ パターンとシステムのワークロードに大きく依存します。ただし、あなたがバックエンド開発者であれば、特に PostgreSQL に関する問題に対処するための別のツールがこの投稿によってあなたの武器庫に追加されることを願っています?
この投稿では、パフォーマンスの低下をシミュレートするシナリオを作成し、EXPLAIN、負荷テスト用の k6 など、問題を調査するためのいくつかのツールを検討し、さらに PostgreSQL のソース コードを詳しく調べます。関連する問題の解決に役立つ記事もいくつか紹介します。
サッカー選手のパフォーマンスを分析する簡単なシステムを作成してみましょう。今のところ、唯一のビジネス ルールは次の質問に答えることです:
次の 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 プランナー/オプティマイザーの詳細については、こちらをご覧ください:
話は簡単なので、実際の例を見てみましょう。まず、ソース コードで定義されているように、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 モジュールによって処理されます。
あなたが (私のように) 視覚的な人間であれば、https://explain.dalibo.com/ など、EXPLAIN 出力を理解するのに役立つツールがあります。以下は、ソート ステップを含むノードを示す例です。これには、ソート方法: 外部マージや使用されるソート領域: 2.2MB:
などの詳細が含まれます。
「統計」セクションは、各クエリ ノードの実行時間の詳細を提供するため、より複雑なクエリを分析する場合に特に役立ちます。この例では、Sort ノードの 1 つで、疑わしいほど長い実行時間 (42 ミリ秒近く) が強調表示されています。
EXPLAIN の出力が示すように、パフォーマンスの問題の主な理由の 1 つは、ディスクを使用するソート ノードです。この問題の副作用は、特にワークロードの高いシステムでは、書き込み 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;
この EXPLAIN 出力では、Sort ノードの 1 つがメモリ内アルゴリズムであるヒープソートを使用しています。コンテキストとして、プランナーはクイックソートよりも実行コストが安い場合にのみヒープソートを選択します。 PostgreSQL ソース コードの意思決定プロセスをさらに詳しく調べることができます。
さらに、以前は約 40 ミリ秒の実行時間を占めていた 2 番目の Sort ノードが実行計画から完全に消えています。この変更は、ハッシュ操作がメモリに収まり、約 480kB を消費するため、プランナーが MergeJoin ではなく HashJoin を選択するようになったために発生します。
結合アルゴリズムの詳細については、次の記事を参照してください。
デフォルトの 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 構成でクエリを実行する 2 つのエンドポイントを公開します。
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 のレイテンシは 43 ミリ秒以上短縮され、テスト ワークロード下でスループットが大幅に向上しました。
パーセンタイル指標が初めての場合は、勉強して理解することをお勧めします。これらの指標は、パフォーマンス分析のガイドとして非常に役立ちます。始めるためのリソースをいくつか紹介します:
この問題について夢を見て、何度も起きて新しい解決策を試し、最終的に work_mem が役立つことを発見した後、次の課題は、この構成に適切な値を見つけることです。 ?
work_mem のデフォルト値 4MB は、他の多くの PostgreSQL 設定と同様、控えめな値です。これにより、PostgreSQL は計算能力が限られた小型のマシンでも実行できます。ただし、メモリ不足エラーで PostgreSQL インスタンスがクラッシュしないように注意する必要があります。 単一のクエリは、十分に複雑な場合、ソート、マージ結合、ハッシュ結合 (hash_mem_multiplier の影響を受ける) などの操作の数に応じて、work_mem で指定されたメモリを複数倍消費する可能性があります。などなど。公式ドキュメントに記載されているように:
値を選択するときは、この事実に留意する必要があります。並べ替え操作は、ORDER BY、DISTINCT、およびマージ結合に使用されます。ハッシュ テーブルは、ハッシュ結合、ハッシュベースの集計、メモ化ノード、および IN サブクエリのハッシュベースの処理で使用されます。
残念ながら、work_mem を設定するための魔法の公式はありません。それは、システムの利用可能なメモリ、ワークロード、クエリ パターンによって異なります。 TimescaleDB チームには自動調整ツールがあり、このトピックは広く議論されています。ここでは、役立つ優れたリソースをいくつか紹介します:
しかし、結局のところ、私見ですが、答えは「テスト」です。今日はテストです。明日はテストです。 永遠にテストしてください。データベースを破壊せずにクエリのパフォーマンスを向上させる、ユースケースにとって許容可能な値が見つかるまでテストを続けます。 ?
以上がPostgreSQL のパフォーマンス チューニング: work_mem の力の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
