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