Ich muss hier mal ein Thema beschreiben, welches uns schon ein ganze Weile beschäftigt und wir bis heute keine effektive Lösung haben.
Warum geht es?
Die SW-Datenbank bei einem Kunden hat gegenwärtig 3 GB. Den meisten Speicherplatz der DB belegt übrigens der Magnalister. Der Kunde selber hat einen komplett eigenen Root-Server bei Hetzner (Server EX62-NVMe). Jeden Tag werden automatische (Plsek, Domain etc…) Backups erstellt. Soweit geht das auch alles, bis auf dem MySQL-Dienst.
Das Problem
Bei diesem Backups wird natürlich auch ein MySQL-Dump erstellt. Sobald ein Dump erstellt wird, bremst dieser Prozess den MySQL-Dienst dermaßen aus, so unter Umständen sich der MySQL-Dienst aufhängt und nicht wieder beruhigt. Im Zusammenhang mit Magnalister (seine Cronjobs etc…) gehen die MySQL-Prozesse gern mal auf 150 hoch. Bedeutet, der Shop ist nicht mehr aufrufbar weil die DB nicht mehr erreichbar ist. Parameter „max_connections“ aktuell bei 151.
Alle bisherige Bemühungen (mehr Buffer, mehr Speicher, mehr Prozesse) haben nichts gebracht um dieses Problem zu lösen. Und so langsam gehen uns die Ideen aus, um das oben erwähnte Problem zu verhindern.
Wer hat ähnliche Erfahrungen gemacht und könnte Hinweise geben?
prüft die Struktur der Datenbank mit dem Query Analyzer MySQL :: MySQL Query Analyzer - oftmals liefern schon kleine Änderungen wie ein zusätzlicher Index dramatisch bessere Performance
wenn alles nichts hilft, würde ich ggf. mal mit Alternativen testen. MariaDB ist in vielen typischen Szenarien einen ganzen Tick schneller als MySQL, vgl. Benchmark: MariaDB vs MySQL on Commodity Cloud Hardware | MariaDB - mittlerweile gibt es ja auch von MariaDB einen Enterprise-Support, daher kann das durchaus eine Option sein.
Das habe ich schon gelesen, nur nutzt mir das nichts. Denn die automatischen Backups werden vom Plesk gemacht gemacht. Da habe ich (nach bisherigen Stand) keine Einfluss auf die Parameter.
Ja zum Einsatz kommt tatsächlich MySQL 5.7. Wäre eine Überlegung wert.
Du suchst nach Lösungen, aber möchtest an den Dump nicht die Hand anlegen? Du bekommst Ansätze genannt und es kommt kein „danke“? Dann erwägst du noch großzügig über den letzten Vorschlag nachzudenken. Man, man, man…
Hm, was meinst du? Wie kann ich denn den Dump, den Plesk erstellt, manuell in die Hand nehmen? Also ich rede hier von einem Plesk-Backup, kein manuelles. Wo kann ich das anpassen? Schreibe es mir gern.
Vielleicht sollte ich noch eine Grafik mit dran hängen, damit es hier nicht zu Mißverständnissen kommt. Es geht um diese Backups. Da ist mir leider keine Funktion bekannt, in der man auf die Parameter der Dump-Funktion Einfluss nehmen kann. Zusätzlich werden diese Backups auf einen extra Backup-Server gespeichert.
Mit Plesk geht das vermutlich nicht, die schreiben ja selbst in ihrer Doku:
If you need to create a dump in another format or to set custom settings for a dump, use the native functionality of database management tools.
Was spricht denn dagegen, das z.B. über SSH oder einen cron job selbst zu übernehmen?
Ansonsten kannst Du erstmal nur versuchen, die Tabellen zu optimieren (und - abhängig von Engine und Einstellungen - auch unbenötigten Platz wieder freizugeben). Schau Dir mal https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html an.
Nun ja, dagegen spricht erst mal nichts. Nur sehr bedauerlich weil es ja schon vom Server mit einer Funktionalität gibt, worauf sich übrigens jeder Kunde (der einen großen Server bucht) verlässt. Was soll man dem Kunden erklären? „Du gibst im Monat schon viel Geld für neuen Server aus, aber das mit dem Backups geht leider nicht. Bitte gibt noch mal Geld aus, damit wir eine manuelle Lösung erstellen”.
Klar werden wir eine extra Lösung nachdenken müssen. Danke erst einmal für die vielen Ideen!
Ein Kunde der 3 GB an Daten produziert, sollte dafür Verständnis haben, dass der Betrieb eines solchen Shops kein Sandkasten ist. Oder man muss es ihm klar machen.
Guck mal ob s_order_basket und s_order_basket_attributes normal sind, oder ob der sich da vollschreibt.
Guck mal Wartungsmodus aktivieren und abwarten bis alle Mysql-Prozesse beendet sind und dann den dump machen, ob es dann besser geht.
gibt es irgendwo hohe „id“ Werte a la s_search_keywords. Je höher umso schlechter die Performance (entweder mein Aberglaube oder tatsache, bin mir da noch nicht sicher), bishin zu überlaufenen Werte-bereichen (siehe Auto Increment ID von Suchindex resetten ) ich lese gerade dass Phil schon sowas in der Art vorschlägt Query Analyzer
Wäre meine persönlich Meinung jetzt auch gewesen, auch wenn vergleichbare andere Kunden weniger Platz verbrauchen. Viel Platz wird (wie Eingangs schon erwähnt) durch den Magnalister verbraten. Da kommt schon etwas zusammen. Ansonsten sieht alles ganz normal aus.
Die Backups, die durch Plesk durchgeführt werden, werden nach erfolgreichen Abschluss auf einem extra Backup-Server geladen. Aber das ist alles klein Problem. Unsere Analysen zeigen deutlich, dass der Dump-Prozess das eigentliche Problem darstellt. Bedingt durch den Magnalister laufen etliche Cronjobs (müssen zeitnah auch laufen). Die laufen natürlich auch in der Nacht. Und da läuft auch das Plesk-Backup - irgendwann muss es ja mal laufen.
Im ungünstigen Falle (Gott sei Dank nicht täglich) schaukeln sich während des Dumps die MySQL-Prozesse hoch. Die Laufzeiten der einzelnen Querys gehen in die Höhe und wenn alles schief läuft kackt dann an diesem Punkt der MySQL-Dienst ab - somit ist der Shop nicht mehr erreichbar. Und dann wird man früh um 7 Uhr aus dem Bett geklingelt.
Es ist genug um daraus eine Herausforderung zu machen. Und die Lösungen und Vorschläge laufen, wie ich eingangs schrieb, auf ein Abspecken und Handarbeit hin.
Ich denke da liegt der Hund begraben. Ohne —single-transaction Parameter lockt der dump jeweils die komplette Tabelle und kollidiert so mit den cronjobs von Magnalister, vermutlich treten dann gehäuft lock wait timeouts auf (vgl. https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handling.html). Auch bei Verwendung von —single-transaction solltest Du zusätzlich versuchen, das zeitlich so zu entzerren, dass die möglichst nicht parallel laufen, denn sonst verlagerst Du das Problem nur auf das transaction log. Ggf. auch mal mit dem Support von Magnalister sprechen, die sollten eigentlich den best practice für Backups benennen können…
Aktuell läuft das Plesk-Backup ohne Probleme durch - haben noch einmal die Zeit umgestellt. Wir werden das die nächsten Tage und Wochen natürlich beobachten. Sollte das alles nicht funktionieren, haben wir schon Pläne geschmiedet das Backup für diese spezielle Domain bzw. deren DB eine eigene Anwendung zu schreiben. Hetzner bietet hierzu wohl auch Lösungen an.
