An welchen Stellschrauben man drehen kann

© Kirsty Pargeter, 123RF

PostgreSQL tunen

,
Von Dutzenden Datenbankparametern sind nicht alle wichtig. Dieser Beitrag erklärt, welche Einstellungen für ein schnelles PostgreSQL am wichtigsten sind.
Egal, um welchen Dienst es sich dreht, den Benutzern geht es immer zu langsam. Der Schwerpunkt des ADMIN-Magazins 05/2011 verrät, mit welchen Tools man ... (mehr)

"Meine Datenbank ist langsam" – solche Hilferufe erreichen den Datenbanksupport häufig. Dann drängen sich immer zwei Fragen auf: Was genau ist langsam? Und: Ist es wirklich die Datenbank? Häufig wird die Schuld an allem Übel zu schnell auf die Datenbank geschoben. Aber selbst die schnellste und bestoptimierte Datenbank kann keinen unvorteilhaft gewählten Algorithmus in der Anwendung wettmachen. Manchmal entpuppt sich auch das Netzwerk als Übeltäter, aber das ist heutzutage seltener der Fall. Als dritte Gruppe von Verdächtigen kommen schließlich graphische Tools von Drittanbietern wie PgAdmin, PhpPgAdmin oder Tora in Betracht.

Prinzipiell findet sich die Ursache für langsame Datenbanken aber wie gesagt am häufigsten auf der SQL-Ebene. Ungenügend durchdachtes Design und wachstumsbedingte Verwerfungen führen die Hitliste der Fehler an. Weitere Beispiele sind: Sich gegenseitig blockierende Statements, unvorteilhaft formulierte Statements, Probleme bei Massenimporten, Probleme mit der Indizierung, langsame Algorithmen in Funktionen und anderes mehr. Nun soll es in diesem Artikel zwar weniger um SQL-Optimierung gehen, aber es ist wichtig, darauf zu verweisen, dass hier normalerweise der Schwerpunkt allen Tunings liegen muss.

Speicher-Stellschrauben

Dieser Artikel wird sich hauptsächlich mit der Optimierung von Datenbankparametern auseinandersetzen. Schon die Vielzahl der Variablen in der Datei »postgresql.conf« kann dabei dazu führen, dass der Admin den Überblick verliert, an welchen Schrauben gedreht werden sollte. Tröstlich ist: Für viele Installationen ist nur ein kleiner Bruchteil der mehr als hundert Tuningparameter von Bedeutung.

In vielen Fällen wichtig ist aber beispielsweise die Variable »max_connections« , die die Anzahl zeitgleicher Verbindungen zum Datenbankserver begrenzt. Ergänzend legt die Variable »superuser_reserved_connections« fest, wie viele Verbindungen von »max_connections« für Superuser reserviert sind (auch für Replikationen sind Superuser erforderlich). Ist der Wert von »max_connections« zu hoch, geht die Performance in den Keller. Mehr als zwei oder drei aktive Verbindungen pro Core lassen die CPU ein Swap-Wettrennen veranstalten, das die Performance herunterschraubt. Besser ist es hier, über Connection Pooling zwischen Server und Anwendung nachzudenken. Gängige Pooler für diesen Zweck sind PgBouncer und pgpool-II.

Das Betriebssystem stellt Pufferspeicher (Cache) für alle Anwendungen bereit, so auch für PostgreSQL. Daneben verwaltet PostgreSQL eigenen Zwischenspeicher in der mit »shared_buffers« festgelegten Größe. Dieser Speicher steht ausschließlich PostgreSQL zur Verfügung. Die Voreinstellungen sind relativ niedrig, da die meisten Unix-Kernel für größere Werte erst angepasst werden müssen. Danach sollte »shared_buffers« auf mindestens 256 MByte gesetzt werden. Die obere Grenze liegt unter Windows bei 512 MByte und unter Unix/Linux bei 8 GByte. Für weniger als 32 GByte RAM gilt der Daumenwert: 1/4 RAM als »shared_buffers« . Bei 32 GByte und mehr sind 8 GByte für »shared_buffers« üblich. Die verbleibenden 3/4 RAM werden in reinen Datenbankservern »effective_cache_size« zugeordnet. Durch diesen Parameter wird PostgreSQL mitgeteilt, mit wie viel Betriebssystem-Cache es rechnen kann, um bei der Query-Planung herauszufinden, ob größere Indexe in den RAM passen oder nicht.

Wer »shared_buffers« umkonfigureren will, muss übrigens unter Umständen im Linux-Kernel die Maximalgröße eines Shared-Memory-Segments (SHMAX) anpassen, was über

sysctl -w kernel.shmax=
<!-- START: including template: design/standard/templates/content/datatype/view/ezxmltags/emphasize.tpl (design:content/datatype/view/ezxmltags/emphasize.tpl) -->
Wert
<!-- STOP: including template: design/standard/templates/content/datatype/view/ezxmltags/emphasize.tpl (design:content/datatype/view/ezxmltags/emphasize.tpl) -->

möglich ist.

Checkpoints

Checkpoints bewirken, dass im Shared-Buffer-Cache abgelegte, geänderte Daten in regelmäßigen Abständen auf die Platte geschrieben werden. Der Vorgabewert von 5 Minuten ( »checkpoint_timeout« ) ist normalerweise ausreichend. Daneben werden Checkpoints auch ausgelöst, wenn die Anzahl an geschriebenen WAL-Dateien (Write Ahead Log) den Wert von »checkpoint_segments« übersteigt. WAL-Dateien sind je 16 MByte groß und »checkpoint_segments« ist mit 3 voreingestellt. Daraus ergibt sich, dass alle 48 MByte ein Checkpoint initiiert wird. Für Systeme mit guter Auslastung ist das viel zu häufig. Auf reinen Datenbankservern wird »checkpoint_segments« meist auf einen Wert zwischen 32 und 128 gesetzt. Bei massiv schreibenden Anwendungen kann der Zwischenspeicher zum Schreiben von WAL ( »wal_buffers« ) schnell überlaufen. Zwischen 1 und 16 MByte reichen aus, damit »wal_buffers« nicht die Hauptbremse ist.

Speicher für Wartungsarbeiten an Tabellen wird durch »maintainance_work_mem« begrenzt. Das Erzeugen von Indexen und die Ausführung des Vacuum-Prozesses sind hier inbegriffen. Meist laufen nicht mehr als drei oder vier Wartungsprozesse zeitgleich. »RAM/16« ist dann ein guter Daumenwert.

Standardmäßig gibt der Server erst ein Commit an den Client zurück, nachdem die Informationen auf die Platte geschrieben wurden. Durch Setzen von »synchronous_commit (on/off)« kann das Verhalten selbst im laufenden Betrieb individuell für jede Sitzung geändert werden. Das Risiko ist, dass ohne synchronen Commit bei einer plötzlichen Abschaltung Daten verloren gehen, die noch nicht auf die Platte geschrieben wurden. Es gibt jedoch Anwendungen, bei denen der Geschwindigkeitsvorteil das Verlustrisiko überwiegt.

comments powered by Disqus
Einmal pro Woche aktuelle News, kostenlose Artikel und nützliche ADMIN-Tipps.
Ich habe die Datenschutzerklärung gelesen und bin einverstanden.

Konfigurationsmanagement

Ich konfiguriere meine Server

  • von Hand
  • mit eigenen Skripts
  • mit Puppet
  • mit Ansible
  • mit Saltstack
  • mit Chef
  • mit CFengine
  • mit dem Nix-System
  • mit Containern
  • mit anderer Konfigurationsmanagement-Software

Ausgabe /2023