Optimieren der Datenbankleistung - Entwickler lieben es normalerweise oder hassen es. Ich genieße das und möchte einige der Methoden teilen, die ich kürzlich verwendet habe, um schlecht ausgeführte Abfragen in PostgreSQL zu optimieren. Meine Methoden sind nicht erschöpfend, sondern ein Lehrbuch für diejenigen, die nur über das Stimmen stapfen.
Suchen Sie nach langsamen Abfragen
Der erste offensichtliche Weg, um mit der Abstimmung zu beginnen, besteht darin, bestimmte Operatoren zu finden, die schlecht funktionieren.
pg_stats_statements
Das Modul
pg_stats_statements ist ein
guter Anfang. Es verfolgt lediglich die Ausführungsstatistik von SQL-Anweisungen und kann eine einfache Möglichkeit sein, ineffiziente Abfragen zu finden.
Sobald Sie dieses Modul installiert haben, ist eine Systemansicht mit dem Namen
pg_stat_statements mit all ihren Eigenschaften verfügbar. Wenn er die Möglichkeit hat, genügend Daten zu sammeln, suchen Sie nach Abfragen mit einem relativ hohen Wert für
total_time. Konzentrieren Sie sich zuerst auf diese Operatoren.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
Das
auto_explain- Modul
ist auch nützlich, um langsame Abfragen zu finden, hat jedoch zwei offensichtliche Vorteile: Es registriert den tatsächlichen Ausführungsplan und unterstützt das Aufzeichnen verschachtelter Anweisungen mit der Option
log_nested_statements . Verschachtelte Anweisungen sind Anweisungen, die innerhalb einer Funktion ausgeführt werden. Wenn Ihre Anwendung viele Funktionen verwendet, ist auto_explain von unschätzbarem Wert, um detaillierte Ausführungspläne zu erhalten.
Die Option
log_min_duration steuert, welche Abfrageausführungspläne basierend auf ihrer Laufzeit protokolliert werden. Wenn Sie beispielsweise den Wert auf 1000 setzen, werden alle Datensätze registriert, die länger als 1 Sekunde dauern.
Index-Tuning
Eine weitere wichtige Optimierungsstrategie besteht darin, sicherzustellen, dass die Indizes korrekt verwendet werden. Voraussetzung ist, dass wir den Statistiksammler einbeziehen.
Postgres Statistics Collector ist ein erstklassiges Subsystem, das alle Arten nützlicher Leistungsstatistiken sammelt.
Durch Aktivieren dieses Kollektors erhalten Sie Tonnen von
pg_stat _... Ansichten , die alle Eigenschaften enthalten. Insbesondere fand ich dies besonders nützlich, um fehlende und nicht verwendete Indizes zu finden.
Fehlende Indizes
Fehlende Indizes sind möglicherweise eine der einfachsten Lösungen zur Verbesserung der Abfrageleistung. Sie sind jedoch keine Silberkugel und sollten korrekt verwendet werden (dazu später mehr). Wenn Sie den Statistikkollektor aktiviert haben, können Sie die folgende Abfrage (
Quelle ) ausführen.
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
Die Abfrage findet Tabellen mit mehr sequentiellen Scans (Index-Scans) als Index-Scans - ein klarer Hinweis darauf, dass der Index hilfreich ist. Dies sagt Ihnen nicht, für welche Spalten der Index erstellt werden soll, daher ist etwas mehr Arbeit erforderlich. Zu wissen, welche Tabellen sie benötigen, ist jedoch ein guter erster Schritt.
Nicht verwendete Indizes
Alle Entitäten indizieren, richtig? Wussten Sie, dass nicht verwendete Indizes die Schreibleistung beeinträchtigen können? Der Grund dafür ist, dass beim Erstellen des Postgres-Index die Aufgabe besteht, diesen Index nach Schreibvorgängen (INSERT / UPDATE / DELETE) zu aktualisieren. Das Hinzufügen eines Index ist daher ein Balanceakt, da es das Lesen von Daten beschleunigen kann (wenn sie korrekt erstellt wurden), aber Schreibvorgänge verlangsamt. Um nicht verwendete Indizes zu finden, können Sie die folgende Abfrage ausführen.
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
Hinweis zur Statistik der Entwicklungsumgebung
Das Verlassen auf Statistiken aus einer lokalen Entwicklungsdatenbank kann problematisch sein. Im Idealfall können Sie die oben genannten Statistiken von Ihrem Arbeitscomputer abrufen oder aus einer wiederhergestellten Arbeitssicherung generieren. Warum? Umgebungsfaktoren können das Verhalten des Postgres-Abfrageoptimierers ändern. Zwei Beispiele:
- Wenn der Computer weniger Speicher hat, kann PostgreSQL möglicherweise keinen Hash-Join ausführen, andernfalls kann und wird es schneller ausgeführt.
- Wenn die Tabelle nicht so viele Zeilen enthält (wie in der Entwicklungsdatenbank), zieht PostgresSQL möglicherweise einen sequentiellen Scan der Tabelle vor, anstatt einen verfügbaren Index zu verwenden. Bei kleinen Tischgrößen kann Seq Scan schneller sein. (Hinweis: Sie können ausführen
SET enable_seqscan = OFF
in einer Sitzung, sodass der Optimierer Indizes verwendet, auch wenn sequentielle Scans schneller sein können. Dies ist nützlich, wenn Sie mit Entwicklungsdatenbanken arbeiten, die nicht viele Daten enthalten.
Ausführungspläne verstehen
Nachdem Sie einige langsame Fragen gefunden haben, ist es Zeit, den Spaß zu beginnen.
ERKLÄREN
Der Befehl
EXPLAIN ist sicherlich erforderlich, wenn Sie Abfragen
einrichten . Er sagt dir, was wirklich passiert. Um es zu verwenden, fügen Sie einfach
EXPLAIN zur Abfrage hinzu und führen Sie es aus. PostgreSQL zeigt Ihnen den verwendeten Ausführungsplan.
Wenn Sie EXPLAIN zum Einstellen verwenden, empfehle ich, immer die Option
ANALYZE (
EXPLAIN ANALYZE ) zu verwenden, da dies genauere Ergebnisse liefert. Die Option ANALYZE führt die Anweisung tatsächlich aus (anstatt sie nur auszuwerten) und erklärt sie dann.
Lassen Sie uns ein Bad nehmen und beginnen, die Ausgabe von
EXPLAIN zu verstehen. Hier ist ein Beispiel:
Knoten
Das erste, was zu verstehen ist, ist, dass jeder eingerückte Block mit dem vorherigen "->" (zusammen mit der obersten Zeile) als Knoten bezeichnet wird. Ein Knoten ist eine logische Arbeitseinheit (ein „Schritt“, wenn Sie möchten) mit den damit verbundenen Kosten und der Vorlaufzeit. Die auf jedem Knoten angegebenen Kosten und Zeit sind kumulativ und bringen alle untergeordneten Knoten zusammen. Dies bedeutet, dass die oberste Zeile (Knoten) die Gesamtkosten und die tatsächliche Zeit für den gesamten Bediener anzeigt. Dies ist wichtig, da Sie leicht einen Drilldown durchführen können, um festzustellen, welche Knoten der Engpass sind.
Kosten
cost=146.63..148.65
Die erste Zahl sind die Anfangskosten (die Kosten für den Erhalt des ersten Datensatzes), und die zweite Zahl sind die Kosten für die Verarbeitung des gesamten Knotens (Gesamtkosten von Anfang bis Ende).
Tatsächlich sind dies die Kosten, die PostgreSQL-Schätzungen erfüllen müssen, um die Anweisung auszuführen. Diese Nummer bedeutet nicht, wie lange es dauern wird, bis die Anforderung abgeschlossen ist, obwohl normalerweise eine direkte Beziehung erforderlich ist, um sie abzuschließen. Die Kosten sind eine Kombination von 5 Arbeitskomponenten, die zur Bewertung der erforderlichen Arbeit verwendet werden: sequentielle Stichprobe, inkonsistente (zufällige) Stichprobe, Zeilenverarbeitung, Verarbeitungsoperator (Funktion) und Aufzeichnung des Verarbeitungsindex. Die Kosten sind die Eingabe / Ausgabe und die Prozessorlast, und es ist wichtig zu wissen, dass PostgresSQL aufgrund der relativ hohen Kosten davon ausgeht, dass mehr Arbeit erforderlich ist. Der Optimierer entscheidet anhand der Kosten, welcher Ausführungsplan verwendet werden soll. Der Optimierer bevorzugt niedrigere Kosten.
Aktuelle Zeit
actual time=55.009..55.012
In Millisekunden ist die erste Zahl die Startzeit (Zeit zum Abrufen des ersten Datensatzes) und die zweite Zahl die Zeit, die zum Verarbeiten des gesamten Knotens erforderlich ist (Gesamtzeit von Anfang bis Ende). Leicht zu verstehen, oder?
Im obigen Beispiel dauerte es 55,009 ms, um den ersten Datensatz zu erhalten, und 55,012 ms, um den gesamten Knoten fertigzustellen.
Erfahren Sie mehr über Ausführungspläne.
Es gibt einige wirklich gute Artikel zum Verständnis der EXPLAIN-Ergebnisse. Anstatt zu versuchen, sie hier noch einmal zu erzählen, empfehle ich, sich die Zeit zu nehmen, um sie wirklich zu verstehen, indem Sie diese 2 wunderbaren Ressourcen besuchen:
Tuning anfordern
Jetzt, da Sie wissen, welche Operatoren schlecht arbeiten und Ihre Ausführungspläne sehen können, ist es an der Zeit, Ihre Abfrage zu optimieren, um die Leistung zu verbessern. Hier nehmen Sie Änderungen an Ihren Abfragen vor und / oder fügen Indizes hinzu, um einen besseren Ausführungsplan zu erhalten. Beginnen Sie mit Engpässen und prüfen Sie, ob Sie Änderungen vornehmen können, um Kosten und / oder Vorlaufzeiten zu reduzieren.
Datencache und Kostenhinweis
Wenn Sie Änderungen vornehmen und Implementierungspläne bewerten, ist es wichtig zu wissen, dass zukünftige Implementierungen möglicherweise vom Zwischenspeichern von Daten abhängen, die eine Vorstellung von den besten Ergebnissen vermitteln, um festzustellen, ob Verbesserungen erzielt werden. Wenn Sie die Anforderung einmal ausführen, eine Korrektur vornehmen und ein zweites Mal ausführen, wird sie höchstwahrscheinlich viel schneller ausgeführt, auch wenn der Ausführungsplan nicht günstiger ist. Dies liegt daran, dass PostgreSQL die beim ersten Start verwendeten Daten zwischenspeichern und beim zweiten Start verwenden kann. Daher müssen Sie die Abfragen mindestens dreimal ausführen und die Ergebnisse mitteln, um die Kosten zu vergleichen.
Dinge, die ich gelernt habe, können helfen, Ausführungspläne zu verbessern:
- Indizes
- Schließen Sie sequentielles Scannen (Seq Scan) durch Hinzufügen von Indizes aus (wenn die Tabellengröße nicht klein ist).
- Achten Sie bei Verwendung eines mehrspaltigen Index darauf, dass Sie die Reihenfolge beachten, in der Sie die enthaltenen Spalten definieren - Weitere Informationen
- Probieren Sie Indizes aus, die für häufig verwendete Daten sehr selektiv sind. Dies wird ihre Verwendung effizienter machen.
- Zustand WO
- Vermeiden Sie LIKE
- Vermeiden Sie Funktionsaufrufe in der WHERE-Klausel
- Vermeiden Sie große Bedingungen in ()
- JOINs
- Versuchen Sie beim Verknüpfen von Tabellen, einen einfachen Gleichheitsausdruck in der ON-Klausel zu verwenden (d. H. A.id = b.person_id). Auf diese Weise können Sie effizientere Join-Methoden verwenden (d. H. Hash-Join, nicht Nested-Loop-Join).
- Konvertieren Sie Unterabfragen nach Möglichkeit in JOIN-Anweisungen, da dies dem Optimierer normalerweise ermöglicht, das Ziel zu verstehen und möglicherweise den besten Plan auszuwählen.
- Verwenden Sie COMPOUNDS richtig: Verwenden Sie GROUP BY oder DISTINCT, nur weil Sie doppelte Ergebnisse erhalten? Dies weist normalerweise auf eine missbräuchliche Verwendung von JOINs hin und kann zu höheren Kosten führen.
- Wenn der Ausführungsplan Hash Join verwendet, kann es sehr langsam sein, wenn die Schätzungen der Tabellengröße falsch sind. Stellen Sie daher sicher, dass Ihre Tabellenstatistiken korrekt sind, indem Sie die Saugstrategie überprüfen .
- Vermeiden Sie nach Möglichkeit korrelierte Unterabfragen . Sie können die Kosten einer Anfrage erheblich erhöhen
- Verwenden Sie EXISTS, wenn Sie das Vorhandensein von Zeichenfolgen anhand eines Kriteriums überprüfen, da es einem Kurzschluss ähnelt (stoppt die Verarbeitung, wenn mindestens eine Übereinstimmung gefunden wird).
- Allgemeine Empfehlungen