Problem, dass der Server ständig ausfiel

Wir hatten in den letzten Wochen vermehrt mit Serverausfällen bei einem unserer Onlineshops zu tun.
Als der Server ausgefallen ist, haben wir normalerweise den Hoster kontaktiert und den Server neu starten lassen.

Da es zum Einen außerhalb der „Kernarbeitszeiten“ 9-18 Uhr oftmals nur bedingt Support gibt und zum Anderen es nicht unser Anspruch ist, nicht zu wissen, warum dies durchgehend passiert, war dies auf Dauer keine Lösung.

Somit haben wir uns die MySQL Prozess genauerer unter die Lupe genommen.
Wer diese nicht kennt, man kann diese z.B. unter phpmyadmin einsehen:

Wenn noch keine Datenbank ausgewählt ist, gibt es den Reiter „Status“ und dort ist eine Liste mit den Prozessen (nur, wenn der eingeloggte User Datenbank Root Rechte hat).

Dort werden in Realtime alle MySQL Abfragen und sonstige Datenbanklastige Variablen angezeigt.

In unserem Fall war es so, dass es eine viel zu lange Prozessliste war.

Was bedeutet das nun genau?

Dies bedeutet, dass es viele Prozesse gibt, die Datenbankabfragen erzeugen.
Das ist an sich nicht weiter schlimm und bei genügend Traffic auch ganz normal.
Was allerdings schlimm ist, sind MySQL Prozesse in dieser Liste, die nicht wieder beendet werden. Dies war auch in unserem Shop so.
Und je mehr nicht beendete MySQL Prozesse sich anstaunten, desto höher die Rechenlast und es ist nur eine Frage der Zeit bis der Server wieder neu gestartet werden muss, weil dieser mit den ganzen nicht beendeten Server bzw. Datenbankanfragen weiter arbeiten kann.

In den Prozessen gibt es eine Tabelle mit den Prozessvariablen.
So gibt es z.B. die ID, die sich immer erhöht. Der Datenbanknutzer, der die Abfrage erstellt, der Host und die Datenbank – sollte selbsterklärend sein.

Kommen wir zu den wichtigen Spalten:
Befehl: Der auszuführende MySQL Befehl. Die am häufigsten auftretenden Commands sind „Query“ und „Sleep“. Query = führt das Statement aus, Sleep = warten auf neuen Statement
Zeit: die laufende Zeit, die der Prozess bereits benötigt.
Status: „An action, event, or state that indicates what the thread is doing. Descriptions for State values can be found at Section 9.14, “Examining Thread Information”. Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.“ – Zitat https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

Nun habe ich in der MySQL Dokumentation nachgeschaut, ob man auf diese Variablen Zugriff hat.
Und in der Tat kann man diese per PHP nutzen.

Mein Ansatz war nun, dass ich einen Cronjob erstelle, der alle Prozesse „killt“, die den Befehl „Sleep“ haben oder dessen Zeit größer als 30 Sekunden ist.
Zudem wird in eine log Datei geschrieben, was gemacht wurde.

So sieht z.B. eine Zeile in der Logdatei aus:

{"success":true,"killed_ids":[],"killed_processes":[],"time":"2017-01-23 19:01:18","validation_errors":[],"system_errors":[]}
{"success":true,"killed_ids":["449915"],"killed_processes":[{"Id":"449915","User":"wmdk","Host":"192.168.0.1:38400","db":"db_cleaner","Command":"Sleep","Time":"65","State":"","Info":null}],"time":"2017-01-25 00:53:02","validation_errors":[],"system_errors":[]}

Wenn der Cronjob ausgeführt wurde, wird success: true geschrieben, auch wenn keine Prozesse beendet wurden. Das ist nur für das Nachvollziehen, das der Cronjob lief, da. Der nächste Parameter ist ob bzw. welche ids beendet wurden („killed_ids“). Danach kommt die damit zusammenhängenden „killed_processes“. Dort wird die „Id“, der Datenbankuser „User“, der Datenbankhost „Host“, der Datenbanknamen „db“, der Befehl den dieser Prozess gerade ausführt „Command“, die Zeit, die dieser eine Prozess bereits am Laufen ist, „Time“ und der Zustand „State“ eingefügt. Zuletzt wird noch der „timestamp“ als Datum + Zeit formatiert zum Zeitpunkt des Cronjobs, die „validations_errors“ oder „system_errors“ mit in das Log geschrieben.

In dem obigen Beispiel sieht man, dass nur die success Meldung sowie die time geschrieben werden, wenn keine Prozesse beendet wurden.

Somit hat man in dem Log alle Daten, die man braucht, um nachzuvollziehen, was die Datenbankprozesse gerade gemacht haben. Zur Analyse von oftmals nicht beendeten Prozessen ist das sehr geeignet.

Vorteil:

  • einfaches, übersichtliches Skript,
  • alle nicht beendeten MySQL Prozesse werden schnell bereinigt,
  • die Datenbank hat somit mehr Rechenpower für die neuen Anfragen

Nachteil:

  • Cronjobs, die z.B. nachts laufen und länger als 30 Sekunden dauern, werden durch diesen gerade beschriebenen Cronjob abgebrochen.
  • Wenn die Zeit jedoch höher gestellt wird (als die 30 Sekunden), steigt die Chance, dass sich zu viele Prozesse wieder anstauen und der Server wieder „in die Knie geht“.
  • Dies ist auch nur eine temporäre Lösung – warum sich die MySQL Prozesse überhaupt anstauen, das sollte die Frage sein.

Der Code zum herunterladen befindet sich weiter unten unter „Downloads“. Der Code an sich sollte selbsterklärend sein.

Bei Fragen können Sie mich gerne unter christian.braukmann@kussin.de erreichen.