MySQLが原因でサーバCPU負荷が発生したときの確認箇所とチューニング方法を紹介！

2019/01/22

この前、ITエンジニアの仕事でサーバのリプレイスを行いました。

サーバのリプレイスをして約1か月後にサーバで高負荷が発生し、webページが利用できないような障害状態になってしまいました。

障害となった高負荷はサーバ上のMySQLが起因で発生してましたが、結果としてMySQLのチューニングにより解決しました！

わたしのこの経験は、これからMySQLを使う人やMySQLの負荷で悩んでいる人に役立てる情報になると思いました。

これからMySQLを使ったサーバを構築する人や今すでに使っている人も参考にしてみてください！

1 サーバのCPU負荷が発生したときの状況
2 障害調査
3 MySQLのチューニング方法
- 3.1 チューニングする場所
- 3.2 設定ファイルの設置手順
4 MySQLのチューニング結果
5 おわりに

サーバのCPU負荷が発生したときの状況

今回発生した障害はサーバの高負荷によって、webページの表示すらできないような状態となりました。

サーバ環境は約1月前に下記の構成でリプレイスしてました。ミドルウェア等の構成やシステムも改変等はありません。

Webサーバ
CPU：2/メモリ：8GB/OS：CentOS6.10系
ミドルウェア
Apache：2.2系/Tomcat：7.0系/MySQL：5.5系

リプレイスしてから1か月ほどは特に問題なく動作していましたが、その後、連携している別サービスも同じ環境にリプレイスし、その翌日から急に負荷が上がり始めました。

CPUは負荷が掛かってきてからは画像の通り95%～100%の状態でした。

この状態ではwebページを表示することすらままならなかったので、一度サーバを再起動してみましたが、起動したとたんにまたCPU負荷が高くなりました。

CPU負荷が95％を超え始めてから30分以上経ち負荷が落ち着く気配もありません。

この時点ではとりあえず一刻も早くwebのシステムが利用できるようにする必要があったので、一旦サーバをシャットダウンさせてCPUを２つ追加して、計4CPUにスペックを上げてみました。

CPUを取り急ぎ増設したところ高負荷の状態は解消し、サービスは復旧しました。

この時点では今回の恒久的な対策ではないですが、サクッとサーバのスペックを拡張できるのはクラウドのいいところの一つですよね。

障害調査

CPU負荷は何が原因で発生していたか、というのはコンソール上で確認できました。

コンソールからTOPコマンドを実行するとMySQLで負荷がかかっていることが分かりました。

障害のときはグラフのようにCPUだけでなくロードアベレージも3以上で最大20を超えるくらい高くなっていました。

特に負荷がかかるのはwebサービスのログイン時です。

TOPコマンドを実行している状態でwebサービスにログインしてみるとCPU負荷が上がるのと、MySQLのプロセスがその負荷の原因であることが分かりました。

負荷の原因となっているのがMySQLということが分かったので、見直してみることとしました。

MySQLのチューニング方法

チューニングする場所

データベース自体は特別複雑なものではなく、SQLを実行してみても処理は特別遅くはありませんでした。

SQLが遅いわけてもなかったので、テーブルのindexを見直すよりMySQLの設定ファイルでチューニングしてみることとしました。

とりあえず、いきなりサービス中の環境で行うわけにもいかないので、検証環境で設定を変えてみます。

まずは設定ファイルの場所を特定しないといけません。設定ファイルはmy.cnfというファイルで、置いてある場所は以下のコマンドで確認できます。

[root@sukimapress mysql]# mysql --help | grep my.cnf

結果はこのように出てきました。

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

my.cnfは結果のとおり、赤字の左側から順番に読み込まれます。

結果の左から順番に設定ファイルを探してみましたが、なぜかコマンド結果のどこにもありません。

一応、findコマンドでもmy.cnfを探しましたが、ファイルがないのです。

もともとサーバとシステムを構築したところは海外の会社らしく、それも8年か9年前の昔のことのうえでドキュメントがほとんど残ってすらなかったので確認しようがありませんでした。

my.cnfファイルがないときはサンプルからコピーします。サンプルは以下の赤字の場所にありました。

[root@sukimapress ~]# cd /usr/share/mysql/
[root@sukimapress mysql]# ls -l
total 1308
～略～
-rwxr-xr-x 1 root root 4697 Oct 12 2011 my-huge.cnf
-rwxr-xr-x 1 root root 19779 Oct 12 2011 my-innodb-heavy-4G.cnf
-rwxr-xr-x 1 root root 4671 Oct 12 2011 my-large.cnf
-rwxr-xr-x 1 root root 4682 Oct 12 2011 my-medium.cnf
-rwxr-xr-x 1 root root 2846 Oct 12 2011 my-small.cnf

サンプルは一つだけでなく、以下の5パターンあります。それぞれサーバのメモリスペックで判断して、どれを使うか決めます。

my-innodb-heavy-4G.cnf：4GB
my-huge.cnf：1GB～2GB
my-large.cnf：～512MB
my-medium.cnf：～128MB
my-small.cnf：～64MB

この設定ファイルがなかったので、おそらく設定はデフォルトの状態とわたしは推測しました。

最初にサーバを構築したのが8年か9年ほど前なので、その当時のサーバスペックは今に比べてかなり低いです。

今のスペックは前述のとおりメモリは8GBなので、今のサーバ上ではメモリが正しく割り当てられていない可能性が高いです。

よって、設定ファイルのサンプルは「my-innodb-heavy-4G.cnf」を使うこととしました。

設定ファイルの設置手順

設定ファイルのサンプルが決まったので、あとはそのファイルを置いて検証してみます。

設定ファイルを置くといってもサンプルの「my-innodb-heavy-4G.cnf」をそのまま置いて利用できるわけではありません。

ファイルの場所を確認したときにでてきた赤字の場所に、my.cnfのファイル名で置かないといけないのです。

[root@sukimapress mysql]# mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

今回は結果のとおり、/etc/の直下にmy.cnfを置くこととしました。

/etn/my-innodb-heavy-4G.cnfをコピーしたら、my.cnfにリネームします。

[root@sukimapress mysql]# mv my-innodb-heavy-4G.cnf my.cnf

ここで、設定ファイルを有効にして正しく反映させたいので以下の手順で進めます。