Was könnte eine hohe CPU-Auslastung des MySQL-Dienstes eines Ubuntu LEMP-Stacks verursachen und was ist als erstes zu tun?

1. Unzureichende MySQL-Konfiguration

  • Ungeeignete Puffergrößen: Zum Beispiel kleine query_cache_size, innodb_buffer_pool_size, oder key_buffer_size.

  • Tabellensperren: Streit um Tabellensperren aufgrund von hohen Schreibvorgängen oder nicht optimierten Operationen.

  • Thread-Management: Eine falsch konfigurierte thread_cache_size kann zu Thread-Engpässen führen.

2. Fehlendes Query Caching

  • Wenn das Abfrage-Caching deaktiviert oder zu klein ist, werden immer wiederkehrende Abfragen an die Datenbank gestellt, was die CPU-Last erhöht.

3. Hohe Lese-/Schreib-Arbeitslast

  • Eine hohe Anzahl von Lesevorgängen (selbst bei einer leseintensiven Einrichtung wie der Ihren) oder Schreibvorgängen, die die Caching-Schichten umgehen, können die Belastung erhöhen.

4. Festplatten-E/A-Engpässe

  • Langsame Festplatten können zu einer hohen CPU-Belastung führen, da MySQL auf Plattenoperationen wartet, insbesondere bei großen temporären Tabellen oder `SELECT'-Operationen ohne geeignete Indizes.

5. Verbindungspool-Probleme

  • Übermäßige oder ineffiziente Handhabung von Verbindungen, wie z.B. das wiederholte Öffnen und Schließen von Verbindungen anstelle der Verwendung eines Verbindungspools.

6. Hintergrundoperationen

  • Aufgaben wie die Replikation, das Schreiben von Binärprotokollen oder Backup-Vorgänge können die CPU stark beanspruchen.

7. Beschädigte Tabellen oder Indizes

  • Beschädigte Tabellen oder Indizes können zu wiederholten Fehlversuchen bei der Ausführung von Abfragen führen, was einen hohen Ressourcenverbrauch verursacht.

8. Unzureichende Server-Ressourcen

  • Überlastete CPU durch konkurrierende Prozesse auf demselben Server oder unterversorgte Hardware.

9. Externe Angriffe oder Missbrauch

  • Brute-Force- oder SQL-Injection-Angriffe: Externer Missbrauch, der direkt auf Ihre Datenbank abzielt.

  • Bots oder Crawler: Übermäßige Anfragen an die Website führen zu hohen MySQL-Abfragen.

Schritte zur Diagnose

  • Überprüfen Sie langsame Abfragen: Verwenden Sie das slow_query_log, um problematische Abfragen zu identifizieren.

  • MySQL-Leistung überwachen: Verwenden Sie Tools wie mysqladmin, MySQL Workbench oder Performance Schema.

  • Servermetriken untersuchen: Verwenden Sie htop, top oder iotop, um Engpässe zu identifizieren.

  • Aktivieren Sie Query Profiling: Führen Sie EXPLAIN für Ihre Abfragen aus, um die Ausführungspläne zu analysieren.

Lösungen

  • Optimieren Sie Abfragen und Indizes.

  • Stimmen Sie die MySQL-Konfiguration mit einem Skript wie mysqltuner.pl ab.

  • Verlagern Sie leseschwere Arbeitslasten auf eine Caching-Schicht (z.B. Redis, Memcached).

  • Erhöhen Sie bei Bedarf die Hardware-Ressourcen (CPU, RAM).

  • Verwenden Sie Verbindungspooling für PHP (z.B. Persistent connections in PDO oder einen Pool wie ProxySQL).

innodb_buffer_pool_size - Verwendung

innodb_buffer_pool_size kann die wichtigste Variable für jede MySQL Datenbank sein, die InnoDB Tabellen verwendet. Die Konfiguration von innodb_buffer_pool_size wird dringend empfohlen, um übermäßige I/O-Nutzung zu reduzieren. Die I/O-Nutzung ist der primäre Engpass für MySQL-Server, da sie langsam ist und eine hohe Nutzung darauf hinweist, dass die Plattensubsysteme überlastet sind. Dies kann die Leistung in Spitzenzeiten und bei Wartungsarbeiten erheblich beeinträchtigen.

Der InnoDB-Pufferpool arbeitet mit dem Caching der Daten, auf die zuletzt zugegriffen wurde. Durch die Zwischenspeicherung im Arbeitsspeicher können Daten aus dem Cache statt von der Festplatte abgerufen werden. Der Pufferpool ist so konzipiert, dass die am häufigsten verwendeten Daten im Cache verfügbar bleiben, indem zwei Teillisten verwaltet werden. Wenn auf neue Daten zugegriffen wird, werden diese oben in der Liste „Älteste“ gespeichert. Das älteste Element in dieser Liste wird entfernt und muss bei einer erneuten Abfrage von der Festplatte geholt werden. Bei einem erneuten Zugriff werden diese Daten an den Anfang der Liste „Neueste“ verschoben.

Wenn der Pufferpool richtig eingestellt ist, können Daten, auf die häufig zugegriffen wird, bei Bedarf schnell aus dem Speicher geholt werden.

Prinzipiell sollte der innodb_buffer_pool_size auf die Größe der Datenbank gesetzt werden.