Die Top 5 MySQL-Performance-Variablen

In MYSQL musst du nur fünf Mysql-Variablen anpassen, um eine bessere Performance für dein System zu erreichen. Neben den unten definierten Variablen können noch weitere InnoDB- und globale MySQL-Variablen angepasst werden, z. B. das Tuning des Workloads oder die Abstimmung der zur Verfügung stehenden Hardware.

1. Innodb_buffer_pool_size

Für den Anfang verwenden wir 50 % bis 70 % des gesamten RAM-Speichers. Wir benutzen die folgende Mysql-Abfrage, um die Gesamtgröße deiner Datenbanken zu ermitteln:

select engine,
round(sum(data_length+index_length)/1024/1024/1024, 2) as total_gb,
round(sum(data_length)/1024/1024/1024,2) as data_gb,
round(sum(index_length)/1024/1024/1024,2) as index_gb
from information_schema.tables
where table_schema not in ('information_schema', 'mysql',
'performance_schema')
and TABLE_TYPE <> 'VIEW' and engine="Innodb"
group by 1

2. innodb_log_file_size

Die Variable „innodb_log_file_size” definiert die Speichergröße der „Redo-Logs“. Was sind Redo-Logs? Siehe dieser Beitrag:

http://hendler-world.de/index.php/2020/11/12/mysql-performance-variablen/

Wie ermittle ich die optimale Speichergröße für meine Redo-Logs?
In der  MYSQL-Konsole können wir die optimale Redo-Speichergröße für unser System ermitteln. Die folgende MYSQL-Abfrage löst eine hohe Auslastung in unserem RAM-Speicher aus. Ziel ist es, dass der RAM-Speicher der hohen Auslastung standhält und der MYSQL-Service nicht automatisch gekillt wird.

$ mysql> pager grep sequence

$ mysql> show engine innodb status; select sleep(60); show engine innodb status;

$ mysql> select (OUTPUT 1 - OUTPUT 2) / 1024 / 1024 as MB_per_min;

Als grobe Faustregel kannst du die innodb_log_file so groß machen, dass sie eine Stunde lang Logs aufnehmen kann.

3. innodb_flush_log_at_trx_commit

In diesem Absatz geht es um die „innodb_flush_log_at_trx_commit“-Variable, welche dafür verantwortlich ist, die Daten in die ib_logfile zu schreiben. Die folgenden Varianten der „innodb_flush_log_at_trx_commit“ gibt es:

innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2

Definition:

innodb_flush_log_at_trx_commit = 0

• Der Commit ist immer erfolgreich – die Daten werden in den MYSQL „Log Buffer“ geschrieben und von da aus an den Festplatten-Cache übergeben. Dabei vertraut der Mysql-Prozess darauf, dass die Daten vom Cache 1-mal pro Sekunde auf die Festplatte bzw. in die ib_logfile geschrieben werden.
• „0“ ist die schnellste aber auch kritischste Variante. Zum einen muss der MySQL-Prozess beim Commit nicht auf den Festplatten-Schreibvorgang warten. Zum anderen haben wir bei einem Systemausfall einen Datenverlust von maximal einer Sekunde.
• Der oben beschriebene Datenverlust kann durch den Einsatz einer BBU (battery backup unit) vermieden werden. Die BBU versorgt bei einem Systemausfall die Festplatte so lange mit Strom, dass der DISK-Cache komplett (physikalisch) auf die Festplatte geschrieben werden kann.

innodb_flush_log_at_trx_commit = 1

• Die Daten werden nach jedem Commit aus dem Memory physikalisch auf die Festplatte bzw. in die ib_logfile geschrieben. Der Commit ist erfolgreich, wenn die (physikalische) Festplatte den Schreibprozess bestätigt hat. Die Daten werden also am Disk-Cache vorbei auf die Festplatte geschrieben. „1“ ist die sicherste aber auch die langsamste Variante. Zum einen können wir bei einem Systemausfall sicher sein, dass alle Commits in der ib_logfile abgespeichert sind. Zum anderen muss Mysql für jedes Commit auf das OK der (physikalischen) Festplatte warten. 

innodb_flush_log_at_trx_commit = 2

• Wie bei Variante „0“ ist das Commit immer erfolgreich. Die Daten werden 1-mal pro Sekunde auf die Festplatte geschrieben.
• Variante „2“ schreibt in den „File System Buffer“, welcher betriebssystemgesteuert auf die Festplatte schreibt. Der MYSQL-Prozess hat hierbei keinen Einfluss auf den Schreibvorgang. Der Unterschied zwischen „0“ und „2“ besteht darin, dass bei einem manuellen oder im Script generierten Commit die Daten ebenfalls auf Disk geschrieben werden. Bei einem Systemausfall haben wir also unter Umständen einen geringeren Datenverlust als bei Variante „0“.

4. innodb_flush_method

O_DIRECT verwendet die fsync()-Methode zum Flushen von Dateien auf die Festplatte. Es wird ebenfalls sichergestellt, dass kein doppeltes Buffering im System erfolgt. Alle Lese- und Schreibvorgänge gehen direkt auf die Festplatte.

5. sync_binlog

Der sync_binlog ist für den Schreibzugriff auf den Binlog zuständig. Mit der sync_binlog wird definiert, ob oder wie oft in den Binlog geschrieben werden soll.

Sync_binlog = 0

Eine ausgeführte Transaktion ist immer erfolgreich. Mysql prüft nicht, ob die Daten aus dem RAM-Speicher auf die Festplatte bzw. in den Binlog geschrieben wurden. Bei einem Systemausfall kann es in einem Master-Slave-Szenario daher vorkommen, dass nicht alle Transaktionen in den Binlog geschrieben wurden.

Sync_binlog = 1

Eine ausgeführte Transaktion ist erst erfolgreich, wenn sie auf die Festplatte bzw. in den Binlog geschrieben wurde. Diese Methode ist sehr langsam, weil jede Transaktion auf einen erfolgreichen Schreibvorgang der Festplatte wartet. Bei einem Systemausfall sind die Daten jedoch vollständig im Binlog vorhanden.

Sync_binlog = 5

Jede fünfte Transaktion wird auf die Festplatte bzw. in den Binlog geschrieben.

Viel Spaß!