{"id":6341,"date":"2019-09-03T12:21:25","date_gmt":"2019-09-03T10:21:25","guid":{"rendered":"https:\/\/u-labs.de\/portal\/?p=6341"},"modified":"2019-09-03T13:00:22","modified_gmt":"2019-09-03T11:00:22","slug":"langsame-insert-abfragen-in-mariadb-mit-docker","status":"publish","type":"post","link":"https:\/\/u-labs.de\/portal\/langsame-insert-abfragen-in-mariadb-mit-docker\/","title":{"rendered":"Langsame INSERT-Abfragen in MariaDB beschleunigen"},"content":{"rendered":"
In einer vBulletin 4 Testinstallation f\u00fcr das neue U-Labs 2.0 sind mir sehr langsame SQL INSERT-Abfragen aufgefallen:<\/p>\n
\n
\nMariaDB [myforum]> insert into tag set tagtext='abc12345',dateline=unix_timestamp(),canonicaltagid=1234;\nQuery OK, 1 row affected (0.24 sec)\n<\/pre>\n<\/div>\n
Die Ausf\u00fchrungszeit variierte zwischen 250 und 300ms. In Anbetracht der simplen Abfrage ein sehr hoher Wert. Das Netzwerk bzw. die DNS-Aufl\u00f6sung war nicht verantwortlich, wie zun\u00e4chst vermutet: Im MariaDB-Image ist das Aufl\u00f6sen 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 \u00e4hnlich langsam. <\/p>\n
InnoDB Flush<\/h2>\n
Dieses Verhalten konnte ebenfalls bei anderen Tabellen beobachtet werden, die InnoDB als Datenbankengine einsetzen. Ein Blick auf die Serverkonfiguration zeigt:<\/p>\n
\n
\nMariaDB [myforum]> show variables like '%innodb_flush%';\n+--------------------------------+----------+\n| Variable_name | Value |\n+--------------------------------+----------+\n| innodb_flush_log_at_timeout | 1 |\n| innodb_flush_log_at_trx_commit | 1 |\n| innodb_flush_method | O_DIRECT |\n| innodb_flush_neighbors | 1 |\n| innodb_flush_sync | ON |\n| innodb_flushing_avg_loops | 30 |\n+--------------------------------+----------+\n<\/pre>\n<\/div>\n
Auff\u00e4llig war innodb_flush_log_at_trx_commit<\/strong>, der auf 1 gesetzt ist. Zu diesem findet sich folgende Erkl\u00e4rung: <\/p>\n\n
\n
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\u00dferhalb der Transaktion) das Protokoll auf der Festplatte l\u00f6schen 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\u00fcrden. Der Wert 0 ist etwas schneller, aber etwas weniger sicher, da Sie Transaktionen verlieren k\u00f6nnen, auch wenn der MySQL-Server abst\u00fcrzt. Wert 2 verursacht nur Datenverlust bei vollst\u00e4ndigem Betriebssystemabsturz.<\/p>\n
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. <\/p>\n
Zur L\u00f6sung habe ich mich entschieden, innodb_flush_log_at_trx_commit<\/strong> auf 2 zu setzen:<\/p>\n
\n
\nset global innodb_flush_log_at_trx_commit = 2;\n<\/pre>\n<\/div>\n
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\u00e4ger geschrieben. <\/p>\n
Im Falle eines Crashes eine bis maximal zwei Sekunden an Transaktionen zu verlieren, d\u00fcrfte im Gegensatz zum Performance-Gewinn in den meisten Anwendungsf\u00e4llen akzeptabel sein. Nat\u00fcrlich sollte man dies immer im Einzelfall abw\u00e4gen. <\/p>\n","protected":false},"excerpt":{"rendered":"
In einer vBulletin 4 Testinstallation f\u00fcr das neue U-Labs 2.0 sind mir sehr langsame SQL INSERT-Abfragen aufgefallen: Die Ausf\u00fchrungszeit variierte zwischen 250 und 300ms. In Anbetracht der simplen Abfrage ein sehr hoher Wert. Das Netzwerk bzw. die DNS-Aufl\u00f6sung war nicht verantwortlich, wie zun\u00e4chst vermutet: Im MariaDB-Image ist das Aufl\u00f6sen der DNS-Namen bereits abgeschaltet. Und auch …<\/p>\n","protected":false},"author":5,"featured_media":6345,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,78],"tags":[497,339],"class_list":["post-6341","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-server","category-software","tag-docker","tag-mysql"],"_links":{"self":[{"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/posts\/6341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/comments?post=6341"}],"version-history":[{"count":7,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/posts\/6341\/revisions"}],"predecessor-version":[{"id":6349,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/posts\/6341\/revisions\/6349"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/media\/6345"}],"wp:attachment":[{"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/media?parent=6341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/categories?post=6341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/u-labs.de\/portal\/wp-json\/wp\/v2\/tags?post=6341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}