Mysql が CPU を過剰に消費する場合の最適化方法の詳細な紹介

以下のエディターは、CPU を過剰に消費する場合の Mysql 最適化方法に関する記事を提供します (必読)。編集者はこれがとても良いと思ったので、参考として共有します。エディターをフォローして見てみましょう

Mysql が CPU を大量に消費する場合、どの側面を最適化する必要がありますか?

CPU 使用率が高すぎる場合は、次のことを考慮できます:

1) 一般に、高い同時実行性要因を排除するには、CPU の使用率を低下させる原因となっているどの SQL ステートメントが実行されているかを見つける必要があります。高すぎる場合は、show processlist ステートメントを使用し、最も負荷の高い SQL ステートメントを見つけて、特定のフィールドにインデックスを適切に確立するなど、SQL を最適化します。

2) 低速クエリ ログを開き、SQL を使用します。これは実行に時間がかかりすぎ、Explain 分析に多くのリソースを消費しすぎます。その結果、CPU が高すぎます。これは主に GroupBy と OrderBy の並べ替えの問題が原因であり、最適化と改善が遅くなります。たとえば、insert ステートメントの最適化、group by ステートメントの最適化、order by ステートメントの最適化、join ステートメントの最適化などです。

3) ファイルとインデックスの定期的な最適化を検討します。テーブル;

5) データベース オブジェクトを最適化します。

6) ロックの問題であるかどうかを検討します。

7) key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size などの一部のパラメータを調整します。データの量が大きすぎる場合は、MySQL クラスターの使用または高可用性環境の構築を検討できます。

9) メモリラッチ(リーク)によりデータベースのCPUが高くなっている可能性があります

10) マルチユーザーの高い同時実行性の場合、どのシステムでも保持できなくなるため、キャッシュを使用する必要があります。 memcached または redis キャッシュ。

11) tmp_table_size のサイズが小さすぎるかどうかを確認します。許可されている場合は、それを少し増やします。

13) MySQL の SQL ステートメント。 sleep 接続タイムアウト 設定の問題 (wait_timeout)

14) show processlist を使用して mysql 接続数をチェックし、mysql で設定された接続数を超えているかどうかを確認します

これは私が遭遇したケースです:

ウェブサイトはピーク時にアクセスされ、ページをクリックすると少し固まります。サーバーにログインすると、以下に示すように、マシンの負荷が少し高く、mysql が多くの CPU リソースを消費していることがわかります。

MySQL の負荷が高いままである場合、スロー クエリ ログ機能がオンになっている場合。最善の方法は、ログ内の実行速度の遅い SQL ステートメントを最適化することです。SQL ステートメントで多数の group by ステートメントが使用されている場合、
union

joint query などにより、パフォーマンスが確実に増加します。 MySQLの占有率。したがって、SQL ステートメントの最適化に加えて、構成の最適化も行う必要があります。 mysql で show processlist を実行すると、次のようなエコー結果が表​​示されます:

1. クエリに大量の Copying to tmp table on disc のステータスが表示されます

これは明らかに一時テーブルが大きすぎるため、mysql が発生します。一時テーブルをハードディスクに書き込むと、全体的なパフォーマンスに影響します。

Mysql の tmp_table_size のデフォルト値はわずか 16MB ですが、現在の状況では明らかに十分ではありません。

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) mysqlターミナルコマンドを入力して変更し、グローバルを追加します。次回mysqlを入力したときに有効になります

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

mysqlにログインします再度

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)

2) my.cnf設定ファイルを変更

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

mysqlを再起動します

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

2。show processlistコマンドの出力は、問題のあるクエリを特定するのに役立ちます。発言。たとえば、次のような結果になります:

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)

以上がMysql が CPU を過剰に消費する場合の最適化方法の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。