こんにちわ。せじまです。 Cherry Trail が出たら艦これ用タブレット買い換えるべきか、思案している今日このごろです。

5.7.6 での InnoDB Adaptive Flushing の重要な変更を三行をまとめると

• redo log の更新頻度も考慮されるようになりました。更新頻度に比例して flush される dirty page の量が増減するようになりました。
• innodb_io_capacity_max 上げ過ぎないほうが良いでしょう。場合によっては innodb_flushing_avg_loops も見なおしてよいでしょう。
• innodb_buffer_pool_instances >= 2 のとき、dirty page が多い instance ほど、多くの dirty page が flush されるようになりました。

詳細な内容についてご興味のある方は続きをどうぞ。

※5.6以前の InnoDB Adaptive Flushing について明るくない方は、先ずはこちらの資料を読んでいただければ、参考になるかと思います。

MySQL 5.7.6 DMR

先日、 InnoDB Deep Talk #2（仮）なるイベントを開催させていただきました。その際、木下さんの資料に、 5.7.6 以降で InnoDB Adaptive Flushing の最適化が入るとありましたが、先日出た 5.7.6 に入ってました（github.comでInnoDBのソースコードを引用できるとは便利な時代になったものです）。昨年の5月にMySQL Server Blog で Future improvements と書かれていた件ですね。このpatchに関する説明はこちらのWorkLogにあるのですが、せっかくなので細かいところを見てみましょう。

MySQLユーザにとって特に興味深いのは、buf_flush_wait_flushed() の追加と、 page_cleaner_flush_pages_recommendation() における次のdiffでしょう

buf_flush_wait_flushed()

transaction log（redo log）に書き込む前に、 log_free_check() で log の空きがあるか確認し、空きがなければ dirty page を flush して Last Checkpoint at を進め、空きを確保する必要があります。

具体的には、max_modified_age_sync(ざっくり75%くらい) 以上 log を使っていた場合、この loop がぐるぐるまわる可能性があったようですが、5.6の log_preflush_pool_modified_pages() は buf_flush_list() を呼ぶだけでした。木下さんが仰るところの 同期flush（の嵐）や 各ユーザースレッドが各々flushバッチを自分が成功するまでリトライとのいうのはこのへんでしょう。

ユーザースレッドがflushするより page cleaner に flush してもらう方が効率良いので、5.7.6からは、log_preflush_pool_modified_pages() ではbuf_flush_wait_flushed()呼んで page cleaner が flush してくれるのを待つようになったようです。

※今までMySQL5.6以前のこのへんの詳細な挙動は把握してなかったのですが、今回のpatchを読んでいて気が付きました。 5.6以前で、transaction log の使用率が75%くらいに達したあたりで猛烈に刺さり始めたとしたら、このあたりが原因でしょう。

page_cleaner_flush_pages_recommendation()

page_cleaner_flush_pages_recommendation() で興味深い修正点は二点あります。

一つ目は flush する dirty page の数を決めるときに、 sum_pages_for_lsn を考慮するようになったことでしょう。 sum_pages_for_lsn は、ざっくりいうと、 redo log の使用率ではなく、 redo log の更新頻度によって値が増減します。たくさん log が書かれると sum_pages_for_lsn が伸びるので、それだけ flush される page の数が増えるようです。WorkLogでいうところの

The factor which considers "How many pages should be flushed to progress the LSN age at the time" should added to the adaptive flushing algorithm.

ですね。これにより、「高頻度で redo log に書いてるんだけど、 log の使用率が低いので innodb_io_capacity_max で指定しただけ flush してくれない」という事態が回避できるようになるでしょう。

二つ目は、 innodb_buffer_pool_instances が 2以上だったときの挙動です。5.7.6以降、 dirty page の多い instance は flush を多く、少ない instance は flush を少なくするように調整するようになりました。具体的には、 sum_pages_for_lsn を求めるとき、 page_cleaner->slots[i].n_pages_requested も計算するようになりました。 page_cleaner->slots が buffer pool の instance ごとに対応しているので、これによって instance ごとの flush の頻度が変わります。5.6.6以降、innodb_buffer_pool_instances のデフォルトは 8 になっていましたが、実は instance ごとに dirty page の偏りができていた可能性がありました。それが 5.7.6 以降、改善されるもようです。

innodb_flushing_avg_loops を見直してもよいかも

5.7.6 以降では、 InnoDB Adaptive Flushing のアルゴリズムが変わったので、 redo log がたまらなくても、 flush の頻度が上がるケースがでてきました。木下さんは資料でinnodb_io_capacity_max を大きめにしている場合注意と明記してくださっています。 innodb_io_capacity_max を上げすぎなければ、更新処理がバーストしても耐えられると思いますが、数分間だけしかバーストしないとわかっているのであれば、 innodb_flushing_avg_loops を大きめにしてもよいかもしれません。

innodb_flushing_avg_loopsはMySQL5.6で追加されましたが、いままでここをチューニングされたことがある方は少ないのではないでしょうか。個人的に、5.6の頃はそれほど効果があるパラメータではなかったと思います。しかし、 5.7.6 では、 srv_flushing_avg_loops(innodb_flushing_avg_loops) 秒ごとに更新される lsn_avg_rate によって、 sum_pages_for_lsn の値が変わってきます。
ざっくりいうと、次のような場合、innodb_flushing_avg_loops をデフォルト30 から上げてもよいでしょう。

• 数分間だけ更新処理が集中するような場合（例：ゲームのイベント開始 or 終了時、メンテナンス明けなどでアクセスが集中する場合）
• 利用しているリソースモニタリングツールの精度が分単位だった場合（30秒間隔でスパイクが発生したとしても、モニタリングツールの精度が足りなくて記録に残せない）

innodb_flushing_avg_loops は SET GLOBAL で変更可能なのがありがたいところです。

WorkLog に書いてないけれど

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

さいごに

5.6がリリースされたころ、5.1や5.5と InnoDB Adaptive Flushing の挙動があまりにも変わったので、当時は「しょーじき困ったな」と思ったものですが、5.7.6 では、5.6の頃と比べて最小限の修正で、 InnoDB Adaptive Flushing がより賢くなりました。5.7のGAが楽しみですねー