Diagramme, Berichte und Analysen - all dies ist im Backoffice eines jeden, auch sehr kleinen Unternehmens, irgendwie vorhanden. Wenn es in regulären Tabellen in Excel / Numbers / Libre überfüllt ist, die Daten jedoch noch nicht sehr groß sind, werden herkömmliche Lösungen für unternehmensinterne Anforderungen häufig mithilfe relationaler Datenbanken wie PostgreSQL, MySQL oder MariaDB erstellt.
Diese Datenbanken sind kostenlos, dank SQL können sie bequem in andere Komponenten des Systems integriert werden, sie sind beliebt und die meisten Entwickler und Analysten können mit ihnen arbeiten. Die Last (Verkehr und Volumen), die sie verdauen können, ist groß genug, um problemlos durchzuhalten, bis sich das Unternehmen komplexere (und teurere) Lösungen für Analysen und Berichte leisten kann.
Ausgangsposition
Aber selbst in einer Technologie, die wiederholt untersucht wurde, gibt es immer verschiedene Nuancen, die die Sorgen der Ingenieure plötzlich verstärken können. Neben der Zuverlässigkeit ist das am häufigsten genannte Problem bei Datenbanken deren Leistung. Offensichtlich nimmt mit zunehmender Datenmenge die DB-Antwortrate ab, aber wenn dies vorhersehbar geschieht und mit der Zunahme der Last übereinstimmt, ist dies nicht so schlimm. Sie können immer im Voraus sehen, wann die Datenbank Aufmerksamkeit erfordert, und ein Upgrade oder einen Übergang zu einer grundlegend anderen Datenbank planen. Viel schlimmer, wenn sich die Datenbankleistung unvorhersehbar verschlechtert.
Das Thema der Verbesserung der Datenbankleistung ist so alt wie die Welt und sehr umfangreich. In diesem Artikel möchte ich mich nur auf eine Richtung konzentrieren. Bei der Bewertung der Effektivität von Abfrageplänen in einer PostgreSQL-Datenbank sowie bei der Änderung dieser Effizienz im Laufe der Zeit, um das Verhalten des Datenbankplaners vorhersehbarer zu machen.
Trotz der Tatsache, dass viele der Dinge, die diskutiert werden, auf alle neueren Versionen dieser Datenbank anwendbar sind, bedeuten die folgenden Beispiele Version 11.2, letztere im Moment.
Bevor wir uns mit den Details befassen, ist es sinnvoll, abzuschweifen und ein paar Worte darüber zu sagen, woher Leistungsprobleme in relationalen Datenbanken kommen können. Womit ist die Datenbank genau beschäftigt, wenn sie "langsamer" wird? Mangel an Speicher (eine große Anzahl von Festplatten- oder Netzwerkzugriffen), ein schwacher Prozessor, all dies sind offensichtliche Probleme mit klaren Lösungen, aber was kann die Ausführungsgeschwindigkeit der Abfrage noch beeinflussen?
Erfrischen Sie Erinnerungen
Damit die Datenbank auf die SQL-Abfrage antworten kann, muss ein Abfrageplan erstellt werden (in den Tabellen und Spalten, um zu sehen, welche Indizes benötigt werden, was von dort ausgewählt werden muss, mit was verglichen werden soll, wie viel Speicher benötigt wird usw.). Dieser Plan wird in Form eines Baums gebildet, dessen Knoten nur einige typische Operationen mit unterschiedlicher Rechenkomplexität sind. Hier sind einige davon zum Beispiel (N ist die Anzahl der Zeilen, mit denen die Operation ausgeführt werden soll):
| Bedienung | Was wird gemacht? | Kosten |
|---|
| SELECT ... WHERE ... Datenabrufvorgänge |
| Seq Scan | Wir laden jede Zeile aus der Tabelle und überprüfen den Zustand. | O (N) |
Index-Scan
(B-Baum-Index) | Die Daten befinden sich direkt im Index, daher suchen wir nach Bedingungen nach den erforderlichen Elementen des Index und nehmen die Daten von dort. | O (log (N)), suche nach einem Element in einem sortierten Baum. |
Index-Scan
(Hash-Index) | Die Daten befinden sich direkt im Index, daher suchen wir nach Bedingungen nach den erforderlichen Elementen des Index und nehmen die Daten von dort. | O (1), Suche nach einem Element in einer Hash-Tabelle, ohne die Kosten für die Erstellung von Hashes |
| Bitmap-Heap-Scan | Wir wählen die Nummern der erforderlichen Zeilen nach Index aus, laden dann nur die erforderlichen Zeilen und führen mit ihnen zusätzliche Prüfungen durch. | Index-Scan + Seq-Scan (M),
Dabei ist M die Anzahl der Zeilen, die nach dem Index-Scan gefunden wurden. Es wird angenommen, dass M << N, d.h. Index ist nützlicher als Seq Scan. |
| Verknüpfungsoperationen (JOIN, SELECT aus mehreren Tabellen) |
| Verschachtelte Schleife | Suchen Sie für jede Zeile aus der linken Tabelle nach einer geeigneten Zeile in der rechten Tabelle. | O (N 2 ).
Wenn jedoch eine der Tabellen viel kleiner als die andere ist (Wörterbuch) und praktisch nicht mit der Zeit wächst, können die tatsächlichen Kosten auf O (N) sinken. |
| Hash beitreten | Für jede Zeile aus der linken und rechten Tabelle berücksichtigen wir den Hash, wodurch die Anzahl der Suchvorgänge nach möglichen Verbindungsoptionen verringert wird. | O (N), aber im Fall einer sehr ineffizienten Hash-Funktion oder einer großen Anzahl identischer Felder für die Verbindung kann O (N 2 ) vorhanden sein. |
| Join zusammenführen | Nach Bedingungen sortieren wir die linke und rechte Tabelle, danach kombinieren wir die beiden sortierten Listen | O (N * log (N))
Kosten sortieren + Liste durchgehen. |
| Aggregationsoperationen (GROUP BY, DISTINCT) |
| Gruppenaggregat | Wir sortieren die Tabelle nach der Aggregationsbedingung und gruppieren dann in der sortierten Liste die benachbarten Zeilen. | O (N * log (N)) |
| Hash-Aggregat | Wir betrachten den Hash für die Aggregationsbedingung für jede Zeile. Für Zeilen mit demselben Hash führen wir eine Aggregation durch. | O (N) |
Wie Sie sehen können, hängen die Kosten einer Abfrage stark davon ab, wie sich die Daten in den Tabellen befinden und wie diese Reihenfolge den verwendeten Hash-Operationen entspricht. Verschachtelte Schleifen können trotz ihrer Kosten in O (N
2 ) rentabler sein als Hash Join oder Merge Join, wenn eine der verknüpften Tabellen zu einer oder mehreren Zeilen degeneriert.
Zu den Kosten gehört neben den CPU-Ressourcen auch die Speichernutzung. Da beide Ressourcen begrenzt sind, muss der Abfrageplaner einen Kompromiss finden. Wenn die Verbindung von zwei Tabellen über Hash Join mathematisch rentabler ist, aber im Speicher einfach kein Platz für eine so große Hash-Tabelle vorhanden ist, kann die Datenbank beispielsweise gezwungen sein, Merge Join zu verwenden. Eine "langsame" verschachtelte Schleife benötigt im Allgemeinen keinen zusätzlichen Speicher und ist bereit, direkt nach dem Start Ergebnisse zu erzielen.
Die relativen Kosten dieser Vorgänge sind in der Grafik deutlicher dargestellt. Dies sind keine absoluten Zahlen, sondern nur ein ungefähres Verhältnis verschiedener Operationen.
Das Nested Loop-Diagramm "beginnt" unten, weil Es erfordert keine zusätzlichen Berechnungen oder Speicherzuweisungen oder das Kopieren von Zwischendaten, hat jedoch O (N
2 ) -Kosten. Merge Join und Hash Join haben höhere Anfangskosten, aber nach einigen N Werten beginnen sie, die verschachtelte Schleife rechtzeitig zu schlagen. Der Planer versucht, den Plan mit den niedrigsten Kosten auszuwählen, und hält sich in der obigen Tabelle an verschiedene Vorgänge mit unterschiedlichen N (grüner gestrichelter Pfeil). Mit der Anzahl der Zeilen bis zu N1 ist es rentabler, Nested Loop zu verwenden. Von N1 bis N2 ist es rentabler, Join zusammenzuführen. Nach N2 wird es für Hash Join rentabler. Für Hash Join ist jedoch Speicher erforderlich, um Hash-Tabellen zu erstellen. Und wenn N3 erreicht wird, wird dieser Speicher nicht mehr ausreichend, was zur erzwungenen Verwendung von Merge Join führt.
Bei der Auswahl eines Plans schätzt der Planer die Kosten für jede Operation im Plan anhand einer Reihe relativer Kosten einiger „atomarer“ Operationen in der Datenbank. B. Berechnungen, Vergleiche, Laden einer Seite in den Speicher usw. Hier ist eine Liste einiger dieser Parameter aus der Standardkonfiguration, von denen es nicht viele gibt:
| Relative Kostenkonstante | Standardwert |
|---|
| seq_page_cost | 1.0 |
| random_page_cost | 4.0 |
| cpu_tuple_cost | 0,01 |
| cpu_index_tuple_cost | 0,005 |
| cpu_operator_cost | 0,0025 |
| parallel_tuple_cost | 0,1 |
| parallel_setup_cost | 1000.0 |
Zwar gibt es nur wenige Konstanten, aber Sie müssen immer noch genau das „N“ kennen, dh genau, wie viele Zeilen aus den vorherigen Ergebnissen in jeder solchen Operation verarbeitet werden müssen. Die Obergrenze ist hier offensichtlich - die Datenbank „weiß“, wie viele Daten sich in einer Tabelle befinden, und kann immer „maximal“ berechnen. Wenn Sie beispielsweise zwei Tabellen mit jeweils 100 Zeilen haben, kann das Verknüpfen dieser Tabellen 0 bis 10.000 Zeilen in der Ausgabe erzeugen. Dementsprechend kann die nächste Eingabeoperation bis zu 10.000 Zeilen haben.
Wenn Sie jedoch zumindest ein wenig über die Art der Daten in den Tabellen wissen, kann diese Anzahl von Zeilen genauer vorhergesagt werden. Wenn Sie beispielsweise für zwei Tabellen mit 100 Zeilen aus dem obigen Beispiel im Voraus wissen, dass der Join nicht 10.000 Zeilen, sondern dieselben 100 Zeilen erzeugt, werden die geschätzten Kosten für die nächste Operation erheblich reduziert. In diesem Fall könnte dieser Plan effektiver sein als andere.
Out-of-the-Box-Optimierung
Damit der Scheduler die Größe der Zwischenergebnisse genauer vorhersagen kann, verwendet PostgreSQL die Statistiksammlung für Tabellen, die in pg_statistic oder in seiner besser lesbaren Version - in pg_stats - akkumuliert ist. Sie wird automatisch beim Start des Vakuums oder explizit mit dem Befehl ANALYZE aktualisiert. In dieser Tabelle werden verschiedene Informationen darüber gespeichert, welche Daten und welche Art von Natur in den Tabellen enthalten sind. Insbesondere Histogramme von Werten, Prozentsatz leerer Felder und andere Informationen. Der Planer verwendet all dies, um die Datenmenge für jede Operation im Planbaum genauer vorherzusagen und somit die Betriebskosten und den Plan als Ganzes genauer zu berechnen.
Nehmen Sie zum Beispiel die Abfrage:
SELECT t1.important_value FROM t1 WHERE t1.a > 100
Angenommen, das Histogramm der Werte in der Spalte „t1.a“ ergab, dass Werte über 100 in ungefähr 1% der Zeilen der Tabelle gefunden werden. Dann können wir vorhersagen, dass eine solche Stichprobe etwa ein Hundertstel aller Zeilen aus der Tabelle „t1“ zurückgibt.
Die Datenbank bietet Ihnen die Möglichkeit, die prognostizierten Kosten des Plans mithilfe des Befehls EXPLAIN und die tatsächliche Betriebszeit mithilfe von EXPLAIN ANALYZE anzuzeigen.
Es scheint, dass mit automatischen Statistiken jetzt alles in Ordnung sein sollte, aber es kann Schwierigkeiten geben. Es gibt einen
guten Artikel von Citus Data , der ein Beispiel für die Ineffizienz automatischer Statistiken und die Erfassung zusätzlicher Statistiken mithilfe von CREATE STATISTICS (verfügbar mit PG 10.0) enthält.
Für den Planer gibt es also zwei Fehlerquellen bei der Kostenberechnung:
- Die relativen Kosten für primitive Operationen (seq_page_cost, cpu_operator_cost usw.) können standardmäßig stark von der Realität abweichen (CPU-Kosten 0,01, Kosten für das Laden von srq-Seiten - 1 oder 4 für zufälliges Laden von Seiten). Weit davon entfernt, dass 100 Vergleiche 1 Seitenladung entsprechen.
- Fehler beim Vorhersagen der Anzahl der Zeilen in Zwischenoperationen. Die tatsächlichen Betriebskosten können in diesem Fall stark von der Prognose abweichen.
Bei komplexen Abfragen kann das Erstellen und Prognostizieren aller möglichen Pläne viel Zeit in Anspruch nehmen. Was nützt es, Daten in 1 Sekunde zurückzugeben, wenn die Datenbank nur eine Minutenanforderung geplant hat? PostgreSQL verfügt über einen Geqo-Optimierer für diese Situation. Es handelt sich um einen Scheduler, der nicht alle möglichen Pläne erstellt, sondern mit einigen zufälligen Plänen beginnt und die besten vervollständigt und Möglichkeiten zur Kostensenkung vorhersagt. All dies verbessert auch nicht die Genauigkeit der Prognose, obwohl es die Suche nach mindestens einem mehr oder weniger optimalen Plan beschleunigt.
Plötzliche Pläne - Konkurrenten
Wenn alles gut geht, wird Ihre Anfrage so schnell wie möglich erfüllt. Wenn die Datenmenge zunimmt, nimmt die Geschwindigkeit der Abfrageausführung in der Datenbank allmählich zu, und nach einiger Zeit können Sie bei Beobachtung grob vorhersagen, wann der Speicher oder die Anzahl der CPU-Kerne erhöht oder der Cluster usw. erweitert werden muss.
Wir müssen jedoch berücksichtigen, dass der optimale Plan Konkurrenten mit engen Ausführungskosten hat, die wir nicht sehen. Und wenn die Datenbank plötzlich den Abfrageplan in einen anderen ändert, ist dies eine Überraschung. Es ist gut, wenn die Datenbank zu einem effizienteren Plan springt. Und wenn nicht? Schauen wir uns zum Beispiel das Bild an. Dies sind die prognostizierten Kosten und die Echtzeit für die Umsetzung von zwei Plänen (rot und grün):
Hier wird ein Plan in Grün und der nächste „Konkurrent“ in Rot angezeigt. Die gepunktete Linie zeigt eine grafische Darstellung der projizierten Kosten, die durchgezogene Linie ist die Echtzeit. Der grau gestrichelte Pfeil zeigt die Planerauswahl.
Angenommen, an einem schönen Freitagabend erreicht die vorhergesagte Anzahl von Zeilen in einer Zwischenoperation N1 und die „rote“ Prognose beginnt, die „grüne“ zu übertreffen. Der Scheduler beginnt damit. Die tatsächliche Ausführungszeit der Abfrage springt sofort (Umschalten von einer grünen durchgezogenen auf eine rote Linie), dh der Zeitplan für die Datenbankverschlechterung hat die Form eines Schritts (oder möglicherweise einer „Wand“). In der Praxis kann eine solche "Wand" die Ausführungszeit der Abfrage um eine Größenordnung oder mehr erhöhen.
Es ist anzumerken, dass diese Situation wahrscheinlich eher für das Backoffice und die Analyse als für das Frontend typisch ist, da letzteres normalerweise für mehr gleichzeitige Abfragen angepasst ist und daher einfachere Abfragen in der Datenbank verwendet, bei denen der Fehler bei Planvorhersagen geringer ist. Wenn es sich um eine Datenbank für Berichte oder Analysen handelt, können Abfragen beliebig komplex sein.
Wie kann man damit leben?
Es stellt sich die Frage: War es irgendwie möglich, solche unsichtbaren Pläne unter Wasser vorauszusehen? Das Problem ist schließlich nicht, dass sie nicht optimal sind, sondern dass der Wechsel zu einem anderen Plan unvorhersehbar und nach dem Gesetz der Gemeinheit im unglücklichsten Moment dafür erfolgen kann.
Leider können Sie sie nicht direkt sehen, aber Sie können nach alternativen Plänen suchen, indem Sie die tatsächlichen Gewichte ändern, mit denen sie ausgewählt werden. Die Bedeutung dieses Ansatzes besteht darin, den aktuellen Plan, den der Planer für optimal hält, aus dem Blickfeld zu entfernen, damit einer seiner engsten Konkurrenten optimal wird und er somit durch das EXPLAIN-Team gesehen werden kann. Durch regelmäßige Überprüfung der Kostenänderungen bei solchen „Wettbewerbern“ und im Hauptplan können Sie die Wahrscheinlichkeit abschätzen, mit der die Datenbank bald zu einem anderen Plan „springt“.
Sie können nicht nur Daten zu Prognosen alternativer Pläne sammeln, sondern diese auch ausführen und deren Leistung messen. Dies gibt auch einen Überblick über das interne „Wohlbefinden“ der Datenbank.
Mal sehen, welche Werkzeuge wir für solche Experimente haben.
Erstens können Sie bestimmte Vorgänge mithilfe von Sitzungsvariablen explizit "verbieten". Praktischerweise müssen sie nicht in der Konfiguration geändert werden und die Datenbank wird neu geladen. Ihr Wert ändert sich nur in der aktuell geöffneten Sitzung und wirkt sich nicht auf andere Sitzungen aus, sodass Sie direkt mit realen Daten experimentieren können. Hier ist eine Liste von ihnen mit Standardwerten. Fast alle Operationen sind enthalten:
| Verwendete Operationen | Standardwert |
|---|
enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_seqscan
enable_sort
enable_tidscan
enable_parallel_hash
enable_partition_pruning | auf |
enable_partitionwise_join
enable_partitionwise_aggregate | aus |
Indem wir bestimmte Vorgänge verbieten oder zulassen, zwingen wir den Scheduler, andere Pläne auszuwählen, die wir mit demselben EXPLAIN-Befehl sehen können. Tatsächlich verbietet das „Verbot“ von Operationen nicht deren Verwendung, sondern erhöht lediglich ihre Kosten erheblich. In PostgreSQL verursacht jede „verbotene“ Operation automatisch Kosten in Höhe von 10 Milliarden konventionellen Einheiten. Darüber hinaus kann sich in EXPLAIN das Gesamtgewicht des Plans als unerschwinglich hoch herausstellen, aber vor dem Hintergrund dieser zehn Milliarden ist das Gewicht der verbleibenden Operationen deutlich sichtbar, da es normalerweise in kleinere Aufträge passt.
Von besonderem Interesse sind zwei der folgenden Operationen:
- Hash Join. Seine Komplexität ist O (N), aber bei einem Fehler mit einer Prognose in der Höhe des Ergebnisses können Sie nicht in den Speicher passen und müssen Merge Join mit einem Preis von O (N * log (N)) durchführen.
- Verschachtelte Schleife. Seine Komplexität ist O (N 2 ), daher beeinflusst der Fehler in der Größenprognose quadratisch die Geschwindigkeit einer solchen Verbindung.
Nehmen wir zum Beispiel einige reelle Zahlen aus Abfragen, deren Optimierung wir in unserem Unternehmen durchgeführt haben.
Plan 1. Bei allen zulässigen Operationen betrugen die Gesamtkosten des optimalsten Plans 274962,09 Einheiten.
Plan 2. Mit der "verbotenen" verschachtelten Schleife stiegen die Kosten auf 40000534153,85. Diese 40 Milliarden, die den größten Teil der Kosten ausmachen, sind trotz des Verbots das Vierfache der verwendeten verschachtelten Schleife. Und die verbleibenden 534153,85 - dies ist genau die Prognose der Kosten aller anderen Operationen im Plan. Wie wir sehen, ist es ungefähr doppelt so hoch wie die Kosten des optimalen Plans, das heißt, es liegt nahe genug daran.
Plan 3. Mit dem „verbotenen“ Hash-Join betrugen die Kosten 383253,77. Der Plan wurde wirklich ohne Verwendung der Hash-Join-Operation erstellt, da wir keine Milliarden sehen. Seine Kosten sind jedoch 30% höher als die des Optimums, was ebenfalls sehr nahe liegt.
In Wirklichkeit waren die Ausführungszeiten der Abfrage wie folgt:
Plan 1 (alle Operationen erlaubt) wurde in ~ 9 Minuten abgeschlossen.
Plan 2 (mit der "verbotenen" verschachtelten Schleife) wurde in 1,5 Sekunden abgeschlossen.
Plan 3 (mit einem "verbotenen" Hash-Join) wurde in ~ 5 Minuten abgeschlossen.
Der Grund ist, wie Sie sehen können, die fehlerhafte Vorhersage der Kosten der verschachtelten Schleife. In der Tat wird beim Vergleich von EXPLAIN mit EXPLAIN ANALYZE ein Fehler mit der Definition dieses unglücklichen N in einer Zwischenoperation festgestellt. Anstelle einer vorhergesagten einzelnen Zeile stieß die verschachtelte Schleife auf mehrere tausend Zeilen, wodurch sich die Ausführungszeit der Abfrage um einige Größenordnungen erhöhte.
Einsparungen mit dem "verbotenen" Hash-Join sind mit dem Ersetzen von Hashing durch Sortieren und Zusammenführen verbunden, was in diesem Fall schneller funktionierte als Hash-Join. Beachten Sie, dass dieser Plan 2 in Wirklichkeit fast zweimal schneller ist als der "optimale" Plan 1. Obwohl vorhergesagt wurde, dass er langsamer sein wird.
Wenn Ihre Anforderung plötzlich (nach einem DB-Upgrade oder nur von selbst) viel länger als zuvor ausgeführt wird, versuchen Sie in der Praxis zunächst, entweder Hash Join oder Nested Loop zu verweigern, und prüfen Sie, wie sich dies auf die Geschwindigkeit der Abfrage auswirkt. In einem erfolgreichen Fall können Sie zumindest einen neuen nicht optimalen Plan verbieten und zum vorherigen schnellen zurückkehren.
Dazu müssen Sie die PostgreSQL-Konfigurationsdateien bei einem Datenbankneustart nicht ändern. In jeder Konsole ist es ganz einfach, den Wert der gewünschten Variablen für eine geöffnete Sitzung aus der Datenbank zu ändern. Die verbleibenden Sitzungen sind nicht betroffen. Die Konfiguration wird nur für Ihre aktuelle Sitzung geändert. Zum Beispiel so:
SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … ( )
Die zweite Möglichkeit, die Wahl des Plans zu beeinflussen, besteht darin, die Gewichte von Operationen auf niedriger Ebene zu ändern. Hier gibt es kein universelles Rezept, aber wenn Sie beispielsweise eine Datenbank mit einem "aufgewärmten" Cache haben und die gesamten Daten im Speicher gespeichert sind, unterscheiden sich die Kosten für das sequentielle Laden von Seiten wahrscheinlich nicht von den Kosten für das Laden einer zufälligen Seite. Während in der Standardkonfiguration zufällig 4-mal teurer ist als sequentiell.
In einem anderen Beispiel betragen die bedingten Kosten für die parallele Verarbeitung standardmäßig 1000, während die Kosten für das Laden einer Seite 1,0 betragen. Es ist sinnvoll, zunächst jeweils nur einen der Parameter zu ändern, um festzustellen, ob sich dies auf die Wahl des Plans auswirkt. Am einfachsten ist es, den Parameter auf 0 oder einen hohen Wert (1 Million) zu setzen.
Beachten Sie jedoch, dass Sie durch eine Verbesserung der Leistung in einer Anforderung die Leistung in einer anderen beeinträchtigen können. Im Allgemeinen gibt es ein weites Feld für Experimente. Es ist besser, sie einzeln zu ändern.
Alternative Behandlungsmöglichkeiten
Eine Geschichte über einen Scheduler wäre unvollständig, ohne mindestens zwei PostgreSQL-Erweiterungen zu erwähnen.
Das erste ist
SR_PLAN , um den berechneten Plan zu speichern und seine weitere Verwendung zu erzwingen. Dies trägt dazu bei, das Datenbankverhalten in Bezug auf die Planauswahl vorhersehbarer zu machen.
Das zweite ist das
Adaptive Query Optimizer , das Feedback an den Scheduler von der Echtzeitausführung der Abfrage implementiert, dh der Scheduler misst die tatsächlichen Ergebnisse der ausgeführten Abfrage und passt seine Pläne in Zukunft in diesem Sinne an. Die Datenbank ist daher für bestimmte Daten und Abfragen "selbstoptimierend".
Was macht die Datenbank sonst noch, wenn sie langsamer wird?
Nachdem wir die Abfrageplanung mehr oder weniger geregelt haben, werden wir sehen, was sowohl in der Datenbank selbst als auch in den Anwendungen, die sie verwenden, um die maximale Leistung zu erzielen, noch verbessert werden kann.
Angenommen, der Abfrageplan ist bereits optimal. Wenn wir die offensichtlichsten Probleme ausschließen (wenig Speicher oder eine langsame Festplatte / ein langsames Netzwerk), fallen immer noch Kosten für die Berechnung der Hashes an. Es gibt wahrscheinlich große Möglichkeiten für zukünftige Verbesserungen von PostgreSQL (unter Verwendung der GPU oder sogar der SSE2 / SSE3 / AVX-Anweisungen der CPU), aber dies wurde bisher nicht durchgeführt und Hash-Berechnungen nutzen fast nie die Hardwarefunktionen der Hardware. Sie können ein wenig in dieser Datenbank helfen.
Wenn Sie bemerken, werden Indizes in PostgreSQL standardmäßig als B-Tree erstellt. Ihr Nutzen ist, dass sie sehr vielseitig sind. Ein solcher Index kann sowohl mit Gleichheitsbedingungen als auch mit Vergleichsbedingungen (mehr oder weniger) verwendet werden. Das Finden eines Elements in einem solchen Index ist ein logarithmischer Aufwand. Wenn Ihre Abfrage jedoch nur eine Gleichheitsbedingung enthält, können Indizes auch als Hash-Index erstellt werden, dessen Kosten konstant sind.
Außerdem können Sie weiterhin versuchen, die Anforderung so zu ändern, dass ihre parallele Ausführung verwendet wird. Um genau zu verstehen, wie man es umschreibt, ist es am besten, sich mit der Liste der Fälle vertraut zu machen, in denen Parallelität vom Planer automatisch verboten wird, und solche Situationen zu vermeiden.
Das Handbuch zu diesem Thema beschreibt kurz alle Situationen, daher ist es nicht sinnvoll, sie hier zu wiederholen.
Was tun, wenn die Anfrage immer noch nicht gut parallel ist? Es ist sehr traurig zu sehen, wie in Ihrer leistungsstarken Multi-Core-Datenbank, in der Sie der einzige Client sind, ein Kern zu 100% belegt ist und alle anderen Kernel ihn nur betrachten. In diesem Fall müssen Sie der Datenbank von der Seite der Anwendung aus helfen. Da jede Sitzung ihren eigenen Kern hat, können Sie mehrere davon öffnen und die allgemeine Abfrage in Teile aufteilen, kürzere und schnellere Auswahlen treffen und sie zu einem gemeinsamen Ergebnis kombinieren, das bereits in der Anwendung vorhanden ist. Dadurch werden die maximal verfügbaren CPU-Ressourcen in der PostgreSQL-Datenbank belegt.
Abschließend möchte ich darauf hinweisen, dass die oben genannten Diagnose- und Optimierungsoptionen nur die Spitze des Eisbergs sind. Sie sind jedoch recht einfach zu verwenden und können dazu beitragen, das Problem schnell direkt anhand der Betriebsdaten zu identifizieren, ohne die Konfiguration zu beeinträchtigen oder den Betrieb anderer Anwendungen zu stören.
Erfolgreiche Anfragen mit genauen und kurzen Plänen.