Detaillierte Einführung in Optimierungsmethoden, wenn MySQL zu viel CPU beansprucht

Der folgende Editor bringt Ihnen einen Artikel über Optimierungsmethoden für Mysql, wenn es zu viel CPU beansprucht (unbedingt lesen). Der Herausgeber findet es ziemlich gut, deshalb werde ich es jetzt mit Ihnen teilen und es allen als Referenz geben. Folgen wir dem Editor und werfen wir einen Blick darauf

Welche Aspekte sollten optimiert werden, wenn MySQL zu viel CPU beansprucht?

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

1) Im Allgemeinen müssen Sie hohe Parallelitätsfaktoren beseitigen Sie müssen immer noch die Ursache finden. Welche SQL-Anweisungen werden ausgeführt, weil Ihre CPU zu hoch ist? Verwenden Sie die Anweisung showprocesslist, um die SQL-Anweisung mit der höchsten Auslastung zu finden und die SQL zu optimieren, z. B. durch die entsprechende Einrichtung eines Index für ein bestimmtes Feld 🎜>

2) Öffnen Sie das langsame Abfrageprotokoll. Verwenden Sie SQL-Anweisungen, deren Ausführung zu lange dauert und die zu viele Ressourcen zum Erklären und Analysieren beanspruchen, was zu einer hohen CPU-Auslastung führt. Die meisten davon werden durch Sortierprobleme bei GroupBy und OrderBy verursacht. und diese dann langsam optimieren und verbessern. Optimieren Sie beispielsweise Einfügungsanweisungen, optimieren Sie Gruppierungsanweisungen, optimieren Sie Verbindungsanweisungen usw.

3) Erwägen Sie die regelmäßige Optimierung von Dateien und Indizes.

4) Analysieren Sie Tabellen regelmäßig und verwenden Sie „Tabelle optimieren“;

5) Datenbankobjekte optimieren; , table_cache, innodb_buffer_pool_size, innodb_log_file_size Warten;

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) Die Datenbank-CPU kann aufgrund von Speicherlatch (Leckage) hoch sein

10) Im Falle einer hohen Parallelität mehrerer Benutzer ist kein System in der Lage, sie zu halten Die Verwendung von Cache ist erforderlich. Ja, Sie können Memcached oder Redis-Cache verwenden.

11) Überprüfen Sie, ob die Größe von tmp_table_size zu klein ist. Erhöhen Sie sie entsprechend ist zu klein konfiguriert, erhöhen Sie es ein wenig;

13) Problem mit der Einstellung des Zeitlimits für die Ruhezustandsverbindung der MySQL-SQL-Anweisung (wait_timeout)

14) Verwenden Sie show Processlist, um die Anzahl der MySQL-Verbindungen zu überprüfen, um festzustellen, ob es überschreitet die von MySQL festgelegte Verbindung

Teilen wir einen Fall mit, auf den wir gestoßen sind:

Auf die Website wird während der Hauptverkehrszeiten zugegriffen und die Seite klickt stecken etwas fest. 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:

MySQL-Last bleibt hoch. Wenn Sie die langsame Abfrageprotokollfunktion öffnen, besteht die beste Möglichkeit darin, die langsame Ausführung von SQL-Anweisungen im langsamen Abfrageprotokoll zu optimieren Union

Union-Abfrage

usw. Dies wird definitiv die Auslastungsrate von MySQL erhöhen. Daher ist es notwendig, die SQL-Anweisung zu optimieren

Zusätzlich zur Optimierung der SQL-Anweisung können Sie auch einige Konfigurationsoptimierungen durchführen. Führen Sie show proceslist in mysql aus. Die folgenden Echo-Ergebnisse werden angezeigt:

1. Die Abfrage weist eine große Anzahl von Kopien in die tmp-Tabelle auf der Festplatte auf

Offensichtlich ist die temporäre Tabelle zu groß, was dazu führt, dass MySQL die temporäre Tabelle auf die Festplatte schreibt, was sich auf die Gesamtleistung auswirkt.

Der Standardwert von tmp_table_size in MySQL beträgt nur 16 MB, was in der aktuellen Situation offensichtlich nicht ausreicht.

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

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmp
dir
 | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in 
set
 (0.00 sec)

1) Geben Sie den MySQL-Terminalbefehl ein Zum Ändern fügen Sie global hinzu. Es wird wirksam, wenn Sie sich das nächste Mal bei MySQL anmelden.
Melden Sie sich erneut bei MySQL an

2) my.cnf

Konfigurationsdatei

mysql> set global tmp_table_size=33554432;
Query OK, 0 rows affected (0.00 sec)

Ändern

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.01 sec)

Mysql neu starten
2. show Processlist; Ausgabe des Befehls Zeigt an, welche Threads ausgeführt werden, was bei der Identifizierung problematischer Abfrageanweisungen helfen kann. Beispielsweise das folgende Ergebnis:

[root@www ~]# vim my.cnf
.....
tmp_table_size = 32M

Id User Host db Command Time State Info 
207 root 192.168.1.25:51718 mytest Sleep 5 
NULL

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库 。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

常见问题：

一般是睡眠连接过多，严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

解决办法 ：

在mysql的配置my.cnf文件中，有一项wait_timeout参数设置.即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止。
wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题。
通常来说，把wait_timeout设置为10小时是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接。

然而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致下面的报错：

The last packet successfully received from the server was 596,688 milliseconds ago.
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

28800seconds，也就是8小时。

如果在wait_timeout秒期间内，数据库连接(java.sql.Connection)一直处于等待状态，mysql就将该连接关闭。这时，你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。
可以将mysql全局变量wait_timeout的缺省值改大。

查看mysql手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。

比如将其改成30天

mysql> set global wait_timeout=124800;
Query OK, 0 rows affected (0.00 sec)

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in Optimierungsmethoden, wenn MySQL zu viel CPU beansprucht. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!