Mysql が CPU を過剰に占有する場合の最適化方法 (必読)

Mysql が CPU を占有しすぎる場合、どこから最適化を開始すればよいでしょうか?

CPU 使用率が高すぎる場合は、次のことを検討してください。

1) 一般的に言えば、同時実行性が高いという要因を除けば、CPU 使用率が高くなりすぎる原因となっている実行中の SQL ステートメントを見つける必要があります。show processlist ステートメントを使用して、最も負荷の高い SQL ステートメントを見つけ、特定のフィールドにインデックスを作成するなどして最適化します。

2) 遅いクエリ ログを開き、実行に時間がかかり、多くのリソースを占有する SQL ステートメントを説明します。CPU 使用率が高い原因は、主に GroupBy と OrderBy の並べ替えの問題であるため、ゆっくりと最適化して改善します。たとえば、挿入ステートメントの最適化、グループ化ステートメントの最適化、順序付けステートメントの最適化、結合ステートメントの最適化などです。

3) ファイルとインデックスを定期的に最適化することを検討してください。

4) テーブルを定期的に分析し、最適化されたテーブルを使用します。

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

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

7) key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size などの MySQL サーバー パラメータを調整します。

8) データ量が多すぎる場合は、MySQL クラスタの使用や高可用性環境の構築を検討してください。

9) データベースのCPU使用率が高いのは、メモリラッチ（リーク）が原因である可能性があります。

10) 複数のユーザーが同時に処理する場合、どのシステムも耐えられないため、memcached または redis キャッシュのいずれかのキャッシュを使用する必要があります。

11) tmp_table_size のサイズが小さすぎないか確認します。可能であれば、適切に増やしてください。

12) max_heap_table_size の設定が小さすぎる場合は、少し増やします。

13) MySQL SQL 文のスリープ接続タイムアウト設定の問題 (wait_timeout)

14) show processlist を使用して MySQL 接続数をチェックし、MySQL によって設定された接続数を超えていないかどうかを確認します。

私が遭遇したケースは次のとおりです。

ウェブサイトはピーク時間帯にアクセスされるため、ページをクリックするときに少し遅延が発生します。サーバーにログインすると、マシンの負荷が少し高く、MySQL が CPU リソースを大量に占有していることがわかります (以下を参照)。

MySQL の負荷は高いままです。スロークエリログ機能をオンにしている場合は、スロークエリログ内の遅い SQL 文を最適化するのが最善の方法です。SQL 文に group by 文や union クエリなどを大量に使用している場合、MySQL の使用率は確実に増加します。したがってSQL文を最適化する必要があります

SQL ステートメントの最適化に加えて、構成の最適化も実行できます。 mysql で show procedurelist を実行すると、次の結果が表示されます。

1. ディスクステータスのtmpテーブルへのコピーを大量にクエリする

明らかに、一時テーブルが大きすぎるため、MySQL は一時テーブルをハードディスクに書き込み、全体的なパフォーマンスに影響を与えます。

Mysql の tmp_table_size のデフォルト値は 16MB のみであり、現状では明らかに不十分です。
mysql> "%tmp%"のような変数を表示します。
+-------------------+----------+
| 変数名 | 値 |
+-------------------+----------+
| 最大tmpテーブル数 | 32 |
| スレーブロードtmpディレクトリ | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
セット内の 4 行 (0.00 秒)

解決策: 一時テーブルのサイズを調整する

1) MySQLターミナルコマンドを入力して変更し、グローバルを追加すると、次回MySQLに入ったときに有効になります。

mysql> グローバル tmp_table_size を 33554432 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

再度mysqlにログインする
mysql> "%tmp%"のような変数を表示します。
+-------------------+----------+
| 変数名 | 値 |
+-------------------+----------+
| 最大tmpテーブル数 | 32 |
| スレーブロードtmpディレクトリ | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+-------------------+----------+
セット内の 4 行 (0.01 秒)

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

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

MySQLを再起動する
[root@www ~]# /etc/init.d/mysqldを再起動します

2. show processlist; コマンドの出力には、実行中のスレッドが表示され、問題のあるクエリを特定するのに役立ちます。たとえば、次の結果になります。

ID ユーザー ホスト db コマンド 時間 状態 情報
207 ルート 192.168.1.25:51718 mytest スリープ 5 NULL
まず、各列の意味と目的を簡単に説明します。最初の列の id は、ステートメントを強制終了する場合に非常に便利なフラグです。ユーザー列には現在のユーザーが表示されます。ルート ユーザーでない場合は、このコマンドは権限範囲内の SQL ステートメントのみを表示します。ホスト列には、ステートメントが発行された IP とポートが表示されます。ハハハ、これを使えば問題のある発言をしたユーザーを追跡できるんですね。 db 列には、プロセスが現在接続されているデータベースが表示されます。コマンド列には、現在の接続によって実行されたコマンドが表示されます。通常は sleep、query、または connect です。時間列は、この状態の継続時間（秒単位）です。状態列には、現在の接続を使用している SQL ステートメントの状態が表示されます。これは非常に重要な列です。すべての状態については後で説明します。状態はステートメント実行における特定の状態にすぎないことに注意してください。たとえば、クエリなどの SQL ステートメントは、完了する前に、tmp テーブルへのコピー、結果のソート、データの送信などの状態を経る必要がある場合があります。情報列には、SQL ステートメントが表示されます。長さが制限されているため、長い SQL ステートメントは完全には表示されませんが、問題のあるステートメントを判断するための重要な基準となります。

よくある質問:

一般的に、スリープ状態の接続が多すぎると、MySQL サーバーのリソース (主に CPU とメモリ) が著しく消費され、MySQL がクラッシュする可能性があります。

解決：

MySQL 構成の my.cnf ファイルには、wait_timeout パラメータ設定があります。スリープ接続のタイムアウトを秒単位で設定できます。接続がタイムアウトすると、MySQL によって自然に終了します。
wait_timeout を大きく設定しすぎると、MySQL 内の多数の SLEEP プロセスが時間内に解放されず、システム パフォーマンスが低下するというデメリットがあります。ただし、このインジケーターを小さく設定しすぎると、「MySQL が消えた」などの問題が発生する可能性があります。
一般的に、wait_timeout を 10 時間に設定するのは良い選択ですが、場合によっては問題が発生する可能性があります。たとえば、CRON スクリプトがあり、2 つの SQL クエリ間の間隔が 10 秒を超える場合、この設定は問題になります (もちろん、これは解決できない問題ではありません。プログラム内で時々 mysql_ping を実行して、サーバーがまだ稼働中であることを認識し、wait_timeout 時間を再計算するようにすることができます)。

MySQL サーバーのデフォルトの「wait_timeout」は 28800 秒、つまり 8 時間です。つまり、接続が 8 時間以上アイドル状態になると、MySQL は自動的に接続を切断します。

ただし、接続プールは接続がまだ有効であると判断します (接続の有効性が検証されていないため)。アプリケーションが接続の使用を要求すると、次のエラーが発生します。

サーバーから正常に受信された最後のパケットは 596,688 ミリ秒前でした。
mysql> 'wait_timeout' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| 待機タイムアウト | 28800 |
+---------------+-------+
セット内の 1 行 (0.00 秒)

28800秒、つまり8時間です。

データベース接続 (java.sql.Connection) が wait_timeout 秒以内に待機状態のままの場合、MySQL は接続を閉じます。この時点では、Java アプリケーションの接続プールは、接続への参照をまだ合法的に保持しています。この接続を使用してデータベース操作を実行すると、上記のエラーが発生します。
MySQL グローバル変数 wait_timeout のデフォルト値を増やすことができます。

MySQLのマニュアルを確認すると、wait_timeoutの最大値は24日/365日（Windows/Linux）であることがわかりました。

たとえば、30日に変更します

mysql> グローバル wait_timeout を 124800 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

上記のMysqlがCPUを占有しすぎる場合の最適化方法（必読）に関する記事は、編集者が皆さんと共有する内容のすべてです。これが皆さんの参考になれば幸いです。また、123WORDPRESS.COMを応援していただければ幸いです。