Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL on IaaSのインスタンスサイズチューニングについて考えてみた
Search
yoku0825
December 03, 2020
Technology
0
690
MySQL on IaaSのインスタンスサイズチューニングについて考えてみた
2020/11/25 日本MySQLユーザ会会(MyNA会) 2020年11月
https://mysql.connpass.com/event/194600/
yoku0825
December 03, 2020
Tweet
Share
More Decks by yoku0825
See All by yoku0825
HeatWave をオンプレの MySQL と同じように使おうとしてみた!
yoku0825
0
43
MySQLのロックの種類とその競合
yoku0825
10
4.1k
MySQL 8.4 LTS が あらわれた
yoku0825
2
1.7k
ぼくたちはMySQL 8.1とどう生きるか
yoku0825
6
2.5k
2022年のMySQLerが20年前のMySQL 4.0に触ると何が起きるか
yoku0825
0
490
テストデータが偏るということについて
yoku0825
3
8.8k
MySQLが得意なこと、不得意なこと(仮)
yoku0825
12
14k
MySQLとインデックスとPHPer
yoku0825
8
8.1k
MySQLとインデックスと私
yoku0825
82
59k
Other Decks in Technology
See All in Technology
LangfuseでAIエージェントの 可観測性を高めよう!/Enhancing AI Agent Observability with Langfuse!
jnymyk
0
170
Cursor AgentによるパーソナルAIアシスタント育成入門―業務のプロンプト化・MCPの活用
os1ma
9
3.2k
All You Need Is Kusa 〜Slackデータで始めるデータドリブン〜
jonnojun
0
140
AWSのマルチアカウント管理 ベストプラクティス最新版 2025 / Multi-Account management on AWS best practice 2025
ohmura
4
210
Amebaにおける Platform Engineeringの実践
kumorn5s
6
900
AIエージェント開発における「攻めの品質改善」と「守りの品質保証」 / 2024.04.09 GPU UNITE 新年会 2025
smiyawaki0820
0
400
Classmethod AI Talks(CATs) #21 司会進行スライド(2025.04.17) / classmethod-ai-talks-aka-cats_moderator-slides_vol21_2025-04-17
shinyaa31
0
460
Automatically generating types by running tests
sinsoku
1
460
こんなデータマートは嫌だ。どんな? / waiwai-data-meetup-202504
shuntak
6
1.8k
Would you THINK such a demonstration interesting ?
shumpei3
1
160
Lightdashの利活用状況 ー導入から2年経った現在地_20250409
hirokiigeta
2
270
クォータ監視、AWS Organizations環境でも楽勝です✌️
iwamot
PRO
1
240
Featured
See All Featured
RailsConf 2023
tenderlove
30
1.1k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
Writing Fast Ruby
sferik
628
61k
Facilitating Awesome Meetings
lara
54
6.3k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
47
5.3k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.4k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
23
2.6k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
233
17k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
119
51k
Fontdeck: Realign not Redesign
paulrobertlloyd
83
5.5k
Git: the NoSQL Database
bkeepers
PRO
430
65k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
32
2.2k
Transcript
MySQL on IaaSのインスタンスサイズチューニ ングについて考えてみた 取り敢えず思考の流れをつらつらと 2020/11/25 yoku0825 日本MySQLユーザ会会(MyNA会) 2020年11月
TL;DR 1/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
雑な「インスタンスサイズ」によらないかもしれないリソースの獲得 ストレージサイズ IOPS 7/46
今回考えるモノ CPU メモリー IOPS ネットワーク帯域 8/46
今回考えるモノ 考え方自体はオンプレ物理マシンと変わらない 物理マシンに比べて 特定のリソースを増やし/減らしやすい ‐ リソース増やす/減らすのに時間がかからない ‐ ボトルネックになる/ならない部分でコストの調整ができる ‐ 9/46
今回考えるモノ CPU メモリー IOPS ネットワーク帯域 10/46
CPU ここ最近、CPU使用率の %user がボトルネックになった人はいますか? %wait が1桁で純粋に CPUスレッド数 < LoadAverage になってしまうようなボトルネックになり
方 ‐ 綺麗にInnoDBの限界近くまで行っているパターンと、オンメモリでテンポラリーテーブル/ ソートバッファを使っている場合の2つに大別できる気がする ‐ 綺麗にインデックスをチューニングしていくと、あんまり当たらなくなる(と思 う) 11/46
MySQLにおけるCPU MySQLは「シングルプロセス, マルチスレッド」 スレッドプール(Not コネクションプール)を使っていなければ1つのコネクション は1つのスレッドに割り当てられる よってどんなダメなクエリーでも「結果セットの錬成が占有するCPUスレッドは1 つだけ」 バックグラウンドI/Oが走る場合は他スレッドになるけどその場合たぶん %user
ではなくて %wait による ‐ 12/46
MySQLにおけるCPU 20CPUスレッドある = 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_size temptable_max_ram 忘れちゃいけないページキャッシュ 19/46
MySQLにおけるメモリーの使い方 innodb_buffer_pool_size InnoDBの性能のコア ‐ 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_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
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
MySQLにおけるメモリーの使い方 innodb_buffer_pool_size temptable_max_ram 忘れちゃいけないページキャッシュ シェルを使ってどうこうするような場合、その分も多少は考慮が必要 バックアップ用の tar, rsync, xtrabackup, ..
mysqlbinlog でバイナリログをデコードするにも入力元も出力先もきっとページキャッシュを使う ‐ 頻度と相談ではあるけどいざという時身動きができないのも困る、1~2GBは最低欲しい ‐ 29/46
部屋とYシャツ とOOM Killer 30/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 Questions and/or Suggestions? 46/46