In einer vBulletin 4 Testinstallation für das neue U-Labs 2.0 sind mir sehr langsame SQL INSERT-Abfragen aufgefallen:
MariaDB [myforum]> insert into tag set tagtext='abc12345',dateline=unix_timestamp(),canonicaltagid=1234;
Query OK, 1 row affected (0.24 sec)
Die Ausführungszeit variierte zwischen 250 und 300ms. In Anbetracht der simplen Abfrage ein sehr hoher Wert. Das Netzwerk bzw. die DNS-Auflösung war nicht verantwortlich, wie zunächst vermutet: Im MariaDB-Image ist das Auflösen der DNS-Namen bereits abgeschaltet. Und auch wenn der MySQL-Client sich direkt im Container des Servers mit Host 127.0.0.1 verbindet, bleibt die SQL-Abfrag ähnlich langsam.
InnoDB Flush
Dieses Verhalten konnte ebenfalls bei anderen Tabellen beobachtet werden, die InnoDB als Datenbankengine einsetzen. Ein Blick auf die Serverkonfiguration zeigt:
MariaDB [myforum]> show variables like '%innodb_flush%';
+--------------------------------+----------+
| Variable_name | Value |
+--------------------------------+----------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+----------+
Auffällig war innodb_flush_log_at_trx_commit, der auf 1 gesetzt ist. Zu diesem findet sich folgende Erklärung:
Damit war das Problem gefunden: Bei jeder INSERT-Abfrage schreibt der Server die Daten auf die Platte. Da der Server einen RAID 1 Verbund aus HDDs besitzt (zwar Enterprise-Platten, aber eben dennoch HDDs), ist hier mit einer gewissen Latenzzeit zu rechnen.
Zur Lösung habe ich mich entschieden, innodb_flush_log_at_trx_commit auf 2 zu setzen:
set global innodb_flush_log_at_trx_commit = 2;
Dadurch hat sich die Abfragenzeit auf 0 ms verringert, da MariaDB nicht mehr auf das Schreiben der Daten wartet. Stattdessen werden diese mit einer Sekunde Versatz asynchron auf die Datenträger geschrieben.
Im Falle eines Crashes eine bis maximal zwei Sekunden an Transaktionen zu verlieren, dürfte im Gegensatz zum Performance-Gewinn in den meisten Anwendungsfällen akzeptabel sein. Natürlich sollte man dies immer im Einzelfall abwägen.
Wird schon länger empfohlen, den Wert von innodb_flush zu ändern. Trotzdem danke für das Tutorial! 🙂