Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

yoku0825
December 03, 2020

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

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

yoku0825

December 03, 2020
Tweet

More Decks by yoku0825

Other Decks in Technology

Transcript

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

    View Slide

  2. TL;DR
    1/46

    View Slide

  3. TL;DR
    大事なことはだいたい @ts4th の資料に書いてある
    さいきんの InnoDB Adaptive Flushing (仮)

    さいきんのMySQLに関する取り組み(仮)

    バッファプール大事
    ピンクのおとうふの思考過程を観察するものと思って聞いていただけると幸い
    2/46

    View Slide

  4. \こんばんは/
    yoku0825@とある企業のDBA
    オラクれない

    ポスグれない

    マイエスキューエる

    生息域
    Twitter: @yoku0825

    Blog: 日々の覚書

    日本MySQLユーザ会

    MySQL Casual

    3/46

    View Slide

  5. お題
    4/46

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  10. 今回考えるモノ
    考え方自体はオンプレ物理マシンと変わらない
    物理マシンに比べて
    特定のリソースを増やし/減らしやすい

    リソース増やす/減らすのに時間がかからない

    ボトルネックになる/ならない部分でコストの調整ができる

    9/46

    View Slide

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

    View Slide

  12. CPU
    ここ最近、CPU使用率の %user がボトルネックになった人はいますか?
    %wait が1桁で純粋に CPUスレッド数 < LoadAverage になってしまうようなボトルネックになり


    綺麗にInnoDBの限界近くまで行っているパターンと、オンメモリでテンポラリーテーブル/
    ソートバッファを使っている場合の2つに大別できる気がする

    綺麗にインデックスをチューニングしていくと、あんまり当たらなくなる(と思
    う)
    11/46

    View Slide

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

    12/46

    View Slide

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

    View Slide

  15. MySQLにおけるCPUまとめ
    vCPUが足りなくなることは稀
    先にメモリーが欲しくなってインスタンスサイズを上げる方が多い ⇒ ついでにvCPUが増える

    集計用クエリーで「どうしても長く走るやつがある」のが予め分かっている場合は
    「バッチが走っている間はvCPU実質マイナスいくら」みたいなのを考慮する
    バッチのクエリー多重度を下げるか、バッチ用のレプリカを作って逃がしちゃうか

    バッチ用のレプリカと割り切れればサービスレベルと同じだけのvCPUは要らないので、コスト
    ダウンできる可能性はある

    14/46

    View Slide

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

    View Slide

  17. メモリー
    「Linuxの free コマンドで出てくる free + cache ぶんが実質使える」は搭載メモ
    リーが大きい間は概ね合ってるけどギリギリを攻める場合には間違っている
    1日に1回しか参照されないバッファプールのページ ( used ) と、毎秒更新される ib_logfile0
    のページキャッシュ ( cache ) 、の2択だったらどっちが大事?

    swap領域はカナリアの籠、上手く使う
    swapin/swapout そのものが悪いのではなく、メモリーが足りない兆候を監視を通して感じ取
    るためのもの

    大きくしすぎて検知しそびれるとスラッシングで死ぬし

    小さくしすぎて検知しそびれるとOOM Killerが仕事する

    16/46

    View Slide

  18. メモリー
    「シングルプロセス, マルチスレッド」モデルにおいて、メモリは「だいたい」共
    有される
    たとえばInnoDBバッファプールは共有される

    スレッドバッファと呼ばれる類(ソートバッファとか)は共有されないけれど、同じ物理メモリ
    の上に展開されるという意味では「メモリは共有している」

    17/46

    View Slide

  19. メモリー
    暴走クエリーがぶん回すスレッドは1つだけだとしても、その裏では「共有してい
    る何か」を枯渇させるかも知れない
    InnoDBバッファプールをガラリと洗い替えしてしまってバッファプールヒット率が下がるとか
    Write on SELECT が発生するだとか

    1つのTempTableのサイズだけで temptable_max_ram に到達してしまうと、他の小さな
    TempTableも全部TempTable on Diskに追いやられるとか
    そうなると今度はI/Oも食いつぶし合う羽目に遭う

    18/46

    View Slide

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

    View Slide

  21. MySQLにおけるメモリーの使い方
    innodb_buffer_pool_size
    InnoDBの性能のコア

    In detail, 片手間MySQLチューニング戦略

    temptable_max_ram
    忘れちゃいけないページキャッシュ
    20/46

    View Slide

  22. バッファプールの特徴
    1. バッファプールに載っているページの SELECT ではDisk I/Oは発生しない
    1-1. バッファプールに載っていないページの SELECT は少なくとも読み取りを発生させる
    場合によっては Write on SELECT で書き込みが発生する

    つまり、ヒット率を上げれば上げるほど読み取りI/Oが節約できる
    21/46

    View Slide

  23. バッファプールの特徴
    2. INSERT, DELETE などバッファプールを使わなくても出来そうな操作も必ずバッ
    ファプールを介して行う
    2-1. バルク操作は案外I/O以外にバッファプールを洗い替えする

    2-2. 書き込みI/Oが増えるのは分かりやすいとしても、読み取りI/Oも一緒に増えていたりする
    pt-ioprofile でよく見てみると、実は.ibdファイルからガリガリ読んでいたり

    「バッチとユーザートラフィックがカブると大惨事」に心当たりがあれば、バッ
    ファプールを増やすのは有効な選択肢
    22/46

    View Slide

  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

    View Slide

  25. バッファプールの特徴
    4. 欲しいページがバッファプールミスヒット && evictするページがダーティー
    ページだと、 Write on SELECT が発生する
    「必ずバッファプールに載せなければいけない」

    「ダーティーページはバッファプールから消す前にflushしないといけない」

    3. で「ダーティーページそれ自体は問題ない」と言っていたやつの「それ以外」のケース

    3. でチューニングしていたやつが 2. のバルク更新で洗い替えられた後に響いてく
    るならこれが影響しているかも
    24/46

    View Slide

  26. バッファプールまとめ
    実はCRUD全ての動作にバッファプールが使われている
    ただのキャッシュだと思って当たると思わぬWrite on SELECTに遭遇する

    余裕を持ったサイジングと「無駄遣いしない努力」
    InnoDB圧縮 (ROW_FORMAT=COMPRESSED)は圧縮前と圧縮後の 両方 がバッファプールに載る
    ストレージを稼ぐためにメモリーを犠牲にするような戦略

    25/46

    View Slide

  27. MySQLにおけるメモリーの使い方
    innodb_buffer_pool_size
    バッファプールに載り切れば、ストレージへのReadは限りなくゼロにできる

    ダーティーページのテーブルスペースファイルへの同期を上手くチューニングすれば、スト
    レージへのWriteも多少減らせる

    temptable_max_ram
    忘れちゃいけないページキャッシュ
    26/46

    View Slide

  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

    View Slide

  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

    View Slide

  30. MySQLにおけるメモリーの使い方
    innodb_buffer_pool_size
    temptable_max_ram
    忘れちゃいけないページキャッシュ
    シェルを使ってどうこうするような場合、その分も多少は考慮が必要
    バックアップ用の tar, rsync, xtrabackup, ..
    mysqlbinlog でバイナリログをデコードするにも入力元も出力先もきっとページキャッシュを使う

    頻度と相談ではあるけどいざという時身動きができないのも困る、1~2GBは最低欲しい

    29/46

    View Slide

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

    View Slide

  32. MySQLにおけるメモリーまとめ
    ほとんど innodb_buffer_pool_size が支配する
    InnoDBの性能の要。物理メモリの75%くらいとは言うけれど、ギリギリを攻める時はそんな
    に振れない

    ギリギリを攻めたい時ほど自重

    バッファプールが大きければ大きいほど、ストレージのI/Oを削減できるチャンス
    が増える
    ページキャッシュは案外無視できない、 Sys::PageCache とかを使って小まめに
    クリーンアップしても良い
    31/46

    View Slide

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

    View Slide

  34. MySQLにおけるIOPSの使い方
    もちろん「データの読み出し」
    ただしバッファプールミスヒット時のみ

    「コミットされたデータの書き出し」
    ib_logfile* への同期書き込み

    バイナリログへの同期書き込み

    .ibd ファイルへは非同期書き込み
    とはいえダブルライトバッファへの同期書き込み

    33/46

    View Slide

  35. MySQLにおけるIOPSの使い方
    on Diskのテンポラリーテーブルへの「読み」と「書き」
    テンポラリーテーブルは「書いたぶんだけ読まないといけない」

    InnoDB Temporaryならある程度バッファプールのクッションが効くとはいえ

    34/46

    View Slide

  36. MySQL vs IOPS
    コスト削減の文脈で言うと、インデックスを効かせていくのが唯一にして最良の戦

    単純な読み取りI/Oの削減

    レコード全体に大してインデックスのサイズは小さく済むため、1ページあたりの充填数が多く
    なる
    バッファプールヒット率の向上

    多くの場合、インデックスショットガンの洗礼を受ける前に sync_binlog の洗礼を受けること
    になる

    35/46

    View Slide

  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

    View Slide

  38. MySQL vs IOPS
    オンプレに比べればずっと「メモリーを増やす」より先に「IOだけ強くする」って
    パターンが多い気がする
    効果測定しやすい

    I/O「回数」に課金される(またはIOPSを増やすのに極端にコストがかかる)場合は
    メモリーを増やすのが節約の道
    7200rpmのHDDだろうと128Gのバッファプールに全て載れば

    書き込み回数のチューニングなんかは @ts4th の資料に詳しい
    さいきんの InnoDB Adaptive Flushing (仮)

    37/46

    View Slide

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

    View Slide

  40. MySQLのネットワークインアウト
    IN(受信)
    クエリー本文

    LODA DATA LOCAL INFILE などのデータだけ大量受信するやつ

    OUT(送信)
    SELECTクエリーの結果セット

    レプリカへのバイナリログ転送

    39/46

    View Slide

  41. MySQLのネットワークまとめ
    まあ…CPUとメモリーのサイズに合わせてインスタンスサイズを選んでいればそう
    そう問題にならない気がする
    トランザクション保護の必要ないバルク登録は1000とか10000とかのサイズに区切ってコミッ
    トする

    レプリカの数を2倍に増やすよりはマスターのリソースを2倍にした方が運用は楽でコストは㌧
    ㌧だったり

    デカすぎる結果セット(・A・)イクナイ!!
    ORDER BY .. LIMIT .. なしの結果セットを全部受け取る… なるほどCSVエクスポート機能 ウッ

    40/46

    View Slide

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

    View Slide

  43. ブロックストレージ独立型
    ブロックストレージだけサイズやIOPSの設定ができるかどうか
    できないならレプリカ使って切り替えながら最適値を探すしかない⇒つらい

    サイズを増やすとベースラインパフォーマンスが上がるくらうどさーびすもあるら
    しいよ
    さいきんのMySQLに関する取り組み(仮)

    42/46

    View Slide

  44. ブロックストレージ非独立 / CPU, メモリー, ネットワーク
    いかにマスター切り替えしながら良い感じのラインを模索していくかになる
    マスター切り替えの仕組みが整っていないなら、どのみち MySQL on IaaS より Managed な方が
    良いと思う
    そもそも「マスター切り替えで非対称な構成」が数少ない MySQL on Iaas の Managed に対するメリット

    物理マシン x NVMeとかだとあんまり気にならないInnoDBバッファプールウォームアップが重
    要だったりする

    ギリギリの、コップの縁を表面張力で耐えてるみたいな緊張感は体に悪い
    ギリギリを攻めるのは楽しいけど、ジャンキーにならないように

    43/46

    View Slide

  45. 雑に思考実

    44/46

    View Slide

  46. まとめ
    大事なことはだいたい @ts4th の資料に書いてある
    さいきんの InnoDB Adaptive Flushing (仮)

    さいきんのMySQLに関する取り組み(仮)

    バッファプール大事
    45/46

    View Slide

  47. Any Questions
    and/or
    Suggestions?
    46/46

    View Slide