Verständnis der MySQL-CPU-Auslastung: Tools und Techniken

Was ist die CPU-Auslastung in MySQL?

Die CPU-Nutzung in MySQL bezieht sich auf den Anteil der Prozessorressourcen eines Computers, den der MySQL-Server für die Ausführung von Vorgängen verwendet, einschließlich der Verarbeitung von Abfragen, der Verwaltung von Datenbanktransaktionen und der Wartung des Datenbanksystems. Im Wesentlichen misst es, wie viel der Rechenkapazität der CPU zu einem bestimmten Zeitpunkt für die Ausführung von MySQL-Aufgaben reserviert ist.

Wenn die MySQL-CPU-Auslastung sehr hoch ist, kann dies darauf hindeuten, dass Ihre Datenbank aktiv eine große Anzahl von Anfragen oder komplexe Abfragen verarbeitet. Andererseits könnte eine anhaltend niedrige CPU-Auslastung darauf hindeuten, dass Sie die Ressourcen nicht ausreichend nutzen. Dies kann bedeuten, dass es an anderer Stelle im System Ineffizienzen oder Engpässe gibt.

So überprüfen Sie die MySQL-CPU-Auslastung

Mit dem oberen Befehl

Der Befehl top ist ein generisches Tool, das in Linux und anderen Unix-ähnlichen Betriebssystemen (wie CentOS) verfügbar ist. Es bietet eine dynamische Echtzeitansicht des aktuellen Status Ihres Systems, einschließlich der CPU-Auslastung aller Prozesse.

1. Öffnen Sie Ihr Terminal.
2. Führen Sie den Befehl top aus.

Suchen Sie in der Liste nach dem Prozess MySQL. In der Spalte %CPU wird die aktuelle CPU-Auslastung Ihrer MySQL-Instanz angezeigt.

Verwenden des Befehls SHOW STATUS

Der MySQL-Befehl SHOW STATUS ist ein leistungsstarkes Tool zum Abrufen einer Vielzahl von Serverstatusinformationen. Obwohl die CPU-Auslastung nicht direkt angezeigt wird, können Sie anhand verschiedener Zähler und Statusanzeigen auf die CPU-Auslastung schließen.
Greifen Sie über die CLI oder einen MySQL-Client auf Ihren MySQL-Server zu.
Führen Sie den Befehl SHOW STATUS: like 'Threads_%' aus.

Aus der Liste der Serverstatus und -variablen sind threads_running und threads_connected die beiden wichtigsten Indikatoren zur Messung der CPU-Auslastung:

• Threads_running – Zeigt die Anzahl der Threads an, die nicht schlafen. Hohe Zahlen können hier auf eine hohe CPU-Auslastung hinweisen.
• Threads_connected – Dies zeigt die Gesamtzahl der offenen Verbindungen zum MySQL-Server. Wenn diese Zahl deutlich höher ist als threads_running, kann dies darauf hindeuten, dass viele Verbindungen inaktiv sind oder auf verfügbare CPU-Ressourcen warten, um ihre Anforderungen zu verarbeiten.

Verwenden des Befehls SHOW PROCESSLIST

Der Befehl SHOW PROCESSLIST liefert einen Echtzeit-Snapshot aller aktiven Threads auf Ihrem MySQL-Server.

1. Greifen Sie über die CLI oder über einen MySQL-Client auf Ihren MySQL-Server zu.
2. Führen Sie SHOW PROCESSLIST aus; Befehl.

Die Ausgabe enthält mehrere Datenspalten für jeden Thread. Achten Sie besonders auf Folgendes:

• Befehlsspalte – Gibt die Art der Operation an, die erstellt wird. Hier können lang laufende Abfragen, insbesondere CPU-intensive Abfragen, identifiziert werden.
• Zeitspalte – Zeigt an, wie lange (in Sekunden) der Befehl ausgeführt wurde. Threads mit einem hohen Wert in der Spalte „Zeit“ können zur CPU-Auslastung beitragen.

Verwenden des Leistungsschemas

Für eine tiefergehende Analyse kann das Performance Schema von MySQL verwendet werden. Es bietet detaillierte Informationen zu Serverereignissen und kann abgefragt werden, um die CPU-Auslastung pro Thread oder pro Abfrage zu analysieren.

So aktivieren Sie das Leistungsschema (falls nicht bereits aktiviert):

1. Suchen Sie Ihre MySQL-Konfigurationsdatei mit dem Namen my.cnf oder my.ini.
2. Fügen Sie den folgenden Eintrag hinzu oder ändern Sie ihn: performance_schema=ON.
3. Starten Sie Ihren MySQL-Server neu, um diese Änderung zu übernehmen.

Führen Sie eine Abfrage aus, die die Tabelle performance_schema.threads mit der Tabelle performance_schema.events_statements_summary_by_thread_by_event_name verknüpft. Diese Abfrage bietet detaillierte Einblicke in die CPU-Auslastung, die mit verschiedenen Threads und Abfragen verbunden ist, die vom Server ausgeführt werden.

Nutzen Sie externe Überwachungstools

Eine Reihe externer Überwachungslösungen, darunter Percona Monitoring and Management (PMM) und Releem, bieten eine detaillierte Analyse der Leistungsmetriken von MySQL, wie z. B. der CPU-Auslastung.

Diese Tools bieten über eine intuitive Benutzeroberfläche einen umfassenden Überblick über die CPU und die zugehörigen Kennzahlen und erleichtern so das Erkennen von Trends und Problemen erheblich.

Diagnose einer hohen CPU-Auslastung in MySQL

Eine hohe CPU-Auslastung in MySQL-Datenbanken wirkt sich auf die Leistung und Zuverlässigkeit Ihrer abhängigen Anwendungen aus. Um dieses Problem effektiv zu diagnostizieren, ist es wichtig, die Betriebsmechanismen von MySQL, die Abfrageausführungsprozesse und die breitere Systemumgebung gründlich zu untersuchen.

1. Ineffiziente Abfragen

Eine der häufigsten Ursachen für eine hohe CPU-Auslastung sind ineffiziente oder komplexe SQL-Abfragen. Abfragen ohne ordnungsgemäße Indizierung oder vollständige Tabellenscans können dazu führen, dass die Datenbank-Engine übermäßig viele CPU-Zyklen verbraucht. Diese Ineffizienz entsteht, weil die Datenbank mehr Daten als nötig lesen, jede Zeile einzeln verarbeiten und komplexe Berechnungen ohne die Hilfe von Indizes durchführen muss.

• Suboptimale Indexnutzung – Ohne Indizes muss MySQL vollständige Tabellenscans durchführen, die sehr CPU-intensiv sind. Indizes sollten strategisch basierend auf Abfragemustern und häufig aufgerufenen Spalten erstellt werden.
• Komplexe Joins – Abfragen mit mehreren Joins, insbesondere bei großen Tabellen oder Tabellen ohne entsprechende Indizes, können zu einer hohen CPU-Auslastung führen. Jeder Join-Vorgang kann die verarbeitete Datenmenge exponentiell erhöhen.
• Aggregationsfunktionen – SUM(), COUNT(), AVG()) über große Datensätze, ohne ordnungsgemäße Indizierung zwingt MySQL dazu, große Datenmengen zu scannen und zu verarbeiten.

Sie können das Protokoll für langsame Abfragen konfigurieren (aktivieren und dann Ihren bevorzugten Schwellenwert für lange Abfragen festlegen) oder Releem Query Analytics verwenden, um zu ermitteln, welche spezifischen Abfragen zu lange brauchen, um die Ausführung abzuschließen. Dies sind die Fragen, die Ihre Aufmerksamkeit erfordern.

2. Hohe Parallelität

MySQL ist für die gleichzeitige Verarbeitung mehrerer Verbindungen und Transaktionen konzipiert. Mit zunehmender Parallelität steigt jedoch auch die Komplexität der Verwaltung dieser gleichzeitigen Verbindungen. Ein separater Thread verwaltet jede aktive Verbindung. Die Verwaltung einer großen Anzahl von Threads erfordert mehr CPU-Ressourcen, nicht nur für die Ausführung von Abfragen, sondern auch für den Overhead des Kontextwechsels zwischen Threads.

3. Sperren Sie den Konflikt

Sperrenkonflikte treten auf, wenn mehrere Transaktionen gleichzeitig versuchen, auf dieselben Daten zuzugreifen. Dies führt dazu, dass sie darauf warten müssen, dass die anderen Transaktionen die Sperren freigeben, bevor sie fortfahren können. Wenn Transaktionen um Sperren für Zeilen oder Tabellen konkurrieren, verbringt die Datenbank-Engine zusätzliche CPU-Zyklen mit der Verwaltung dieser Sperren.

Sie können feststellen, ob ein Sperrenkonflikt auftritt, indem Sie sich die Tabelle information_schema.innodb_lock_waits ansehen. Verwenden Sie die folgende Abfrage, um Transaktionen zu identifizieren, die gesperrt sind:

SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

Lang laufende Transaktionen können auch Einblicke in den Grad der Konflikte in Ihrer Datenbank geben. Verwenden Sie den Befehl SHOW ENGINE INNODB STATUS, um eine Liste aller offenen Transaktionen von der neuesten zur ältesten anzuzeigen. Untersuchen Sie die ältesten Transaktionen, um eine Vorstellung davon zu bekommen, wie lange sie laufen, welche Tabellen und Zeilen beteiligt sind und welche Anweisungen beteiligt sind.

4. Schlecht konfigurierter MySQL-Server

Die Leistung von MySQL hängt stark von seiner Konfiguration ab. Parameter, die nicht für Ihre Arbeitslast oder Hardware optimiert sind, können zu einer ineffizienten CPU-Auslastung führen. Zu den wichtigsten Parametern gehören:

• Unangemessene Pufferpoolgröße – Der InnoDB-Pufferpool reduziert den Festplatten-I/O, indem er Daten und Indizes im Speicher zwischenspeichert. Ein Pufferpool mit falscher Größe kann zu häufigen Festplattenlesevorgängen führen. Da das System Festplatten-E/A-Vorgänge verarbeitet, erhöht sich die MySQL-CPU-Auslastung.
• Thread-Cache-Größe – Wenn der Thread-Cache zu klein ist, verbringt MySQL möglicherweise zusätzliche CPU-Zyklen damit, Threads für jede neue Verbindung zu erstellen und zu zerstören, anstatt vorhandene Threads wiederzuverwenden.

5. Probleme beim Schemadesign

Das physische Design der Datenbank, einschließlich Tabellenstrukturen und Datentypen, kann sich auch auf die CPU-Effizienz auswirken. Wenn Sie größere Datentypen als nötig verwenden (wie BIGINT über INT), verarbeitet MySQL mehr Daten als nötig und verbraucht mehr CPU-Ressourcen.

Normalisierung ist eine Datenbankentwurfstechnik, mit der Tabellen so organisiert werden, dass Datenredundanz reduziert und die Datenintegrität verbessert wird. Der Prozess umfasst das Aufteilen einer Datenbank in zwei oder mehr Tabellen und das Definieren von Beziehungen zwischen den Tabellen. Während eine Übernormalisierung zu komplexen Verknüpfungen führen kann, kann eine Unternormalisierung zu redundanter Datenverarbeitung und größeren Scans führen, was beides die CPU-Auslastung erhöht.

11 Tipps zur Reduzierung der hohen MySQL-CPU-Auslastung

1. Die Ressourcenkontrollfunktionen von MySQL können dazu beitragen, zu verhindern, dass ein einzelner Benutzer zu viel CPU-Zeit verbraucht, und sorgen so für eine gleichmäßige Auslastung aller Anwendungen. Percona hat den Artikel dazu veröffentlicht.
2. Analysieren Sie Abfragemuster und erstellen Sie Indizes für Spalten, die häufig in WHERE-Klauseln, JOIN-Bedingungen und als Teil von ORDER BY- und GROUP BY-Anweisungen verwendet werden. Seien Sie vorsichtig bei einer Überindizierung, da dies zu einem erhöhten Overhead bei Schreibvorgängen führen kann.
3. Dieser Befehl zeigt Ihnen, wie die Datenbank eine bestimmte Abfrage ausführt, einschließlich der verwendeten Indizes. Der Ausführungsplan kann aufdecken, ob eine Abfrage zu einem vollständigen Tabellenscan führt oder ob eine Abfrage einen Index ineffizient oder überhaupt nicht verwendet. Diese Ergebnisse würden als Ziel für die Abfrageoptimierung dienen.
4. Reduzieren Sie die Komplexität von Verknüpfungen, insbesondere bei Verknüpfungen mit mehreren Tabellen oder großen Datensätzen. Alle verknüpften Tabellen sollten über entsprechende Indizes für die Verknüpfungsspalten verfügen. Erwägen Sie die Denormalisierung Ihres Schemas, wenn bestimmte Verknüpfungen die CPU-Auslastung stark beeinträchtigen.
5. Teilen Sie komplexe Abfragen in einfachere auf, um vollständige Tabellenscans zu vermeiden und die Nutzung temporärer Tabellen zu reduzieren, insbesondere solche, die auf der Festplatte erstellt wurden. Gehen Sie mit Unterabfragen und abgeleiteten Tabellen vorsichtig um.
6. Passen Sie die Größe des InnoDB-Pufferpools so an, dass möglichst viel vom Arbeitsdatensatz in den Speicher passt, und reduzieren Sie so die Festplatten-E/A und die CPU-Auslastung für Festplattenlesevorgänge. Die Pufferpoolgröße sollte idealerweise auf etwa 70–80 % des verfügbaren Systemspeichers eingestellt werden.
7. Der Standardwert für die Thread-Cache-Größe könnte für Systeme mit hoher Auslastung oder mit häufigem Verbindungswechsel zu niedrig sein. Konfigurieren Sie die Thread-Cache-Größe, um den Aufwand für das Erstellen und Zerstören von Threads für Verbindungen zu reduzieren. Ein Ausgangspunkt kann darin bestehen, die Thread-Cache-Größe auf die Anzahl gleichzeitiger Verbindungen festzulegen, die Ihr System normalerweise zu Spitzenzeiten verarbeitet.
8. Implementieren Sie Verbindungspooling in der Anwendungsschicht, um den Overhead durch häufige Verbindungen und Trennungen zur Datenbank zu reduzieren. Durch das Verbindungspooling kann eine Reihe von Verbindungen zwischen mehreren Clients wiederverwendet werden, wodurch die mit der Verbindungsverwaltung verbundene CPU-Last verringert wird.
9. MySQL sollte auf einem Server mit dedizierten Ressourcen laufen. Das Teilen von Ressourcen mit anderen CPU-intensiven Anwendungen kann zu Konflikten und hoher CPU-Auslastung führen.
10. Verwenden Sie Hardware, die Ihren Workload-Anforderungen entspricht. In CPU-gebundenen Szenarien kann die Wahl von Prozessoren mit höherer Kernanzahl und schnelleren Taktraten die Leistung verbessern. Ziehen Sie SSDs anstelle von Festplatten als Speicher in Betracht, um die mit Festplatten-E/A-Vorgängen verbundene CPU-Last zu reduzieren.
11. Das Leistungsschema von MySQL und das langsame Abfrageprotokoll können verwendet werden, um Datenbankvorgänge zu überwachen und Muster mit hoher CPU-Auslastung zu identifizieren. Diese Tools können dabei helfen, ineffiziente Abfragen und suboptimale Konfigurationen zu erkennen, die zu einer hohen CPU-Auslastung beitragen.

Releems Fähigkeiten zur MySQL-CPU-Optimierung

Releem bietet eine Reihe von Funktionen, mit denen Sie Ihre CPU-Auslastung überwachen und verwalten können:

• Intuitive Oberfläche: Die benutzerfreundliche Oberfläche stellt CPU-Auslastungstrends im Laufe der Zeit in einem visuell zugänglichen Format dar. Es vereinfacht den Optimierungsprozess und ermöglicht es Ihnen, fundierte Entscheidungen einfacher zu treffen. Es ist nicht erforderlich, die CPU-Auslastung manuell aus anderen Variablen zu interpretieren oder die CLI zu verwenden.
• Überwachung: Releem überwacht Ihren Server und erfasst wichtige Parameter und Schlüsselmetriken wie CPU-Auslastung, Größe des InnoDB-Pufferpools, Thread-Cache-Größe und mehr. Diese Echtzeit-Datenerfassung bietet unschätzbare Einblicke in die Leistungsdynamik Ihres Servers. Automatische Leistungsanalyse Durch die Auswertung der während der Überwachung gesammelten Zahlen werden Bereiche ermittelt, in denen die CPU-Effizienz verbessert werden kann.
• Konfigurationsempfehlungen: Releem schlägt dann einfach anzuwendende Konfigurationsempfehlungen vor, um Ihre MySQL-CPU-Auslastung zu optimieren. Diese Empfehlungen sind auf die spezifischen Eigenschaften und Arbeitslastmuster Ihres Servers zugeschnitten.
• Abfrageanalyse: Untersucht das langsame Abfrageprotokoll, um Abfragen zu identifizieren und zu analysieren, die möglicherweise CPU-Ressourcen beanspruchen. Mit diesem Wissen können Sie Abfragen optimieren, die richtige Indizierung anwenden und andere Korrekturmaßnahmen ergreifen.

Wenn Sie diese Funktionen ausprobieren möchten, können Sie sich kostenlos anmelden, um loszulegen!

Das obige ist der detaillierte Inhalt vonVerständnis der MySQL-CPU-Auslastung: Tools und Techniken. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!