Heim >Backend-Entwicklung >Golang >PostgreSQL-Leistungsoptimierung: Die Kraft von work_mem
PostgreSQL-Leistungsoptimierung: Die Kraft von work_mem
- Mary-Kate OlsenOriginal
- 2024-12-05 03:36:10739Durchsuche
Briefing
Vor Jahren wurde ich damit beauftragt, ein Leistungsproblem in einem kritischen System des Unternehmens, in dem ich arbeitete, zu lösen. Es war eine harte Herausforderung, schlaflose Nächte und noch mehr Haarausfall. Das Backend nutzte PostgreSQL, und nach viel Mühe und Recherche stellte sich heraus, dass die Lösung so einfach wie eine Zeile war:
ALTER USER foo SET work_mem='32MB';
Um ehrlich zu sein, könnte dies Ihr Leistungsproblem sofort lösen oder auch nicht. Dies hängt stark von Ihren Abfragemustern und der Auslastung Ihres Systems ab. Wenn Sie jedoch ein Backend-Entwickler sind, hoffe ich, dass dieser Beitrag Ihr Arsenal um ein weiteres Tool zur Lösung von Problemen erweitert, insbesondere mit PostgreSQL?
In diesem Beitrag erstellen wir ein Szenario zur Simulation von Leistungseinbußen und erkunden einige Tools zur Untersuchung des Problems, wie EXPLAIN, k6 für Lasttests und sogar einen Einblick in den Quellcode von PostgreSQL. Ich werde auch einige Artikel veröffentlichen, die Ihnen bei der Lösung verwandter Probleme helfen sollen.
- ➡️ Github-Repository mit der vollständigen Implementierung
Fallstudie
Lassen Sie uns ein einfaches System zur Analyse der Leistung von Fußballspielern erstellen. Im Moment besteht die einzige Geschäftsregel darin, diese Frage zu beantworten:
- Wer sind die besten N-Spieler, die am meisten Punkte erzielt haben?
Das folgende SQL erstellt unser Datenmodell und füllt es auf:
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);
Das Skript zum Initialisieren und Füllen der Datenbank ist im Github-Repository verfügbar.
Ja, wir könnten eine Datenbank entwerfen, um die Systemleistung zu verbessern, aber das Hauptziel besteht hier darin, nicht optimierte Szenarien zu untersuchen. Vertrauen Sie mir, Sie werden wahrscheinlich auf Systeme wie dieses stoßen, bei denen entweder schlechte anfängliche Designentscheidungen oder unerwartetes Wachstum erhebliche Anstrengungen zur Leistungsverbesserung erfordern.
Debuggen des Problems
Um das Problem im Zusammenhang mit der work_mem-Konfiguration zu simulieren, erstellen wir eine Abfrage zur Beantwortung dieser Frage: Wer sind die Top-2000-Spieler, die am meisten zu Toren beitragen?
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;
Okay, aber wie können wir Engpässe in dieser Abfrage identifizieren? Wie andere DBMS unterstützt PostgreSQL den Befehl EXPLAIN, der uns hilft, jeden vom Abfrageplaner ausgeführten Schritt zu verstehen (optimiert oder nicht).
Wir können Details analysieren wie:
- Welche Art von Scan wurde verwendet? Index-Scan, Nur-Index-Scan, Seq-Scan usw.
- Welcher Index wurde verwendet und unter welchen Bedingungen?
- Wenn es um Sortieren geht, welche Art von Algorithmus wurde verwendet? Ist es vollständig auf den Arbeitsspeicher angewiesen oder ist eine Festplattennutzung erforderlich?
- Die Verwendung von gemeinsamen Puffern.
- Schätzung der Ausführungszeit.
Mehr über den PostgreSQL-Planer/Optimierer erfahren Sie hier:
- offizielle Dokumentation
- pganalyze – Grundlagen der Postgres-Abfrageplanung
- Cybertec – Erklärung zur Interpretation von Postgresql
Reden ist billig
Reden ist billig, also lasst uns in ein praktisches Beispiel eintauchen. Zuerst reduzieren wir work_mem auf den kleinstmöglichen Wert, nämlich 64 KB, wie im Quellcode definiert:
ALTER USER foo SET work_mem='32MB';
Als nächstes analysieren wir die Ausgabe des EXPLAIN-Befehls:
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);
Wir können sehen, dass die Ausführungszeit 82,718 ms betrug und der verwendete Sortieralgorithmus eine externe Zusammenführung war. Dieser Algorithmus verlässt sich auf die Festplatte statt auf den Speicher, da die Daten die Work_Mem-Grenze von 64 KB überschritten haben.
Zu Ihrer Information: Das Modul tuplesort.c markiert, wann der Sortieralgorithmus die Festplatte verwendet, indem es den Status in dieser Zeile auf SORTEDONTAPE setzt. Festplatteninteraktionen werden vom logtape.c-Modul verarbeitet.
Wenn Sie ein visueller Mensch sind (wie ich), gibt es Tools, die Ihnen helfen können, die EXPLAIN-Ausgabe zu verstehen, wie zum Beispiel https://explain.dalibo.com/. Unten sehen Sie ein Beispiel, das einen Knoten mit dem Sortierschritt zeigt, einschließlich Details wie Sortiermethode: externe Zusammenführung und verwendeter Sortierraum: 2,2 MB:
Der Abschnitt „Statistik“ ist besonders nützlich für die Analyse komplexerer Abfragen, da er Details zur Ausführungszeit für jeden Abfrageknoten bereitstellt. In unserem Beispiel wird eine verdächtig hohe Ausführungszeit – fast 42 ms – in einem der Sortierknoten hervorgehoben:
- Sie können diesen Abfrageplan hier visualisieren und analysieren: https://explain.dalibo.com/plan/2gd0a8c8fab6a532#stats
Wie die EXPLAIN-Ausgabe zeigt, ist einer der Hauptgründe für das Leistungsproblem der Sortierknoten mithilfe der Festplatte. Ein Nebeneffekt dieses Problems, insbesondere in Systemen mit hoher Arbeitslast, sind Spitzen bei den Schreib-I/O-Metriken (ich hoffe, Sie überwachen diese; wenn nicht, viel Glück, wenn Sie sie brauchen!). Und ja, selbst schreibgeschützte Abfragen können Schreibspitzen verursachen, da der Sortieralgorithmus Daten in temporäre Dateien schreibt.
Lösung
Wenn wir dieselbe Abfrage mit work_mem=4MB (der Standardeinstellung in PostgreSQL) ausführen, verkürzt sich die Ausführungszeit um über 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;
- Eine visuelle Analyse finden Sie unter diesem Link: https://explain.dalibo.com/plan/b094ec2f1cfg44f6#
In dieser EXPLAIN-Ausgabe verwendet einer der Sortierknoten jetzt einen speicherinternen Algorithmus, Heapsort. Aus Gründen des Kontexts entscheidet sich der Planer nur dann für Heapsort, wenn die Ausführung kostengünstiger ist als Quicksort. Im PostgreSQL-Quellcode können Sie tiefer in den Entscheidungsprozess eintauchen.
Außerdem verschwindet der zweite Sortierknoten, der zuvor fast 40 ms Ausführungszeit ausmachte, vollständig aus dem Ausführungsplan. Diese Änderung tritt auf, weil der Planer jetzt einen HashJoin anstelle eines MergeJoin auswählt, da die Hash-Operation in den Speicher passt und etwa 480 KB verbraucht.
Weitere Informationen zu Join-Algorithmen finden Sie in diesen Artikeln:
- HashJoin-Algorithmus
- MergeJoin-Algorithmus
Auswirkungen auf die API
Das standardmäßige work_mem reicht nicht immer aus, um die Arbeitslast Ihres Systems zu bewältigen. Sie können diesen Wert auf Benutzerebene anpassen mit:
ALTER USER foo SET work_mem='32MB';
Hinweis: Wenn Sie einen Verbindungspool oder einen Verbindungspooler verwenden, ist es wichtig, alte Sitzungen wiederzuverwenden, damit die neue Konfiguration wirksam wird.
Sie können diese Konfiguration auch auf Datenbanktransaktionsebene steuern. Lassen Sie uns eine einfache API ausführen, um die Auswirkungen von work_mem-Änderungen mithilfe von Lasttests mit k6 zu verstehen und zu messen:
-
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);
Die API wurde in Go implementiert und stellt zwei Endpunkte bereit, die die Abfrage mit unterschiedlichen work_mem-Konfigurationen ausführen:
-
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;
Unten finden Sie die Docker-Compose-Datei mit allen Abhängigkeiten, die zum Ausführen des Auslastungstests erforderlich sind:
-
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;
Wir können die Umgebungsvariable ENDPOINT festlegen, um das zu testende Szenario zu definieren: /low-work-mem oder /optimized-work-mem. Führen Sie den Test mit folgendem Befehl aus: docker compose up --abort-on-container-exit. Für dieses Beispiel habe ich Docker-Version 20.10.22 verwendet.
-
ENDPUNKT testen: /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. -
Test-ENDPUNKT: /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 |
Die Ergebnisse zeigen, dass der Endpunkt mit einem höheren work_mem den Endpunkt mit einer niedrigeren Konfiguration übertraf. Die p90-Latenz sank um über 43 ms und der Durchsatz verbesserte sich unter der Testauslastung deutlich.
Wenn Perzentilmetriken für Sie neu sind, empfehle ich Ihnen, sie zu studieren und zu verstehen. Diese Kennzahlen sind für die Steuerung von Leistungsanalysen äußerst hilfreich. Hier sind einige Ressourcen, die Ihnen den Einstieg erleichtern:
- K6-Reaktionszeit
- p90 vs. p99
Abschluss
Nachdem Sie von dem Problem geträumt haben, mehrmals aufgewacht sind, um neue Lösungen auszuprobieren, und schließlich festgestellt haben, dass work_mem helfen könnte, besteht die nächste Herausforderung darin, den richtigen Wert für diese Konfiguration herauszufinden. ?
Der Standardwert von 4 MB für work_mem ist wie viele andere PostgreSQL-Einstellungen konservativ. Dadurch kann PostgreSQL auf kleineren Maschinen mit begrenzter Rechenleistung ausgeführt werden. Wir müssen jedoch aufpassen, dass die PostgreSQL-Instanz nicht aufgrund von Fehlern wegen unzureichendem Arbeitsspeicher zum Absturz kommt. Eine einzelne Abfrage kann, wenn sie komplex genug ist, ein Vielfaches des durch work_mem angegebenen Speichers verbrauchen, abhängig von der Anzahl der Vorgänge wie Sortierungen, Merge-Joins, Hash-Joins (beeinflusst durch hash_mem_multiplier), und mehr. Wie in der offiziellen Dokumentation angegeben:
Diese Tatsache muss bei der Wahl des Wertes berücksichtigt werden. Sortieroperationen werden für ORDER BY-, DISTINCT- und Merge-Joins verwendet. Hash-Tabellen werden in Hash-Joins, Hash-basierter Aggregation, Memoize-Knoten und Hash-basierter Verarbeitung von IN-Unterabfragen verwendet.
Leider gibt es keine Zauberformel für die Einstellung von work_mem. Dies hängt vom verfügbaren Speicher, der Arbeitslast und den Abfragemustern Ihres Systems ab. Das TimescaleDB-Team verfügt über ein Tool zur automatischen Optimierung und das Thema wird ausführlich diskutiert. Hier sind einige hervorragende Ressourcen, die Ihnen als Orientierung dienen:
- Alles, was Sie über work_mem wissen, ist falsch
- Wie soll ich work_mem für ein bestimmtes System optimieren
Aber am Ende lautet die Antwort meiner Meinung nach: TESTEN. TESTEN SIE HEUTE. MORGEN TESTEN. TESTEN SIE FÜR IMMER. Testen Sie weiter, bis Sie einen akzeptablen Wert für Ihren Anwendungsfall gefunden haben, der die Abfrageleistung verbessert, ohne Ihre Datenbank zu sprengen. ?
Das obige ist der detaillierte Inhalt vonPostgreSQL-Leistungsoptimierung: Die Kraft von work_mem. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!