やさしいDBパフォーマンス最適化@関西エンジニア学生Meetup in 大阪 #1

DBパラメータチューニングにおけるLT会登壇資料

"パフォーマンス最適化"という言葉は曖昧で、何をどうすればいいか迷いがちです。本資料ではMySQLのInnoDBを題材に、メモリ構成の式 最大メモリ使用量 ≧ Global Buffer + Thread Buffer × コネクション数 を軸に解説。メモリを増やせばOOM、コネクションを絞れば枯渇、ディスクに逃がせばI/Oバウンドと、どこかを立てれば別が崩れるトレードオフの構造を整理し、限られたリソースの中でパラメータをどう調整するかの考え方を紹介します。

イベント名「関西エンジニア学生Meetup in 大阪 #1 〜LT会＆交流会〜」
https://connpass.com/event/387136/

Transcript

1. やさしい DBパフォーマンス最適化 1 2026/03/23 (Mon) 立命館大学公認学生IT団体 watnow 新B4 (29卒) 金久保　仁理 (@nyu029) @関西エンジニア学生 Meetup in 大阪 #1
2. プログラミング人生で最低 N 回は聞き覚えのある単語 「パフォーマンスを最適化してください」 2
3. 曖昧すぎる単語「パフォーマンス」「最適化」 3 パフォーマンス最適化とは「何」を「何する」ことなのか？ • そもそも「パフォーマンスを最適化する」とはなんなのか？ • 単語が曖昧すぎる。。。 • パフォーマンスってなんですか？ • 最適化ってなんですか？ • なんかいい感じにするってどゆこと • パフォ...
4. パフォーマンス最適化とは 4 パフォーマンス: - 提供速度 (スループット・レイテンシ ) が定めた基準より上であること - 提供効率 (コスト・リソース ) が予想を超えないこと パフォーマンス最適化するとは 提供効率を保ったまま、提供速度を改善すること あるいはその逆
5. 我々はパフォーマンスを上げたい 5 より速く、より安く、より正確にサービスをデリバリーする DBパフォーマンス最適化が求められるケース • XやThreadsなどの大規模データでのタイムライン表示 • ゲームのランキング表示 • Youtube上の動画推薦 etc… なぜ必要なのか • 超大規模 (10TB+など) なデータ量に耐えるDBの運用 • 大量なデータから適切なものを推薦するために複雑なクエリを 使う • ロード遅延がサービスの根幹を揺るがす
6. 6 何を最適化するか
7. 7 何を最適化するか 今回は パラメータ最適化の 話がメインです！
8. システムが管理する値をクエリで取得できる 8 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域
9. もしデータベース自体の設定で パフォーマンス劣化が起きていたとしたら どうやって改善する？ 9
10. もしデータベース自体の設定で パフォーマンス劣化が起きていたとしたら どうやって改善する？ 10 パラメータ最適化して 直すしかない
11. InnoDBストレージエンジンの仕組み 11 実はメモリ構成をシンプルな式で表せられる 最大メモリ使用量 ≧ Global Buffer + Thread Buffer × コネクション数 + C(その他) [GiB] 各コンポーネント • Global Buffer Size: サーバ全体で必要な計算リソースの確保 innodb_buffer_pool_size + innodb_log_buffer_pool_size + key_buffer_size + … (複数要素) • Thread Buffer Size: スレッド単位で必要な計算リソースの確保 binlog_cache_size + join_buffer_size + read_buffer_size + sort_buffer_size + … (複数要素) 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域 MySQLは内部的にInnoDBというストレージエンジンを利用している
12. DBメモリ領域のイメージ図 12
13. DBメモリ内に Global Buffer領域を確保する 13 DBは起動後、最初にグローバルバッファを確保する
14. スレッド単位で Thread Buffer領域を確保する 14 コネクション数に合わせてスレッド単位で必要な スレッドバッファが次々に確保されていく (n=2)
15. スレッド単位で Thread Buffer領域を確保する 15 コネクション数に合わせてスレッド単位で必要な スレッドバッファが次々に確保されていく (n=10)
16. 最適化を体験してみよう 16
17. 問題1: とにかくたくさんメモリを割り当てれば？ 17 コネクション数に合わせてスレッド単位で必要な スレッドバッファが次々に確保されていく (n=10)
18. 問題1: DBに接続できる数には上限値がある 18 あるコネクション数を境にスレッドバッファがメモリ領域を超え てしまう Out Of Memory (OOM)
19. パフォーマンス最適化の工夫 19 実はメモリ構成をシンプルな式で表せられる 最大メモリ使用量 ≧ Global Buffer + Thread Buffer × コネクション数 + C(その他) [GiB] 各コンポーネント • Global Buffer Size: サーバ全体で必要な計算リソースの確保 innodb_buffer_pool_size + innodb_log_buffer_pool_size + key_buffer_size + … (複数要素) • Thread Buffer Size: スレッド単位で必要な計算リソースの確保 binlog_cache_size + join_buffer_size + read_buffer_size + sort_buffer_size + … (複数要素) 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域 ゴリゴリ資源が使えるわけではなく制御が必要 (最大メモリを越えてはならない )
20. 問題2: コネクション絞れば解決では？ 20 コネクション数に合わせてスレッド単位で必要な スレッドバッファが次々に確保されていく (n=10)
21. 問題2: メモリアウトを恐れてコネクションを渋る 21 これ以上コネクションが伸びず、コネクション枯渇 まだメモリ余ってるのに。。。
22. パフォーマンス最適化の難工夫 22 実はメモリ構成をシンプルな式で表せられる 最大メモリ使用量 ≧ Global Buffer + Thread Buffer × コネクション数 + C(その他) [GiB] 各コンポーネント • Global Buffer Size: サーバ全体で必要な計算リソースの確保 innodb_buffer_pool_size + innodb_log_buffer_pool_size + key_buffer_size + … (複数要素) • Thread Buffer Size: スレッド単位で必要な計算リソースの確保 binlog_cache_size + join_buffer_size + read_buffer_size + sort_buffer_size + … (複数要素) 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域 コネクションを渋るとコネクション枯渇が起きてアプリが遅延 (Too Many Connections Error)
23. 問題3: 足りないなら外部ディスクを使えばいいじゃん 23
24. 問題3: ディスク領域はめっっちゃ遅い 24 disk diskという領域を使うことで一時的にくるバッファ領域を超える データサイズでも処理できるようになる 一方で、外部のファイルに読み書きするため I/Oバウンド が跳ね 上がり、パフォーマンス低下の原因になる できるだけディスクは使いたくない！ (超遅くなる)
25. パフォーマンス最適化の難しさ 25 実はメモリ構成をシンプルな式で表せられる 最大メモリ使用量 ≧ Global Buffer + Thread Buffer × コネクション数 + C(その他) [GiB] 各コンポーネント • Global Buffer Size: サーバ全体で必要な計算リソースの確保 innodb_buffer_pool_size + innodb_log_buffer_pool_size + key_buffer_size + … (複数要素) • Thread Buffer Size: スレッド単位で必要な計算リソースの確保 binlog_cache_size + join_buffer_size + read_buffer_size + sort_buffer_size + … (複数要素) 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域 あるいは限られた資源の中で 過剰資源から不足資源にリソースを割り当てる必要がある
26. 各バッファの割り当て変更 26 disk diskという領域を使うことで一時的にくるバッファ領域を超える データサイズでも処理できるようになる 一方で、外部のファイルに読み書きするため I/Oバウンド が跳ね 上がり、パフォーマンス低下の原因になる 余ってるメモリを拝借し、 スレッド内のメモリ配分を書き換えること で、ディスクを使わなくても耐えるようにす る
27. 再掲: クエリでシステム変数を書き換えられる 27 最初に全体確保するメモリ領域 スレッドができてから確保されるメモリ領域 全体で利用するような各計算領域 各スレッドでの計算領域 忘れないでね
28. なぜ現場のエンジニアは情報に辿り着けるのか 28 A. 一次情報に正面から立ち向かっている • 公式Docsをしっかり読んでいる • 公式のトラブルシュートでも日/英でニュアンス が違ったりバージョンが古いといったことはザ ラにある (翻訳者のベストエフォートになりやすい) • 英語原文のものをできるだけ読む https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 英語読め
29. 実際はもっと詳細な計算が必要 29 • 実際にGlobal BufferとThread Bufferとして含めるべき値と、除外する値の精査 • 出てきた値を使って、最大使用可能メモリ、セーフティの割り出し • 逆算して得られるセーフティな最大コネクション数などを算出する 参照: https://repost.aws/knowledge-center/low-freeable-memory-rds-mysql-mariadb
30. パフォーマンス最適化のおもしろさ 30 • 自分で式や理論を組み立てて検証する • 構築した理論が上手くハマった時とてつもなく気持ちがいい • 1箇所変更したらパフォーマンスが X倍にになった！みたいな瞬間がある • ベンダーや環境によっては目が飛び出る額のコスト効果になる時がある
31. さいごに 31 パフォーマンス最適化 は気持ちいいぞ 懇親会で色々お話ししましょう😄 https://zenn.dev/masamichi2004/articles/7f751aa0434b2d 今回の発展的な内容を 記事として公開しています