Beseitigung von Engpässen in PostgreSQL unter hoher Last

Hallo. Derzeit kann sich OTUS für den neuen PostgreSQL- Kursstrom anmelden . In diesem Zusammenhang haben wir traditionell für Sie eine Übersetzung von nützlichem Material zu diesem Thema vorbereitet.

---

Ausgehend von dem Artikel von Peter Zaitsev über MySQL-Leistungsengpässe möchte ich ein wenig über PostgreSQL sprechen.



ORM-Frameworks werden heutzutage häufig für die Arbeit mit PostgreSQL verwendet. Sie funktionieren normalerweise gut, aber mit der Zeit steigt die Last und es wird notwendig, den Datenbankserver zu optimieren. So zuverlässig PostgreSQL auch ist, es kann sich mit zunehmendem Datenverkehr verlangsamen.



Es gibt viele Möglichkeiten, Leistungsengpässe zu beseitigen. In diesem Artikel konzentrieren wir uns jedoch auf Folgendes:

• Serverparameter
• Verbindungsmanagement
• Autovakuumeinstellung
• Zusätzliche Autovakuumeinstellung
• Aufblähende Tische (Aufblähen)
• Hot Spots in Daten
• Anwendungsserver
• Reproduzieren
• Serverumgebung

Über "Kategorien" und "mögliche Auswirkungen"

"Komplexität" bezieht sich darauf, wie einfach es ist, die vorgeschlagene Lösung zu implementieren. Und die "möglichen Auswirkungen" geben einen Hinweis auf den Grad der Verbesserung der Systemleistung. Aufgrund des Alters des Systems, seines Typs, seiner technischen Verschuldung usw. Eine genaue Beschreibung der Komplexität und der Auswirkungen kann problematisch sein. Schließlich liegt in schwierigen Situationen immer die endgültige Wahl bei Ihnen.



Kategorien:

• Komplexität
  
  
  • Niedrig
  • Durchschnittlich
  • Hoch
  • Niedrig Mittel Hoch
• Mögliche Auswirkungen
  
  
  
  
  • Niedrig
  • Der Durchschnitt
  • Hoch
  • Niedrig Mittel Hoch

Serverparameter

Schwierigkeit: gering.

Mögliche Auswirkungen: Hoch.



Vor nicht allzu langer Zeit gab es Zeiten, in denen aktuelle Versionen von Postgres auf i386 ausgeführt werden konnten. Die Standardeinstellungen haben sich seitdem geändert, sind jedoch weiterhin so konfiguriert, dass die geringste Menge an Ressourcen verwendet wird.



Diese Einstellungen sind sehr einfach zu ändern und werden normalerweise während der Erstinstallation konfiguriert. Falsche Werte dieser Parameter können zu einer hohen CPU- und E / A-Auslastung führen:

• Parameter effektiv_cache_size ~ 50 bis 75%
• Parameter shared_buffers ~ 1/4 - 1/3 der RAM-Größe
• Parameter work_mem ~ 10MB

Der empfohlene Wert für effektiv_cache_size kann, obwohl typisch, genauer berechnet werden, wenn wir auf "top" verweisen - frei + zwischengespeichert .



Die Berechnung des Werts von shared_buffers ist ein interessantes Rätsel. Sie können es aus zwei Blickwinkeln betrachten: Wenn Sie eine kleine Datenbank haben, können Sie den Wert von shared_buffers so groß einstellen, dass er in die gesamte Datenbank im RAM passt. Auf der anderen Seite können Sie das Laden nur häufig verwendeter Tabellen und Indizes in den Speicher konfigurieren (denken Sie an die 80/20). Früher wurde empfohlen, den Wert auf 1/3 der RAM-Größe festzulegen, aber im Laufe der Zeit wurde er mit zunehmender Speichermenge auf 1/4 reduziert. Wenn wenig Speicher zugewiesen wird, erhöhen sich die E / A- und Prozessorlast. Zu viel Speicherzuordnung wird angezeigt, wenn das Plateau der Prozessor- und E / A-Last erreicht wird.







Ein weiterer zu berücksichtigender Faktor ist der Betriebssystem-Cache . Bei genügend RAM speichert Linux Tabellen und Indizes im Speicher und kann PostgreSQL abhängig von den Einstellungen glauben machen, dass Daten eher von der Festplatte als vom RAM gelesen werden. Dieselbe Seite befindet sich sowohl im Postgres-Puffer als auch im Betriebssystem-Cache. Dies ist einer der Gründe dafür, dass shared_buffers nicht sehr groß werden. Mit der Erweiterung pg_buffercacheSie können die Verwendung des Caches in Echtzeit sehen.



Der Parameter work_mem gibt die Speichermenge an, die für Sortiervorgänge verwendet wird. Wenn Sie diesen Wert zu niedrig einstellen, wird eine schlechte Leistung garantiert, da die Sortierung mithilfe temporärer Dateien auf der Festplatte durchgeführt wird. Obwohl das Festlegen eines großen Werts die Leistung nicht beeinträchtigt, besteht bei einer großen Anzahl von Verbindungen die Gefahr, dass der Arbeitsspeicher knapp wird. Durch Analysieren des von allen Anforderungen und Sitzungen verwendeten Speichers können Sie den erforderlichen Wert berechnen.



Mit EXPLAIN ANALYZE können Sie sehen, wie die Sortiervorgänge ausgeführt werden, und durch Ändern des Werts für die Sitzung bestimmen, wann der Flush auf die Festplatte beginnt.



Sie können auch Benchmarks verwenden Systeme.

Verbindungsmanagement

Schwierigkeit: gering.

Mögliche Auswirkungen: Niedrig-Mittel-Hoch



Hohe Last ist normalerweise mit erhöhten Client-Sitzungen pro Zeiteinheit verbunden. Zu viele von ihnen können Prozesse blockieren, Verzögerungen verursachen oder sogar zu Fehlern führen.



Die einfache Lösung besteht darin, die maximale Anzahl gleichzeitiger Verbindungen zu erhöhen:

# postgresql.conf: default is set to 100<br />max_connections

Ein effizienterer Ansatz ist jedoch das Verbindungspooling . Es gibt viele Lösungen, aber die beliebteste ist pgbouncer . PgBouncer kann Verbindungen in einem von drei Modi verwalten:

• (session pooling). . , . , . .
• (transaction pooling). . PgBouncer , , .
• (statement pooling). . . , .

Sie sollten auch auf Secure Socket Layer (SSL) achten. Wenn diese Option aktiviert ist, verwenden Verbindungen standardmäßig SSL, wodurch der Prozessor im Vergleich zu unverschlüsselten Verbindungen stärker belastet wird. Für reguläre Clients können Sie die hostbasierte Authentifizierung ohne SSL ( pg_hba.conf) konfigurieren und SSL für Verwaltungsaufgaben oder für die Streaming-Replikation verwenden.

Autovakuumeinstellung

Schwierigkeit: Mittel.

Mögliche Auswirkungen: Niedrig-Mittel.



Die Parallelitätskontrolle für mehrere Versionen ist eines der Grundprinzipien, die PostgreSQL zu einer so beliebten Datenbanklösung machen. Eines der lästigen Probleme ist jedoch, dass für jeden geänderten oder gelöschten Datensatz nicht verwendete Kopien erstellt werden, die schließlich entsorgt werden müssen. Ein falsch konfigurierter Autovakuumprozess kann die Leistung beeinträchtigen. Je mehr der Server ausgelastet ist, desto mehr manifestiert sich das Problem.



Die folgenden Parameter werden zur Steuerung des Autovacuum-Daemons verwendet:

• autovacuum_max_workers. ( ). , . . . .
• maintenance_work_mem. , . , . , .
• autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.

Achten Sie darauf, RAM und CPU nicht zu überlasten. Je höher der Anfangswert ist, desto größer ist das Risiko einer Ressourcenverarmung, wenn die Systemlast zunimmt. Wenn zu hoch eingestellt, kann die Leistung dramatisch abnehmen, wenn ein bestimmtes Lastniveau überschritten wird.



Ähnlich wie bei der Berechnung von work_mem kann dieser Wert arithmetisch berechnet werden oder es können Benchmarks durchgeführt werden, um optimale Werte zu erhalten .

Zusätzliche Autovakuumeinstellung

Schwierigkeit: hoch.

Mögliche Auswirkungen: Hoch.



Diese Methode sollte aufgrund ihrer Komplexität nur verwendet werden, wenn die Systemleistung bereits am Rande der physischen Grenzen des Hosts liegt und dies wirklich zu einem Problem geworden ist.



Die Autovakuum-Laufzeitoptionen werden in konfiguriert postgresql.conf. Leider gibt es keine Einheitslösung, die in einem Hochlastsystem funktioniert.



Speicheroptionen für Tabellen . In einer Datenbank fällt häufig ein erheblicher Teil der Last auf nur wenige Tabellen. Das Anpassen der Autovakuumeinstellungen für eine Tabelle ist eine gute Möglichkeit, um zu vermeiden, dass VACUUM manuell gestartet werden muss, was sich erheblich auf das System auswirken kann.



Sie können Tabellen mit dem folgenden Befehl anpassen :

ALTER TABLE .. SET STORAGE_PARAMETER

Aufblähende Tische (Aufblähen)

Schwierigkeit: gering.

Mögliche Auswirkungen: Mittelhoch.



Im Laufe der Zeit kann die Systemleistung durch unangemessene Bereinigungsrichtlinien aufgrund übermäßigen Aufblähens von Tabellen beeinträchtigt werden. Selbst das Einrichten des Autovacuum-Daemons und das manuelle Starten von VACUUM lösen das Problem nicht. In diesen Fällen hilft die Erweiterung pg_repack .



Mit der Erweiterung pg_repack können Sie Tabellen und Indizes in der Produktion neu erstellen und neu organisieren

Hot Spots in Daten

Schwierigkeit: hoch.

Mögliche Auswirkungen: Niedrig-Mittel-Hoch.



Wie bei MySQL verlässt sich PostgreSQL auf Ihre Datenströme, um Hotspots zu entfernen, und kann sogar die Architektur Ihres Systems ändern.



Zunächst sollten Sie Folgendes beachten:

• Indizes . Stellen Sie sicher, dass die durchsuchten Spalten Indizes enthalten. Sie können Systemkataloge und -ansichten verwenden, um zu überwachen und zu überprüfen, ob Abfragen Indizes verwenden. Verwenden Sie die Erweiterungen pg_stat_statement und pgbadger, um die Abfrageleistung zu analysieren.
• Haufen nur Tupel (heiß) . Möglicherweise sind zu viele Indizes vorhanden. Sie können das potenzielle Aufblähen und die Tabellengröße reduzieren, indem Sie nicht verwendete Indizes löschen.
• . , , . , , , . , . , , .
• . postgres. , .
• . , . . , !

Schwierigkeit: gering.

Mögliche Auswirkungen: Hoch.



Vermeiden Sie es, Anwendungen (PHP, Java und Python) und Postgres auf demselben Host auszuführen. Seien Sie vorsichtig mit Anwendungen in diesen Sprachen, da sie viel RAM verbrauchen können, insbesondere den Garbage Collector, was zu einem Wettbewerb mit Datenbanksystemen um Ressourcen führen und die Gesamtleistung verringern kann.

Reproduzieren

Schwierigkeit: gering.

Mögliche Auswirkungen: Hoch.



Synchrone und asynchrone Replikation. Neuere Versionen von Postgres unterstützen die logische und Streaming-Replikation sowohl im synchronen als auch im asynchronen Modus. Während der Standardreplikationsmodus asynchron ist, müssen Sie die Auswirkungen der Verwendung der synchronen Replikation berücksichtigen, insbesondere in Netzwerken mit erheblicher Latenz.

Serverumgebung

Last but not least ist es eine einfache Erhöhung der Host-Kapazität. Lassen Sie uns einen Blick darauf werfen, welche Auswirkungen die einzelnen Ressourcen auf die PostgreSQL-Leistung haben:

• . , . . , , -.
• . , , . .
• . .
  
  
  • -, ,
• .
  
  
  
  
  • . , .
  • .
  • . .
  • WAL-, , , . , (log shipping) , , .

: