Zabbix MySQL tunen

Hardware richtig dimensionieren

Schon im Kapitel Den Zabbix-Server installieren wurde darauf hingewiesen, dass Empfehlungen bezüglich der Hardware nur sehr schwer abzugeben sind. Grundsätzlich gilt: Je mehr Hosts und Items Sie anlegen, desto mehr muss die Hardware arbeiten. Die größte Last verursacht dabei die Datenbank. Achten Sie also bei der Auswahl der Hardware darauf, dass diese für den Betrieb einer Datenbank optimiert ist. Konkret bedeutet dies:

  • viel Arbeitsspeicher
  • schnelle Festplatten (möglichst SAS oder SSD) mit einem Hardware-Raidcontroller mit Write-Cache
  • Die modernen CPUs sind so leistungsfähig, dass diese in der Regel nicht zum Flaschenhals werden.

Beobachten Sie besonders die IO-Wait-Werte Ihrer Zabbix-Datenbank. Wenn die Datenbank zu häufig auf die Festplatte warten muss, ist das ein Zeichen für zu geringe Hardwareressourcen.

Der Zabbix-Server-Daemon, PHP und der Webserver brauchen ebenfalls Arbeitsspeicher. Wenn auf dem Zabbix-Server der Arbeitsspeicher knapp wird, können Sie auch Datenbank und Zabbix-Server auf getrennte Hardware legen. Nachfolgend finden Sie einige Tipps, wie Sie die Leistung der Datenbank erhöhen können. An dieser Stelle kann keine umfassende Erklärung über MySQL-Tuning gegeben werden. Zahlreiche Bücher befassen sich exklusiv mit diesem Thema.

LogSlowQueries aktivieren

Der Zabbix-Server kann lang laufende Queries loggen. Sollten sich viele lange Queries im Log sammeln, ist das ein klares Zeichen dafür, dass die Datenbank der Anzahl von Anfragen nicht mehr gewachsen ist. Wenn Sie nachfolgende Tuning-Tipps umsetzen, sollten Sie immer wieder das Logfile prüfen. Werden die lang laufenden Queries trotz optimierter Einstellungen nicht weniger, sind diese höchstwahrscheinlich an die Grenzen der Hardware gestoßen.
Der Zabbix-Server loggt lang laufende Queries eigenständig und nutzt dabei nicht das SlowQueryLog von MySQL. Aktivieren Sie diese Option in der Datei zabbix_server.conf. Tasten Sie sich mit den Einstellungen von ca. 1,5 Sekunden langsam nach unten:

### Option: LogSlowQueries
#       How long a database query may take before being logged (in milliseconds)
#       0 - don't log slow queries.
#
# Mandatory: no
# Range: 1-3600000
# Default:
LogSlowQueries=1500

MySQL-Datenbank tunen

Nachfolgende Einstellungen nehmen Sie in der Datei /etc/my.cnf oder /etc/mysql/my.cnf vor. Die Änderungen werden erst nach einem Neustart der Datenbank wirksam.

MySQL Bufferpool erhöhen

Mit dem Parameter innodb_buffer_pool_size legen Sie fest, wie viel Hauptspeicher MySQL zum Puffern von Operationen nutzen darf. Die Standardwerte sind in der Regel viel zu niedrig eingestellt. Als Faustregel gilt: MySQL sollte 80% des RAMs als Pufferpools nutzen können. Wenn der Zabbix-Server auf derselben Hardware arbeitet wie MySQL, müssen Sie RAM für Zabbix und den Webserver bereithalten. Geben Sie 50% des verfügbaren RAMs als Pufferpool und tasten Sie sich langsam nach oben.

MySQL Innodb-Logfile vergrößern

Bevor MySQL die Transaktionen in die Tabellendateien auf der Festplatte schreibt, werden diese in das Innodb-Logfile geschrieben. Dieses Logfile stellt auch eine Art Puffer da. Je größer das Logfile ist, desto schneller kann die Datenbank eine Transaktion als geschrieben melden. Auch hier sind die Standardeinstellungen für innodb_log_file_size ebenfalls viel zu klein gewählt. Stellen Sie je nach Anzahl der Datenbankoperationen einen Wert zwischen 512MB und 1GB ein.
Nachdem Sie den Wert geändert haben, müssen Sie die Datenbank herunterfahren. Alle Transaktionen werden dann aus dem Logfile in die Tabellendateien geschrieben. Dann müssen Sie das alte Logfile löschen, damit beim nächsten Start der Datenbank eine neue Datei mit der neuen Größe angelegt wird.

rm /var/lib/mysql/ib_logfile*

Festplattenzugriffe reduzieren

Jede Transaktion wird in das Inno-DB-Logfile und ggf. auch in den Doublewritebuffer geschrieben. Das Logfile wiederum wird sofort auf die Festplatte geschrieben. MySQL bestätigt die Transaktion erst dann, wenn die Festplatte bestätigt hat, dass alle Information gesichert wurden. So ist die größtmögliche Datensicherheit gewährleistet. Der Zabbix-Server speichert aber in der Regel keine wichtigen Daten, so dass der Verlust eines Messwertes schwerwiegende Auswirkungen hat. Zabbix speichert keine Bankdaten.
Mit der nachfolgenden Einstellung schalten Sie den Doublewritebuffer ab:

innodb_doublewrite = 0
When enabled (the default), InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. This variable can be turned off with --skip-innodb_doublewrite for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.

Mit flush_log_at_trx_commit = 2 oder flush_log_at_trx_commit = 0 bestätigt MySQL eine Transaktion schon dann, wenn diese im Cache des Betriebssystems angekommen ist. Es wird nicht mehr auf die Bestätigung der Festplatte gewartet.

innodb_flush_log_at_trx_commit = 2 # dont flush at every commit
innodb_flush_log_at_trx_commit = 0 # No writes from the log buffer to the log file are performed at transaction commit. Flush every second

Zitat aus dem MySQL-Manual: "Hat innodb_flush_log_at_trx_commit den Wert 0, wird einmal pro Sekunde der Logpuffer in die Logdatei geschrieben und diese auf die Festplatte zurückgespeichert, doch beim Committen einer Transaktion wird nichts veranlasst. Ist der Wert 1 (der Standard), wird bei jedem Commit der Logpuffer in die Logdatei und diese auf die Festplatte geschrieben. Ist der Wert 2, wird der Puffer bei jedem Commit in die Datei übertragen, aber diese nicht beim Commit, sondern einmal pro Sekunde auf die Festplatte zurückgeschrieben. Beachten Sie jedoch, dass dieser Schreibvorgang aus Gründen der Prozessplanung in Wirklichkeit nicht unbedingt exakt einmal pro Sekunde stattfindet."

MySQL Buffer-Pool-Instanzen erhöhen

Ab der MySQL-Version 5.5 kann der Buffer-Pool in Regionen unterteilt werden. Das beschleunigt gleichzeitige Zugriffe auf den Speicher. Setzen Sie innodb_buffer_pool_instances größer eins nur dann, wenn Sie mindestens 2GB als Innodb-Buffer-Pool zugewiesen haben.

Innodb-Plugin nutzen

Zabbix nutzt für alle Tabellen den Tabellentreiber Innodb. Seit der Version 5.1.38 können Sie zwei verschiedene „Arten“ von Innodb nutzen:

  • Standard-Innodb
  • Innodb-Plugin

Das Innodb-Plugin bietet verschiedene Optimierungen, die Sie nutzen sollten. Tragen Sie in der my.cnf Folgendes ein, um das Plugin zu verwenden:

ignore-builtin-innodb
plugin-load               = innodb=ha_innodb_plugin.so
innodb_file_per_table     = 1
innodb_file_format        = barracuda

Mehr erfahren Sie in der offiziellen MySQL-Dokumentation .

Ab MySQL-Version 5.5 ist das Innodb-Plugin Standard und muss nicht aktiviert werden.

Transaktionssicherheit reduzieren

In vielen Fällen können Sie Abstriche bei der Transaktionssicherheit zugunsten von Performance machen. Die häufigsten Inserts in die Datenbank sind die neu eingetroffenen Messwerte der Items. Wenn solche Inserts schon bestätigt werden, wenn Sie in den RAM und noch nicht auf die Festplatte geschrieben wurden, dann ist das in vielen Fällen vertretbar, bringt aber eine höhere Schreibgeschwindigkeit.

Lesen Sie Details in der offiziellen Dokumentation nach und fügen Sie ggf. in die my.cnf Folgendes ein:

innodb_doublewrite             = 0
innodb_flush_log_at_trx_commit = 0
innodb_support_xa              = No

Das Dateisystem tunen

  • Legen Sie für die Datenbank eine extra Partition an. Diese muss in der Regel nach /var/lib/mysql gemountet werden.
  • Verwenden Sie ext4 als Dateisystem.
  • Verwenden Sie Mount-Optionen.
# /etc/fstab
UUID==<UUID> /var/lib/mysql ext4 noatime,barrier=0,errors=remount-ro 0 1

Sie können die Mount-Optionen auch ohne Reboot des System aktivieren:

mount -o remount,noatime,barrier=0 /dev/sda1 /

Wenn Sie anschließen das Kommando mount ohne Argumente aufrufen, sehen Sie, mit welchen Optionen die Dateisysteme gemounted sind.

Hinweis zu barrier=0:

Write barriers enforce proper on-disk ordering of journal commits, making volatile disk write caches safe to use, at some performance penalty. If your disks are battery-backed in one way or another, disabling barriers may safely improve performance. The mount options "barrier" and "nobarrier" can also be used to enable or disable barriers, for consistency with other ext4 mount options. Quelle: Kernel.org

Mit der Option noatime unterbinden Sie, dass für jede Datei gespeichert wird, wenn diese das letzte Mal geöffnet wurde. Damit reduzieren Sie Schreibvorgänge.

Hinweis zur Option data=writeback für ext3:

When tuning ext3 for best benchmark numbers, it is often worthwhile to try changing the data journaling mode; '-o data=writeback' can be faster for some workloads. (Note however that running mounted with data=writeback can potentially leave stale data exposed in recently written files in case of an unclean shutdown, which could be a security exposure in some situations.) Configuring the filesystem with a large journal can also be helpful for metadata-intensive workloads. Quelle: Kernel.org

Wenn das System über mehrere physikalische Festplatten verfügt, können Sie das Dateisystem erheblich beschleunigen, wenn Sie die Daten und das Journal von Ext4 auf getrennten Laufwerken speichern. Im Internet finden Sie viele Benchmarks, die diese Annahme bestätigen.

Eine weitere Optimierung des IO-Systems kann in der Verwendung eines SSD-Cache liegen. All aktuellen Distributionen haben die passenden Kernel-Module und die entsprechenden Werkzeuge an Bord. Vor eine klassische Festplatte mit Spindeln „schalten“ Sie eine SSD als Write-Read-Cache. Die gängigsten Cache-Module sind Flashcache und DM-Cache.

IO-Schedulers wechseln

Durch Verwenden eines speziellen IO-Schedulers können Sie unter Umständen die IO-Last des Servers reduzieren und damit die Datenbank beschleunigen. Ein Scheduler ist dafür verantwortlich, wie die Festplatte(n) Lese- und Schreibvorgänge koordiniert. Prüfen Sie mit folgendem Kommando, welchen IO-Scheduler das System verwendet:

cat /sys/block/sda/queue/scheduler

Der verwendete Scheduler wird in Klammern angezeigt. Red Hat empfiehlt für den Betrieb „deadline“ als Scheduler, der bei Red Hat und CentOS auch die Standardeinstellung darstellt.
Wenn Sie ein Hardware-Raid mit Write-Cache oder eine SSD verwenden, kann der Scheduler „noop“ unter Umständen die Performance erhöhen. NOOP lässt jede Optimierung der Schreibprozesse durch den Kernel weg und arbeitet die Anfragen sequenziell ab. Dadurch wird Zeit gespart. Bei SSDs kann NOOP sinnvoll sein, da es keine Lese- und Schreibköpfe gibt und die Datenblöcke nicht „geordnet“ geschrieben werden müssen.

Ähnlich verhält es sich bei Hardware-Raid-Systemen. Der Raid Controller optimiert die Operationen, und ein weiterer Optimierer – der IO-Scheduler des Kernels – der keinen Einblick in diese Optimierungen hat, wäre verschwenderisch und im schlimmsten Fall kontraproduktiv.

Probieren Sie es einfach aus. Mit den passenden Graphen zur IO-Last, die Ihnen Zabbix präsentiert, werden Sie leicht feststellen, welcher Scheduler die beste Leistung bzw. die größte Entlastung des IO-Systems bringt.

Im Zabbix-Forum finden Sie weitere hilfreiche Hinweise zum Optimieren des Zabbix-Servers und der Datenbank.