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:
Sie stellen fest, dass Innodb 100 Mal langsamer ist als MyISAM? Wahrscheinlich hast du vergessen, diesen Wert anzupassen. Der Standardwert von 1 bedeutet, dass jeder Update Transaktions-Commit (oder jede Anweisung außerhalb der Transaktion) das Protokoll auf der Festplatte löschen muss, was ziemlich kostspielig ist, besonders wenn Sie keinen Battery Backed up Cache haben. Viele Anwendungen, insbesondere solche, die aus MyISAM-Tabellen verschoben wurden, sind mit dem Wert 2 in Ordnung, was bedeutet, dass das Protokoll nicht auf der Festplatte, sondern nur im Betriebssystem-Cache gespeichert wird. Das Protokoll wird immer noch jede Sekunde auf die Festplatte geschrieben, so dass Sie normalerweise nicht mehr als 1-2 Sekunden an Updates verlieren würden. Der Wert 0 ist etwas schneller, aber etwas weniger sicher, da Sie Transaktionen verlieren können, auch wenn der MySQL-Server abstürzt. Wert 2 verursacht nur Datenverlust bei vollständigem Betriebssystemabsturz.
percona.com
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! 🙂