PostgreSQL beschleunigen

Die nächste Maßnahme beim Postgres-Tuning sollte gar nicht direkt der Datenbank gelten, sondern dem Betriebssystem. Weil PostgreSQL auf den Read-Ahead-Buffer des Betriebssystems baut, kann es sich lohnen, den zu vergrößern – per Default setzt ihn Ubuntu nur auf den niedrigen Wert »256« . Früher bewerkstelligte man diese Änderung mit »hdparm« , aber der Kernel 2.6 bringt jetzt ein eigenes Kommando dafür mit »blockdev --setra 4096 /dev/sda« (Device und Anzahl Blöcke sind Beispielwerte). Allerdings überwiegen bei dem Workload des hier verwendeten Benchmarks Schreiboperationen, sodass sich nach der Anpassung kein merklich positiver Effekt einstellte. Wo die Verhältnisse andere sind, sollte man das fragliche Kommando in »rc.local« oder einem anderen Init-Skript unterbringen, sonst ist die Einstellung beim nächsten Booten futsch.

Auch die folgende Maßnahme hängt engt mit Linux zusammen. Will man nämlich der Datenbank mehr Shared Memory zukommen lassen (hauptsächlich in Form von Shared Buffers), dann darf dessen Menge nicht das im Betriebssystem definierte Limit überschreiten – andernfalls würde die Datenbank nicht starten, weil sie dann versucht, mehr Speicher zu allozieren, als sie erhalten kann. Einer Faustregel zufolge sollte man ein Viertel des RAMs für die Shared Buffers reservieren, im vorliegenden Fall also 2 GByte. Dafür kann man so vorgehen:

• Zunächst editiert man »/etc/sysctl.conf« und ergänzt dort die Zeilen »kernel.shmmax = 4190150656« und »kernel.shmall = 10229896« .
• Danach kann man diese Einstellungen per »sysctl -p« laden (und müsste das ebenfalls nach jedem Booten tun). Der Betriebssystem-Parameter »SHMMAX« ist auf genau vier GByte eingestellt.
• Jetzt kann man in der »postgresql.conf« kongruente Puffergrößen für Caches konfigurieren: »max_connection = 25« und »shared_buffers = 2GB« (vorher: 32 MByte). Damit PostgreSQL weiß, wie viel Cache es vom Betriebssystem erwarten kann, setzt man schließlich noch den Wert von »effective_cache_size« auf »RAM minus Shared Buffers« , im vorliegenden Fall »effective_cache_size = 6GB« .

Ein wenig zur Verwunderung der Tester hatte diese Maßnahme allerdings ebenfalls keinen deutlichen Einfluß auf die Postgres-Performance ( Abbildung 4 , Kurve C). Zwar nutzt die Datenbank den Cache mit einer ordentlichen Trefferquote (94 Prozent), doch spielt er wahrscheinlich wegen der überwiegenden Schreiboperationen keine entscheidende Rolle.

Dafür fiel auf, dass PostgreSQL offenbar sensibler auf die im Zuge der Benchmarks steigende Datenbankgröße reagiert. Ein Leistungsabfall mit längerer Testdauer war auch bei MySQL zu beobachten – PostgreSQL aber produzierte mit einer jungfräulichen Datenbank regelmäßig Ausreißer mit deutlich erhöhten Transaktionszahlen, die sich aber bereits nach einigen Dutzend Testläufen, in deren Verlauf sich die Datenbank vergrößerte, wieder auf den vorigen Mittelwert einpendelten.

Aus diesen Grund haben wir MySQL einmal nach rund 700 Durchläufen und PostgreSQL mehrmals nach je rund 350 Durchläufen komplett resettet und die Testdatenbank neu aufgesetzt. Nach jeder Serie wäre eine solche Aktion aber wegen des damit verbundenen Aufwands undurchführbar und mit Blick auf die Verhältnisse in der Praxis auch unrealistisch gewesen.

Der nächste Tuning-Versuch galt noch einmal der Speicherverwaltung. Über die Variable »work_mem« lässt sich die Menge an Memory konfigurieren, die für interne Sortieroperationen und Hash-Tabellen zur Verfügung stehen soll. Der Default beträgt ein Megabyte, die Tester erhöhten den Wert auf 24 MByte. Via »maintanance_work_mem« lässt sich außerdem vorherbestimmen, wie viel Speicher maximal für Operationen wie »VACUUM« , »CREATE INDEX« oder »ALTER TABLE ADD FOREIGN KEY« verfügbar ist. Der Defaultwert beläuft sich hier auf 16 MByte, für die Benchmarks wurde er auf 512 MByte gesteigert. Die Effekte der beiden Maßnahmen verloren sich allerdings wieder im statistischen Rauschen ( Abbildung 4 , Kurve D).

Log-Tuning

Nun versuchte das Benchmark-Team, den Hebel beim Logging anzusetzen. An einem sogenannten Checkpoint garantiert PostgreSQL, dass alle Heap- und Index-Datendateien auf den neuesten Stand gebracht und alle geänderten Speicherseiten auf die Festplatte geschrieben worden sind. Nach einem Crash würde die Recovery-Prozedur einen solchen Checkpoint suchen, von dem sie weiß, dass alle Operationen, die vorher stattgefunden haben, auf der Festplatte gelandet sind. Wie oft der Background Writer-Prozess diese Checkpoints auslöst, ist einstellbar. Eine Methode ist, die Anzahl der jeweils 16 MByte großen Log-Segmente ( »checkpoint_segments« ) vorzugeben, nach denen jeweils ein Checkpoint geschrieben werden muss. Voreingestellt sind drei. Dieser Wert ist besonders bei vielen Schreiboperationen sehr klein. Deshalb wurde er jetzt auf 32 gesetzt, was zwar möglicherweise die Zeit für ein Crash Recovery erhöht, aber etliche der I/O-intensiven Checkpoints einspart.

Diese Maßnahme hatte einen deutlichen Erfolg: Die Performance steigerte sich um bis zu 100 Prozent ( Abbildung 4 , Kurve E). Spätere Versuche ergaben, dass noch höhere Werte für »checkpoint_segments« hier keine nennenswerte Verbesserung bringen.

In dieselbe Richtung ist ein weiterer Schritt möglich, der allerdings – ganz ähnlich wie zuvor bei MySQL – auf Kosten der Sicherheit geht. Stellt man nämlich die Option »synchronous_commit« (Default) ab, dann wartet der Datenbankserver nicht mehr darauf, dass die Write Ahead-Logs (WAL) tatsächlich auf der Festplatte gelandet sind, bevor er eine Erfolgsmeldung an den Client weitergibt. Das geht schneller, es können im Fall eines Crashs dabei aber einzelne Transaktionen verloren gehen. Es gibt durchaus Fälle, in denen man das für eine höhere Performance ohne große Bauchschmerzen in Kauf nehmen kann. Auf eine Finanztransaktion trifft das sicher nicht zu, auf ein Forum-Posting eher. Man muss also wissen, was man tut.

Wir haben – schon allein aus Gründen der Chancengleichheit – aber auch diese Tuningvariante gestestet. Das Resultat: Mit asynchronem Commit ist die Bremse endgültig gelöst, und die Menge der erreichbaren Transaktionen klettert mit über 15 000 pro Minute in den Bereich, den auch MySQL maximal erreichte ( Abbildung 4 , Kurve F).