MySQL 8 Performance steigern: Wie man die Redo Log Größe für optimale Datenbank-Effizienz einstellt

MySQL nutzt intern Redo Logs, um während der Crash-Wiederherstellung Daten zu korrigieren, die von unvollständigen Transaktionen geschrieben wurden. Doch wie bestimmt man die richtige Größe des Redo Logs für eine effiziente MySQL Datenbank-Performance? Dieser Beitrag bietet eine detaillierte Anleitung.

MySQL 8 Konfiguration: Die Bedeutung der Redo Log Größe

Ab MySQL 8.0.30 sollte die Variable innodb_redo_log_capacity für die Optimierung der Redo Logs angepasst werden. Diese Variable bestimmt die Kapazität des Redo Logs. Diese ist dynamisch, was bedeutet, dass keine Ausfallzeit erforderlich ist, wenn Anpassungen vorgenommen werden müssen.

Formel zur Berechnung der geschriebenen MB pro Minute ins Log

Folgende hilfreiche Formel wird verwendet, um zu sehen, wie viele MB in einer Minute ins Log geschrieben wurden:

mysql> select ( - ) / 1024 / 1024 as MB_per_min;
mysql> pager grep sequence; show engine innodb statusG select sleep(60); show engine innodb statusG nopager;
mysql> select (<second_value> - <first_value>) / 1024 / 1024 as MB_per_min;

Hierbei repräsentieren <second_value> und <first_value> die Log-Sequenznummern zu zwei verschiedenen Zeitpunkten, wobei der Unterschied zwischen ihnen die Menge an Daten ist, die in einer Minute geschrieben wurde.

MySQL-Versionen vor 8.0.30:

innodb_log_file_size und innodb_log_files_in_group:

Standardmäßig erstellt InnoDB zwei Redo-Log-Dateien (innodb_log_files_in_group) im Datenverzeichnis, die ib_logfile0 und ib_logfile1 heißen, und schreibt in zirkulärer Weise in diese Dateien; jede mit einer Größe von innodb_log_file_size.

MySQL-Versionen 8.0.30 und höher:

innodb_redo_log_capacity

InnoDB erstellt 32 Dateien (jede von ihnen mit einer Größe von innodb_redo_log_capacity / 32) im #innodb_redo Verzeichnis im Datenverzeichnis, es sei denn, ein anderes Verzeichnis wurde durch die Variable innodb_log_group_home_dir angegeben.

Wie wird also mit der neuen Variable der Wert berechnet?

1. Wenn innodb_redo_log_capacity konfiguriert ist, wird dieser Wert verwendet (wobei innodb_log_file_size und innodb_log_files_in_group ignoriert werden).
2. Wenn innodb_redo_log_capacity nicht gesetzt ist, aber innodb_log_file_size und innodb_log_files_in_group konfiguriert sind, berechnet MySQL den Wert (innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity).
3. Wenn keiner der Werte konfiguriert ist, wird die Redo-Log-Kapazität auf den Standardwert von innodb_redo_log_capacity (100 MB) gesetzt.

Nur zur Erinnerung, warum Sie dies anpassen möchten: Wenn das Redo Log voll ist, muss MySQL einen Checkpoint ausführen (Flushing vom InnoDB Buffer Pool auf die Festplatte). Wenn Ihre Redo Logs klein sind, werden Checkpoints häufiger durchgeführt, was zu mehr I/O führt und die Leistung beeinträchtigt.

Eine Faustregel hier ist, die Redo Logs so zu konfigurieren, dass sie eine Stunde Datenverkehr aufnehmen können, sodass das Checkpointing auf eine sanfte, weniger aggressive Weise durchgeführt wird. Andernfalls kann das Flushing zum denkbar schlechtesten Zeitpunkt erfolgen, da der Checkpoint unvermeidlich ist.

Formel testen

Um eine allgemeine Vorstellung zu bekommen, wurden einige grundlegende mysqlslap Befehle in einer Schleife ausgeführt, um Last auf der Datenbank zu erzeugen. Dies ist weit entfernt von einem realen Szenariotest, sollte aber in diesem speziellen Fall hilfreich sein.

$ while true; do mysqlslap --auto-generate-sql -vv --concurrency=100 --iterations=200 --number-char-cols=7 --number-int-cols=10; done

Die oben genannte Formel wurde getestet:

mysql> pager grep sequence; show engine innodb statusG select sleep(60); show engine innodb statusG nopager;
Log sequence number  3182015832
Log sequence number  3252604221
mysql> select (3252604221 - 3182015832) / 1024 / 1024 as MB_per_min;

Nach mehreren Durchläufen ergab sich, dass mit einer konstanten Last die Formel einen Durchschnitt von 63,8 MB pro Minute (3,74 GB pro Stunde) ergibt.

Das funktioniert zwar, aber es handelt sich um einen manuellen Vorgang, der während der Stoßzeiten mehrmals online durchgeführt werden muss.

Fazit

Auch wenn sich die Variablen kürzlich geändert haben, ist es wichtig, die Redo Logs weiterhin zu überwachen und zu konfigurieren, insbesondere in schreibintensiven Umgebungen. Es wird empfohlen, PMM zu verwenden, um detailliertere Informationen zu erhalten. PMM ist Open Source und kann schnell installiert und verwendet werden.