Optimierungsmethoden, wenn MySQL zu viel CPU beansprucht (unbedingt lesen)

Wenn MySQL zu viel CPU beansprucht, wo sollten wir mit der Optimierung beginnen?

Wenn die CPU-Auslastung zu hoch ist, können Sie Folgendes in Betracht ziehen:

1) Abgesehen vom hohen Parallelitätsfaktor müssen Sie im Allgemeinen noch herausfinden, welche SQL-Anweisungen ausgeführt werden, die Ihre CPU-Auslastung erhöhen. Verwenden Sie die Anweisung „show processlist“, um die SQL-Anweisung mit der höchsten Auslastung zu finden und zu optimieren, z. B. um einen Index für ein bestimmtes Feld zu erstellen.

2) Öffnen Sie das langsame Abfrageprotokoll und erklären Sie die SQL-Anweisungen, deren Ausführung zu lange dauert und zu viele Ressourcen beansprucht. Die hohe CPU-Auslastung wird hauptsächlich durch Sortierprobleme bei GroupBy und OrderBy verursacht. Optimieren und verbessern Sie diese dann langsam. Beispielsweise können Sie Insert-Anweisungen, Group-By-Anweisungen, Order-By-Anweisungen, Join-Anweisungen usw. optimieren.

3) Erwägen Sie, Dateien und Indizes regelmäßig zu optimieren;

4) Analysieren Sie die Tabelle regelmäßig und verwenden Sie die Funktion „Tabelle optimieren“.

5) Datenbankobjekte optimieren;

6) Überlegen Sie, ob es sich um ein Schlossproblem handelt.

7) Passen Sie einige MySQL-Serverparameter an, etwa key_buffer_size, table_cache, innodb_buffer_pool_size, innodb_log_file_size usw.

8) Wenn die Datenmenge zu groß ist, können Sie die Verwendung eines MySQL-Clusters oder den Aufbau einer Hochverfügbarkeitsumgebung in Betracht ziehen.

9) Eine hohe CPU-Auslastung der Datenbank kann durch Speicherverriegelung (Speicherleck) verursacht werden.

10) Bei einer hohen Gleichzeitigkeit mehrerer Benutzer kann kein System mithalten. Daher muss ein Cache verwendet werden, entweder Memcached oder Redis Cache.

11) Überprüfen Sie, ob die Größe von tmp_table_size zu klein ist. Wenn möglich, erhöhen Sie sie entsprechend.

12) Wenn max_heap_table_size zu klein konfiguriert ist, erhöhen Sie es ein wenig.

13) Problem mit der Einstellung des Timeout für die Sleep-Verbindung bei MySQL SQL-Anweisungen (wait_timeout)

14) Verwenden Sie „show processlist“, um die Anzahl der MySQL-Verbindungen zu überprüfen und festzustellen, ob sie die von MySQL festgelegte Anzahl von Verbindungen überschreitet.

Hier ist ein Fall, den ich erlebt habe:

Der Zugriff auf die Website erfolgt während der Stoßzeiten und beim Anklicken der Seiten kommt es zu leichten Verzögerungen. Melden Sie sich beim Server an und stellen Sie fest, dass die Maschinenlast etwas hoch ist und MySQL viele CPU-Ressourcen beansprucht, wie unten gezeigt:

Die MySQL-Last bleibt hoch. Wenn die Funktion „Protokoll für langsame Abfragen“ aktiviert ist, besteht die beste Möglichkeit darin, die langsamen SQL-Anweisungen im Protokoll für langsame Abfragen zu optimieren. Wenn die SQL-Anweisungen eine große Anzahl von Group-By-Anweisungen, Union-Abfragen usw. verwenden, steigt die MySQL-Nutzungsrate definitiv an. Sie müssen also die SQL-Anweisung optimieren

Zusätzlich zur Optimierung von SQL-Anweisungen können Sie auch einige Konfigurationsoptimierungen durchführen. Führen Sie „show procedureslist“ in mysql aus. Die folgenden Ergebnisse werden angezeigt:

1. Abfrage einer großen Anzahl von Kopien in die temporäre Tabelle auf dem Datenträgerstatus

Da die temporäre Tabelle zu groß ist, schreibt MySQL die temporäre Tabelle offensichtlich auf die Festplatte, was die Gesamtleistung beeinträchtigt.

Der Standardwert von tmp_table_size in Mysql beträgt nur 16 MB, was in der aktuellen Situation offensichtlich nicht ausreicht.
mysql> Variablen wie "%tmp%" anzeigen;
+----------------------+----------+
| Variablenname | Wert |
+----------------------+----------+
| max_tmp_tabellen | 32 |
| Slave_Load_Tempoverzeichnis | /tmp |
| tmp_tabellengröße | 16777216 |
| temporäres Verzeichnis | /tmp |
+----------------------+----------+
4 Zeilen im Satz (0,00 Sek.)

Lösung: Passen Sie die Größe der temporären Tabelle an

1) Geben Sie den MySQL-Terminalbefehl ein, um ihn zu ändern, global hinzuzufügen und er wird wirksam, wenn Sie das nächste Mal MySQL eingeben

mysql> setze globale tmp_table_size=33554432;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Melden Sie sich erneut bei MySQL an
mysql> Variablen wie "%tmp%" anzeigen;
+----------------------+----------+
| Variablenname | Wert |
+----------------------+----------+
| max_tmp_tabellen | 32 |
| Slave_Load_Tempoverzeichnis | /tmp |
| tmp_tabellengröße | 33554432 |
| temporäres Verzeichnis | /tmp |
+----------------------+----------+
4 Zeilen im Satz (0,01 Sek.)

2) Änderung der Konfigurationsdatei my.cnf

[root@www ~]# vim meine.cnf
.....
temporäre Tabellengröße = 32 M

Starten Sie MySQL neu
[root@www ~]# /etc/init.d/mysqld neu starten

2. Die Ausgabe des Befehls „show processlist;“ zeigt, welche Threads ausgeführt werden, was bei der Identifizierung problematischer Abfragen helfen kann. Beispielsweise ergeben sich folgende Ergebnisse:

Id Benutzer Host db Befehl Zeit Status Info
207 root 192.168.1.25:51718 mytest Schlaf 5 NULL
Lassen Sie mich zunächst kurz die Bedeutung und den Zweck jeder Spalte erklären. Die erste Spalte, id, ist ein Flag, das sehr nützlich ist, wenn Sie eine Anweisung löschen möchten. In der Benutzerspalte wird der aktuelle Benutzer angezeigt. Wenn Sie nicht der Root-Benutzer sind, zeigt dieser Befehl nur die SQL-Anweisungen innerhalb Ihres Berechtigungsbereichs an. In der Spalte „Host“ werden die IP-Adresse und der Port angezeigt, von denen die Anweisung ausgegeben wurde. Haha, es kann verwendet werden, um den Benutzer aufzuspüren, der die problematische Aussage gemacht hat. Die Spalte „db“ zeigt an, mit welcher Datenbank der Prozess aktuell verbunden ist. In der Befehlsspalte wird der von der aktuellen Verbindung ausgeführte Befehl angezeigt. Normalerweise ist das „sleep“, „Query“ oder „Connect“. Die Zeitspalte gibt die Dauer dieses Zustands in Sekunden an. Die Spalte „Status“ zeigt den Status der SQL-Anweisung unter Verwendung der aktuellen Verbindung an. Dies ist eine sehr wichtige Spalte. Alle Status werden später beschrieben. Bitte beachten Sie, dass der Status nur ein bestimmter Status bei der Ausführung der Anweisung ist. Beispielsweise muss eine SQL-Anweisung wie eine Abfrage möglicherweise die Zustände „In temporäre Tabelle kopieren“, „Ergebnis sortieren“, „Daten senden“ usw. durchlaufen, bevor sie abgeschlossen werden kann. Die Spalte „Info“ zeigt die SQL-Anweisung an. Aufgrund der begrenzten Länge werden lange SQL-Anweisungen nicht vollständig angezeigt, dies ist jedoch eine wichtige Grundlage für die Beurteilung problematischer Anweisungen.

Häufig gestellte Fragen:

Im Allgemeinen gibt es zu viele ruhende Verbindungen, die die MySQL-Serverressourcen (hauptsächlich CPU und Speicher) stark beanspruchen und zu einem MySQL-Absturz führen können.

Lösung:

In der MySQL-Konfigurationsdatei my.cnf gibt es eine Einstellung für den Parameter wait_timeout. Sie können das Timeout für die Verbindung im Ruhezustand in Sekunden festlegen. Wenn eine Verbindung abläuft, wird sie von MySQL automatisch beendet.
Es gibt Nachteile, wenn wait_timeout zu groß eingestellt wird. Dies bedeutet, dass eine große Anzahl von SLEEP-Prozessen in MySQL nicht rechtzeitig freigegeben werden kann, was die Systemleistung beeinträchtigt. Dieser Indikator kann jedoch nicht zu klein eingestellt werden, da sonst Probleme wie „MySQL ist weg“ auftreten können.
Generell ist es eine gute Wahl, wait_timeout auf 10 Stunden zu setzen, aber in manchen Fällen kann es zu Problemen kommen. Wenn beispielsweise ein CRON-Skript vorhanden ist und das Intervall zwischen zwei SQL-Abfragen größer als 10 Sekunden ist, ist diese Einstellung problematisch (natürlich ist das kein unlösbares Problem. Sie können im Programm von Zeit zu Zeit mysql_ping ausführen, damit der Server weiß, dass Sie noch am Leben sind, und die wait_timeout-Zeit neu berechnen):

Das standardmäßige „wait_timeout“ des MySQL-Servers beträgt 28.800 Sekunden oder 8 Stunden. Dies bedeutet, dass MySQL eine Verbindung automatisch trennt, wenn sie länger als 8 Stunden inaktiv ist.

Der Verbindungspool geht jedoch davon aus, dass die Verbindung noch gültig ist (da die Gültigkeit der Verbindung nicht überprüft wird). Wenn die Anwendung die Verwendung der Verbindung anfordert, tritt der folgende Fehler auf:

Das letzte erfolgreich vom Server empfangene Paket liegt 596.688 Millisekunden zurück.
mysql> Variablen wie „wait_timeout“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Wartezeitüberschreitung | 28800 |
+---------------+-------+
1 Zeile im Satz (0,00 Sek.)

28800 Sekunden oder 8 Stunden.

Wenn die Datenbankverbindung (java.sql.Connection) innerhalb von wait_timeout Sekunden im Wartezustand bleibt, schließt MySQL die Verbindung. Zu diesem Zeitpunkt enthält der Verbindungspool Ihrer Java-Anwendung noch immer einen Verweis auf die Verbindung. Bei Verwendung dieser Verbindung zum Ausführen von Datenbankvorgängen tritt der obige Fehler auf.
Sie können den Standardwert der globalen MySQL-Variable wait_timeout erhöhen.

Beim Überprüfen des MySQL-Handbuchs habe ich festgestellt, dass die Maximalwerte für wait_timeout 24 Tage/365 Tage (Windows/Linux) betragen.

Ändern Sie es beispielsweise auf 30 Tage

mysql> globales Wait_Timeout = 124800 festlegen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Der obige Artikel zu Optimierungsmethoden, wenn MySQL zu viel CPU-Leistung beansprucht (unbedingt lesen) ist der gesamte Inhalt, den der Herausgeber mit Ihnen teilt. Ich hoffe, er kann Ihnen als Referenz dienen und ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen.