3 Minuten Lesezeit (523 Worte)

Mach es einfach mal einfach. InnoDB Konfiguration im Automatikmodus

Mittwoch, 06. April 2022

Immer wieder fragen uns Kunden, wie Sie die wichtigsten Parameter der Storage-Engine InnoDB einstellen sollen. Die Antwort kann ab der MySQL Version 8.0 sehr einfach sein: innodb_dedicated_server = ON

Warum kompliziert, wenn es auch einfach geht?

Spätestens mit der Version MySQL 8.0 ist die Engine InnoDB der De-facto-Standard beim Betrieb von MySQL Datenbanken. Eine sinnvolle Konfiguration der wichtigsten Parameter ist unabdingbar, um eine gute Performance des Servers grundlegend zu gewährleisten. Diese Parameter beinhalten auf jeden Fall:

innodb_buffer_pool_size (Größe des Speicherbereichs der Engine)
innodb_log_buffer_size (Größe der Transaktionsprotokolle aka Redo-Logs)
innodb_log_files_in_group (Anzahl der Transaktionsprotokolle)
innodb_flush_method (legt die Methode fest, mit der Daten physikalisch persistiert/geschrieben) werden

Dezidiert dediziert!

Wie der Parameter "innodb_dedicated_server" schon vom Namen her nahelegt, ist diese Einstellung nur für Systeme empfohlen auf denen dediziert eine MySQL Datenbank betrieben wird, die hauptsächlich InnoDB als Engine nutzt. Weitere MySQL Instanzen oder andere Applikationen sollten hier damit nicht betrieben werden.

Die einzelnen, oben genannten Parameter, werden dann anhand von offengelegten Formeln berechnet, können aber jederzeit durch eine "dedizierte" Konfiguration manuell angepasst werden.

Die Formel(n) für den Erfolg

Die Größe des Buffer Pools wird direkt über den Speicher des Servers errechnet. Dabei wird die folgende Methodik verwendet:

Server hat weniger als 1 GB RAM --> 128 MB
Server hat zwischen 1 und 4 GB RAM --> RAM * 0,5
Server hat mehr als 4 GB RAM --> RAM * 0,75

In unserem Beispiel hat unser kleiner Server (Linux VM) lediglich 2 GB RAM. Damit allokiert die MySQL Instanz automatisch "2 GB * 0,5 = 1 GB" InnoDB Buffer Pool.

[root@localhost ~]# mysql -uroot --execute="select variable_value/1024/1024 'Buffer Pool GB'  from performance_schema.global_variables where variable_name = 'InnoDB_Buffer_Pool_Size'"; 

+----------------+ 
| Buffer Pool GB | 
+----------------+ 
|           1024 | 
+----------------+

Ähnlich verhält es sich für die anderen Parameter. Die "Formel" für die "innodb_log_file_size" ist wie folgt (ab Version 8.0.14):

Bei weniger als 8 GB Buffer Pool Size --> 512 MB
Zwischen 8 und 128 GB Buffer Pool Size --> 1024 MB
Über 128 GB Buffer Pool Size (ja, das gibt es 😉 ) --> 2048 MB

Die Anzahl der Log-Dateien ("innodb_log_files_in_group") erklärt sich so:

Buffer Pool Size ist kleiner als 8 GB --> round(buffer pool size)
Buffer Pool Size zwischen 8 und 128 GB --> round(buffer pool size + 0,75)
Mehr als 128 GB Buffer Pool Size --> 64

Zu guter Letzt wird der Parameter "innodb_flush_method" in aller Regel auf "O_DIRECT_NO_FSYNC" gesetzt. Dies verhindert das zweimalige Buffer zwischen OS-Cache und der Festplatte und funktioniert sehr gut für I/O-Systeme mit geringer Latenz.

Fazit: Einfach (und) gut?

Sofern die Rahmenbedingungen (dedizierte MySQL InnoDB Datenbankserver) stimmen, sind die gewählten Parameter für ein initiales Setup durchaus brauchbar. Natürlich sollte im weiteren Betrieb (sofern Probleme auftauchen) immer wieder geprüft werden, ob trotzdem ein "manueller" Eingriff notwendig sein könnte. Wie bei jeder "Faustformel" haben auch die hier sinnvollen Einstellungen nicht den Anspruch in jedem (!) Falle korrekt zu sein.

Sie haben Fragen rund um den Betrieb von MySQL? Dann sprechen Sie uns an.