5 Minuten Lesezeit (1030 Worte)

Immer schön flexibel bleiben: MySQL Dynamic Redo Log Size

Dienstag, 13. Juni 2023

Durch die Einführung von dynamisch verstellbaren Redo Logs mit der Version 8.0.30 gewinnen MySQL-Administratoren deutlich an Flexibilität. Standardmäßig wird die Größe der InnoDB Redo Logs, ähnlich wie auch der InnoDB Buffer, bei der Serverinitialisierung definiert. Bis dato war ein Server Restart für die Anpassung der InnoDB-Redo-Log-Größe notwendig. Dieser Restart fällt nun weg.

Nutzen der InnoDB Redo Logs

Die InnoDB Redo Logs sind in erster Linie für den Schutz vor Datenverlust bei Transaktionen zuständig. Ähnlich wie auch bei einer Oracle-Datenbank werden Änderungen an Datensätzen zunächst im Redo Log protokolliert, bevor der eigentliche Datensatz im Buffer verändert wird. Synchron zur Transaktion/zum Commit werden diese Redo-Log-Informationen aus dem Memory in die physikalischen Redo-Log-Dateien geschrieben. Ohne ein Logging in dieses Protokoll wären Änderungen bei einem Servercrash verloren und Datenverlust eine Realität. Mit dem sogenannten WAL(Write-Ahead-Logging)-Prinzip werden die Änderungen aus dem Buffer I/O in die Logs geschrieben und können somit im Crash-Fall performant restauriert (recovered) werden.

Die Redo Log File Capacity

Um die Redo Logs dynamisch skalieren zu können, muss der Parameter innodb_redo_log_capacity verwendet werden. Die zuvor genutzten Parameter innodb_log_file_size * innodb_log_files_in_group sind ab diesem Moment wirkungslos und dienen nur noch der Herleitung für den Wert der Redo Log Capacity:

innodb_log_group_capacity = innodb_log_file_size * innodb_log_files_in_group

mysql> show variables like 'innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------
2 rows in set (0.00 sec)

In unserem Beispiel ergibt sich der Wert für innodb_log_group_capacity daher mathematisch wie folgt:

innodb_log_group_capacity = 50331648 * 2

mysql> show variables like '%innodb_redo_log%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| innodb_redo_log_archive_dirs |           |
| innodb_redo_log_capacity     | 104857600 |
| innodb_redo_log_encrypt      | OFF       |
+------------------------------+-----------+
3 rows in set (0.00 sec)

Solange kein explizites Verzeichnis über den Parameter innodb_log_group_home_dir gesetzt wird, werden die InnoDB Redo Log Files in das Datadir der Datenbank gelegt. Hier existiert fortan ein Unterverzeichnis mit dem Namen #innodb_redo. Innerhalb dieses Verzeichnisses liegen zwei verschiedene Arten von Redo Log Files: Ordinary & Spare. Während es sich bei den Ordinary Files um solche handelt, die gerade verwendet werden, warten die Spare Files noch auf ihren Einsatz. Insgesamt existieren in unserem Beispiel 32 Files, die sich anteilig aus der Größe des Parameters innodb_redo_log_capacity ergeben:

bash-4.4# pwd
/var/lib/mysql/#innodb_redo
bash-4.4# ls -al
total 102400
-rw-r-----  1 root  root  3276800 Aug 29 18:01 '#ib_redo10_tmp'
-rw-r-----  1 root  root  3276800 Aug 29 18:01 '#ib_redo11_tmp'
…
-rw-r-----  1 root  root  3276800 Aug 29 18:01 '#ib_redo39_tmp'
-rw-r-----  1 root  root  3276800 Aug 29 18:01 '#ib_redo40_tmp'
-rw-r-----  1 mysql mysql 3276800 Aug 29 18:03 '#ib_redo9'
drwxr-x--- 34 mysql mysql    1088 Aug 29 18:01  .
drwxr-xr-x 30 mysql mysql     960 Aug 29 18:01  ..

In unserem Beispiel sind fast alle Files noch ungenutzt (Spare), was sich an dem Suffix _tmp erkennen lässt. Ohne großen Aufwand lassen sich die Redo Logs nun online anpassen:

mysql> set global innodb_redo_log_capacity = 2*1024*1024*1024;
Query OK, 0 rows affected (0.58 sec)
mysql> show variables like 'Innodb_redo_log_capacity';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_redo_log_capacity | 2147483648 |
+--------------------------+------------+
1 row in set (0.01 sec)

bash-4.4# ls -altr
total 2031872
drwxr-xr-x 30 mysql mysql      960 Aug 29 18:01  ..
-rw-r-----  1 mysql mysql   262144 Aug 29 19:59 '#ib_redo10'
-rw-r-----  1 root  root  67108864 Aug 29 20:04 '#ib_redo11_tmp'
-rw-r-----  1 root  root  67108864 Aug 29 20:04 '#ib_redo12_tmp'
-rw-r-----  1 root  root  67108864 Aug 29 20:04 '#ib_redo13_tmp'
…
-rw-r-----  1 root  root  67108864 Aug 29 20:04 '#ib_redo40_tmp'
drwxr-x--- 34 mysql mysql     1088 Aug 29 20:04  .
-rw-r-----  1 root  root  67108864 Aug 29 20:04 '#ib_redo41_tmp'

Es gibt eine Vielzahl an Parametern, die den Status der Redo Logs beschreiben:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo%';
+-------------------------------------+------------+
| Variable_name                       | Value      |
+-------------------------------------+------------+
| Innodb_redo_log_read_only           | OFF        |
| Innodb_redo_log_uuid                | 1075899837 |
| Innodb_redo_log_checkpoint_lsn      | 31607771   |
| Innodb_redo_log_current_lsn         | 31607771   |
| Innodb_redo_log_flushed_to_disk_lsn | 31607771   |
| Innodb_redo_log_logical_size        | 512        |
| Innodb_redo_log_physical_size       | 3276800    |
| Innodb_redo_log_capacity_resized    | 104857600  |
| Innodb_redo_log_resize_status       | OK         |
| Innodb_redo_log_enabled             | ON         |
+-------------------------------------+------------+

Ob die dynamischen Redo Log Files aktuell verwendet werden können und somit kein Problem vorliegt, sagt beispielsweise die Variable innodb_redo_log_resize_status aus. Für weitere Infos verweise ich an dieser Stelle gerne auf die offizielle Dokumentation.

Fazit

Durch die Möglichkeit, die Redo-Log-Größe dynamisch anzupassen, liefert MySQL weitere Pluspunkte im Hinblick auf einen Enterprise fähigen Betrieb. Die Gelegenheiten für einen potenziell notwendigen Restart werden Schritt für Schritt reduziert und die Administration optimiert. Das Handling ist sehr einfach und ermöglicht eine unkomplizierte Anpassung im laufenden Betrieb.

Exkurs: Wie groß sollten die Redo Logs sein?

Grundsätzlich sollte diese Frage immer in Absprache mit der Anwendung beantwortet werden. Als Faustregel kann hierfür die Änderungsrate der Log Sequence innerhalb einer Minute herangezogen werden:

mysql> show engine innodb status\G select sleep(60); show engine innodb Status \G
Log sequence number          32341033
1 row in set (0.01 sec)

1 row in set (1 min 0.07 sec)

Log sequence number          51433670
1 row in set (0.00 sec)

Innerhalb von 60 Sekunden habe ich 2-mal die „sakila“-Datenbank erstellt und wieder gelöscht. Die Log Sequence hat sich dementsprechend erhöht. Anhand der Differenz lässt sich nun das Log Volume pro Minute errechnen:

mysql> select (51433670 - 32341033) / 1024 / 1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 18.20815754 |
+-------------+
1 row in set (0.00 sec)

Eine Empfehlung lautet hier, dass die Logs mindestens eine Stunde an Transaktionen halten sollten. In diesem Fall würde dies eine Log File-Größe von 18 MB * 60 = 1080 MB, also ca. 1 GB bedeuten. Für dieses Beispiel könnten wir den Parameter innodb_redo_log_capacity also auf 1 GB setzen. Die 32 einzelnen Redo Log Files würden entsprechend angepasst werden.