SQLを繰り返し実行したら段階的に応答速度が上がった話

MySQL Casual Advent Calendar 2016 - Qiitaの6日目の記事です。

AdventCalendar自体初参加でドキドキ、してたら、成り行きで2日連続。
コレ用のきれいなエビデンス取れるような環境要していなかったので、普段より荒っぽいですが、Casualな感じで失礼します。

大きなテーブルを繰り返しSELECTしてたら、挙動が変わったんですよ。

バッファに載っているなら載っているで早いだろうし、載っていいなら最初ガッツリ遅くて、次からグイっと速くなるだろうと思っていたんですよ。

バッファに載りきらないなら、何回やっても遅いだろうと思っていたんですよ。

で、ちょいと計測的なことをやってた関係で、同じSQLを何度か叩いて平均、中央を見ようと思っていたんです。

そしたら、
45.71秒、44.90秒、24.44秒、13.32秒、13.12秒・・・
と、段階的に応答時間が短縮していきました。

えっ(ﾟ◇ﾟ；)

迷わずmysql-casualのSlackへ。
優しいお兄さん達が丁寧に教えてくれました！

原因はバッファプールの「Old」と「Young」

innodb_buffer_pool_sizeは2GB用意していて、テスト対象のテーブルは1.6GBだったので、まあ、運悪くてもちょっと溢れる程度だろうと思っていたのですが、そこが過ちでした。

MySQLのバッファプールは、「Old」と「Young」に分けて管理されていると。

名前からして、LRUリストの新しいやつ、古いやつってイメージは分かるんですが、バッファ上に無いデータを取ってきた時、最初は「Old」にデータを格納するんだそうです。

で、一定時間経過すると、Youngの方に移すようになってます。
この機構により、「大量にデータを読み込むけど、参照頻度が低い」といったSQLによってバッファが汚染されるのを防ぐんだそうです。

このOldとYoungの比率を決めるパラメータが「innodb_old_blocks_pct」で、OldとYoungの割合におけるOld分を指定します。

そのデフォルトが「37（％）」

37％！
2GBの37％＝0.74GB！

最初にバッファに乗るのはコレだけ。
そのSQLでOldバッファに乗らないデータを取ってくる場合、（Youngも使えない間は）何回叩いてもDiskIOが発生してしまう！

SQLの1発目で、Oldに乗る範囲だけOldに乗る。
OldからYoungに移ったあと、もう一度SQL叩いたところで、空いたOldに残りからまた0.74GBが乗る！
YoungとOldにほぼ乗ったところで、もう一回SQL叩いたところで、ほぼDiskIO不要になる！

そんな感じで、「段階的に」応答速度が上がる、という事象として現れていました。

innodb_old_blocks_pctをいじる

このパラメータ、「5～95」までの値を取ることができるので、試しに「95」まで引き上げてみました。

結果、
35.87秒、6.95秒、6.95秒、7.30秒、7.03秒
と、一発でほぼバッファに乗り、その後は見事に応答速度が安定しました。

バッファの状態確認SQL

yoku0825先生より、バッファに各インデックスがどれだけ乗っているかをチェックするSQLを教えていただきました。
なお、10GB以上のバッファプールに対して実行するのは非推奨とのことです。

SELECT table_name, index_name, is_old, sum(data_size) AS data_size FROM information_schema.innodb_buffer_page_lru GROUP BY 1, 2, 3;

バッファプールの制御をもう少し詳しく。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.9.1 InnoDB バッファープール

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数

を読みましょう。

OldからYoungに移す制御のパラメータ「innodb_old_blocks_pct」は、5.6.5以前と5.6.6以後でデフォルトが変わって、挙動がかなり変わってます。

innodb_old_blocks_time

古いサブリストに挿入されたブロックが、その最初のアクセス後、新しいサブリストに移動するまでに、そこにとどまる必要のある時間をミリ秒 (ms) 単位で指定します。デフォルト値は 0 です。挿入後にどのくらいの期間でアクセスが発生するかに関係なく、古いサブリストに挿入されたブロックは、Innodb がバッファープールから、挿入されたブロックのページの 1/4 を削除したときに、新しいサブリストに移動されます。この値が 0 より大きい場合、ブロックは最初のアクセス後、少なくともそのミリ秒でアクセスが発生するまで、古いサブリストに残ります。たとえば、1000 の値では、ブロックは最初のアクセス後、それらが新しいサブリストに移動される資格を得るまで、1 秒間古いサブリストにとどまります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.9.1 InnoDB バッファープール

innodb_old_blocks_time

値を 0 にすると、古いサブリストに挿入されたブロックは、挿入後にどのくらいの期間でアクセスが発生するのかには関係なく、最初のアクセスの直後に新しいサブリストに移動します。この値が 0 より大きい場合、ブロックは最初のアクセス後、少なくともそのミリ秒でアクセスが発生するまで、古いサブリストに残ります。たとえば、1000 の値では、ブロックは最初のアクセス後、それらが新しいサブリストに移動される資格を得るまで、1 秒間古いサブリストにとどまります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数

あれ？微妙に記述が違う。。。
0のときはいきなりYoungに行くの？一度Oldに入って、1/4消されたらYoungに行くの？

動的変更可能なパラメータとはいえ、グローバルパラメータなので、変更する場合は影響範囲をしっかり確認しましょう。

まとめ

・MySQLのバッファプールはYoungとOldの２つにわかれてて、大きなデータを流す一過性のSQLからバッファを保護してる。
・比率と挙動はパラメータで変更可能！
・そんなわけだから、メモリ計算気をつけて！
・MySQL-CasualのSlackに来ると、やさしいお兄さんたちが待ってるよ！

今度こそ、次回は、12/7（水）、@mita2さんの予定です。