Allgemeiner Leitfaden für PostgreSQL-Performance-Tuning und -Optimierung

PostgreSQL-Hardware-Optimierung

Durch die Überwachung der Leistung können Datenbankadministratoren Informationen oder Probleme erkennen und analysieren und nach Möglichkeiten zu deren Optimierung suchen. Zum Beispiel können PostgreSQL, Big Data oder der Abruf von Millionen von Zeilen die Leistung drastisch beeinträchtigen, sodass eine Optimierung in diesem Fall für eine effektive Leistung entscheidend sein kann. Beim Schreiben von Abfragen sollten die Entwickler jedoch die Hardwarekapazität, die Tabellenpartitionierung, die Indexnutzung, die Konfigurationsparameter usw. berücksichtigen.

Zunächst sollten Sie die von Ihnen verwendete Hardwarekonfiguration analysieren.

RAM
Je mehr Speicherplatz Sie zum Speichern von Daten haben, desto mehr Festplatten-Cache, weniger E/A und bessere Leistung erhalten Sie. Wenn Sie mehr Arbeitsspeicher verbrauchen, als Ihnen zur Verfügung steht, kann es sein, dass Sie in Protokollen die Fehlermeldung "Out of memory" erhalten oder dass laufende Prozesse abgebrochen werden, um mehr Speicherplatz freizugeben. Es kann sich also lohnen, den Arbeitsspeicher zu erhöhen, um ohne Unterbrechungen arbeiten zu können.

Festplatte
Wenn es um Festplatten geht, ist es wichtig, die Festplattennutzung und ihre Parameter zu überwachen und zu analysieren, wie Sie sie konfigurieren können, damit der PostgreSQL-Server effizient läuft. Langsame Antwortzeiten können eine schlechte Leistung verursachen. Wenn die Anwendung an Eingabe- und Ausgabeoperationen gebunden ist, ist das schnellere Laufwerk in diesem Fall eine gute Wahl, um die Leistung zu verbessern. Die Verwendung von separaten Laufwerken oder Tablespaces für unterschiedliche Operationen, z. B. mit Daten oder Indizes, kann ebenfalls auftretende Leistungsprobleme lösen.

CPU
Die effiziente Leistung von PostgreSQL hängt stark von der CPU-Nutzung ab. Komplexe Operationen wie Vergleiche, Tabellen-Joins, Hashing, Datengruppierung und Sortierung benötigen mehr Prozessorzeit. Wenn Sie mit großen Datenbanken arbeiten, kann die CPU-Geschwindigkeit die Leistung entweder verschlechtern oder verbessern. Eine Erhöhung der CPU-Kapazität kann mit höheren Kosten verbunden sein. Daher sollten Sie die CPU-Nutzung und die auf dem Server ausgeführten Operationen gründlich analysieren. Wenn die CPU keine Leistungseinbußen verursacht oder Sie mit anderen Parametern, wie z. B. einer Erhöhung des Arbeitsspeichers, eine bessere Leistung erzielen können, sollten Sie diese Parameter eventuell aktualisieren.

Abstimmung des Betriebssystems

Abstimmung der Konfigurationsparameter

Konfigurationsparameter können einen erheblichen Einfluss auf die Datenbankleistung und die Ressourcennutzung in PostgreSQL haben. Um die Leistung für Ihre Arbeitslast zu optimieren, können Sie die Parameter in der Datei $ PGDATA / postgresql.conf anpassen. Im Folgenden finden Sie einen Überblick über die am häufigsten verwendeten Parameter:

max_connections
Legt die maximale Anzahl der gleichzeitig geöffneten Datenbankverbindungen auf dem PostgreSQL-Server fest. Es wird nicht empfohlen, viele Verbindungen gleichzeitig zu setzen, da dies die Speicherressourcen beeinträchtigen und die Größe der PostgreSQL-Datenbankstrukturen erhöhen kann.

shared_buffers
Bestimmt die Menge an Speicher, die für gemeinsam genutzte Speicherpuffer auf dem Datenbankserver verwendet werden soll. In der Regel ist es optimal, 25% des verfügbaren Arbeitsspeichers zu verwenden, damit die Leistung nicht beeinträchtigt wird. Sie können jedoch versuchen, einen anderen Schwellenwert festzulegen, um zu testen, ob er für Ihre Arbeitslast geeignet ist.

effective_cache_size
Bestimmt die Gesamtmenge an Speicher für die Zwischenspeicherung von Daten pro Abfrage. Dieser Parameter kann die Verwendung von Indizes beeinflussen. Je höher der Wert ist, desto eher wird die Indexsuche verwendet. Bei den niedrigsten Werten wird wahrscheinlich die sequenzielle Suche verwendet.

work_mem
Legt die Menge an Speicher fest, die für interne Sortiervorgänge und Hash-Tabellen verwendet wird, bevor temporäre Dateien auf der Festplatte verwendet werden. Wenn Sie diesen Parameter erhöhen möchten, bedenken Sie, dass er pro Vorgang verwendet wird. Wenn also mehrere Vorgänge gleichzeitig ausgeführt werden, wird jeder Vorgang das angegebene Speichervolumen verwenden.

fsync
Wenn diese Option aktiviert ist, zeichnet der Server die Änderungen physisch auf der Festplatte auf, z. B. durch Ausführen der Systemaufrufe fsync(). Dies verhindert Datenverluste oder -beschädigungen und gewährleistet die Konsistenz der Datenbank im Falle eines Hardware- oder Betriebssystemausfalls.

commit_delay
Legt die Verzögerung in Mikrosekunden zwischen dem Commit der Transaktion und dem Speichern der WAL auf der Festplatte fest. Dieser Parameter kann die Leistung verbessern, wenn während des aktuellen Vorgangs mehrere Transaktionen übertragen werden.

random_page_cost
Ermöglicht es dem PostgreSQL-Optimierer, die Kosten für das Lesen einer zufälligen Seite von der Festplatte zu schätzen und über die Verwendung von Index- oder sequenziellen Scans zu entscheiden. Je höher der Wert ist, desto eher werden sequenzielle Scans verwendet.

Berichterstattung und Protokollierung

Mit Hilfe von Protokollen und Fehlerberichten können Sie analysieren, wie die Anwendung funktioniert oder welche Fehler bei der Durchführung eines bestimmten Vorgangs auftreten. Darüber hinaus können Sie die Leistung der Datenbank mit aktivierten Protokollen für die Abfragen optimieren. Um zu scannen und ein tieferes Verständnis für mögliche Leistungsprobleme zu bekommen, sehen wir uns einige Protokollparameter an:

• logging_collector: Wenn aktiviert, wird die Ausgabe in Protokolldateien aufgezeichnet.
• log_statement: Steuert, welche SQL-Befehle protokolliert werden sollen. Sie können es auf ddl setzen, um strukturelle Änderungen an der Datenbank zu protokollieren, auf mod setzen, um Datenänderungen zu protokollieren, und auf all setzen, um alle Änderungen zu protokollieren.
• log_min_duration_statement: Erkennt langsame Abfragen in der Datenbank. Wenn diese Option aktiviert ist, wird die Dauer aller Befehle aufgezeichnet, allerdings wird die E/A-Kapazität erhöht.
• log_min_error_statement: Zeichnet fehlgeschlagene SQL-Anweisungen im Serverprotokoll auf.
• log_line_prefix: Legt das Format der Datenbankprotokolle fest. Es kann verwendet werden, um die Lesbarkeit zu verbessern.
• log_lock_waits: Identifiziert langsame Leistung und Wartezeiten auf Sperren aufgrund von Verzögerungen beim Sperren.

VACUUM-Verarbeitung in PostgreSQL

VACUUM wird als eine der nützlichsten Funktionen in PostgreSQL angesehen. Die VACUUM-Verarbeitung ist eine Operation, die aktualisierte oder gelöschte Zeilen bereinigt, um freien Speicherplatz für andere Operationen zurückzugewinnen oder wiederzuverwenden. Das Vakuumieren ist in PostgreSQL standardmäßig konfiguriert und aktiviert. Sie können jedoch die Einstellungen anpassen, um Ihren Geschäftszielen und Ihrer Arbeitslast gerecht zu werden oder die Leistung zu verbessern. Je häufiger Sie das Vakuumieren durchführen, desto besser kann die Leistung der Datenbank sein.

DBAs können entweder VACUUM ausführen, das parallel zu anderen Datenbankoperationen durchgeführt werden kann, oder VACUUM FULL, das eine exklusive Sperre für die zu leerende Tabelle erfordert und nicht mit anderen Operationen durchgeführt werden kann. Bei letzterem wird die Tabelle in einer neuen Plattendatei überschrieben, sodass freier Speicherplatz für das Betriebssystem übrig bleibt. Demzufolge wird dieser Typ viel langsamer ausgeführt. Für Routineoperationen ist es daher besser, VACUUM zu verwenden.

Außerdem können Datenbankadministratoren den VACUUM ANALYZE-Befehl ausführen, der zunächst eine VACUUM-Operation durchführt und dann einen ANALYZE-Befehl für die ausgewählte Tabelle ausführt. Der ANALYZE-Befehl sammelt Statistiken, um die effizienteste und optimierte Methode zur Ausführung der Abfrage zu ermitteln.

Suche nach langsamen Abfragen

Um die Leistung der Datenbank zu optimieren und langsame und ineffiziente PostgreSQL-Abfragen zu erkennen, können Sie den Abfrageplan untersuchen, indem Sie die PostgreSQL-Befehle EXPLAIN und EXPLAIN ANALYZE ausführen.

EXPLAIN
EXPLAIN ermöglicht es Ihnen, den generierten Ausführungsplan für jede SQL-Abfrage anzuzeigen, ohne sie auszuführen. Der Plan hat eine hierarchische Struktur der Baumplan-Knotenpunkten. Der Plan zeigt an, wie die Tabelle in der Abfrage durchsucht wird. Im Plan können Sie die geschätzten Startkosten, die Gesamtkosten, die für die Ausführung erforderlich sind, die Anzahl der Tabellenzeilen und deren durchschnittliche Breite, die im Ergebnis zurückgegeben werden, sehen. Ebenso berechnet der Plan die durchschnittliche Zeit, die für die Ausführung der Abfrage benötigt wird.

EXPLAIN ANALYZE
EXPLAIN ANALYZE ermöglicht es Ihnen, für jede in Ihrer Anwendung laufende SQL-Abfrage ein Profil zu erstellen und zu sehen, wie die Abfrage tatsächlich verarbeitet wird. Dieser Befehl führt die Abfrage aus und gibt dann Statistiken, die tatsächliche Zeilenzahl, die Planung und die Ausführungszeit zusammen mit denselben Schätzungen wie ein Standard-EXPLAIN-Befehl aus.

Im Allgemeinen helfen beide Befehle bei der Analyse der Ausführung von Abfragen und bei der Suche nach der optimalen Methode, mit der Sie die Abfragen neu schreiben und effizienter gestalten können.

Datenbank-Design

Manchmal kann das Datenbank-Design zu einer langsamen Leistung führen, vor allem wenn es sich um große Tabellen handelt. Um die Leistung der PostgreSQL-Datenbank zu optimieren und die E/A zu verbessern, können Sie große Datenmengen in kleine Datenmengen partitionieren, d. h. eine einzelne Tabelle in mehrere separate, logisch verbundene Tabellen aufteilen. Die Haupttabelle speichert Informationen, auf die Sie häufig zugreifen werden, und andere Tabellen halten zusätzliche Informationen bereit.

Außerdem können Sie Teilindizes für Spalten erstellen, die Sie häufig verwenden. Dies kann die Abfrageleistung beschleunigen. Allerdings sollten Sie mit Indizes vorsichtig sein, da ihre übermäßige Verwendung die Leistung beeinträchtigen kann.

Analyse der Leistungsmöglichkeiten von PostgreSQL

Für die Leistungsoptimierung von PostgreSQL hat Devart dbForge Studio for PostgreSQL entwickelt. Es ist ein leistungsstarkes GUI-Tool, das über ein fortschrittliches integriertes Query Profiler verfügt, der entwickelt wurde, um die Leistung von langsam laufenden Abfragen zu optimieren und zu tunen und um Engpässe zu erkennen, die die Leistung verschlechtern können.

Mit dem PostgreSQL-Profiler können Sie:

• Leistungsproblemen analysieren und beheben
• Query-Profiling-Ergebnissen durch Anzeige ihrer Unterschiede in einem visuellen Dashboard vergleichen
• Einen Execution-Explain-Plan untersuchen, um die Leistung von langsam laufenden PostgreSQL-Abfragen zu verbessern
• Durch die Analyse der Metriken im Plan-Diagramm sich ein tiefes Verständnis für alle Vorgänge verschaffen
• Die teuersten Vorgänge auf der Liste der wichtigsten Vorgänge prüfen
• Den Plan im Format XML teilen

Schlussfolgerung

Die Analyse der Leistung von PostgreSQL-Abfragen ist entscheidend für die effiziente Arbeit von Datenbanken und den Datenabruf. Durch die Anwendung der oben genannten Tipps und Praktiken kann nicht nur die Leistung der Datenbank verbessert werden, sondern es ist auch möglich, langsame Abfragen zu identifizieren. Ansonsten kann die Indexleistung abgestimmt und optimiert werden, um PostgreSQL-Abfragen zu beschleunigen und Probleme zu untersuchen, die eine schlechte Leistung verursachen können. Zusammen können diese Maßnahmen dazu beitragen, die Effizienz und Leistung der Datenbank zu steigern und eine reibungslose Funktionsweise zu gewährleisten.