MySQL InnoDBパフォーマンスチューニング

■漢の記事
http://enterprisezine.jp/dbonline/detail/3829
■Cactiの見方およびフラッシュの挙動について
https://www.slideshare.net/nobuhatano/osc2015-my-sqlr3
31ページがとってもわかりやすい。
■page_cleanerについてのトラブルシューティング
http://stackoverflow.com/questions/41134785/how-to-solve-mysql-warning-innodb-page-cleaner-1000ms-intended-loop-took-xxx
■innodb_lru_scan_deptについてのMySQL公式解説
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_lru_scan_depth

まとめ

• パフォーマンスが低下したら、まずはinnodb checkpoint ageの値が上昇していないか確認し、ログファイルからテーブルへの書き出し待ちが発生していないか確認しろ。
• innodb_buffer_pool_sizeは搭載メモリの7～8割割り当てろ
• innodb_log_file_sizeを大きくすること（4GB以上？）で、ログファイルからダーティページをテーブルにフラッシュする回数の低下と処理の平滑化を図れ。
• InnoDBバッファプールサイズ大きく、なおかつI/O負荷が高い場合は、innodb_lru_scan_depth をデフォルトの1024から256程度に減らすことで、1秒ごとのフラッシュ対象チェックの範囲を狭めることで、一回あたりのフラッシュ処理の軽減と、フラッシュ処理の平滑化を促す。
  ただし、一回あたりのフラッシュサイズは小さくなるので、ログサイズが小さくて、フラッシュのタイミングを遅らせることができない場合は、むしろ減らすことでパフォーマンスが低下すると思われる。
• あまりにもディスクI/Oが不足しているときは、innodb_flush_log_at_trx_commitを2に変更して、ログファイルへの書き込み時に、即座にディスクへの同期をしないようにする。
  デメリットとして、ディスクへの書き込み前にクラッシュした場合、データは消失する。つまりこのパラメーターはスレーブに設定するなら、最悪マスターから復旧できるのでリスクは少ないが、マスター側に設定するのはリスクが大きい。

■page_cleanerについて追記

忙しい人のための MySQL 5.7.6 DMR における InnoDB Flushing の変更点について

page_cleanerログについて。

page cleaner の flushing が重くてループが意図した間隔で回ってないときは、次のメッセージが error log に落ちるので、監視してもよいでしょう

ということらしい。
と、いうことはアダプティブフラッシングが重い時に出るログなのだろうか。
つまり、
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_flushing_avg_loops

InnoDB が以前に計算されたフラッシュ状態のスナップショットを保持する繰り返しの数です。これにより、適応型フラッシュがワークロードの変更に対応する速度が制御されます。この値を大きくすると、ワークロードが変化するにつれて、フラッシュ操作の速度が円滑かつ徐々に変化します。この値を小さくすると、適応型フラッシュがワークロードの変化にすばやく適応します。これにより、ワークロードが突然に増減した場合に、フラッシュアクティビティーが急増する可能性があります。

ということなので、更新が集中した際に、一気にフラッシュが発生して遅くなっている可能性があるので、innodb_flushing_avg_loops を増やすことで、フラッシュの頻度を急激に増加させないようにすれば、フラッシュ処理で動作が重くなるリスクを回避できるということ。
再発したら、innodb_flushing_avg_loops を増やすという選択肢も検討する。