Hohe CPU-Auslastung in Instanzen optimieren

Eine hohe CPU-Auslastung in einer Instanz kann durch verschiedene Gründe verursacht werden, z. B. eine Erhöhung der Arbeitslasten, umfangreiche Transaktionen, langsame Abfragen und lang andauernde Transaktionen.

Der Recommender für unterdimensionierte Instanzen analysiert die CPU-Auslastung. Wenn die CPU-Auslastung in den letzten 30 Tagen für einen beträchtlichen Zeitraum bei oder über 95 % gelegen hat, benachrichtigt Sie der Recommender und bietet Ihnen zusätzliche Informationen, um das Problem zu beheben.

In diesem Dokument wird erläutert, wie Sie eine Cloud SQL for MySQL-Instanz überprüfen und optimieren, wenn diese Instanz laut dem Recommender für unterdimensionierte Instanzen eine hohe CPU-Auslastung hat.

Empfehlungen

Die CPU-Auslastung steigt proportional mit der Arbeitslast. Prüfen Sie die laufenden Abfragen und optimieren Sie sie, um die CPU-Auslastung zu reduzieren. Hier sind einige Schritte zum Prüfen des CPU-Verbrauchs.

  1. Prüfen Sie Threads_running und Threads_connected.

    Verwenden Sie die folgende Abfrage, um die Anzahl der aktiven Threads zu sehen:

    > SHOW STATUS like 'Threads_%';

    Threads_running ist eine Teilmenge von Threads_connected. Die restlichen Threads sind inaktiv. Eine Erhöhung von Threads_running würde zu einer Erhöhung der CPU-Nutzung führen. Es empfiehlt sich zu prüfen, was in diesen Threads ausgeführt wird.

  2. Abfragestatus prüfen

    Führen Sie den Befehl SHOW PROCESSLIST aus, um die laufenden Abfragen zu sehen. Er gibt der Reihe nach alle verbundenen Threads sowie ihre aktuell ausgeführte SQL-Anweisung zurück. 

    mysql> SHOW [FULL] PROCESSLIST;

    Achten Sie auf die Spalten für den Status und die Dauer. Prüfen Sie, ob viele Abfragen im selben Status festhängen.

    • Wenn für viele Threads Updating angezeigt wird, kann es zu Konflikten durch Datensatzsperren kommen. Fahren Sie mit dem nächsten Schritt fort.
    • Wenn bei vielen Threads Waiting für die Tabellenmetadatensperre angezeigt wird, prüfen Sie die Abfrage, um die Tabelle zu bestimmen, und suchen Sie nach einer DDL (z. B. ALTER TABLE), die die Metadatensperre verursachen könnte. Eine DDL wartet möglicherweise auch auf eine Tabellenmetadatensperre, wenn eine frühzeitige Abfrage, z. B. ein lang andauerndes SELECT query, sie verursacht.

  3. Auf Konflikte durch Datensatzsperren prüfen

    Wenn Transaktionen Sperren für beliebte Indexdatensätze verursachen, blockieren sie andere Transaktionen, die dieselben Sperren anfordern. Dies kann zu einer verketteten Wirkung und zu einer Reihe von Anfragen führen, die hängen bleiben, und den Wert von Threads_running erhöhen. Verwenden Sie die Tabelle information_schema.innodb_lock_waits, um Sperrenkonflikte zu diagnostizieren.

    Die folgende Abfrage listet jede blockierende Transaktion und die Anzahl der zugehörigen blockierten Transaktionen auf. 

    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;

    Sowohl eine einzelne große DML als auch viele gleichzeitige kleine DML können zu Zeilensperrenkonflikten führen. Dies können Sie auf der Anwendungsseite mit den folgenden Schritten optimieren:

    • Vermeiden Sie lange Transaktionen, da Zeilensperren beibehalten werden, bis die Transaktion endet.
    • Teilen Sie eine einzelne große DML in kleine DMLs auf.
    • Teilen Sie eine einzelne Zeilen-DML in kleine Blöcke auf.
    • Minimieren Sie Konflikte zwischen Threads. Wenn der Anwendungscode beispielsweise einen Verbindungspool verwendet, weisen Sie demselben Thread einen ID-Bereich zu.

  4. Lang andauernde Transaktionen ermitteln

    • SHOW ENGINE INNODB STATUS verwenden

      Im Bereich TRANSAKTIONEN werden alle offenen Transaktionen von der neuesten zur ältesten sortiert angezeigt. 

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      Beginnen Sie mit den ältesten Transaktionen und suchen Sie nach Antworten auf die folgenden Fragen:

      • Wie lange laufen diese Transaktionen schon?
      • Wie viele Sperrstrukturen und Zeilensperren sind vorhanden?
      • Wie viele Logeinträge zum Rückgängigmachen gibt es?
      • Welche Hosts und Nutzer stellen eine Verbindung her?
      • Was ist die laufende SQL-Anweisung?

    • information_schema.innodb_trx verwenden

      Wenn SHOW ENGINE INNODB STATUS abgeschnitten wurde, können Sie alle offenen Transaktionen alternativ mit der Tabelle information_schema.innodb_trx überprüfen:

      SELECT
 trx_id, trx_state,
 timestampdiff(second, trx_started, now()) AS active_secs,
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked,
 trx_lock_structs,
 trx_rows_locked,
 trx_rows_modified,
 trx_query
FROM information_schema.innodb_trx

    Wenn die Transaktionen die aktuell lang andauernden Anweisungen anzeigen, können Sie diese Transaktionen entweder stoppen, um den Druck auf dem Server zu verringern, oder auf den Abschluss der kritischen Transaktionen warten. Wenn für die älteren Transaktionen keine Aktivitäten angezeigt werden, fahren Sie mit dem nächsten Schritt fort, um den Transaktionsverlauf zu finden.

  5. SQL-Anweisungen von lang andauernden Transaktionen prüfen

    • performance_schema verwenden

      Damit Sie performance_schema verwenden können, müssen Sie es zuerst aktivieren. Es ist eine Änderung, die einen Neustart der Instanz erfordert. Prüfen Sie nach dem Aktivieren von performance_schema, ob die Instrumente und Nutzer aktiviert sind:

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      Wenn sie nicht aktiviert sind, aktivieren Sie sie:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      Standardmäßig behält jeder Thread die letzten zehn Ereignisse bei, die durch performance_schema_events_statements_history_size definiert sind. Diese reichen im Allgemeinen aus, um die Transaktion im Anwendungscode zu finden. Dieser Parameter ist nicht dynamisch.

      Fragen Sie mit der mysql thread id, die processlist_id entspricht, die Verlaufsereignisse ab:

      SELECT
 t.thread_id,
 event_name,
 sql_text,
 rows_affected,
 rows_examined,
 processlist_id,
 processlist_time,
 processlist_state
FROM events_statements_history h
INNER JOIN threads t
ON h.thread_id = t.thread_id
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • Log für langsame Abfragen verwenden

      Bei der Fehlerbehebung können Sie alle Abfragen, die länger als N Sekunden in Anspruch genommen haben, im Log für langsame Abfragen erfassen. Sie können die Logs für langsame Abfragen aktivieren. Bearbeiten Sie dazu die Instanzeinstellungen auf der Instanzseite der Google Cloud Console oder der gcloud CLI und sehen Sie sich die Logs dann in der Loganzeige der Google Cloud Console oder der gloud CLI an.

  6. Semaphor-Konflikte prüfen

    In einer gleichzeitigen Umgebung können Mutex und Lese-/Schreibvorgänge, die gemeinsam genutzte Ressourcen in Anspruch nehmen, die Konfliktursache sein und die Serverleistung verlangsamen. Wenn die Semaphor-Wartezeit mehr als 600 Sekunden beträgt, kann das System abstürzen um die Ausführungsunterbrechung zu beenden.

    Verwenden Sie den folgenden Befehl, um den Semaphor-Konflikt anzuzeigen:

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    Bei jedem Semaphor-Wartevorgang zeigt die erste Zeile den Thread, der wartet, den spezifischen Semaphor und die Wartezeit. Wenn es bei wiederholter Ausführung von SHOW ENGINE INNODB STATUS zu vielen Semaphoren kommt, insbesondere zu Wartezeiten von mehr als ein paar Sekunden, bedeutet dies, dass das System Nebenläufigkeitsengpässe aufweist.

    Es gibt unterschiedliche Konfliktursachen bei verschiedenen Arbeitslasten und Konfigurationen.

    Wenn sich die Semaphoren oft in btr0sea.c befinden, kann die adaptive Hash-Indexierung die Konfliktquelle sein. Versuchen Sie, sie mit der Google Cloud Console oder der gcloud CLI zu deaktivieren.

  7. Lange SELECT-Abfragen optimieren

    Überprüfen Sie zuerst die Abfrage. Identifizieren Sie das Ziel der Abfrage und den besten Weg, die Ergebnisse zu erhalten. Der beste Abfrageplan ist jener, der den Datenzugriff minimiert.

    • Prüfen Sie den Abfrageausführungsplan:
    mysql> EXPLAIN <the query>;

    In der MySQL-Dokumentation erfahren Sie, wie die Ausgabe interpretiert und die Abfrageeffizienz bewertet wird.

    • Richtigen Index verwenden

    Prüfen Sie in der Schlüsselspalte, um zu sehen, ob der erwartete Index verwendet wird. Aktualisieren Sie andernfalls die Indexstatistiken:

    mysql> analyze table <table_name>

    Erhöhen Sie die Anzahl der Beispielseiten, die zur Berechnung von Indexstatistiken verwendet werden. Weitere Informationen finden Sie in der MySQL-Dokumentation.

    • Index vollständig nutzen

    Wenn Sie einen mehrspaltigen Index verwenden, prüfen Sie die key_len-Spalten, um festzustellen, ob der Index vollständig zum Filtern der Datensätze genutzt wird. Die Spalten ganz links müssen Gleichheitsvergleiche sein und der Index kann bis zur ersten Bereichsbedingung (einschließlich dieser) verwendet werden.

    • Optimierer-Hinweise verwenden

    Eine weitere Möglichkeit, die Verwendung des richtigen Index sicherzustellen, ist die Nutzung von Indexhinweisen und Hinweisen für die Tabellen-Join-Reihenfolge.

  8. Lange Verlaufsliste mit READ COMMITTED vermeiden

    Die Verlaufsliste ist die Liste der nicht gelöschten Transaktionen im Tablespace zum Rückgängigmachen. Die Standardisolationsebene einer Transaktion ist REPEATABLE READ, sodass eine Transaktion während ihrer gesamten Dauer denselben Snapshot lesen muss. Daher blockiert eine SELECT-Abfrage das Löschen von Logdatensätzen zum Rückgängigmachen, die seit dem Start der Abfrage (oder Transaktion) erstellt wurden. Eine lange Verlaufsliste verlangsamt daher die Abfrageleistung. Wenn Sie vermeiden möchten, dass eine lange Verlaufsliste erstellt wird, können Sie z. B. die Transaktionsisolationsebene in READ COMMITTED ändern. Bei READ COMMITTED wird die Verlaufsliste nicht mehr für eine konsistente Leseansicht benötigt. Sie können die Transaktionsisolationsebene global für alle Sitzungen, für eine einzelne Sitzung oder für die nächste einzelne Transaktion ändern. Weitere Informationen finden Sie in der MySQL-Dokumentation.

  9. Serverkonfiguration abstimmen

    Es gibt viel über die Serverkonfiguration zu sagen. Die vollständige Thematik wird in diesem Dokument nicht behandelt. Es sollte aber erwähnt werden, dass der Server auch verschiedene Statusvariablen meldet, die Hinweise darauf geben, wie gut die zugehörigen Konfigurationen sind. Beispiel:

    • Passen Sie thread_cache_size an, wenn Threads_created/Connections groß ist. Ein ordnungsgemäßer Thread-Cache würde die Erstellungszeit des Threads reduzieren und eine stark gleichzeitige Arbeitslast unterstützen.
    • Passen Sie table_open_cache an, wenn Table_open_cache_misses/Table_open_cache_hits nicht trivial ist. Tabellen im Tabellen-Cache sparen Ausführungszeit bei Abfragen und können in einer stark gleichzeitigen Umgebung einen Unterschied machen.

  10. Unerwünschte Verbindung beenden

    Sie können die Abfrage beenden, wenn sie ungültig zu sein scheint oder nicht mehr benötigt wird. Informationen zum Identifizieren und Beenden des MySQL-Threads finden Sie unter Datenbankverbindungen verwalten.

Wenn die CPU-Auslastung immer noch hoch ist und die Abfragen erforderlichen Traffic verursachen, sollten Sie eventuell die CPU-Ressourcen in Ihrer Instanz erhöhen, um Datenbankabstürze oder -ausfälle zu vermeiden.