Duell der Datenbanken: In einem Shootout messen sich MySQL und PostgreSQL. Der Schwerpunkt vom ADMIN 06/2011 überprüft, wer schneller ist und gibt einen ... (mehr)

Tuning, konkret

Die erste Aktion im Falle von MySQL war der Umstieg von dem schon leicht angestaubten 5.1.41 (Abbildung 1, Kurve A) aus dem Ubuntu-Repository auf die aktuelle Version 5.5.15 (Abbildung 1, Kurve B). Die jüngere Datenbank war aus dem Stand und mit der Standard-Konfiguration gut doppelt so schnell, was auch daran liegt, dass hier einige Parameter von vornherein großzügiger eingestellt waren als beim alten MySQL. Mit Tuning lässt sich zwar auch die alte Version merklich beschleunigen, die neuere behält aber die Nase vorn, weswegen alle folgenden Benchmarks mit der jüngeren Datenbank absolviert wurden. Gelegentliche Upgrades lohnen bei MySQL nach diesen Zahlen sicherlich.

Abbildung 1: Tuningeffekte verschiedener Setups bei MySQL (Erläuterungen im Text).

Im zweiten Schritt stand eine Vergrößerung des Innodb-Bufferpools an. Gehandelt wird für den zugehörigen Parameter »innodb_buffer_pool_size« ein Richtwert von 80 Prozent des vorhandenen RAM oder – wenn man viel RAM zur Verfügung hat und die Datenbank kleiner ist – so viel RAM, wie es dem Wert Datenbankgröße plus 10 Prozent entspricht. Die alte Datenbankversion sieht allerdings per Default nur mickrige 8 MByte vor, die neuere immerhin 128 MByte.

Wichtig ist auf jeden Fall, ausreichend Speicher übrig zu behalten, um sowohl die Bedürfnisse des Betriebssystems (Page Tables, Socket Buffers, etc.) als auch die anderer MySQL-Konsumenten (Query Cache, temporäre Tabellen, Threads, Key Buffer und so weiter) befriedigen zu können. Der Testserver verfügte über 8 GByte RAM, die Benchmark-Datenbank war initial um die 4 GByte groß, also erwiesen sich 5 GByte für den Innodb-Bufferpool als gute Wahl. Sie brachte prompt einen Leistungssprung um 88 Prozent (Abbildung 1, Kurve C) gegenüber der ungetuneten Konfiguration des neueren MySQL.

Die dritte Maßnahme betraf das Innodb-Logfile. Will man dessen Größe ändern, ist es übrigens wichtig, bei heruntergefahrener Datenbank die alten Logs zu löschen – sonst passen sie anschließend nicht zur neu konfigurierten Größe, und MySQL verweigert den Start. Hat man sie dagegen entfernt, legt sie die Datenbank beim Booten mit den neuen Proportionen wieder an.

Außerdem ist zu bedenken, dass ein stark vergrößertes Logfile das Wiederanlaufen nach einem Crash deutlich ausbremsen kann, weil die Datenbank zunächst das gesamte Log nach Transaktionen durchsucht, die nur im Speicher ausgeführt wurden, es aber noch nicht auf die Platte geschafft haben. Die Dauer dieser sogenannten Redo-Phase ist proportional zur Größe des Logs, hängt aber ansonsten von so vielen weiteren Faktoren ab, dass man Werte für eine konkrete Datenbank am besten experimentell ermittelt. Schließlich gibt es eine weitere Obergrenze: Die Größe aller Logfiles zusammen muss unter 4 GByte bleiben. Alle diese Erwägungen einbeziehend fiel die Wahl auf ein 1 GByte großes Innodb-Log. Der Erfolg war ein Leistungszuwachs von weiteren 52 Prozent (Abbildung 1, Kurve D) gegenüber der Vorversion der Konfiguration.

Die vierte Tuning-Maßnahme führt in einen Grenzbereich, denn sie tangiert die Sicherheit der Datenbank. Per Default ist der Parameter »flush_log_at_trx_commit« auf »1« eingestellt, was bedeutet, dass MySQL das Transaction-Log jedesmal auf die Platte schreibt, wenn eine Transaktion committet wird. Damit kann keine Transaktion verloren gehen, ganz egal, ob MySQL oder das Betriebssystem abstürzt. Für OLTP-Workloads mit vielen Schreiboperationen auf den Massenspeicher kann das aber eine Bremse sein, die sich lösen lässt, wenn man den Parameter ändert. Setzt man ihn auf » « schaltet man den Log-Flush nach Commit ganz ab, setzt man ihn auf »2« , wandern die Log-Daten zwar ebenfalls nicht sofort auf die Platte, aber immerhin in den Cache des Betriebssystems. Damit überlebt die Transaktion einen Datenbank-Crash, ginge aber bei einem Betriebssystemabsturz verloren. Wir haben uns für diesen letzteren Fall als Kompromiss entschieden, das ergab gegenüber der Vorgänger-Konfiguration noch einmal mehr als eine Verdoppelung der Performance (Abbildung 1, Kurve E).

Der fünfte Schritt dreht sich um den Parameter »innodb_buffer_pool_instances« , mit dessen Hilfe sich der Buffer Pool in separate Regionen unterteilen lässt, was wiederum die Bearbeitung konkurrierender Lese- und Schreiboperationen begünstigt. Für den Wert von »innodb_buffer_pool_instances« liest man verschiedene Empfehlungen. Unser Experte empfahl, ihn auf die Anzahl Cores zu setzen. Eine andere Empfehlung, »(innodb_buffer_pool_size in GB + number of CPUs)/2« , wäre im vorliegenden Fall ungefähr auf dasselbe hinausgelaufen und auch den Hinweis von MySQL selbst, jeder Teil des Bufferpools solle minimal 1 GByte betragen, erfüllt die gewählte Einstellung nach der Anzahl Cores. Das Ganze brachte wiederum eine deutliche Steigerung (Abbildung 1, Kurve F).

Diese Kurve fällt – wie prinzipiell alle anderen – mit steigender Anzahl Warehouses wieder deutlich ab, weil am oberen Ende unseres Belastungsspektrums mit bis zu 200 Threads mehr parallele Aktionen ausgeführt werden als der maximalen Performance zuträglich sind. Sicher nicht zufällig fällt das Maximum der meisten Leistungskurven in einen Bereich, in dem 30 bis 60 Terminal-Threads zeitgleich laufen.

Aus einer anderen Welt scheinen erwartungsgemäß die Resultate, die sich einstellen, wenn man das Data Directory auf eine SSD (Abbildung 2) verlegt. In der Spitze schnellen sie bis fast an die 20 000 New Orders pro Minute hoch, also noch einmal mehr als doppelt so viel, wie mit allen bisherigen Tuningmaßnahmen zusammen maximal zu erzielen war. Das liegt einfach daran, dass hier mit der Festplatte die langsamste Komponente eliminiert wurde. Unter dem Strich bleibt bis hierhin zu vermerken: Das aktuelle MySQL ließ sich in der Spitze auf ungefähr die zehnfache Leistung der ungetuneten älteren Version beschleunigen, die wir als Baseline vermessen hatten. Mit einer SSD ergeben sich mehr als 20fach höhere Leistungswerte.

Abbildung 2: Beide Datenbanken – maximal getunt – mit und ohne SSD als Massenspeicher für die Datenfiles. Die Werte bei Verwendung einer SSD sind noch einmal wenigstens doppelt so hoch.

PostgreSQL im Rennen

Weiter ging es mit PostgreSQL. Auch hier haben wir zunächst die Version vermessen, die das Ubuntu-Repository mitbringt, das war das Release 8.4.8 (Abbildung 4 Kurve A). Schon der Fairness halber wechselten wir dann aber auch hier zum derzeit aktuellen PostgreSQL 9.1.0 (Abbildung 4, Kurve B). Diese Version war zwar nicht sehr viel schneller als der Vorgänger, gewann aber aus dem Stand den Wettbewerb der Default-Konfigurationen. Das ältere Postgres ist mit den Standard-Einstellungen ungefähr so schnell wie das ungetunete neuere MySQL, aber das neueste PostgreSQL mit Standardkonfiguration kann noch einmal leicht zulegen und diese Konkurrenz für sich entscheiden (Abbildung 3).

Abbildung 4: Diverse PostgreSQL-Setups im Vergleich (Erläuterungen im Text).

Der Grund dafür dürfte sein, dass PostgreSQL beim Initialisieren der Datenbank während der Installation aktiv überprüft, auf welche Werte einige Parameter gesetzt werden können, ohne dass sie in Konflikt mit dem Betriebssystem geraten. MySQL spart sich diesen Check und verwendet in seiner Default-Konfiguration stattdessen so kleine Werte, dass ein Konflikt unwahrscheinlich ist. Dadurch ist es ohne Anpassung aber auch langsamer, weil es den vorhandenen Spielraum nicht ausnutzt. Zudem kann PostgreSQL unter Umständen einen weiteren kleinen Vorteil ausspielen: Seine Stored Procedures für den Benchmark sind kompilierte C-Programme, wogegen MySQL für denselben Zweck SQL-Skripte verwenden muss, die der Server interpretiert. Allerdings ist der Unterschied nicht sehr groß, und das Ergebnis sagt auch noch nichts über die Steigerungsfähigkeit im Laufe des folgenden Tunings – es bleibt also spannend.

Ähnliche Artikel

comments powered by Disqus

Artikel der Woche

Eigene Registry für Docker-Images

Wer selber Docker-Images herstellt, braucht auch eine eigene Registry. Diese gibt es ebenfalls als Docker-Image, aber nur mit eingeschränkter Funktionalität. Mit einem Auth-Server wird daraus ein brauchbares Repository für Images. (mehr)
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

Google+

Ausgabe /2019