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

Slide 1

Slide 1 text

MySQL on IaaSのインスタンスサイズチューニングについて考えてみた取り敢えず思考の流れをつらつらと 2020/11/25 yoku0825 日本MySQLユーザ会会(MyNA会) 2020年11月

Slide 2

Slide 2 text

TL;DR 1/46

Slide 3

Slide 3 text

TL;DR 大事なことはだいたい @ts4th の資料に書いてあるさいきんの InnoDB Adaptive Flushing （仮） ‐ さいきんのMySQLに関する取り組み（仮） ‐ バッファプール大事ピンクのおとうふの思考過程を観察するものと思って聞いていただけると幸い 2/46

Slide 4

Slide 4 text

＼こんばんは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 3/46

Slide 5

Slide 5 text

お題 4/46

Slide 6

Slide 6 text

「MySQL on IaaSを使う場合のIaaSのインスタンスサイズってどうしてます？」 5/46

Slide 7

Slide 7 text

雑な「インスタンスサイズ」によるリソースの獲得 CPU メモリーネットワーク帯域 6/46

Slide 8

Slide 8 text

雑な「インスタンスサイズ」によらないかもしれないリソースの獲得ストレージサイズ IOPS 7/46

Slide 9

Slide 9 text

今回考えるモノ CPU メモリー IOPS ネットワーク帯域 8/46

Slide 10

Slide 10 text

今回考えるモノ考え方自体はオンプレ物理マシンと変わらない物理マシンに比べて特定のリソースを増やし/減らしやすい ‐ リソース増やす/減らすのに時間がかからない ‐ ボトルネックになる/ならない部分でコストの調整ができる ‐ 9/46

Slide 11

Slide 11 text

今回考えるモノ CPU メモリー IOPS ネットワーク帯域 10/46

Slide 12

Slide 12 text

CPU ここ最近、CPU使用率の %user がボトルネックになった人はいますか？ %wait が1桁で純粋に CPUスレッド数 < LoadAverage になってしまうようなボトルネックになり方 ‐ 綺麗にInnoDBの限界近くまで行っているパターンと、オンメモリでテンポラリーテーブル/ ソートバッファを使っている場合の2つに大別できる気がする ‐ 綺麗にインデックスをチューニングしていくと、あんまり当たらなくなる（と思う） 11/46

Slide 13

Slide 13 text

MySQLにおけるCPU MySQLは「シングルプロセス, マルチスレッド」スレッドプール(Not コネクションプール)を使っていなければ1つのコネクションは1つのスレッドに割り当てられるよってどんなダメなクエリーでも「結果セットの錬成が占有するCPUスレッドは1 つだけ」バックグラウンドI/Oが走る場合は他スレッドになるけどその場合たぶん %user ではなくて %wait による ‐ 12/46

Slide 14

Slide 14 text

MySQLにおけるCPU 20CPUスレッドある = 10本ダメクエリーが同時に押し寄せてきても、残りの10本はフツーにクエリーを捌ける (ただし、スライドの後ろに出てくるメモリーとI/O の制約は受ける) 物理的なハコなら、クロックを上げると案外レスポンスタイム短くなったりする (ただしディスクアクセスが発生しないCPUネックなクエリーに限る)が、IaaSでクロック上げようとする && お値段をキープしようとするとメモリーが犠牲になるのであまり狙わないパターン 13/46

Slide 15

Slide 15 text

MySQLにおけるCPUまとめ vCPUが足りなくなることは稀先にメモリーが欲しくなってインスタンスサイズを上げる方が多い ⇒ ついでにvCPUが増える ‐ 集計用クエリーで「どうしても長く走るやつがある」のが予め分かっている場合は「バッチが走っている間はvCPU実質マイナスいくら」みたいなのを考慮するバッチのクエリー多重度を下げるか、バッチ用のレプリカを作って逃がしちゃうか ‐ バッチ用のレプリカと割り切れればサービスレベルと同じだけのvCPUは要らないので、コストダウンできる可能性はある ‐ 14/46

Slide 16

Slide 16 text

今回考えるモノ CPU メモリー IOPS ネットワーク帯域 15/46

Slide 17

Slide 17 text

メモリー「Linuxの free コマンドで出てくる free + cache ぶんが実質使える」は搭載メモリーが大きい間は概ね合ってるけどギリギリを攻める場合には間違っている 1日に1回しか参照されないバッファプールのページ ( used ) と、毎秒更新される ib_logfile0 のページキャッシュ ( cache ) 、の2択だったらどっちが大事？ ‐ swap領域はカナリアの籠、上手く使う swapin/swapout そのものが悪いのではなく、メモリーが足りない兆候を監視を通して感じ取るためのもの ‐ 大きくしすぎて検知しそびれるとスラッシングで死ぬし ‐ 小さくしすぎて検知しそびれるとOOM Killerが仕事する ‐ 16/46

Slide 18

Slide 18 text

メモリー「シングルプロセス, マルチスレッド」モデルにおいて、メモリは「だいたい」共有されるたとえばInnoDBバッファプールは共有される ‐ スレッドバッファと呼ばれる類（ソートバッファとか)は共有されないけれど、同じ物理メモリの上に展開されるという意味では「メモリは共有している」 ‐ 17/46

Slide 19

Slide 19 text

メモリー暴走クエリーがぶん回すスレッドは1つだけだとしても、その裏では「共有している何か」を枯渇させるかも知れない InnoDBバッファプールをガラリと洗い替えしてしまってバッファプールヒット率が下がるとか Write on SELECT が発生するだとか ‐ 1つのTempTableのサイズだけで temptable_max_ram に到達してしまうと、他の小さな TempTableも全部TempTable on Diskに追いやられるとかそうなると今度はI/Oも食いつぶし合う羽目に遭う ‐ 18/46

Slide 20

Slide 20 text

MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュ 19/46

Slide 21

Slide 21 text

MySQLにおけるメモリーの使い方 innodb_buffer_pool_size InnoDBの性能のコア ‐ In detail, 片手間MySQLチューニング戦略 ‐ temptable_max_ram 忘れちゃいけないページキャッシュ 20/46

Slide 22

Slide 22 text

バッファプールの特徴 1. バッファプールに載っているページの SELECT ではDisk I/Oは発生しない 1-1. バッファプールに載っていないページの SELECT は少なくとも読み取りを発生させる場合によっては Write on SELECT で書き込みが発生する ‐ つまり、ヒット率を上げれば上げるほど読み取りI/Oが節約できる 21/46

Slide 23

Slide 23 text

バッファプールの特徴 2. INSERT, DELETE などバッファプールを使わなくても出来そうな操作も必ずバッファプールを介して行う 2-1. バルク操作は案外I/O以外にバッファプールを洗い替えする ‐ 2-2. 書き込みI/Oが増えるのは分かりやすいとしても、読み取りI/Oも一緒に増えていたりする pt-ioprofile でよく見てみると、実は.ibdファイルからガリガリ読んでいたり ‐ 「バッチとユーザートラフィックがカブると大惨事」に心当たりがあれば、バッファプールを増やすのは有効な選択肢 22/46

Slide 24

Slide 24 text

バッファプールの特徴 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

Slide 25

Slide 25 text

バッファプールの特徴 4. 欲しいページがバッファプールミスヒット && evictするページがダーティーページだと、 Write on SELECT が発生する「必ずバッファプールに載せなければいけない」 ‐ 「ダーティーページはバッファプールから消す前にflushしないといけない」 ‐ 3. で「ダーティーページそれ自体は問題ない」と言っていたやつの「それ以外」のケース ‐ 3. でチューニングしていたやつが 2. のバルク更新で洗い替えられた後に響いてくるならこれが影響しているかも 24/46

Slide 26

Slide 26 text

バッファプールまとめ実はCRUD全ての動作にバッファプールが使われているただのキャッシュだと思って当たると思わぬWrite on SELECTに遭遇する ‐ 余裕を持ったサイジングと「無駄遣いしない努力」 InnoDB圧縮 (ROW_FORMAT=COMPRESSED)は圧縮前と圧縮後の両方がバッファプールに載るストレージを稼ぐためにメモリーを犠牲にするような戦略 ‐ 25/46

Slide 27

Slide 27 text

MySQLにおけるメモリーの使い方 innodb_buffer_pool_size バッファプールに載り切れば、ストレージへのReadは限りなくゼロにできる ‐ ダーティーページのテーブルスペースファイルへの同期を上手くチューニングすれば、ストレージへのWriteも多少減らせる ‐ temptable_max_ram 忘れちゃいけないページキャッシュ 26/46

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュシェルを使ってどうこうするような場合、その分も多少は考慮が必要バックアップ用の tar, rsync, xtrabackup, .. mysqlbinlog でバイナリログをデコードするにも入力元も出力先もきっとページキャッシュを使う ‐ 頻度と相談ではあるけどいざという時身動きができないのも困る、1～2GBは最低欲しい ‐ 29/46

Slide 31

Slide 31 text

部屋とYシャツとOOM Killer 30/46

Slide 32

Slide 32 text

MySQLにおけるメモリーまとめほとんど innodb_buffer_pool_size が支配する InnoDBの性能の要。物理メモリの75%くらいとは言うけれど、ギリギリを攻める時はそんなに振れない ‐ ギリギリを攻めたい時ほど自重 ‐ バッファプールが大きければ大きいほど、ストレージのI/Oを削減できるチャンスが増えるページキャッシュは案外無視できない、 Sys::PageCache とかを使って小まめにクリーンアップしても良い 31/46

Slide 33

Slide 33 text

今回考えるモノ CPU メモリー IOPS ネットワーク帯域 32/46

Slide 34

Slide 34 text

MySQLにおけるIOPSの使い方もちろん「データの読み出し」ただしバッファプールミスヒット時のみ ‐ 「コミットされたデータの書き出し」 ib_logfile* への同期書き込み ‐ バイナリログへの同期書き込み ‐ .ibd ファイルへは非同期書き込みとはいえダブルライトバッファへの同期書き込み ‐ 33/46

Slide 35

Slide 35 text

MySQLにおけるIOPSの使い方 on Diskのテンポラリーテーブルへの「読み」と「書き」テンポラリーテーブルは「書いたぶんだけ読まないといけない」 ‐ InnoDB Temporaryならある程度バッファプールのクッションが効くとはいえ ‐ 34/46

Slide 36

Slide 36 text

MySQL vs IOPS コスト削減の文脈で言うと、インデックスを効かせていくのが唯一にして最良の戦略単純な読み取りI/Oの削減 ‐ レコード全体に大してインデックスのサイズは小さく済むため、1ページあたりの充填数が多くなるバッファプールヒット率の向上 ‐ 多くの場合、インデックスショットガンの洗礼を受ける前に sync_binlog の洗礼を受けることになる ‐ 35/46

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

MySQL vs IOPS オンプレに比べればずっと「メモリーを増やす」より先に「IOだけ強くする」ってパターンが多い気がする効果測定しやすい ‐ I/O「回数」に課金される(またはIOPSを増やすのに極端にコストがかかる)場合はメモリーを増やすのが節約の道 7200rpmのHDDだろうと128Gのバッファプールに全て載れば ‐ 書き込み回数のチューニングなんかは @ts4th の資料に詳しいさいきんの InnoDB Adaptive Flushing （仮） ‐ 37/46

Slide 39

Slide 39 text

今回考えるモノ CPU メモリー IOPS ネットワーク帯域 38/46

Slide 40

Slide 40 text

MySQLのネットワークインアウト IN(受信) クエリー本文 ‐ LODA DATA LOCAL INFILE などのデータだけ大量受信するやつ ‐ OUT(送信) SELECTクエリーの結果セット ‐ レプリカへのバイナリログ転送 ‐ 39/46

Slide 41

Slide 41 text

MySQLのネットワークまとめまあ…CPUとメモリーのサイズに合わせてインスタンスサイズを選んでいればそうそう問題にならない気がするトランザクション保護の必要ないバルク登録は1000とか10000とかのサイズに区切ってコミットする ‐ レプリカの数を2倍に増やすよりはマスターのリソースを2倍にした方が運用は楽でコストは㌧㌧だったり ‐ デカすぎる結果セット(・A・)ｲｸﾅｲ!! ORDER BY .. LIMIT .. なしの結果セットを全部受け取る… なるほどCSVエクスポート機能ｳｯ ‐ 40/46

Slide 42

Slide 42 text

今回考えたモノ CPU メモリー IOPS ネットワーク帯域 41/46

Slide 43

Slide 43 text

ブロックストレージ独立型ブロックストレージだけサイズやIOPSの設定ができるかどうかできないならレプリカ使って切り替えながら最適値を探すしかない⇒つらい ‐ サイズを増やすとベースラインパフォーマンスが上がるくらうどさーびすもあるらしいよさいきんのMySQLに関する取り組み（仮） ‐ 42/46

Slide 44

Slide 44 text

ブロックストレージ非独立 / CPU, メモリー, ネットワークいかにマスター切り替えしながら良い感じのラインを模索していくかになるマスター切り替えの仕組みが整っていないなら、どのみち MySQL on IaaS より Managed な方が良いと思うそもそも「マスター切り替えで非対称な構成」が数少ない MySQL on Iaas の Managed に対するメリット ‐ 物理マシン x NVMeとかだとあんまり気にならないInnoDBバッファプールウォームアップが重要だったりする ‐ ギリギリの、コップの縁を表面張力で耐えてるみたいな緊張感は体に悪いギリギリを攻めるのは楽しいけど、ジャンキーにならないように ‐ 43/46

Slide 45

Slide 45 text

雑に思考実験 44/46

Slide 46

Slide 46 text

まとめ大事なことはだいたい @ts4th の資料に書いてあるさいきんの InnoDB Adaptive Flushing （仮） ‐ さいきんのMySQLに関する取り組み（仮） ‐ バッファプール大事 45/46

Slide 47

Slide 47 text

Any Questions and/or Suggestions? 46/46