MySQL の CPU 負荷が高い問題のトラブルシューティング

MySQL による CPU 負荷の上昇

今日の午後、MySQL によってサーバーの負荷が高くなる問題を発見しました。この問題の背景は次のとおりです。

新しいサーバーに、新しい MySQL インスタンスが作成されました。サーバー上の MySQL プロセスは 1 つしかありませんでしたが、CPU 負荷は高いままでした。top コマンドクエリの結果は次のようになりました。

[dba_mysql@dba-mysql ~]$ トップ 
トップ - 17:12:44 104 日、20 分、2 ユーザー、負荷平均: 1.06、1.02、1.00
タスク: 合計 218、実行中 1、スリープ中 217、停止中 0、ゾンビ 0
CPU0: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU1: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU2: 0.0%us、0.0%sy、0.0%ni、100.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU3: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU4: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU5: 0.0%us、0.0%sy、0.0%ni、100.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU6: 100.0%us、0.0%sy、0.0%ni、0.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU7: 0.0%us、0.0%sy、0.0%ni、100.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
メモリ: 合計 16318504k、使用済み 7863412k、空き 8455092k、バッファ 322048k
スワップ: 合計 5242876k、使用済み 0k、空き 5242876k、キャッシュ済み 6226588k

  PID ユーザー PR NI 仮想リソース SHR S %CPU %MEM 時間+ コマンド                                     
 75373 mysql 20 0 845分 699分 29分 S 100.0 4.4 112256:10 mysqld                                     
 43285 ルート 20 0 174m 40m 19m S 0.7 0.3 750:40.75 領事                                      
116553 ルート 20 0 518分 13分 4200 S 0.3 0.1 0:05.78 ファルコンエージェント                                   
116596 誰もいない 20 0 143分 6216 2784 S 0.3 0.0 0:00.81 パイソン                                      
124304 dba_mysq 20 0 15144 1420 1000 R 0.3 0.0 0:02.09 トップ                                       
   1 ルート 20 0 21452 1560 1248 S 0.0 0.0 0:02.43 初期化

上記の結果から、8 コア CPU のうち、負荷が 100% になっているのは 1 つのコアのみで、他のコアはすべて 0% であることがわかります。CPU 使用率によるソートの結果からも、mysqld プロセスがより多くの CPU を占有していることが分かります。

この問題に遭遇したことは一度もありませんでした。最初に思ったのは、CPU リソースを常に占有している遅いクエリなど、ビジネスレベルの問題があるのではないかということでした。そこで、MySQL にログインし、show processlist を使用して現在のプロセスを表示しました。いくつかの更新操作を除いて、他の SQL ステートメントは実行されていないことがわかりました。そこで、スローログをもう一度確認してみたところ、スローログ内の SQL 文の実行時間が非常に短いことがわかりました。そのほとんどは、インデックスを使用していないことが原因でした。ただし、スキャンされたレコード数は非常に少なく、数百行しかありませんでした。ビジネスレベルでは問題がないように見えました。

ビジネスレベルの問題を排除したので、データベースレベルの問題を見てみましょう。バッファープールを確認すると、値は次のようになります。

[email protected]:(なし) 17:20:35>>'%pool%' のような変数を表示します。
+-------------------------------------+----------------+
| 変数名 | 値 |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 5242880 |
| innodb_buffer_pool_dump_at_shutdown | オン |
| innodb_buffer_pool_dump_now | オフ |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | オフ |
| innodb_buffer_pool_load_at_startup | オン |
| innodb_buffer_pool_load_now | オフ |
| innodb_buffer_pool_size | 5242880 |
| thread_pool_high_prio_mode | トランザクション |
| スレッドプールの高優先度チケット | 4294967295 |
| スレッドプールアイドルタイムアウト | 60 |
| スレッドプールの最大スレッド数 | 100000 |
| スレッドプールオーバーサブスクライブ | 3 |
| スレッドプールサイズ | 8 |
| スレッドプールのストール制限 | 500 |
+-------------------------------------+----------------+
セット内の行数は 17 です (0.01 秒)

この結果から、バッファプールのサイズが 5M しかないことがわかります。これは間違いなく問題です。通常、オンライン環境のバッファプールは 1G 以上です。そこで、my.cnf 構成ファイルを確認したところ、このインスタンスが起動されたときに、innodb_buffer_pool_size 設定が 0M になっていることがわかりました。はい、その通りです。0M でした。ここで、別のパラメータについて言及する必要があります。innodb_buffer_pool_size のサイズは、innodb_buffer_pool_chunk_size のサイズと同じであることがわかります。チャンクの概念はメモリブロックです。つまり、バッファプールが適用されるたびに、「メモリブロック」単位で適用されます。バッファプールには複数のメモリブロックが含まれているため、バッファプールのサイズはチャンクサイズの整数倍である必要があります。

innodb_buffer_pool_chunk_size の値自体は 5M なので、これを 0M に設定すると、サイズは自動的に 5M の倍数に設定され、innodb_buffer_pool_size の値は 5M になります。

バッファプールの値は比較的小さいため、これを 1G に変更して、この問題が引き続き発生するかどうかを確認します。

[email protected]:(なし) 17:20:41>>グローバルinnodb_buffer_pool_size=1073741824を設定します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
[email protected]:(なし) 17:23:34>>'%pool%' のような変数を表示します。         
+-------------------------------------+----------------+
| 変数名 | 値 |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 5242880 |
| innodb_buffer_pool_dump_at_shutdown | オン |
| innodb_buffer_pool_dump_now | オフ |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | オフ |
| innodb_buffer_pool_load_at_startup | オン |
| innodb_buffer_pool_load_now | オフ |
| innodb_buffer_pool_size | 1074790400 |
| thread_pool_high_prio_mode | トランザクション |
| スレッドプールの高優先度チケット | 4294967295 |
| スレッドプールアイドルタイムアウト | 60 |
| スレッドプールの最大スレッド数 | 100000 |
| スレッドプールオーバーサブスクライブ | 3 |
| スレッドプールサイズ | 8 |
| スレッドプールのストール制限 | 500 |
+-------------------------------------+----------------+
セット内の行数は 17 です (0.00 秒)

操作は上記の通りです。このようにして、バッファプールの値を 1G に変更します。設定した値は 1073741824 ですが、実際の値は 1074790400 になります。この理由は、上で述べたように、チャンクサイズ値の影響です。

この時点で、top コマンドを使用して CPU 使用率を観察します。

[dba_mysql@dba-mysql ~]$ トップ
トップ - 22:19:09 104 日、5:26、ユーザー 2 人、負荷平均: 0.45、0.84、0.86
タスク: 合計 218、実行中 1、スリープ中 217、停止中 0、ゾンビ 0
CPU0: 0.3%us、0.3%sy、0.0%ni、99.3%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU1: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU2: 1.0%us、0.0%sy、0.0%ni、99.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU3: 1.0%us、0.0%sy、0.0%ni、99.0%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU4: 0.3%us、0.3%sy、0.0%ni、99.3%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU5: 0.3%us、0.0%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU6: 0.0%us、0.3%sy、0.0%ni、99.7%id、0.0%wa、0.0%hi、0.0%si、0.0%st
CPU7: 0.7%us、0.0%sy、0.0%ni、99.3%id、0.0%wa、0.0%hi、0.0%si、0.0%st
メモリ: 合計 16318504k、使用済み 8008140k、空き 8310364k、バッファ 322048k
スワップ: 合計 5242876k、使用済み 0k、空き 5242876k、キャッシュ済み 6230600k

  PID ユーザー PR NI 仮想リソース SHR S %CPU %MEM 時間+ コマンド                                     
 43285 ルート 20 0 174m 40m 19m S 1.0 0.3 753:07.38 領事                                      
116842 ルート 20 0 202分 17分 5160 S 1.0 0.1 0:21.30 パイソン                                      
 75373 mysql 20 0 1966分 834分 29分 S 0.7 5.2 112313:36 mysqld                                      
116553 ルート 20 0 670分 14分 4244 S 0.7 0.1 0:44.31 ファルコンエージェント                                   
116584 ルート 20 0 331分 11分 3544 S 0.7 0.1 0:37.92 python2.6                                    
   1 ルート 20 0 21452 1560 1248 S 0.0 0.0 0:02.43 初期化

CPU 使用率が下がっていることがわかります。偶発的な現象を防ぐために、バッファプールのサイズを元の 5M の値に戻しましたが、以前の問題が再現しました。つまり、大きなバッファプールを設定することは確かに解決策です。

この時点で問題は解決しましたが、この問題の背後にあるいくつかの事柄については考える価値があります。なぜバッファプールが小さいと、CPU の 1 つの使用率が 100% になるのでしょうか。

ここで、考えられる原因の 1 つは、5M のバッファプールが小さすぎるため、業務 SQL がデータを読み取るときにディスクと頻繁にやり取りすることになります。ディスク速度が比較的遅いため、IO 負荷が増加し、CPU 負荷が高くなりすぎます。1 つの CPU だけが比較的高い負荷を持ち、他の CPU はほぼ 0 である理由については、この問題を確認する必要がある可能性があります。知っている方がいらっしゃいましたら、お知らせください。

上記は、MySQL CPU 高負荷問題のトラブルシューティングの詳細な内容です。MySQL CPU 高負荷の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: