Problembehandlung bei hoher CPU-Auslastung in Azure Database for MySQL - Flexibler Server
Azure Database for MySQL – Flexibler Server bietet eine Reihe von Metriken, mit denen Sie Ressourcenengpässe und Leistungsprobleme auf dem Server identifizieren können. Um festzustellen, ob Ihr Server eine hohe CPU-Auslastung aufweist, überwachen Sie Metriken wie „Host-CPU-Prozent“, „Verbindungen insgesamt“, „Host-Arbeitsspeicherprozent“ und „E/A-Prozent“. Das Anzeigen einer Kombination dieser Metriken bietet manchmal Erkenntnisse dazu, was die erhöhte CPU-Auslastung in Ihrer Instanz von Azure Database for MySQL – Flexibler Server verursachen könnte.
Ziehen Sie beispielsweise einen plötzlichen Anstieg der Verbindungen in Betracht, die einen Anstieg der Datenbankabfragen initiieren, was dazu führt, dass die CPU-Auslastung aufnimmt.
Neben der Erfassung von Metriken ist es wichtig, auch die Arbeitsauslastung zu verfolgen, um zu verstehen, ob eine oder mehrere Abfragen den Anstieg der CPU-Auslastung verursachen.
Ursachen für hohe CPU-Auslastung
CPU-Spitzen können aus verschiedenen Gründen auftreten, in erster Linie aufgrund von Spitzen bei den Verbindungen und schlecht geschriebenen SQL-Abfragen oder einer Kombination aus beidem:
Spitzen bei Verbindungen
Wenn sich die Anzahl der Verbindungen erhöht, kommt es auch zu mehr Threads. Dies wiederum kann die CPU-Auslastung steigern, weil diese Verbindungen neben ihren Abfragen und Ressourcen verarbeitet werden müssen. Um Probleme mit Spitzen bei Verbindungen zu beheben, sollten Sie die Metrik Verbindungen gesamt überprüfen. Im nächsten Abschnitt finden Sie weitere Details zu diesen Verbindungen. Mit performance_schema können Sie ermitteln, welche Hosts und Benutzer gerade mit dem Server verbunden sind. Verwenden Sie dazu die folgenden Befehle:
Gerade verbundene Hosts
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Gerade verbundene Benutzer
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Schlecht geschriebene SQL-Abfragen
Abfragen, deren Ausführung aufwendig ist und bei denen viele Zeilen ohne Index gescannt werden, und Abfragen, bei denen vorübergehende Sortierungen und andere ineffiziente Pläne durchgeführt werden, können eine sehr hohen CPU-Auslastung verursachen. Während einige Abfragen in einer einzelnen Sitzung schnell ausgeführt werden können, können sie bei der Ausführung in mehreren Sitzungen CPU-Spitzen verursachen. Daher ist es wichtig, Ihre Abfragen, die Sie über Prozessliste anzeigen aufzeichnen, immer zu erläutern und sicherzustellen, dass ihre Ausführungspläne effizient sind. Dies erreichen Sie wie folgt: Stellen Sie sicher, dass eine minimale Anzahl von Zeilen überprüft wird, indem Sie Filter/WHERE-Klauseln und Indizes verwenden und die Verwendung großer temporärer Sortierungen sowie anderer fehlerhafter Ausführungspläne vermeiden. Weitere Informationen zu Ausführungsplänen finden Sie unter EXPLAIN-Ausgabeformat.
Erfassen von Details zum aktuellen Workload
Der Befehl SHOW (FULL) PROCESSLIST zeigt eine Liste aller Benutzersitzungen an, die derzeit mit der Instanz von Azure Database for MySQL – Flexibler Server verbunden sind. Außerdem werden Details zum aktuellen Status und zur Aktivität jeder Sitzung bereitgestellt.
Dieser Befehl erzeugt nur eine Momentaufnahme des aktuellen Sitzungsstatus und stellt keine Informationen zu historischen Sitzungsaktivitäten bereit.
Sehen wir uns die Beispielausgabe der Ausführung dieses Befehls an.
SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL |
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails; |
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 5 rows in set (0.00 sec) |
Es sind zwei Sitzungen vorhanden, die im Besitz des Benutzers „adminuser“ des Kunden sind, beide von derselben IP-Adresse:
- Sitzung 24835 Hat für die letzten sieben Sekunden eine SELECT-Anweisung ausgeführt.
- Die Sitzung 24837 führt die Anweisung „vollständige Prozessliste anzeigen“ aus.
Bei Bedarf kann es erforderlich sein, eine Abfrage zu beenden, z. B. eine Berichterstellungs- oder HTAP-Abfrage, die dazu geführt hat, dass die CPU-Auslastung ihrer Produktionsauslastung zu einem Anstieg geführt hat. Berücksichtigen Sie jedoch immer die potenziellen Folgen des Beendens einer Abfrage, bevor Sie die Aktion in einem Versuch ausführen, die CPU-Auslastung zu verringern. Andere Zeiten, wenn zeitintensive Abfragen identifiziert werden, die zu CPU-Spitzen führen, optimieren Sie diese Abfragen so, dass die Ressourcen optimal genutzt werden.
Detaillierte aktuelle Workloadanalyse
Sie müssen mindestens zwei Informationsquellen verwenden, um genaue Informationen zum Status einer Sitzung, einer Transaktion und einer Abfrage zu erhalten:
- Die Prozessliste des Servers aus der Tabelle INFORMATION_SCHEMA.PROCESSLIST, auf die Sie auch zugreifen können, indem Sie den Befehl „[VOLLSTÄNDIGE] PROZESSLISTE ANZEIGEN“ ausführen.
- Transaktionsmetadaten von InnoDB aus der Tabelle INFORMATION_SCHEMA.INNODB_TRX.
Mit Informationen aus nur einer dieser Quellen ist es unmöglich, den Verbindungs- und Transaktionsstatus zu beschreiben. Die Prozessliste informiert Sie beispielsweise nicht darüber, ob eine geöffnete Transaktion vorhanden ist, die mit einer der Sitzungen verknüpft ist. Andererseits zeigen die Transaktionsmetadaten nicht den Sitzungszustand und die in diesem Zustand verbrachten Zeit an.
Nachfolgend finden Sie eine Beispielabfrage, die Prozesslisteninformationen mit einigen der wichtigen InnoDB-Transaktionsmetadaten kombiniert:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
Das folgende Beispiel zeigt die Ausgabe dieser Abfrage:
****************** 1. row ******************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
****************** 2. row ******************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Eine Analyse dieser Informationen nach Sitzung wird in der folgenden Tabelle aufgeführt.
| Bereich | Analyse |
|---|---|
| Sitzung 11 | Diese Sitzung ist derzeit im Leerlauf (schlafend), ohne dass Abfragen ausgeführt werden, und dies seit 636 Sekunden. Innerhalb der Sitzung hat eine Transaktion, die für 2908 Sekunden geöffnet war, 17 825 792 Zeilen geändert und verwendet die Isolation „REPEATABLE READ“. |
| Sitzung 12 | Die Sitzung führt derzeit eine SELECT-Anweisung aus, die 15 Sekunden lang ausgeführt wurde. Innerhalb der Sitzung wird keine Abfrage ausgeführt, wie durch die NULL-Werte für „trx_started“ und „trx_age_seconds“ angegeben. Die Sitzung hält weiterhin die GC-Grenze ein, solange sie ausgeführt wird, es sei denn, sie verwendet die entspanntere Isolation „READ COMMITTED“. |
Wenn eine Sitzung als im Leerlauf gemeldet wird, führt sie keine Anweisungen mehr aus. An diesem Punkt hat die Sitzung alle vorherigen Arbeiten abgeschlossen und wartet auf neue Anweisungen vom Client. Leerlaufsitzungen sind jedoch weiterhin für einige CPU-Auslastung und Arbeitsspeichernutzung verantwortlich.
Auflisten geöffneter Transaktionen
Die Ausgabe der folgenden Abfrage enthält eine Liste aller Transaktionen, die derzeit auf dem Datenbankserver ausgeführt werden, um die Startzeit der Transaktion zu ermitteln, sodass Sie leicht erkennen können, ob zeitintensive sowie blockierende Transaktionen vorhanden sind, die die erwartete Laufzeit überschreiten.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Grundlegendes zu Threadzuständen
Transaktionen, die zur höheren CPU-Auslastung während der Ausführung beitragen, können Threads in verschiedenen Zuständen aufweisen, wie in den folgenden Abschnitten beschrieben. Verwenden Sie diese Informationen, um den Abfragelebenszyklus und verschiedene Threadzustände besser zu verstehen.
Überprüfen von Berechtigungen/Öffnen von Tabellen
Dieser Zustand bedeutet in der Regel, dass der Vorgang „Tabelle Öffnen“ eine lange Zeit in Anspruch nimmt. Normalerweise können Sie die Tabellencachegröße erhöhen, um das Problem zu verbessern. Tabellen, die langsam geöffnet werden, können jedoch auch auf andere Probleme hinweisen, z. B. dass zu viele Tabellen unter derselben Datenbank vorhanden sind.
Senden von Daten
Während dieser Zustand bedeuten kann, dass der Thread Daten über das Netzwerk sendet, kann er auch angeben, dass die Abfrage Daten vom Datenträger oder Arbeitsspeicher liest. Dieser Zustand kann durch eine sequenzielle Tabellenüberprüfung verursacht werden. Sie sollten die Werte der innodb_buffer_pool_reads und innodb_buffer_pool_read_requests überprüfen, um zu ermitteln, ob eine große Anzahl von Seiten vom Datenträger in den Arbeitsspeicher bereitgestellt wird. Weitere Informationen finden Sie unter Behandlung von Problemen mit geringem Arbeitsspeicher in Azure Database for MySQL – Flexibler Server.
Wird aktualisiert
Dieser Zustand bedeutet in der Regel, dass der Thread einen Schreibvorgang ausführt. Überprüfen Sie die IO-bezogene Metrik im Leistungsmonitor, um ein besseres Verständnis darüber zu erhalten, was die aktuellen Sitzungen tun.
Warten auf <lock_type>-Sperre
Dieser Zustand gibt an, dass der Thread auf eine zweite Sperre wartet. In den meisten Fällen ist es möglicherweise eine Metadatensperre. Sie sollten alle anderen Threads überprüfen und sehen, wer die Sperre übernimmt.
Grundlegendes zu und Analysieren von Warteereignissen
Es ist wichtig, die zugrunde liegenden Warteereignisse im MySQL-Modul zu verstehen, da lange Wartezeiten oder eine große Anzahl von Wartevorgängen in einer Datenbank zu einer erhöhten CPU-Auslastung führen können. Im folgenden Beispiel wird der entsprechende Befehl und die Beispielausgabe angezeigt.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| 10 rows in set (0.00 sec) |
Einschränken der Ausführungsszeit von SELECT-Anweisungen
Wenn Sie nichts über die Ausführungskosten und die Ausführungszeit für Datenbankvorgänge mit SELECT-Abfragen wissen, können zeitintensive SELECT-Vorgänge zu Unvorhersehbarkeit oder Volatilität auf dem Datenbankserver führen. Die Größe von Anweisungen und Transaktionen sowie die zugeordnete Ressourcenauslastung wächst weiterhin je nach dem zugrunde liegenden Datasetwachstum. Aufgrund dieses ungebundenen Wachstums dauern Endbenutzerauszüge und Transaktionen länger und länger, was immer mehr Ressourcen verbraucht, bis sie den Datenbankserver überwältigen. Wenn Sie ungebundene SELECT-Abfragen verwenden, empfiehlt es sich, den Parameter „max_execution_time“ so zu konfigurieren, dass alle Abfragen, welche diese Dauer überschreiten, abgebrochen werden.
Empfehlungen
- Stellen Sie sicher, dass Ihre Datenbank über genügend Ressourcen verfügt, um Ihre Abfragen auszuführen. Manchmal müssen Sie die Instanzgröße hochskalieren, um mehr CPU-Kerne und zusätzlichen Arbeitsspeicher für Ihren Workload zu erhalten.
- Vermeiden Sie umfangreiche oder lange Transaktionen, indem Sie sie in kleinere Transaktionen aufteilen.
- Führen Sie SELECT-Anweisungen nach Möglichkeit auf Lesereplikat-Servern aus.
- Verwenden Sie Warnungen für „Host-CPU-Prozent“, damit Sie Benachrichtigungen erhalten, wenn das System einen der angegebenen Schwellenwerte überschreitet.
- Verwenden Sie Query Performance Insights oder Azure Workbooks, um problematische oder langsam ausgeführte Abfragen zu ermitteln und dann zu optimieren.
- Erfassen Sie für Datenbankserver in der Produktion in regelmäßigen Abständen Diagnosedaten, um sicherzustellen, dass alles reibungslos ausgeführt wird. Beheben Sie andernfalls die festgestellten Probleme.