Maximale Effizienz in MySQL - Ein tiefer Einblick in den innodb_log_file_size

Die Performance von Datenbanken ist das Rückgrat moderner Softwareanwendungen. Aspekte wie Datenmanipulation, Abfrageverarbeitung und Workload-Management sind essenziell, um eine Datenbank optimal zu konfigurieren. In diesem Kontext ist die richtige Einstellung bestimmter Parameter in verbreiteten Datenbankmanagementsystemen wie MySQL von entscheidender Bedeutung.

In diesem Beitrag werfen wir einen genauen Blick auf einen entscheidenden Konfigurationsparameter in MySQL:  innodb_log_file_size.
Er legt die Größe der Transaktionsprotokolldateien fest, die vom InnoDB-Storage-Engine genutzt werden. Die Größe dieses Parameters kann signifikante Auswirkungen auf die Performance von Datenbankoperationen haben.

Mit diesem Experiment wollen wir den Einfluss verschiedener innodb_log_file_size-Größen auf die Datenbankoperationen direkt beobachten und herausfinden, welche Konfiguration die beste Performance erzielt. Die daraus gewonnenen Erkenntnisse sind äußerst nützlich für Datenbankadministratoren, Entwickler und Systemarchitekten, um die Performance ihrer MySQL-Datenbanken zu optimieren.

Bevor wir in die Details des Experiments eintauchen, werfen wir zunächst einen Blick auf die Bedeutung dieses Parameters und das Experiment selbst.

Zunächst einmal prüfen wir den Wert von innodb_log_file_size in der Datei
/etc/mysql/mariadb.conf/50-server.cnf:

# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G

Wir wissen zwar, dass es einen innodb_log_file_size-Wert gibt und wir diesen über die Datei anpassen können, doch wie Sie sehen, wird dieser Parameter dort zunächst nicht explizit erwähnt.
Es gibt mehrere Möglichkeiten, den Standardwert zu finden. In MySQL können wir dies beispielsweise so überprüfen:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%innodb_log%';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| innodb_log_buffer_size      | 16777216  |
| innodb_log_file_size        | 100663296 |
| innodb_log_group_home_dir   | ./        |
| innodb_log_write_ahead_size | 8192      |
+-----------------------------+-----------+
4 rows in set (0,002 sec)

Mit diesem Befehl haben wir alle Variablen aufgelistet und spezifisch nach denen gesucht, die "innodb_log" enthalten.

Wie Sie sehen können, gibt es einen festgelegten Wert für innodb_log_file_size.
Um ihn anschaulicher zu machen, können wir einfach den innodb_log_file_size auswählen und seinen Wert in Megabyte ausgeben lassen:

MariaDB [(none)]> select @@innodb_log_file_size/1024/1024;
+----------------------------------+
| @@innodb_log_file_size/1024/1024 |
+----------------------------------+
|                      96.00000000 |
+----------------------------------+
1 row in set (0,000 sec)

Hier sehen wir unseren Standardwert für innodb_log_file_size: 96 MB.

Damit Änderungen wirksam werden, müssen wir den Server nach den Anpassungen neu starten.

root@ip-172-31-28-146:/etc/mysql/mariadb.conf.d# vim 50-server.cnf
root@ip-172-31-28-146:/etc/mysql/mariadb.conf.d# mysql -e "select @@innodb_log_file_size/1024/1024;"
+----------------------------------+
| @@innodb_log_file_size/1024/1024 |
+----------------------------------+
|                      96.00000000 |
+----------------------------------+
root@ip-172-31-28-146:/etc/mysql/mariadb.conf.d# service mysqld restart
root@ip-172-31-28-146:/etc/mysql/mariadb.conf.d# mysql -e "select @@innodb_log_file_size/1024/1024;"
+----------------------------------+
| @@innodb_log_file_size/1024/1024 |
+----------------------------------+
|                     512.00000000 |
+----------------------------------+

Nach dem Neustart zeigt die neue Abfrage den aktualisierten innodb_log_file_size-Wert von 512 MB an.

Der innodb_log_file_size-Parameter ist eine entscheidende Konfigurationseinstellung, die sich direkt auf die Performance von MySQL-Datenbanken auswirkt. Dieser Parameter legt die Größe der Transaktionsprotokolldateien fest, die von der InnoDB Storage Engine verwendet werden. Eine fehlerhafte Konfiguration kann die Datenbank-Performance erheblich beeinträchtigen.

Unser Experiment zielt darauf ab, die Bedeutung verschiedener innodb_log_file_size-Werte hervorzuheben, indem wir ihren Einfluss auf Datenbankoperationen bewerten. Durch Experimente mit unterschiedlichen Größen können wir die Dauer der Datenbankoperationen vergleichen und so die effizienteste Größe ermitteln.

Nun werden wir die Größe der innodb_log_file_size auf 512 MB, 1 GB, 2 GB, 4 GB und 8 GB festlegen und sie genauer untersuchen. Bei jeder innodb_log_file_size-Einstellung führen wir einige Tests durch. Wir laden Dateien unterschiedlicher Größe mit der DATA LOAD INFILE-Methode und analysieren die PMM-Grafiken, um die Verarbeitungszeiten zu vergleichen.

Wie können wir Daten laden und die Zeit messen?

Für diesen Prozess nutzen wir die DATA LOAD INFILE-Methode, die speziell zum Importieren großer Datenmengen verwendet wird.

Wir verwenden den "time"-Befehl, um zu messen, wie lange das Einfügen der Daten dauert, da der Befehl dafür bekannt ist, die Ausführungszeit eines Befehls zu ermitteln. Der Output des "time"-Befehls enthält Zeitmessungen eines Prozesses, wie Echtzeit, Benutzerzeit und Systemzeit.

root@ip-172-31-28-146:/var/lib/mysql/mydb# time mysql mydb -e "LOAD DATA INFILE '1GB.csv' INTO TABLE user_inform FIELDS TERMINATED BY ';' IGNORE 1 ROWS;"
real 3m6. 355s
user 0m0. 000s
sys 0m0. 009s

"real": Die Echtzeit zeigt, wie lange der Prozess insgesamt gedauert hat. In diesem Fall betrug die Gesamtdauer 3 Minuten und 6,355 Sekunden.

Schauen wir uns nun den Code genauer an, den wir zum Laden der Daten verwenden:

mysql>LOAD DATA INFILE '1GB.csv' INTO TABLE user_inform FIELDS TERMINATED BY ';' IGNORE 1 ROWS;

LOAD DATA INFILE '1GB.csv' INTO TABLE user_inform: Hiermit wird die Datei 1GB.csv in die Tabelle user_inform geladen. Der Befehl LOAD DATA INFILE ermöglicht das Laden von Daten aus einer Datei in eine Tabelle.

FIELDS TERMINATED BY ' ; ': Gibt das Zeichen an, das als Trennzeichen für die Felder verwendet wird. In diesem Fall ist es das " ; "Zeichen.

IGNORE 1 ROWS: Überspringt die erste Zeile während des Ladevorgangs. Typischerweise enthalten CSV-Dateien eine Kopfzeile, und das Überspringen dieser Zeile verhindert Datenfehler.

Ich habe 500-MB-Dateien mit einer Größe von 1 GB, 2 GB, 3 GB, 4 GB, 8 GB und 10 GB in die von uns spezifizierte innodb_log_file_size hochgeladen und dabei die Zeit gemessen.
Anschließend habe ich den innodb_log_file_size-Wert erhöht und denselben Installationsprozess wiederholt.
Die gesammelten Ergebnisse habe ich in einer Tabelle zusammengefasst.

Zeittabelle für InnoDB_log_file_size und Datengröße

In der ersten Spalte der Tabelle sind die von uns festgelegten innodb_log_file_size-Werte aufgelistet: 512M, 1G, 2G, 4G und 8G.
In der ersten Zeile der Tabelle stehen die Dateigrößen, die wir für die Tests mit der DATA LOAD INFILE-Methode verwendet haben: 500M, 1G, 2G, 3G, 4G, 8G und 10G.

Anhand der Ergebnisse aus der Tabelle können wir Folgendes feststellen:

• Wenn ausreichend Platz durch den innodb_log_file_size-Parameter vorhanden ist, kann unsere Datenbank die Daten schnell verarbeiten.
• Das Hochladen von Dateien, die größer sind als der festgelegte innodb_log_file_size, dauert erheblich länger.
• Mit einer größeren innodb_log_file_size kann die Datenbank I/O-Operationen effizienter durchführen, was zu einem schnelleren Datenimport führt.

Werfen wir nun einen detaillierten Blick auf einige Etappen:

Zunächst setzen wir die innodb_log_file_size auf 512M und fügen dann Dateien von 500M, 1G und 2G in leere Tabellen ein.

Als innodb_log_file_size auf 512M festgelegt war, verursachte das Hochladen einer Datei, die kleiner als diese Kapazität ist, keine Schwierigkeiten. Der Vorgang dauerte 1 Minute und 29,74 Sekunden.
Beim Versuch, eine 1-GB-Datei hochzuladen, stieg die Verarbeitungszeit jedoch auf 5 Minuten und 35,272 Sekunden.
Als wir dann eine 2-GB-Datei hochladen wollten, betrug die Verarbeitungszeit 6 Minuten und 39,896 Sekunden.

Nun laden wir dieselben Dateien hoch, während innodb_log_file_size auf 1GB eingestellt ist, und betrachten die Ergebnisse.

Beim Hochladen der 500-MB-Datei wurde der Prozess problemlos abgewickelt und dauerte 1 Minute und 28 Sekunden.
Beim Hochladen einer 1-GB-Datei dauerte der Vorgang 3 Minuten und 6,355 Sekunden.

Wenn wir die bisherigen Ergebnisse vergleichen und analysieren, können wir Folgendes feststellen:

Wenn die hochgeladene Datei kleiner als der konfigurierte innodb_log_file_size war, wurde der Prozess problemlos in ähnlichen Zeiten abgeschlossen. Bei größeren Dateien jedoch zeigte sich ein deutlicher Unterschied: Bei einer innodb_log_file_size von 512MB dauerte es 5 Minuten und 35,272 Sekunden, um eine 1-GB-Datei hochzuladen.
Bei einer innodb_log_file_size von 1GB wurde derselbe Vorgang in 3 Minuten und 6,355 Sekunden abgeschlossen.
Als innodb_log_file_size auf 2GB eingestellt war und wir eine 3-GB-Datei hochluden, dauerte der Prozess 11 Minuten und 22,13 Sekunden.

Schauen wir uns die Situation anhand von Grafiken genauer an:

Hier stehen wir nun mit einer innodb_log_file_size von 1GB und laden eine Datei mit einer Größe von 1GB hoch.

Interpretation der Grafik:

• Die Grafik zeigt eine Reihe von Spitzen im checkpoint age, was auf Phasen hinweist, in denen das checkpoint age scharf ansteigt.
• Die höchsten Peaks erreichen wiederholt die maximale checkpoint age-Linie, überschreiten diese jedoch nicht. Das bedeutet, dass das System effektiv in der Lage ist, das checkpoint age unter dem festgelegten Maximum zu halten.
• Nach jeder Spitze folgt ein starker Rückgang, der darauf hinweist, dass Checkpoints erstellt und das checkpoint age auf einen niedrigeren Wert zurückgesetzt wird. Dieses Verhalten ist gesund, da es ein kontinuierliches Wachstum verhindert, das zu Leistungsproblemen führen könnte.

Die maximale checkpoint age-Linie, dargestellt durch die rote Linie, liegt konstant bei 858,31 MiB während des gesamten beobachteten Zeitraums.

Zu diesem Zeitpunkt haben wir eine innodb_log_file_size von 2GB und laden Daten in Größen von 1GB und 2GB hoch.

Interpretation der Grafik:

• Erster Anstieg und Abfall: Zu Beginn gibt es einen starken Anstieg der uncheckpointed bytes, der kurz nach 10:42 etwa 1,40 GiB erreicht. Dieser Anstieg könnte mit dem Hochladen der 1-GB-Datei zusammenhängen. Danach folgt ein rascher Rückgang auf nahezu null, was darauf hindeutet, dass die Daten entweder checkpointed oder gelöscht wurden, wodurch das checkpoint age effektiv zurückgesetzt wird.
• Zweite größere Spitze: Ein signifikanterer Anstieg der uncheckpointed bytes beginnt kurz nach 11:00 Uhr und steigt erneut auf etwa 1,40 GiB an. Dies stimmt mit dem Hochladen der 2-GB-Datei überein. Der Peak ist höher als der erste und bleibt länger erhöht, was auf ein größeres Datenvolumen hinweist.
• Allmählicher Rückgang nach dem Peak: Nach dem Höhepunkt gegen 11:16 gibt es einen allmählichen Rückgang der uncheckpointed bytes, was darauf hindeutet, dass das System aktiv die neuen Daten checkpointed. Dieser allmähliche Rückgang setzt sich fort, bis er gegen Ende des beobachteten Zeitraums einen stabilen Zustand erreicht.
• Effektives Management des Checkpoint Age: In der Grafik bleiben die uncheckpointed bytes konsequent unter der maximalen checkpoint age-Linie, die bei etwa 1,63 GiB liegt. Das zeigt, dass das System in der Lage ist, das checkpoint age innerhalb akzeptabler Grenzen zu halten und ein Überlaufen oder eine übermäßige Ansammlung von uncheckpointed Daten zu verhindern.
• Rücksetzung des Checkpoint-Alters: Die starken Rückgänge nach jeder Spitze zeigen die Fähigkeit des Systems, das checkpoint age durch das Erstellen von Checkpoints zurückzusetzen. Dieses Verhalten ist entscheidend für die Gesundheit des Datenbanksystems, da es sicherstellt, dass das checkpoint age nicht kontinuierlich wächst, was sonst zu Leistungseinbußen oder Stabilitätsproblemen führen könnte.

In der Regel ist das checkpoint age in InnoDB, dargestellt durch den Wert in der Grafik, niedriger als innodb_log_file_size. Dies liegt an mehreren Betriebsverhalten und Einstellungen im MySQL-Datenbanksystem.

Analyse der Datenbank-Performance-Faktoren

Checkpointing-Prozess:

Wenn wir Daten in die Datenbank laden möchten, werden sie nicht direkt an den gewünschten Speicherort geschrieben. Stattdessen werden sie zunächst in Protokolldateien gespeichert. Dann beginnt der Checkpointing-Prozess, bei dem die Daten sicher übertragen werden. Der Checkpointing-Prozess ist eine wichtige Prozedur in einem Datenbankmanagementsystem (DBMS), die in regelmäßigen Abständen abläuft, um die Sicherheit der Datenbank zu gewährleisten. Dabei werden Änderungen in der Datenbank dauerhaft auf die Festplatte geschrieben, was zur Datenintegrität beiträgt und bei Wiederherstellungsoperationen eine kritische Rolle spielt. In der Regel umfasst dieser Prozess das Schreiben der Protokolldateien in die Datenbankdateien auf der Festplatte, was die Zuverlässigkeit der Datenbank erhöht.

Experimentergebnisse:

Die Ergebnisse des Experiments zeigen deutlich, dass der innodb_log_file_size-Parameter erheblichen Einfluss auf die Datenbank-Performance hat, insbesondere bei großen Datei-Uploads.

• Größe der Transaktionsprotokolle:
  Die Größe der Transaktionsprotokolldateien, die durch innodb_log_file_size festgelegt wird, beeinflusst direkt die Effizienz der Datenbankoperationen. Größere Protokollgrößen ermöglichen ein effizienteres Handling großer Transaktionen, was zu schnelleren Upload-Zeiten führt.
• I/O-Operationen:
  Die Geschwindigkeit und Effizienz der Eingabe-/Ausgabeoperationen spielen eine entscheidende Rolle bei der Performance der Datenbank. Mit einem größeren innodb_log_file_size kann die Datenbank I/O-Operationen effektiver durchführen, was schnellere Daten-Uploads zur Folge hat.
• Transaktionsbeständigkeit:
  Größere Transaktionsprotokollgrößen können die Transaktionsbeständigkeit verbessern, indem sie mehr Transaktionen puffern, bevor sie auf die Festplatte geschrieben werden. Dies kann zu einer besseren Gesamtleistung führen, insbesondere bei hohem Transaktionsvolumen.
• Ressourcennutzung:
  Das Experiment verdeutlicht, wie wichtig eine optimale Ressourcennutzung, insbesondere des Arbeitsspeichers, für die Datenbank-Performance ist. Ein größerer innodb_log_file_size-Parameter kann mehr Speicher erfordern, aber bei richtiger Konfiguration zu einer insgesamt besseren Performance führen.

Die Analyse unterstreicht das Zusammenspiel zwischen innodb_log_file_size, Transaktionsverarbeitung und Datenbankleistung. Die Optimierung dieses Parameters entsprechend der spezifischen Arbeitslast und den verfügbaren Ressourcen kann die Effizienz und Reaktionsfähigkeit von MySQL-Datenbanken erheblich verbessern.

Danke fürs Lesen! Sollten Sie Probleme mit Ihrer Datenbank haben, zögern Sie nicht, uns zu kontaktieren. Einen Blick auf unsere Kontaktseite lohnt sich!