説明会
何年も前、私は働いていた会社の重要なシステムのパフォーマンスの問題を解決する任務を負っていました。それは厳しい挑戦であり、眠れない夜が続き、さらに抜け毛が増えました。バックエンドには PostgreSQL が使用され、多くの努力と調査の結果、解決策は 1 行のシンプルなものであることが判明しました。
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 モジュールによって処理されます。
あなたが (私のように) 視覚的な人間であれば、https://explain.dalibo.com/ など、EXPLAIN 出力を理解するのに役立つツールがあります。以下は、ソート ステップを含むノードを示す例です。これには、ソート方法: 外部マージや使用されるソート領域: 2.2MB:
などの詳細が含まれます。
「統計」セクションは、各クエリ ノードの実行時間の詳細を提供するため、より複雑なクエリを分析する場合に特に役立ちます。この例では、Sort ノードの 1 つで、疑わしいほど長い実行時間 (42 ミリ秒近く) が強調表示されています。
- このクエリ プランはここで視覚化して分析できます: https://explain.dalibo.com/plan/2gd0a8c8fab6a532#stats
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;
- 視覚的な分析については、次のリンクを確認してください: https://explain.dalibo.com/plan/b094ec2f1cfg44f6#
この EXPLAIN 出力では、Sort ノードの 1 つがメモリ内アルゴリズムであるヒープソートを使用しています。コンテキストとして、プランナーはクイックソートよりも実行コストが安い場合にのみヒープソートを選択します。 PostgreSQL ソース コードの意思決定プロセスをさらに詳しく調べることができます。
さらに、以前は約 40 ミリ秒の実行時間を占めていた 2 番目の Sort ノードが実行計画から完全に消えています。この変更は、ハッシュ操作がメモリに収まり、約 480kB を消費するため、プランナーが MergeJoin ではなく HashJoin を選択するようになったために発生します。
結合アルゴリズムの詳細については、次の記事を参照してください。
- ハッシュ結合アルゴリズム
- マージ結合アルゴリズム
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 構成でクエリを実行する 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 ミリ秒以上短縮され、テスト ワークロード下でスループットが大幅に向上しました。
パーセンタイル指標が初めての場合は、勉強して理解することをお勧めします。これらの指標は、パフォーマンス分析のガイドとして非常に役立ちます。始めるためのリソースをいくつか紹介します:
- k6 応答時間
- p90 対 p99
結論
この問題について夢を見て、何度も起きて新しい解決策を試し、最終的に work_mem が役立つことを発見した後、次の課題は、この構成に適切な値を見つけることです。 ?
work_mem のデフォルト値 4MB は、他の多くの PostgreSQL 設定と同様、控えめな値です。これにより、PostgreSQL は計算能力が限られた小型のマシンでも実行できます。ただし、メモリ不足エラーで PostgreSQL インスタンスがクラッシュしないように注意する必要があります。 単一のクエリは、十分に複雑な場合、ソート、マージ結合、ハッシュ結合 (hash_mem_multiplier の影響を受ける) などの操作の数に応じて、work_mem で指定されたメモリを複数倍消費する可能性があります。などなど。公式ドキュメントに記載されているように:
値を選択するときは、この事実に留意する必要があります。並べ替え操作は、ORDER BY、DISTINCT、およびマージ結合に使用されます。ハッシュ テーブルは、ハッシュ結合、ハッシュベースの集計、メモ化ノード、および IN サブクエリのハッシュベースの処理で使用されます。
残念ながら、work_mem を設定するための魔法の公式はありません。それは、システムの利用可能なメモリ、ワークロード、クエリ パターンによって異なります。 TimescaleDB チームには自動調整ツールがあり、このトピックは広く議論されています。ここでは、役立つ優れたリソースをいくつか紹介します:
- work_mem についてあなたが知っていることはすべて間違っています
- 特定のシステムに合わせて work_mem を調整するにはどうすればよいですか
しかし、結局のところ、私見ですが、答えは「テスト」です。今日はテストです。明日はテストです。 永遠にテストしてください。データベースを破壊せずにクエリのパフォーマンスを向上させる、ユースケースにとって許容可能な値が見つかるまでテストを続けます。 ?
以上がPostgreSQL のパフォーマンス チューニング: work_mem の力の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
GOプログラミング言語を備えたスケーラブルなシステムを構築しますApr 25, 2025 am 12:19 AMgoisidealforbuildingscalablessystemsduetoitssimplicity、効率性、およびビルド・インコンカレンシsupport.1）
GOでINIT機能を効果的に使用するためのベストプラクティスApr 25, 2025 am 12:18 AMintionSingOrunautomaticallyは（）andareuseforstingupenments andinitializingvariables.usemforsimpletasks、回避効果を回避し、測定可能性を測定することを検討します。
GOパッケージのINIT機能の実行順序Apr 25, 2025 am 12:14 AMgoinitializeSpackages intheordertheyareimport extionsitions withinitionsiteintheirdefinition ordord、およびfilenamesdetermineTheOordCrossMultiplefiles.thisprocesccanbeandeanded by -dependenciessedieSiesは、このマイレアドカンフレシニティン化の対象となります
Goでカスタムインターフェイスを定義および使用しますApr 25, 2025 am 12:09 AMcustominterfacesingoarecrucialforwritingfficable、maintable、andtatablecode.theyeNabledeveloveerStofofofovioroverimplementation、拡張、methodsodsignaturesthattypespessmustimment、interfaceforoderueusavelya
GOのモッキングとテストのためにインターフェイスを使用しますApr 25, 2025 am 12:07 AMシミュレーションとテストにインターフェイスを使用する理由は、インターフェイスにより、実装を指定せずに契約の定義を可能にし、テストをより孤立し、メンテナンスしやすくするためです。 1）インターフェイスの暗黙的な実装により、モックオブジェクトを簡単に作成できます。これにより、テストの実際の実装を置き換えることができます。 2）インターフェイスを使用すると、ユニットテストでのサービスの実際の実装を簡単に置き換えることができ、テストの複雑さと時間を短縮できます。 3）インターフェイスによって提供される柔軟性により、さまざまなテストケースのシミュレートされた動作の変更が可能になります。 4）インターフェイスは、テスト可能なコードを最初から設計し、コードのモジュール性と保守性を向上させるのに役立ちます。
GOのパッケージ初期化にinitを使用しますApr 24, 2025 pm 06:25 PMGoでは、init関数はパッケージの初期化に使用されます。 1）init関数は、パッケージの初期化時に自動的に呼び出され、グローバル変数の初期化、接続の設定、構成ファイルの読み込みに適しています。 2）ファイルの順序で実行できる複数のinit関数がある場合があります。 3）それを使用する場合、実行順序、テストの難易度、パフォーマンスへの影響を考慮する必要があります。 4）副作用を減らし、依存関係の注入を使用し、初期化を遅延させることをお勧めします。
GoのSelectステートメント：マルチプレックスコンカレント操作Apr 24, 2025 pm 05:21 PMgo'sselectStatementStreamLinesConcurrentProgrambyMultipLexIngoperations.1）Itallow swaitingonMultipleChanneloperations、実行、exectingThefirstreadyone.2）
Go：Context and Waitgroupsの高度な並行性テクニックApr 24, 2025 pm 05:09 PMコンテキストアンドウェイトグループは、フォーマネングに焦点を合わせており、contextAllowsingSignalingCancellationAndDeadlinesAcrossapiboundariesを採用し、GoroutinesscanSclacefly.2）WaitGroupssynchronizeGoroutines、Allcompletebebroproproproproproproprotinesを保証します
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
Video Face Swap
完全無料の AI 顔交換ツールを使用して、あらゆるビデオの顔を簡単に交換できます。
人気の記事
ホットツール
SublimeText3 中国語版
中国語版、とても使いやすい
SAP NetWeaver Server Adapter for Eclipse
Eclipse を SAP NetWeaver アプリケーション サーバーと統合します。
WebStorm Mac版
便利なJavaScript開発ツール
SublimeText3 Linux 新バージョン
SublimeText3 Linux 最新バージョン
MinGW - Minimalist GNU for Windows
このプロジェクトは osdn.net/projects/mingw に移行中です。引き続きそこでフォローしていただけます。 MinGW: GNU Compiler Collection (GCC) のネイティブ Windows ポートであり、ネイティブ Windows アプリケーションを構築するための自由に配布可能なインポート ライブラリとヘッダー ファイルであり、C99 機能をサポートする MSVC ランタイムの拡張機能が含まれています。すべての MinGW ソフトウェアは 64 ビット Windows プラットフォームで実行できます。
ホットトピック
7711
15164014139452128825123229