MySQL on IaaSのインスタンスサイズチューニングについて考えてみた

2020/11/25 日本MySQLユーザ会会(MyNA会) 2020年11月 https://mysql.connpass.com/event/194600/

1. MySQL on IaaSのインスタンスサイズチューニ ングについて考えてみた 取り敢えず思考の流れをつらつらと 2020/11/25 yoku0825 日本MySQLユーザ会会(MyNA会) 2020年11月
2. TL;DR 1/46
3. TL;DR 大事なことはだいたい @ts4th の資料に書いてある さいきんの InnoDB Adaptive Flushing （仮） ‐ さいきんのMySQLに関する取り組み（仮） ‐ バッファプール大事 ピンクのおとうふの思考過程を観察するものと思って聞いていただけると幸い 2/46
4. ＼こんばんは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 3/46
5. お題 4/46
6. 「MySQL on IaaSを使 う場合のIaaSのインスタ ンスサイズってどうして ます？」 5/46
7. 雑な「インスタンスサイズ」によるリソースの獲得 CPU メモリー ネットワーク帯域 6/46
8. 雑な「インスタンスサイズ」によらないかもしれないリソースの獲得 ストレージサイズ IOPS 7/46
9. 今回考えるモノ CPU メモリー IOPS ネットワーク帯域 8/46
10. 今回考えるモノ 考え方自体はオンプレ物理マシンと変わらない 物理マシンに比べて 特定のリソースを増やし/減らしやすい ‐ リソース増やす/減らすのに時間がかからない ‐ ボトルネックになる/ならない部分でコストの調整ができる ‐ 9/46
11. 今回考えるモノ CPU メモリー IOPS ネットワーク帯域 10/46
12. CPU ここ最近、CPU使用率の %user がボトルネックになった人はいますか？ %wait が1桁で純粋に CPUスレッド数 < LoadAverage になってしまうようなボトルネックになり 方 ‐ 綺麗にInnoDBの限界近くまで行っているパターンと、オンメモリでテンポラリーテーブル/ ソートバッファを使っている場合の2つに大別できる気がする ‐ 綺麗にインデックスをチューニングしていくと、あんまり当たらなくなる（と思 う） 11/46
13. MySQLにおけるCPU MySQLは「シングルプロセス, マルチスレッド」 スレッドプール(Not コネクションプール)を使っていなければ1つのコネクション は1つのスレッドに割り当てられる よってどんなダメなクエリーでも「結果セットの錬成が占有するCPUスレッドは1 つだけ」 バックグラウンドI/Oが走る場合は他スレッドになるけどその場合たぶん %user ではなくて %wait による ‐ 12/46
14. MySQLにおけるCPU 20CPUスレッドある = 10本ダメクエリーが同時に押し寄せてきても、残りの10本 はフツーにクエリーを捌ける (ただし、スライドの後ろに出てくるメモリーとI/O の制約は受ける) 物理的なハコなら、クロックを上げると案外レスポンスタイム短くなったりする (ただしディスクアクセスが発生しないCPUネックなクエリーに限る)が、IaaSでク ロック上げようとする && お値段をキープしようとするとメモリーが犠牲になるの であまり狙わないパターン 13/46
15. MySQLにおけるCPUまとめ vCPUが足りなくなることは稀 先にメモリーが欲しくなってインスタンスサイズを上げる方が多い ⇒ ついでにvCPUが増える ‐ 集計用クエリーで「どうしても長く走るやつがある」のが予め分かっている場合は 「バッチが走っている間はvCPU実質マイナスいくら」みたいなのを考慮する バッチのクエリー多重度を下げるか、バッチ用のレプリカを作って逃がしちゃうか ‐ バッチ用のレプリカと割り切れればサービスレベルと同じだけのvCPUは要らないので、コスト ダウンできる可能性はある ‐ 14/46
16. 今回考えるモノ CPU メモリー IOPS ネットワーク帯域 15/46
17. メモリー 「Linuxの free コマンドで出てくる free + cache ぶんが実質使える」は搭載メモ リーが大きい間は概ね合ってるけどギリギリを攻める場合には間違っている 1日に1回しか参照されないバッファプールのページ ( used ) と、毎秒更新される ib_logfile0 のページキャッシュ ( cache ) 、の2択だったらどっちが大事？ ‐ swap領域はカナリアの籠、上手く使う swapin/swapout そのものが悪いのではなく、メモリーが足りない兆候を監視を通して感じ取 るためのもの ‐ 大きくしすぎて検知しそびれるとスラッシングで死ぬし ‐ 小さくしすぎて検知しそびれるとOOM Killerが仕事する ‐ 16/46
18. メモリー 「シングルプロセス, マルチスレッド」モデルにおいて、メモリは「だいたい」共 有される たとえばInnoDBバッファプールは共有される ‐ スレッドバッファと呼ばれる類（ソートバッファとか)は共有されないけれど、同じ物理メモリ の上に展開されるという意味では「メモリは共有している」 ‐ 17/46
19. メモリー 暴走クエリーがぶん回すスレッドは1つだけだとしても、その裏では「共有してい る何か」を枯渇させるかも知れない InnoDBバッファプールをガラリと洗い替えしてしまってバッファプールヒット率が下がるとか Write on SELECT が発生するだとか ‐ 1つのTempTableのサイズだけで temptable_max_ram に到達してしまうと、他の小さな TempTableも全部TempTable on Diskに追いやられるとか そうなると今度はI/Oも食いつぶし合う羽目に遭う ‐ 18/46
20. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュ 19/46
21. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size InnoDBの性能のコア ‐ In detail, 片手間MySQLチューニング戦略 ‐ temptable_max_ram 忘れちゃいけないページキャッシュ 20/46
22. バッファプールの特徴 1. バッファプールに載っているページの SELECT ではDisk I/Oは発生しない 1-1. バッファプールに載っていないページの SELECT は少なくとも読み取りを発生させる 場合によっては Write on SELECT で書き込みが発生する ‐ つまり、ヒット率を上げれば上げるほど読み取りI/Oが節約できる 21/46
23. バッファプールの特徴 2. INSERT, DELETE などバッファプールを使わなくても出来そうな操作も必ずバッ ファプールを介して行う 2-1. バルク操作は案外I/O以外にバッファプールを洗い替えする ‐ 2-2. 書き込みI/Oが増えるのは分かりやすいとしても、読み取りI/Oも一緒に増えていたりする pt-ioprofile でよく見てみると、実は.ibdファイルからガリガリ読んでいたり ‐ 「バッチとユーザートラフィックがカブると大惨事」に心当たりがあれば、バッ ファプールを増やすのは有効な選択肢 22/46
24. バッファプールの特徴 3. ダーティーページの.ibdファイルとの同期は page_cleaner_thread の仕事で バックグラウンド page_cleaner_thread をチューニングすると「同期しすぎ」を減らして Write combined を狙い やすくなる innodb_adaptive_flushing_lwm, innodb_log_file_size, innodb_page_cleaners, innodb_lru_scan_depth あたり ‐ 「ダーティーページ」は.ibdファイルとの同期が取れてないってだけで、(READ-COMMITTED なら)データとしてはバッファプールから返すものが正しいので、ある程度以上増えても「それ 自体が」問題ではない ‐ 書き込めば書き込むほどすり減っていくSSDとか、IO回数単位で課金されるブロッ クストレージなどでは積極的にチューニングできる 23/46
25. バッファプールの特徴 4. 欲しいページがバッファプールミスヒット && evictするページがダーティー ページだと、 Write on SELECT が発生する 「必ずバッファプールに載せなければいけない」 ‐ 「ダーティーページはバッファプールから消す前にflushしないといけない」 ‐ 3. で「ダーティーページそれ自体は問題ない」と言っていたやつの「それ以外」のケース ‐ 3. でチューニングしていたやつが 2. のバルク更新で洗い替えられた後に響いてく るならこれが影響しているかも 24/46
26. バッファプールまとめ 実はCRUD全ての動作にバッファプールが使われている ただのキャッシュだと思って当たると思わぬWrite on SELECTに遭遇する ‐ 余裕を持ったサイジングと「無駄遣いしない努力」 InnoDB圧縮 (ROW_FORMAT=COMPRESSED)は圧縮前と圧縮後の 両方 がバッファプールに載る ストレージを稼ぐためにメモリーを犠牲にするような戦略 ‐ 25/46
27. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size バッファプールに載り切れば、ストレージへのReadは限りなくゼロにできる ‐ ダーティーページのテーブルスペースファイルへの同期を上手くチューニングすれば、スト レージへのWriteも多少減らせる ‐ temptable_max_ram 忘れちゃいけないページキャッシュ 26/46
28. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram かつての MIN(heap_table_size, max_tmp_table_size) に相当するようなもの セッション単位ではなくサーバーグローバル（全セッション合算） ‐ ここからこぼれるとTempTable on DiskまたはInnoDB Temporaryに落ちる 想像以上にここに全部乗っかった状態(TempTable on Memory)はテンポラリーテーブルでも速い こぼれた途端地獄、とくにTempTable on DiskはI/Oをかなり食おうとする…んだけど、8.0の中でも8.0.18と 8.0.22でなんか違和感を感じてベンチマーク未了 ‐ 忘れちゃいけないページキャッシュ 27/46
29. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュ innodb_flush_method <> O_DIRECT の場合は.ibdファイルもページキャッシュに載る ‐ InnoDBログ, バイナリログは O_DIRECT 設定するようなパラメーターはない、基本ページ キャッシュに載る Percona Serverには innodb_flush_method= ALL_O_DIRECT あるけど 1日に1回しか参照されないバッファプールのページ ( used ) と、毎秒更新される ib_logfile0 のページキャッシュ ( cache ) 、の2択だったらどっちが大事？ (再掲) ‐ 28/46
30. MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュ シェルを使ってどうこうするような場合、その分も多少は考慮が必要 バックアップ用の tar, rsync, xtrabackup, .. mysqlbinlog でバイナリログをデコードするにも入力元も出力先もきっとページキャッシュを使う ‐ 頻度と相談ではあるけどいざという時身動きができないのも困る、1～2GBは最低欲しい ‐ 29/46
31. 部屋とYシャツ とOOM Killer 30/46
32. MySQLにおけるメモリーまとめ ほとんど innodb_buffer_pool_size が支配する InnoDBの性能の要。物理メモリの75%くらいとは言うけれど、ギリギリを攻める時はそんな に振れない ‐ ギリギリを攻めたい時ほど自重 ‐ バッファプールが大きければ大きいほど、ストレージのI/Oを削減できるチャンス が増える ページキャッシュは案外無視できない、 Sys::PageCache とかを使って小まめに クリーンアップしても良い 31/46
33. 今回考えるモノ CPU メモリー IOPS ネットワーク帯域 32/46
34. MySQLにおけるIOPSの使い方 もちろん「データの読み出し」 ただしバッファプールミスヒット時のみ ‐ 「コミットされたデータの書き出し」 ib_logfile* への同期書き込み ‐ バイナリログへの同期書き込み ‐ .ibd ファイルへは非同期書き込み とはいえダブルライトバッファへの同期書き込み ‐ 33/46
35. MySQLにおけるIOPSの使い方 on Diskのテンポラリーテーブルへの「読み」と「書き」 テンポラリーテーブルは「書いたぶんだけ読まないといけない」 ‐ InnoDB Temporaryならある程度バッファプールのクッションが効くとはいえ ‐ 34/46
36. MySQL vs IOPS コスト削減の文脈で言うと、インデックスを効かせていくのが唯一にして最良の戦 略 単純な読み取りI/Oの削減 ‐ レコード全体に大してインデックスのサイズは小さく済むため、1ページあたりの充填数が多く なる バッファプールヒット率の向上 ‐ 多くの場合、インデックスショットガンの洗礼を受ける前に sync_binlog の洗礼を受けること になる ‐ 35/46
37. MySQL vs IOPS 「テンポラリーテーブルをストレージに落としたら負け」はまだ現役 かつては tmp_table_size, max_heap_table_size の小さい方 ‐ 今は temptable_max_ram ‐ temptable_use_mmap のON/OFFで TempTable on Disk か InnoDB Temporaryを打ち分ける ‐ 36/46
38. MySQL vs IOPS オンプレに比べればずっと「メモリーを増やす」より先に「IOだけ強くする」って パターンが多い気がする 効果測定しやすい ‐ I/O「回数」に課金される(またはIOPSを増やすのに極端にコストがかかる)場合は メモリーを増やすのが節約の道 7200rpmのHDDだろうと128Gのバッファプールに全て載れば ‐ 書き込み回数のチューニングなんかは @ts4th の資料に詳しい さいきんの InnoDB Adaptive Flushing （仮） ‐ 37/46
39. 今回考えるモノ CPU メモリー IOPS ネットワーク帯域 38/46
40. MySQLのネットワークインアウト IN(受信) クエリー本文 ‐ LODA DATA LOCAL INFILE などのデータだけ大量受信するやつ ‐ OUT(送信) SELECTクエリーの結果セット ‐ レプリカへのバイナリログ転送 ‐ 39/46
41. MySQLのネットワークまとめ まあ…CPUとメモリーのサイズに合わせてインスタンスサイズを選んでいればそう そう問題にならない気がする トランザクション保護の必要ないバルク登録は1000とか10000とかのサイズに区切ってコミッ トする ‐ レプリカの数を2倍に増やすよりはマスターのリソースを2倍にした方が運用は楽でコストは㌧ ㌧だったり ‐ デカすぎる結果セット(・A・)ｲｸﾅｲ!! ORDER BY .. LIMIT .. なしの結果セットを全部受け取る… なるほどCSVエクスポート機能 ｳｯ ‐ 40/46
42. 今回考えたモノ CPU メモリー IOPS ネットワーク帯域 41/46
43. ブロックストレージ独立型 ブロックストレージだけサイズやIOPSの設定ができるかどうか できないならレプリカ使って切り替えながら最適値を探すしかない⇒つらい ‐ サイズを増やすとベースラインパフォーマンスが上がるくらうどさーびすもあるら しいよ さいきんのMySQLに関する取り組み（仮） ‐ 42/46
44. ブロックストレージ非独立 / CPU, メモリー, ネットワーク いかにマスター切り替えしながら良い感じのラインを模索していくかになる マスター切り替えの仕組みが整っていないなら、どのみち MySQL on IaaS より Managed な方が 良いと思う そもそも「マスター切り替えで非対称な構成」が数少ない MySQL on Iaas の Managed に対するメリット ‐ 物理マシン x NVMeとかだとあんまり気にならないInnoDBバッファプールウォームアップが重 要だったりする ‐ ギリギリの、コップの縁を表面張力で耐えてるみたいな緊張感は体に悪い ギリギリを攻めるのは楽しいけど、ジャンキーにならないように ‐ 43/46
45. 雑に思考実 験 44/46
46. まとめ 大事なことはだいたい @ts4th の資料に書いてある さいきんの InnoDB Adaptive Flushing （仮） ‐ さいきんのMySQLに関する取り組み（仮） ‐ バッファプール大事 45/46
47. Any Questions and/or Suggestions? 46/46