Standardmäßig ist die PostgreSQL-Standardkonfiguration nicht für eine bestimmte Arbeitslast optimiert. Standardwerte werden festgelegt, um sicherzustellen, dass PostgreSQL überall ausgeführt wird, mit den geringsten Ressourcen, die es verbrauchen kann, und damit es keine Schwachstellen verursacht. Es verfügt über Standardeinstellungen für alle Datenbankparameter. Es liegt in erster Linie in der Verantwortung des Datenbankadministrators oder Entwicklers, PostgreSQL entsprechend der Arbeitslast seines Systems abzustimmen. In diesem Blog werden wir grundlegende Richtlinien für das Festlegen von PostgreSQL-Datenbankparametern festlegen, um die Datenbankleistung entsprechend der Arbeitslast zu verbessern.
Beachten Sie, dass die Optimierung der PostgreSQL-Serverkonfiguration zwar die Leistung verbessert, ein Datenbankentwickler jedoch auch beim Schreiben von Abfragen für die Anwendung sorgfältig sein muss. Wenn Abfragen vollständige Tabellenscans durchführen, bei denen ein Index verwendet werden könnte, oder umfangreiche Joins oder teure Aggregatvorgänge ausführen, kann das System auch dann noch schlecht funktionieren, wenn die Datenbankparameter optimiert sind. Es ist wichtig, beim Schreiben von Datenbankabfragen auf die Leistung zu achten.
Nichtsdestotrotz sind auch Datenbankparameter sehr wichtig, also werfen wir einen Blick auf die acht, die das größte Potenzial zur Verbesserung der Leistung haben.
Abstimmbare Parameter von PostgreSQL
shared_buffer
PostgreSQL verwendet einen eigenen Puffer und verwendet auch Kernel-gepufferte IO. Das bedeutet, dass Daten zweimal im Speicher gespeichert werden, zuerst im PostgreSQL-Puffer und dann im Kernel-Puffer. Im Gegensatz zu anderen Datenbanken bietet PostgreSQL keine direkte E/A. Dies wird als doppelte Pufferung bezeichnet. Der PostgreSQL-Puffer wird shared_buffer bezeichnet, der für die meisten Betriebssysteme der effektivste abstimmbare Parameter ist. Dieser Parameter legt fest, wie viel dedizierter Speicher von PostgreSQL für den Cache verwendet wird.
Der Standardwert von shared_buffer ist sehr niedrig eingestellt und Sie werden davon nicht viel profitieren. Sie ist niedrig, da bestimmte Maschinen und Betriebssysteme keine höheren Werte unterstützen. Aber in den meisten modernen Maschinen müssen Sie diesen Wert für eine optimale Leistung erhöhen.
Der empfohlene Wert beträgt 25% des gesamten Arbeitsspeichers Ihres Computers. Sie sollten einige niedrigere und höhere Werte ausprobieren, da wir in einigen Fällen eine gute Leistung mit einer Einstellung über 25% erzielen. Die Konfiguration hängt wirklich von Ihrer Maschine und dem Arbeitsdatensatz ab. Wenn Ihr Arbeitsdatensatz problemlos in Ihren RAM passt, sollten Sie den Wert shared_buffer erhöhen, um Ihre gesamte Datenbank zu enthalten, sodass sich der gesamte Arbeitsdatensatz im Cache befinden kann. Das heißt, Sie möchten offensichtlich nicht den gesamten RAM für PostgreSQL reservieren.
In Produktionsumgebungen wird beobachtet, dass ein großer Wert für shared_buffer eine wirklich gute Leistung ergibt, obwohl Sie immer einen Benchmark verwenden sollten, um die richtige Balance zu finden.
Überprüfen Sie shared_buffer Wert
|
1 2 3 4 5 |
testdb=# show shared_buffers; shared_buffers —————- 128 MB (1 Zeile) |
Hinweis: Seien Sie vorsichtig, da einige Kernel keinen größeren Wert zulassen, insbesondere in Windows gibt es keine Verwendung von höheren Werten.
wal_buffers
PostgreSQL schreibt seinen WAL-Datensatz (Write Ahead Log) in die Puffer und dann werden diese Puffer auf die Festplatte geleert. Die Standardgröße des Puffers, definiert durch wal_buffers, beträgt 16 MB, aber wenn Sie viele gleichzeitige Verbindungen haben, kann ein höherer Wert eine bessere Leistung erzielen.
effective_cache_size
Die effective_cache_size enthält eine Schätzung des für die Datenträgerzwischenspeicherung verfügbaren Arbeitsspeichers. Es ist nur eine Richtlinie, nicht die genaue zugewiesene Speicher- oder Cachegröße. Es weist keinen tatsächlichen Speicher zu, sondern teilt dem Optimierer die Menge des im Kernel verfügbaren Caches mit. Wenn der Wert dafür zu niedrig festgelegt ist, kann der Abfrageplaner entscheiden, einige Indizes nicht zu verwenden, auch wenn sie hilfreich wären. Daher ist das Festlegen eines großen Werts immer von Vorteil.
work_mem
Diese Konfiguration wird für komplexe Sortierungen verwendet. Wenn Sie eine komplexe Sortierung durchführen müssen, erhöhen Sie den Wert work_mem für gute Ergebnisse. In-Memory-Sortierungen sind viel schneller als Sortierungen, die auf die Festplatte verschüttet werden. Das Festlegen eines sehr hohen Werts kann zu einem Speicherengpass für Ihre Bereitstellungsumgebung führen, da dieser Parameter pro Benutzersortiervorgang gilt. Wenn also viele Benutzer versuchen, Sortiervorgänge auszuführen, wird das System work_mem * Gesamtsortiervorgänge für alle Benutzer zuweisen. Das globale Festlegen dieses Parameters kann zu einer sehr hohen Speicherauslastung führen. Daher wird dringend empfohlen, dies auf Sitzungsebene zu ändern.
work_mem = 2 MB
|
Der Sortierknoten der ersten Abfrage hat geschätzte Kosten von 514431,86. Kosten sind eine beliebige Berechnungseinheit. Für die obige Abfrage haben wir eine work_mem von nur 2MB. Lassen Sie uns zu Testzwecken diese auf 256 MB erhöhen und sehen, ob es Auswirkungen auf die Kosten gibt.
work_mem = 256 MB
|
Die Abfragekosten werden von 514431,86 auf 360617,36 reduziert – eine Reduzierung um 30 %.
maintenance_work_mem
maintenance_work_mem ist eine Speichereinstellung, die für Wartungsaufgaben verwendet wird. Der Standardwert ist 64 MB. Das Festlegen eines großen Werts hilft bei Aufgaben wie VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY und ALTER TABLE.
maintenance_work_mem = 10 MB
|
maintenance_work_mem = 256 MB
|
Die Indexerstellungszeit beträgt 170091.371ms, wenn maintenance_work_mem auf nur 10 MB festgelegt ist, aber das wird auf 111274.903 ms reduziert, wenn wir maintenance_work_mem Einstellung auf 256 MB erhöhen.
synchronous_commit
Dies wird verwendet, um zu erzwingen, dass commit wartet, bis WAL auf den Datenträger geschrieben wird, bevor ein Erfolgsstatus an den Client zurückgegeben wird. Dies ist ein Kompromiss zwischen Leistung und Zuverlässigkeit. Wenn Ihre Anwendung so konzipiert ist, dass die Leistung wichtiger ist als die Zuverlässigkeit, deaktivieren Sie synchronous_commit. Dies bedeutet, dass zwischen dem Erfolgsstatus und einem garantierten Schreibvorgang auf die Festplatte eine Zeitlücke besteht. Im Falle eines Serverabsturzes können Daten verloren gehen, obwohl der Client beim Commit eine Erfolgsmeldung erhalten hat. In diesem Fall wird eine Transaktion sehr schnell festschreiben, da sie nicht darauf wartet, dass eine WAL-Datei geleert wird, sondern die Zuverlässigkeit beeinträchtigt wird.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL schreibt Änderungen in WAL. Der Prüfpunktprozess löscht die Daten in die Datendateien. Diese Aktivität wird ausgeführt, wenn CHECKPOINT auftritt. Dies ist ein teurer Vorgang und kann eine große Menge an IO verursachen. Dieser gesamte Prozess beinhaltet teure Lese-/Schreibvorgänge auf der Festplatte. Benutzer können CHECKPOINT jederzeit ausstellen, wann immer es notwendig erscheint, oder das System durch die PostgreSQL-Parameter checkpoint_timeout und checkpoint_completion_targetautomatisieren.
Der Parameter checkpoint_timeout wird verwendet, um die Zeit zwischen WAL-Prüfpunkten festzulegen. Wenn Sie dies zu niedrig einstellen, verringert sich die Wiederherstellungszeit des Absturzes, da mehr Daten auf die Festplatte geschrieben werden, aber es beeinträchtigt auch die Leistung, da jeder Prüfpunkt am Ende wertvolle Systemressourcen verbraucht. Die checkpoint_completion_target ist der Bruchteil der Zeit zwischen den Kontrollpunkten für die Fertigstellung des Kontrollpunkts. Eine hohe Häufigkeit von Prüfpunkten kann sich auf die Leistung auswirken. Für einen reibungslosen Checkpointing muss checkpoint_timeout ein niedriger Wert sein. Andernfalls sammelt das Betriebssystem alle schmutzigen Seiten an, bis das Verhältnis erreicht ist, und geht dann für einen großen Flush.
Schlussfolgerung
Es gibt mehr Parameter, die angepasst werden können, um eine bessere Leistung zu erzielen, aber diese haben weniger Auswirkungen als die hier hervorgehobenen. Am Ende müssen wir immer bedenken, dass nicht alle Parameter für alle Anwendungstypen relevant sind. Einige Anwendungen funktionieren besser, indem sie einen Parameter optimieren und andere nicht. Die Optimierung von PostgreSQL-Datenbankparametern muss für die spezifischen Anforderungen einer Anwendung und des Betriebssystems, auf dem sie ausgeführt wird, erfolgen.