Hallo Khabrovites! Ich mache Sie auf eine Übersetzung des Artikels
"Wie eine einzelne Änderung der PostgreSQL-Konfiguration die Leistung langsamer Abfragen um das 50-fache verbessert" von Pavan Patibandla aufmerksam. Es hat mir sehr geholfen, die Leistung von PostgreSQL zu verbessern.
Unser Ziel bei Amplitude ist es, benutzerfreundliche interaktive Produktanalysen bereitzustellen, damit jeder Antworten auf seine Fragen zum Produkt finden kann. Um die Benutzerfreundlichkeit sicherzustellen, muss Amplitude diese Antworten schnell bereitstellen. Als sich einer unserer Kunden darüber beschwerte, wie lange es gedauert hat, die Dropdown-Liste der Ereigniseigenschaften in die Amplitude-Benutzeroberfläche zu laden, haben wir eine detaillierte Untersuchung des Problems gestartet.
Durch Verfolgen der Verzögerung auf verschiedenen Ebenen haben wir festgestellt, dass es 20 Sekunden dauerte, bis eine bestimmte PostgreSQL-Abfrage abgeschlossen war. Dies war für uns eine Überraschung, da beide Tabellen Indizes in der Join-Spalte haben.
Langsame Anfrage
Der PostgreSQL-Ausführungsplan für diese Abfrage war für uns unerwartet. Trotz der Tatsache, dass beide Tabellen Indizes haben, hat PostgreSQL beschlossen, einen Hash-Join mit sequentiellem Scannen einer großen Tabelle durchzuführen. Das sequentielle Scannen einer großen Tabelle nahm den größten Teil der Abfragezeit in Anspruch.
Plan zur Ausführung langsamer Abfragen
Ich hatte zunächst den Verdacht, dass dies an einer Fragmentierung liegen könnte. Nachdem ich die Daten überprüft hatte, stellte ich fest, dass Daten nur zu dieser Tabelle hinzugefügt und von dort praktisch nicht gelöscht werden. Da es hier nicht viel hilft, den Platz mit VACUUM zu räumen, fing ich an, weiter zu graben. Dann habe ich die gleiche Anfrage auf einem anderen Client mit einer guten Antwortzeit versucht. Zu meiner Überraschung sah der Ausführungsplan für Abfragen völlig anders aus!
Ausführungsplan für dieselbe Anforderung auf einem anderen Client
Interessanterweise hatte Anwendung A nur Zugriff auf zehnmal mehr Daten als Anwendung B, aber die Antwortzeit war 3.000 Mal länger.
Um alternative PostgreSQL-Abfragepläne anzuzeigen, habe ich die Hash-Verbindung deaktiviert und die Abfrage neu gestartet.
Alternativer Ausführungsplan für langsame Abfragen
Bitte schön! Dieselbe Anforderung wird 50-mal schneller ausgeführt, wenn eine verschachtelte Schleife anstelle eines Hash-Joins verwendet wird. Warum hat PostgreSQL den schlechtesten Plan für Anwendung A gewählt?
Bei näherer Betrachtung der geschätzten Kosten und der tatsächlichen Vorlaufzeit für beide Pläne waren die geschätzten Verhältnisse von Kosten und tatsächlicher Vorlaufzeit sehr unterschiedlich. Der Hauptschuldige für diese Diskrepanz war die Kostenschätzung des sequentiellen Scannens. PostgreSQL schätzt, dass sequentielle Scans besser wären als mehr als 4000 Index-Scans, aber tatsächlich waren Index-Scans 50-mal schneller.
Dies führte mich zu den Konfigurationsoptionen
random_page_cost und
seq_page_cost . Die Standardwerte für PostgreSQL sind
4 und
1 für
random_page_cost ,
seq_page_cost , die für die Festplatte konfiguriert sind, bei der der wahlfreie Zugriff auf die Festplatte teurer ist als der sequentielle Zugriff. Diese Kosten waren jedoch für unsere Bereitstellung mit dem
gp2-EBS- Volume, bei dem es sich um Solid-State-Laufwerke handelt, ungenau. Für unsere Bereitstellung ist der wahlfreie und sequentielle Zugriff nahezu identisch.
Ich habe den Wert von
random_page_cost auf
1 geändert und
die Anforderung
wiederholt . Dieses Mal verwendete PostgreSQL die verschachtelte Schleife und die Abfrage wurde 50-mal schneller ausgeführt. Nach der Änderung haben wir auch eine signifikante Verringerung der maximalen Antwortzeit von PostgreSQL festgestellt.
Die Gesamtleistung einer langsamen Anforderung hat sich erheblich verbessert.
Wenn Sie SSD verwenden und PostgreSQL mit Standardkonfiguration verwenden, empfehlen wir Ihnen,
random_page_cost und
seq_page_cost festzulegen . Sie werden möglicherweise von der dramatischen Leistungsverbesserung überrascht sein.
Ich
möchte von mir selbst hinzufügen, dass ich die Mindestparameter
seq_page_cost = random_page_cost = 0.1 festgelegt habe , um den Daten im Speicher (Cache) Vorrang vor Prozessoroperationen zu geben, da ich für PostgreSQL eine große Menge RAM zugewiesen habe (die Größe des RAM überschreitet die Größe der Datenbank auf der Festplatte). Es ist nicht sehr klar, warum die Postgres-Community immer noch die Standardeinstellungen verwendet, die für einen Server mit wenig RAM und Festplatten relevant sind, und nicht für moderne Server. Hoffentlich wird dies bald behoben.