MySQL サーバーのパフォーマンスチューニングを my.cnf の設定項目から解説します。

状況

僕が担当したサービスが　秒間5リクエスト でサーバーダウン。
今までは ただ波が去るのを待っていた とのこと。
そこで設定変更、およびチューニングをした内容を紹介したいと思います。

参考資料

builderscon tokyo 2017 で登壇した際のスライドです。合わせて参考になれば幸いです。
初めてのMySQLサーバーチューニング -データベースは怖くない！-

問題

DBサーバーがダウンしてサービスが止まり、その時間だけ売り上げがゼロ。
事業としてだけでなく、関連する全てに迷惑をかけていました。
僕が担当するまでは、打つ手もなくただ波が去るのを待っていました。

経緯

常識的な範囲のリクエスト数で、DBサーバーがダウンする事象が発生。
swapを設定したら一瞬で食い潰した、というところまでヒアリング。
いくつかあると思われる原因のうち、もっとも根本的な「my.cnf」の設定を徹底的に見直します。

対象

DB専用サーバーが対象です。
構成は以下になります。

名称	内容
サーバー	AWS EC2
MEM	8GB
Storage	32GB
MySQL	5.5系

※RDSは使っていません。

負荷を見てみる

DBサーバーの負荷状況を見てみます。
当時の監視ツールの画像がないのですが、以下の状況でした。

LA(Load Average)が突き抜けている
リクエスト数は「常識的に考えて」それほどでもない
メモリの使用量にあまり変化がない
swapはしていない
ストレージ容量を結構食っている

WEBサーバーから見れば、処理待ちのままプロセスが処理されていない典型的なパターンだったと思います。
DBサーバーとしては、LAに対し、メモリの使用量があっていないように思われました。

仮説

上記の状態から、仮説を立てます。

スロークエリ が頻発しているのではないか
メモリ が正しく割り当てられていないのではないか
各種ログ の設定が適切ではないのではないか

仮説を検証することで、対策をしていきます。

設定を見直す

上記の仮説の設定は、MySQLの設定ファイルである「my.cnf」に記述されています。
そこで「my.cnf」を見直してみます。

my.cnf の内容

結論、ほぼデフォルトで構成されていました。

MySQLの設定を根本から見直す

ことがここで決定されました。

スロークエリの調査

スロークエリが頻発していれば、当然パフォーマンスに大きな影響を与えます。
どうしても集計処理に必要な場合などを除き、スロークエリは撲滅すべきです。

スロークエリの内容

/var/log/slow.log を見てみる。
my.cnf に設定されていないので、クエリが保存されていない(´・ω・`)

もっとも手っ取り早いのが実際に実行されたクエリを見ること。
それができないとなると・・・MySQLの各種データを調べ、そこから類推することにします。

プログラムで実行されたクエリを実行する

パフォーマンスに影響を与えているプログラムでは、１０個のクエリが発行されていました。
そのうち1つが index設定もれ でスロークエリに。

index を追加

alter table table_name add index index_name(culumn_name);

indexを設定したらあっさり改善。次に行きましょう。

MySQL診断ツール (MySQL Tuner) を使う

MySQL Tuner を使用しました。

https://github.com/rackerhacker/MySQLTuner-perl

GNU GPLライセンスなので無料で利用できます。

診断結果

以下の結果になりました。

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_file_per_table=ON
innodb_buffer_pool_size (>= 12G) if possible.
innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible.

では順番に見ていきましょう。

Enable the slow query log to troubleshoot bad queries

スロークエリが記録されてないよ。
ということなので、おとなしく設定を追加します。

[mysqld]

# スロークエリの出力設定
slow_query_log=ON

# スロークエリと判定する秒数
long_query_time=3

# スロークエリログの場所
log-slow-queries=/var/log/slow.log

スロークエリの秒数は、今後絞っていく想定です。
管理画面に影響を与えるので、いったん３秒としています。
※スロークエリのログファイルはあらかじめ空で作っておき、パーミッションを正しく設定しておいたほうがいい場合もあります。

Set thread_cache_size to 4 as a starting value

スレッドキャッシュが無効になっていることで診断されたものです。
thread_cache_size (start at 4)
でも同じことを言われています。

まずは4つから始めてみよう、とのことですが、今までの経緯を考えて、以下の設定を追加します。

[mysqld]

# スレッドキャッシュ保持最大数
thread_cache_size=100

MySQLはクライアントからの接続ごとに、スレッドを生成し、破棄します。
このスレッドをキャッシュにしておくことで、別のクライアントからの接続時の負荷を軽減できます。
キャッシュするスレッド数を定義することで進めます。

query_cache_size (>= 8M)

クエリキャッシュが無効になっています。
同一のクエリが複数のリクエストから実行される場合、結果は同じなのでキャッシュから返すべきです。
が、その設定が無効。
これではどんなSQLも毎回DBにアクセスしなければなりません。

こちらも設定を追加します。

[mysqld]

# クエリキャッシュ最大サイズ
query_cache_limit=16M

# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M

# クエリキャッシュのタイプ(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
query_cache_type=1

ただし、高負荷の場合、かつクエリキャッシュヒット率が低い場合、設定をOFFにしたほうがいい場合もあります。
運用をしっかり監視して、調整していきます。
今回はONにすることで進めます。

innodb_file_per_table=ON

InnoDB のデータ領域を「テーブル単位にしたほうがいいよ」ということです。
以下の設定を追加します。

[mysqld]

innodb_file_per_table=ON

InnoDBの場合、データファイル、ログファイルがストレージ上に作成されます。
データファイルの管理方法には2通りあります。「共有領域」で管理する方法と、「テーブル個別領域」で管理する方法です。

共有領域
全テーブルを一括で管理する領域。1つのテーブルの1レコードの変更でも、領域全体に更新がかかる。

テーブル個別領域
テーブルを１つずつ個別に管理する領域。更新対象のテーブルの領域にのみ、更新がかかる。

共有領域は1つの場所で全テーブルを管理しているのに対し、テーブル個別領域はテーブル個別に領域を割り当てています。
共有領域は全テーブルが集まっていますから、たとえ1レコードだとしても更新に時間がかかります。

注意点

テーブル個別の設定をしても、今までのデータがテーブル個別に分割されるわけではありません。
設定後にCREATEされたテーブルには反映されますが、データすべてに対応するには、各テーブルをデータごと流し込む必要があります。
今回の対応では、メンテナンス時間を設け、いったんスキーマ全体をdumpし、リストアすることで、全データに反映するという手段を用いました。

innodb_buffer_pool_size (>= 12G) if possible.

データとindexをメモリにキャッシュしておく領域のサイズを設定しなさいよ、とのこと。
ここで設定した値までは、メモリにキャッシュすることができるよ、その分は高速に動くよ、という解釈で間違いではないと思います。
推奨のサイズは物理メモリの8割、と言われていますから、以下の設定を追加します。

[mysqld]

# InnoDBのデータとインデックスをキャッシュするバッファのサイズ(推奨は物理メモリの8割)
innodb_buffer_pool_size=6G

最適な結論としては、全データサイズを指定することになります。
が、そんなに潤沢にメモリ設定はしないでしょうし、テーブル構造やプログラム、設定を最適化すれば、全データをメモリに載せる必要はないとも言えます。
パワーは最後に頼るべきものとして、設計はちゃんとしておきたいですね。

ちなみにこの設定、読み込みだけでなく、書き込み時にもパフォーマンス向上が見込まれると
MySQL公式
には記載されているようです。

innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible.

InnoDBの更新ログを記録するディスク上のファイルを、innodb_buffer_pool_sizeの4分の1にしてね、とのこと。
基本は、1MB以上、4GB未満(MySQL5.5)、とMySQL公式に記載があります。
innodb_log_files_in_groupの数値も考えて、今回は4分の1より少し少なめの設定にします。
適切かどうかは随時見直しをしていきたいです。

[mysqld]

# InnoDBの更新ログを記録するディスク上のファイルサイズ(innodb_buffer_pool_sizeの4分の1程度)
innodb_log_file_size=1G

InnoDBの更新ログとは、文字どおり「commit待ちのログを記録する」ものです。
設定によってはストレージ容量を圧迫します。
「ログファイルだし容量でかいから消しちゃえ」とうっかり消しちゃう声を聞きますが、これ手で消しちゃダメです。

DBの実データは分散されて配置されているので、更新は時間がかかります。
InnoDBの更新ログはシーケンシャルなので、更新が速いです。
なので、いったんInnoDBの更新ログに積んでおいて、順次DBの実データに反映、という方法で更新します。

「InnoDBのログとテーブルスペースの関係」というタイトルで、
漢（オトコ）のコンピュータ道
に詳述されています。

ここまでのまとめ

スロークエリが頻発しているのではないか

発生していた。ログも記録されていなかったので設定を追加した。

メモリが正しく割り当てられていないのではないか

クエリキャッシュ、スレッドキャッシュ、データとindexのキャッシュが割り当てられていなかった。
実際のデータ容量を鑑み、適切な値を計算して設定を追加した。

ログの設定が適切ではないのではないか

InnoDBの更新ログの設定がされていなかった。
更新処理に影響を与えていた可能性があるので、適切な値を計算して設定を追加した。

データが共有領域に保存されていた

テーブル個別の領域に変更し、更新対象を絞った。

これらを解消すべく、my.cnf の設定を変更してきました。

my.cnf 各種設定内容

今回の設定見直しで更新した my.cnf は以下の内容になります。
※上述した以外にも、設定した項目はいくつかあります。

my.cnf

[mysqld]

# #################
# innodb
# #################

# InnoDBのデータとインデックスをキャッシュするバッファのサイズ(推奨は物理メモリの8割)
innodb_buffer_pool_size=6G

# InnoDBのデータ・ディクショナリーや内部データ構造情報を持つバッファのサイズ
innodb_additional_mem_pool_size=20M

# コミットされていないトランザクションのためのバッファのサイズ
innodb_log_buffer_size=64M

# InnoDBの更新ログを記録するディスク上のファイルサイズ(innodb_buffer_pool_sizeの4分の1程度)
innodb_log_file_size=1G

# データやインデックスを共有ではなくテーブル個別に保存する
innodb_file_per_table=1

# #################
# query cache
# #################

# クエリキャッシュ最大サイズ
query_cache_limit=16M

# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M

# クエリキャッシュのタイプ(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
query_cache_type=1

# #################
# slow query log
# #################

# スロークエリの出力設定
slow_query_log=ON

# スロークエリと判定する秒数
long_query_time=3

# スロークエリログの場所(あらかじめ作成しておく必要あり)
log-slow-queries=/var/log/slow.log

# #################
# etc
# #################

# インデックス未使用でのJOIN時に使用するバッファ
join_buffer_size=256K

# クライアントからサーバーに送信できるパケットの最大長
max_allowed_packet=8M

# フルスキャンのレコードバッファ
read_buffer_size=1M

# キーを使用したソートで読み込まれた行がキャッシュされるバッファ
read_rnd_buffer_size=2M

# ソート時に使用されるバッファ
sort_buffer_size=4M

# MEMORYテーブルの最大サイズ。このサイズを超えたMEMORYテーブルはディスク上に作成
max_heap_table_size=16M

# スレッド毎に作成される一時的なテーブルの最大サイズ。スレッドバッファ
tmp_table_size=16M

# スレッドキャッシュ保持最大数
thread_cache_size=100

# コネクションタイムアウト時間
wait_timeout=30

反映手順

設定を有効にするため、以下の手順でメンテナンスを実施しました。
再起動の際に問題となる部分、前もって実施することなどは、別エントリーにしたいと思います。

プログラムをメンテナンスモードにし、DBへのアクセスを停止
DBをdump
更新後のmy.cnfに差し替え
mysql 停止
各ログファイルなどの削除
mysql 開始
- 設定内容に間違いや、割り当てメモリ数がオーバーした場合などは、起動でエラーになります
- エラーパターンとログの見方と解決方法は別エントリーにしたいと思います。
DBのリストア
- 全テーブルに適用させるために、mysqldump でDB全体をdumpし、DB削除した上でdumpを流し込む、という作業を実施します
動作検証
メンテナンスモード解除
経過観察

実際には、サービスを停止する時間の事前の調整、アナウンス、問題が発生した場合の切り戻し手順など、いろいろ決定して進めていますが、当エントリーでは省略します。

結果

結論、 大幅にパフォーマンスは改善 されました。

メモリが適切に、積まれた分だけ稼働するようになった
テーブル個別の領域にすることで、更新系が早くなった
集計処理以外のスロークエリがなくなった
【2017/03/20 追記】クエリキャッシュのヒット率が 83% になった

ざっと計測したところ、 秒間200リクエスト は問題なく処理できました。
詳しい検証結果と方法は、次回のエントリーで順次書いていきたいと思います。

課題

最重要課題であるパフォーマンス改善は、結論として達成できています。
が、my.cnf を「計算上」適切に設定しただけです。
まずは初手。
以下の課題を認識して、順次検証していきます。

メモリ使用状況の検証

各種値を定期的に見直し、検証を続けていきたいです。
何らかのスクリプトを書くと思います。

ステージング環境の用意と負荷テスト

サーバー台数、スペック、プログラム、データをすべて本番と同一にしたステージング環境を用意しました。
簡単なスクリプトで並列処理を実行し、どこで落ちるかを見定めつつ、負荷テスト実施したいと思います。

管理画面のスロークエリ対策

スロークエリログを記録したことで、どのクエリが遅いか確実にわかるようになりました。
管理画面の集計処理や、膨大なログの絞り込み処理など、必要な機能で発生するスロークエリ、no index クエリの対策を実施します。

プログラム上の処理の切り分け

プログラムを改修することで、WEBとして最低限しなければならない処理、DBとして後回しにできる処理を分け、WEBとDBで最適化を図ります。

DBの状態監視

DBのデータの増え方、スロークエリレポートなど、デイリーで状態を監視する仕組みを導入していきたいです。

全体のまとめ

基本的な設定は「最初にちゃんとやろう！」

今回つくづく思ったことです。
問題が発生する前に防げた内容もたくさんありました。
細かい設定も積み重なると大変なことになるので、最初にやっておきたいですね。

スロークエリは監視しよう！

サービスの各機能の特徴を鑑みた上で、スロークエリを監視・撲滅する機構を作りましょう！

DB設計は計画的に！

求められた機能と求められたパフォーマンスに応えられるDB設計をしましょう。
また、なんでもかんでもDBに貯めればいいってもんじゃないですし、なんでもかんでもindexにすればいいわけでもありません。
データの性質を見極めて、適切なデータをDBに反映しましょう。

プログラム設計は（以下略

高負荷時、多量のデータを扱う場合など、「サービスが最大の負荷を受けたとき」を常に考えて実装しましょう。

所感

たった１本のプログラムのパフォーマンス解析から、初手でここまで対応するとは思いませんでした。
が、調べれば必ずわかりますし、対策すれば結果は出ます。
アプリケーションばかり作っていた僕ですが、DBやサーバー構成周りに触れることで、また新たな「技術の楽しさ」を発見しました。

アプリエンジニアがアプリだけ作れればいいってもんじゃあありません。
男坂も連載が始まったと聞きます。

「オレはようやくのぼりはじめたばかりだからなこのはてしなく遠いインフラ坂をよ・・・」

これからも登り続けていこうと思います。

続編

MySQLパフォーマンスチューニング -クエリキャッシュ適用状況の確認-
クエリキャッシュの効果を測定しています。

続々編

[MySQL レプリケーション設定 -手順・各種ステータスの詳細とトラブルシューティング-]
(http://qiita.com/mamy1326/items/a337ed0fa22104b08f3d)
マスターが安定したので、スレーブを作ってレプリケーションを実施しました。

2017/03/21 追記

mysqlをdisる会
こちらの方がより casual & cooooooooooooooooooooooooool!!!!
MySQLの話で笑いをこらえて読んだのは初めてです。

2017/10/04 追記

対応当時は MySQL5.5 でしたが、現在はローカル環境で 5.7系 を趣味で触り倒してます。
また、人前で登壇させて頂く機会を何度か頂けましたので、参考資料として最新のスライドを紹介させて頂きます。
初めてのMySQLサーバーチューニング -データベースは怖くない！-

MySQLパフォーマンスチューニング -my.cnfの見直し-

状況