• Zuhause
  • Artikel
  • Das Postgres-Handbuch zur Leistungsoptimierung: Abfragepläne
Veröffentlicht am 06-05-2019

Das Postgres-Handbuch zur Leistungsoptimierung: Abfragepläne

Verlangsamt sich Postgres auf dich? So identifizieren Sie Verbesserungsbereiche mithilfe von Abfrageplänen.

gojek.jobs

Postgres ist eine der am weitesten verbreiteten Open-Source-Datenbanken der Welt. Bei GOJEK sind viele unserer Produkte auch von Postgres abhängig. Wenn Sie jedoch eine Super-App in maßstäblichem Maßstab erstellen und betreiben, kann das Datenvolumen, das durch die Pipelines fließt, selbst die effizientesten Systeme verlangsamen.

Manchmal erleben wir auch eine Leistungsverschlechterung bei Postgres. Für ein Startup wie GOJEK ist das keine nachhaltige Sache.

Wir mussten die Dinge optimieren, um die Leistung zu verbessern. Also haben wir den Optimierer anvisiert.

Der Optimierer ist das Gehirn der Datenbank, die Abfragen interpretiert und die schnellste Ausführungsmethode bestimmt. Eine einzelne Abfrageoptimierung kann die Datenbankleistung drastisch steigern.

Lebenszyklus der Postgres-Abfrage

In diesem Beitrag wird beschrieben, wie Sie die Leistung von Postgres mit Tools wie EXPLAIN und ANALYZE (die Postgres bereitstellt) analysieren.

Lernen Sie EXPLAIN & ANALYZE kennen

EXPLAIN gibt eine genaue Aufschlüsselung einer Abfrage an. Der Ausführungsplan basiert auf den Statistiken der Tabelle und gibt den effizientesten Pfad zu den Daten an. Dabei werden verschiedene Datenbank-Paradigmen (z. B. Indizes) berücksichtigt. EXPLAIN errät nur einen Plan, den es für wahrscheinlich hält.

Hier kommt ANALYZE ins Spiel. ANALYZE führt grundsätzlich eine Abfrage aus, um die Verarbeitungszeit für die Ausführung einer Abfrage zu ermitteln.

Die Befehle EXPLAIN und ANALYZE tragen zur Verbesserung der Datenbankleistung in Postgres bei:

a) Anzeigen des Ausführungsplans, den der PostgreSQL-Planer für die angegebene Anweisung generiert
b) Den Befehl tatsächlich ausführen, um die Laufzeit anzuzeigen.

Den Rahmen finden

Nehmen wir an, wir haben eine Tabelle namens Schemata.

EXPLAIN SELECT * FROM Schemata;

FRAGEPLAN | Seq-Scan nach Schemata (Kosten = 0,00.,64 Zeilen = 328 Breite = 479)

SELECT * FROM pg_class Dabei gilt relname = 'schemen'

pg_class enthält die Metadaten zu den Tabellen.

Die Kosten im Abfrageplan werden anhand der folgenden Formel berechnet

COST = (Festplattenleseseiten * seq_page_cost) + (gescannte Zeilen * cpu_tuple_cost)

  • Festplatten gelesene Seiten und gescannte Zeilen sind die Eigenschaften von pg_class.
  • Seq-Seitenkosten sind die geschätzten Kosten für das Abrufen der Platte.
  • CPU-Tupelkosten sind die geschätzten Kosten für die Verarbeitung jeder Zeile.

z.B. KOSTEN (54 * 1,0) + (1000 * 0,01) = 64

Mal sehen, wie sich der Abfrageplan ändert, wenn wir in der select-Anweisung Filter anwenden.

EXPLAIN SELECT * FROM Schemas WHERE Status = "Aktiv"

FRAGEPLAN | Seq Scan auf Schemata (Kosten = 0.00..66.50 Zeilen = 960 Breite = 384) Filter: ((Status) :: Text = "Aktiv" :: Text)

Die geschätzten Kosten sind hier höher als bei der vorherigen Abfrage. Postgres führt zuerst einen Sequenzscan von mehr als 1000 Zeilen durch und filtert dann die Zeilen anhand der WHERE-Klausel aus.

Planungskosten-Konstanten

Die in diesem Abschnitt beschriebenen Kostenvariablen werden auf einer beliebigen Skala gemessen. Nur ihre relativen Werte sind wichtig. Wenn Sie alle Werte um denselben Faktor erhöhen oder verkleinern, ändert sich die Auswahl des Planers nicht. Standardmäßig basieren diese Kostenvariablen auf den Kosten für sequentielle Seitenabrufe. Das heißt, Seq_page_cost wird herkömmlicherweise auf 1,0 gesetzt, und die anderen Kostenvariablen werden mit Bezug darauf gesetzt. Sie können jedoch auch eine andere Skala verwenden, z. B. tatsächliche Ausführungszeiten in Millisekunden auf einer bestimmten Maschine.

Leider gibt es keine genau definierte Methode zur Bestimmung der idealen Werte für die Kostenvariablen. Sie werden am besten als Durchschnittswerte für die gesamte Mischung von Abfragen behandelt, die eine bestimmte Installation erhält. Dies bedeutet, dass eine Änderung dieser Daten aufgrund weniger Experimente sehr riskant ist. (Planerkostenkonstanten angeben).

EXPLAIN SELECT-Schemas.Regeln FROM Schemas_Regeln JOIN-Schemas ON (Schemas_Regeln.Scheme_id = Schemas.id) wobei Schema_Name = 'Wochenendschema';

Der Abfrageplaner entscheidet manchmal, einen zweistufigen Plan zu verwenden. Der Grund für die Verwendung eines Knotens mit zwei Plänen ist, dass der erste Plan die durch den Index identifizierten Zeilenpositionen in physikalische Reihenfolge sortiert, bevor er sie liest, und der andere Plan holt diese Zeilen tatsächlich aus der Tabelle.

Bis zu den Schrauben und Muttern

Der erste Plan, der die Sortierung durchführt, wird als Bitmap-Scan bezeichnet.

Die am häufigsten auftretenden Übereinstimmungen werden mit dem seq-Scan gescannt und die am wenigsten verbreiteten Übereinstimmungen werden mit dem Index-Scan gescannt. Alles dazwischen wird mit dem Bitmap-Heap-Scan und einem Index-Scan geprüft. Einer der Gründe dafür ist, dass die zufällige E / A im Vergleich zu sequentiellen E / A sehr langsam ist. Dies wird alles durch die Analyse von Statistiken gesteuert.

Bitmap-Heap-Scan

Ein Bitmap-Heap-Scan ist wie ein Seq-Scan - der Unterschied besteht darin, dass er nicht jede Festplattenseite besucht, sondern ANDs und ORs des jeweiligen Index zusammen durchsucht und nur die zu besuchenden Plattenseiten besucht. Dies unterscheidet sich von der Indexsuche, bei der der Index Zeile für Zeile in der Reihenfolge besucht wird, was dazu führt, dass die Plattenseiten mehrmals besucht werden. Ein Bitmap-Scan öffnet sequentiell eine kurze Liste von Festplattenseiten und erfasst jede zutreffende Zeile in jeder.

Sequenzieller Scan vs. Index-Scan

Es gibt Fälle, in denen ein sequenzieller Scan schneller ist als ein Index-Scan. Beim Lesen von Daten von einer Festplatte ist eine sequentielle Methode normalerweise schneller als das Lesen in zufälliger Reihenfolge. Dies liegt daran, dass für einen Index-Scan mehrere E / A für jede Zeile erforderlich sind. Dazu gehört das Nachschlagen einer Zeile im Index und darauf basierend das Abrufen und Abrufen der Zeile aus dem Speicher (Heap). Andererseits erfordert ein sequenzieller Scan eine einzelne E / A-Operation, um mehr als einen Block abzurufen, der mehrere Zeilen enthält.

Abfrageplan für JOINS

Das Optimierungsprogramm muss den richtigen Join-Algorithmus auswählen, wenn in der select-Anweisung mehrere Tabellen verknüpft werden sollen. Postgres verwendet drei verschiedene Arten von Verknüpfungsalgorithmen, die auf der Art der verwendeten Verknüpfung basieren.

Lass uns eintauchen

Verschachtelte Schleife: Hier kann der Planer für jedes der Elemente in der ersten Tabelle entweder einen sequentiellen Scan oder einen Index-Scan verwenden. Der Planer verwendet einen sequentiellen Scan, wenn die zweite Tabelle klein ist. Die grundlegende Logik der Wahl zwischen einem sequenziellen Scan und einem Index-Scan gilt auch hier. Hash Join: Bei diesem Algorithmus erstellt der Planer eine Hashtabelle der kleineren Tabelle auf dem Join-Schlüssel. Die größere Tabelle wird dann durchsucht und durchsucht die Hashtabelle nach den Zeilen, die die Join-Bedingung erfüllen. Dies erfordert viel Speicherplatz, um die Hashtabelle an erster Stelle zu speichern. Merge Join: Dies ähnelt dem Merge-Sortieralgorithmus. Hier sortiert der Planer die beiden zu fügenden Tabellen nach dem Join-Attribut. Die Tabellen werden dann parallel gescannt, um die übereinstimmenden Werte zu finden.

Weitere Informationen darüber, wie wir mit Join-Anweisungen erklären / analysieren können, finden Sie hier

Postgres Performance Tuning ist eine komplizierte Aufgabe. Die Komplexität besteht darin, aus den vielen von Postgres bereitgestellten Werkzeugen das geeignete "einstellbare" Verfahren zu finden. Wie Sie vielleicht bereits vermutet haben, gibt es keine Lösung für die Lösung von Leistungsproblemen in Postgres - der Anwendungsfall bestimmt die Abstimmungsanforderungen.

Bei den Themen, die wir in diesem Beitrag behandelt haben, geht es um das Erkennen von Problemen und Statistiken. Wir werden erklären, wie wir dies für eine Lösung zur Optimierung von Postgres in einem kommenden Beitrag verwenden können. Behalten Sie diesen Bereich im Auge und folgen Sie dem Blog für weitere Updates!

#DYK, dass wir nicht nur eine Super-App erstellen, sondern auch viel Zeit mit Lernen und Entwicklung verbringen? Bei GOJEK geht es nicht nur darum, Wissen zu erwerben und zu teilen, sondern auch coole Dinge zu bauen. Möchten Sie Teil dieser Wachstumskultur sein? Nun, wir stellen ein, also gehen Sie zu gojek.jobs und nutzen Sie die Gelegenheit, On-Demand-Plattformen neu zu definieren. Und auch etwas Neues lernen.

gojek.jobs

Siehe auch

Amazon Echo ist erstaunlich. Macht es uns auch anspruchsvoller?Gitcoin OSS Fund: Radikale Ergebnisse der zweiten RundeKann Ihr Telefon nicht ablegen? Versuche dies.Edgeware bei der NYC Blockchain Week ´19Grundlegende PC-Teile & ZusammenstellenKastifiziert: Knut Lingmann