Introduction détaillée aux méthodes d'optimisation lorsque Mysql consomme trop de CPU

L'éditeur suivant vous proposera un article sur les méthodes d'optimisation pour Mysql lorsqu'il consomme trop de CPU (à lire absolument). L'éditeur le trouve plutôt bon, je vais donc le partager avec vous maintenant et le donner comme référence pour tout le monde. Suivons l'éditeur et jetons un oeil

Quand Mysql consomme trop de CPU, quels aspects faut-il optimiser ?

Si l'utilisation du processeur est trop élevée, vous pouvez considérer les éléments suivants :

1) De manière générale, pour éliminer les facteurs de concurrence élevés, vous il faut encore trouver la cause. Quelles instructions SQL sont exécutées parce que votre CPU est trop élevé ? Utilisez l'instruction show processlist pour trouver l'instruction SQL avec la charge la plus lourde et optimiser le SQL, par exemple en établissant de manière appropriée un index sur un certain champ ; 🎜>

2) Ouvrez le journal des requêtes lentes, utilisez les instructions SQL qui prennent trop de temps à s'exécuter et occupent trop de ressources à expliquer et à analyser, ce qui entraîne une consommation élevée de CPU. La plupart d'entre elles sont causées par des problèmes de tri GroupBy et OrderBy, puis lentement les optimiser et les améliorer. Par exemple, optimisez les instructions d'insertion, optimisez les instructions group by, optimisez les instructions order by, optimisez les instructions de jointure, etc.

3) Pensez à optimiser régulièrement les fichiers et les index

4) Analysez régulièrement les tables ; et utilisez la table d'optimisation ;

5) Optimisez les objets de la base de données

6) Déterminez s'il s'agit d'un problème de verrouillage

7) Ajustez certains paramètres du serveur MySQL, tels que key_buffer_size ; , table_cache, innodb_buffer_pool_size, innodb_log_file_size Attendez ;

8) Si la quantité de données est trop importante, vous pouvez envisager d'utiliser un cluster MySQL ou de créer un environnement à haute disponibilité.

9) Le processeur de la base de données peut être élevé en raison d'un verrou de mémoire (fuite)

10) Dans le cas d'une concurrence élevée multi-utilisateurs, aucun système ne pourra le contenir, donc l'utilisation du cache est nécessaire Oui, vous pouvez utiliser le cache memcached ou redis ;

11) Vérifiez si la taille de tmp_table_size est trop petite. Si cela est autorisé, augmentez-la de manière appropriée ; est configuré trop petit, augmentez-le un peu ;

13) Problème de configuration du délai d'expiration de la connexion en veille de l'instruction MySQL SQL (wait_timeout)

14) Utilisez show processlist pour vérifier le nombre de connexions MySQL pour voir si il dépasse la connexion définie par mysql

Voici un cas que j'ai rencontré :

Le site Web est consulté pendant les heures de pointe, et le les clics sur les pages sont un peu bloqués. Connectez-vous au serveur et constatez que la charge de la machine est un peu élevée et que MySQL consomme beaucoup de ressources CPU, comme indiqué ci-dessous :

Charge MySQL reste élevé. Si vous ouvrez la fonction de journal des requêtes lente, le meilleur moyen est d'optimiser l'exécution lente des instructions SQL dans le journal des requêtes lentes. Si l'instruction SQL utilise un grand nombre d'instructions group by et d'autres, . union

Union query, etc. Cela augmentera certainement le taux d'occupation de mysql. Par conséquent, vous devez optimiser l'instruction SQLEn plus d'optimiser l'instruction SQL, vous pouvez également effectuer une optimisation de la configuration. Exécutez show proceslist dans MySQL ; les résultats d'écho suivants apparaissent :

1 La requête comporte un grand nombre de copies vers la table tmp sur l'état du disque

<.> De toute évidence, la table temporaire est trop volumineuse, ce qui oblige MySQL à écrire la table temporaire sur le disque dur, ce qui affecte les performances globales.

La valeur par défaut de tmp_table_size dans Mysql n'est que de 16 Mo, ce qui n'est évidemment pas suffisant dans la situation actuelle.

Solution : Ajuster la taille de la table temporaire

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) Entrez la commande du terminal mysql pour modifier, ajoutez global, cela prendra effet la prochaine fois que vous vous connecterez à mysql

Connectez-vous à nouveau à mysql

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

2) my.cnf

Fichier de configuration

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)

Modifier

Redémarrer MySQL

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

2. show processlist ; sortie de la commande Affiche les threads en cours d'exécution, ce qui peut aider à identifier les instructions de requête problématiques. Par exemple, le résultat suivant :

[root@www ~]# /etc/init.d/mysqld restart

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)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!