Standardmäßig ist PostgreSQL nicht für die Arbeitslast konfiguriert. Standardwerte werden festgelegt, um sicherzustellen, dass PostgreSQL überall mit den geringsten Ressourcen funktioniert. Es gibt Standardeinstellungen für alle Datenbankeinstellungen. Die Hauptverantwortung eines Datenbankadministrators oder -entwicklers besteht darin, PostgreSQL so zu konfigurieren, dass es der Systemlast entspricht. In diesem Blog werden die grundlegenden Empfehlungen zum Optimieren der PostgreSQL-Datenbankeinstellungen erläutert, um die Datenbankleistung entsprechend der Arbeitslast zu verbessern.
Beachten Sie, dass die Optimierung der Konfiguration eines PostgreSQL-Servers zwar die Leistung verbessert, der Datenbankdesigner jedoch auch beim Schreiben von Abfragen vorsichtig sein muss. Wenn Abfragen einen vollständigen Tabellenscan durchführen, bei dem ein Index verwendet werden kann, oder umfangreiche Verknüpfungen oder teure Aggregationsvorgänge ausführen, funktioniert das System möglicherweise immer noch schlecht, selbst wenn die Datenbankeinstellungen korrekt konfiguriert sind. Beim Schreiben von Datenbankabfragen ist es wichtig, auf die Leistung zu achten.
Datenbankparameter sind jedoch auch sehr wichtig. Schauen wir uns also die acht an, die das größte Potenzial zur Leistungsverbesserung aufweisen.
Benutzerdefinierte PostgreSQL-Optionen
PostgreSQL verwendet einen eigenen Puffer und eine gepufferte Kernel-E / A. Dies bedeutet, dass die Daten zweimal im Speicher gespeichert werden, zuerst im PostgreSQL-Puffer und dann im Kernel-Puffer. Im Gegensatz zu anderen Datenbanken bietet PostgreSQL keine direkten E / A-Vorgänge. Dies wird als doppelte Pufferung bezeichnet. Der PostgreSQL-Puffer heißt
shared_buffer und ist der effizienteste benutzerdefinierte Parameter für die meisten Betriebssysteme. Dieser Parameter legt fest, wie viel zugeordneter Speicher PostgreSQL für das Caching verwendet.
Der Standardwert für shared_buffer ist sehr niedrig eingestellt und Sie werden nicht viel davon profitieren. Dies liegt daran, dass einige Maschinen und Betriebssysteme keine höheren Werte unterstützen. Bei den meisten modernen Maschinen müssen Sie diesen Wert jedoch erhöhen, um eine optimale Leistung zu erzielen.
Der empfohlene Wert beträgt 25% des gesamten Arbeitsspeichers des Computers. Sie sollten einige niedrigere und höhere Werte ausprobieren, da Sie in einigen Fällen mit einer Einstellung von mehr als 25% eine gute Leistung erzielen können. Die tatsächliche Konfiguration hängt jedoch von Ihrem Computer und dem Arbeitsdatensatz ab. Wenn Ihr Arbeitsdatensatz problemlos in Ihren Arbeitsspeicher passt, können Sie den Wert von shared_buffer erhöhen, sodass er Ihre gesamte Datenbank enthält und sich der gesamte Arbeitsdatensatz im Cache befindet. Sie möchten jedoch offensichtlich nicht den gesamten RAM für PostgreSQL reservieren.
Es wird darauf hingewiesen, dass in Produktionsumgebungen eine gute Leistung Shared_Buffer wirklich eine große Bedeutung beimisst, obwohl Tests immer durchgeführt werden sollten, um das richtige Gleichgewicht zu erreichen.
Überprüfen des Werts von shared_buffertestdb=
Hinweis : Seien Sie vorsichtig, da einige Kernel insbesondere unter Windows keinen größeren Wert unterstützen.wal_buffers
PostgreSQL schreibt zuerst die Einträge in der WAL (Voraufzeichnungsprotokoll) in die Puffer, und dann werden diese Puffer auf die Festplatte geleert. Die von
wal_buffers definierte
Standardpuffergröße beträgt 16 MB. Wenn Sie jedoch viele gleichzeitige Verbindungen haben, kann ein höherer Wert die Leistung verbessern.
effektive_cache_size
effektive_cache_size liefert eine Schätzung des für das Zwischenspeichern der Festplatte verfügbaren Speichers. Dies ist nur eine Richtlinie, nicht die genaue Menge des zugewiesenen Speichers oder Caches. Es ordnet keinen tatsächlichen Speicher zu, teilt dem Optimierer jedoch mit, wie viel Cache im Kernel verfügbar ist. Wenn dieser Parameter zu niedrig eingestellt ist, entscheidet der Abfrageplaner möglicherweise, einige Indizes nicht zu verwenden, auch wenn sie nützlich sind. Daher ist es immer sinnvoll, einen großen Wert festzulegen.
work_mem
Diese Einstellung wird für die komplexe Sortierung verwendet. Wenn Sie eine komplexe Sortierung durchführen müssen, erhöhen Sie den Wert von
work_mem , um gute Ergebnisse zu
erzielen . Das Sortieren im Speicher ist viel schneller als das Sortieren von Daten auf der Festplatte. Das Festlegen eines sehr hohen Werts kann zu einem Speicherengpass für Ihre Umgebung führen, da sich diese Option auf den Benutzersortiervorgang bezieht. Wenn Sie also viele Benutzer haben, die versuchen, Sortiervorgänge auszuführen, wird das System Folgendes hervorheben:
work_mem * total sort operations
für alle Benutzer. Das globale Einstellen dieses Parameters kann zu einer sehr hohen Speichernutzung führen. Daher wird dringend empfohlen, dass Sie es auf Sitzungsebene ändern.
work_mem = 2 MB testdb=
Der anfängliche Anforderungssortierknoten wird bei 514431.86 ausgewertet. Die Kosten sind eine willkürlich berechnete Einheit. Für die obige Anfrage haben wir work_mem nur 2 MB. Erhöhen Sie diesen Wert zu Testzwecken auf 256 MB und prüfen Sie, ob sich dies auf die Kosten auswirkt.
work_mem = 256 MB testdb=
Die Anforderungskosten wurden von 514431,86 auf 360617,36 reduziert, d. H. Um 30% gesenkt.
wartung_arbeit_mem
tenance_work_mem ist ein Speicherparameter, der 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.
wartung_arbeit_mem = 10MB postgres=
wartung_arbeit_mem = 256MB postgres=
Die Indexerstellungszeit beträgt 170091,371 ms, wenn der Parameter "tenance_work_mem "auf nur 10 MB festgelegt ist. Sie verringert sich jedoch auf 111274,903 ms, wenn der Parameter "tenance_work_mem" auf 256 MB erhöht wird.
synchronous_commit
Wird verwendet, um sicherzustellen, dass ein Transaktions-Commit darauf wartet, dass eine WAL auf die Festplatte schreibt, bevor ein erfolgreicher Abschlussstatus an den Client zurückgegeben wird. Dies ist ein Kompromiss zwischen Leistung und Zuverlässigkeit. Wenn Ihre Anwendung so konzipiert ist, dass Leistung wichtiger als Zuverlässigkeit ist, deaktivieren Sie
synchronous_commit . In diesem Fall wird die Transaktion sehr schnell festgeschrieben, da nicht auf das Zurücksetzen der WAL-Datei gewartet wird, sondern die Zuverlässigkeit beeinträchtigt wird. Bei einem Serverausfall können Daten verloren gehen, auch wenn der Client eine Nachricht erhalten hat, die angibt, dass das Transaktions-Commit erfolgreich abgeschlossen wurde.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL schreibt Änderungen an der WAL. Der Checkpoint-Prozess löscht Daten in Dateien. Diese Aktion wird ausgeführt, wenn ein Haltepunkt (CHECKPOINT) auftritt. Dies ist eine teure Operation und kann eine große Anzahl von E / A-Operationen verursachen. Dieser gesamte Prozess beinhaltet teure Lese- / Schreibvorgänge auf die Festplatte. Benutzer können die Checkpoint-Task (CHECKPOINT) bei Bedarf jederzeit starten oder den Start mithilfe der Parameter
checkpoint_timeout und
checkpoint_completion_target automatisieren.
Mit dem Parameter checkpoint_timeout wird die Zeit zwischen WAL-Haltepunkten festgelegt. Wenn Sie den Wert zu niedrig einstellen, wird die Wiederherstellungszeit nach einem Fehler verkürzt, da mehr Daten auf die Festplatte geschrieben werden. Außerdem wird die Leistung verringert, da jeder Prüfpunkt letztendlich wertvolle Systemressourcen verbraucht.
checkpoint_completion_target ist der Bruchteil der Zeit zwischen Checkpoints, um einen Checkpoint abzuschließen. Hochfrequenzprüfpunkte können die Leistung beeinträchtigen. Um den Checkpoint-Job reibungslos abzuschließen, muss
checkpoint_timeout niedrig sein. Andernfalls sammelt das Betriebssystem alle verschmutzten Seiten, bis das Verhältnis eingehalten wird, und führt dann einen großen Reset durch.
Fazit
Es gibt mehr Optionen, die Sie optimieren können, um eine bessere Leistung zu erzielen, aber sie haben weniger Auswirkungen als die hier hervorgehobenen. Am Ende müssen wir immer daran denken, dass nicht alle Parameter für alle Arten von Anwendungen relevant sind. Einige Anwendungen funktionieren beim Festlegen von Optionen besser, andere nicht. Die PostgreSQL-Datenbankeinstellungen müssen auf die spezifischen Anforderungen der Anwendung und des Betriebssystems zugeschnitten sein, auf dem sie ausgeführt wird.