急成長でぶつかったMySQLの罠とその向き合い方

Transcript

1. 徳富 博（プラットフォームエンジニアリングチーム） 急成長でぶつかったMySQLの罠と その向き合い方 @yannKazu1 ふかぼりSRE
2. 自己紹介
3. 自己紹介 徳富 博 - 所属: タイミー プラットフォームエンジニアリング チーム - 好きな技術: Go, Ruby, TypeScript, AWS, GCP… - 野菜を育ててます
4. 今日お話しすること このセッションの位置づけ 以前、アドベントカレンダーで Aurora MySQL の運用ナレッジについてのブログ (https://tech.timee.co.jp/entry/2025/12/03/104738） を書きました。今日はその内容をベースにして、どう向き合っているかについてご紹介できればと思います。 なお、弊社では Aurora MySQL を使っているので、その前提でお聞きいただけると助かります！
5. 急成長の文脈 🚀 プロダクトの成長によるアクセス数の急激な増加 急成長によりトラフィックが急増。今まで問題として認知されていなかった部分が 顕著なボトルネックとして表面化するようになった 📦 プロダクトの成長によるデータ量の急激な増加 レコード数が膨れ上がり、以前は一瞬で終わっていたクエリの処理速度が 目に見えて低下。インデックス設計の重要性が一気に増した 👥 開発者増加による Migration頻度の増加 開発チームの規模が大きくなるにつれてスキーマ変更・カラム追加の頻度が急増 ALTER TABLE に伴うロックの影響が無視できなくなってきた
6. 本日のアジェンダ 01 01 罠①：DDL実行時の落とし穴 「安全なはず」が止まる・落ちる 02 罠②：Drop Table中にに MDLによるデッドロックが大量発生する しかも SHOW ENGINE INNODB STATUS には出てこない 03 罠③：レプリカの重いクエリが Writerに影響する Undo ログと RollbackSegmentHistoryListLength 04 罠④：同時リクエストによるデッドロック ギャップロック・ロック昇格 05 罠⑤：意外に広いロック範囲 UPDATEのネクストキーロック・外部キーのSロック 06 罠⑥：急成長でじわじわ悪化するスロークエリ リリース時は無害だったクエリが、データ増加×トラフィック増加で詰まる 07 罠⑦：急成長で Buffer Poolが足りなくなる キャッシュが効かなくなりRead I/Oが急増する 08 まとめ：どう向き合うか 改善策と運用プラクティス
7. CHAPTER 02 罠①：DDL 実行時の落とし穴 「安全なはず」が止まる・落ちる
8. この章で話すこと 1 メタデータロックに注意 ALTER は止まらない？ INPLACE でも ALTER の開始・完了時に MDL（排他ロック）は必ず発生する。 その間、後続の SELECT / UPDATE は MDL 待ちキューに詰まる。 Aurora レプリカの挙動 Aurora レプリカは MDL 待ちが溜まらず、待機中の SELECT がエラーで落ちる。 → lost connection をリトライできる仕組みが必要。 2 外部キー制約に注意 FK 追加 = COPY 強制 foreign_key_checks=1（デフォルト）のまま外部キーを追加すると COPY アルゴリズムが強制される。 COPY = テーブル全コピー ＋ その間 INSERT / UPDATE / DELETE が完全停止。 対策 ridgepole / migration 実行前に SET SESSION foreign_key_checks = 0 を先に実行。 これだけで INPLACE になり、DML を止めずにオンライン実行できる。
9. 1: メタデータロックに注意
10. 「オンラインDDLなら安全」という誤解 アルゴリズムが何であれ、MDL は必ず存在する ✗ よくある誤解 「オンラインDDLなら日中でも 止まらず特に何も気にせずALTERできる」 「ロックはゼロ」 ✅ 実際には オンラインDDLはデータ再構築をバックグラウンドで行うが ALTER で排他MDLを取得するタイミングがある → その間の SELECT / UPDATE はMDL待ちキューに詰まる ALTER のアルゴリズムによって MDL 取得タイミング・条件は変わる ALGORITHM=COPY 新テーブルへの全コピー方式。 開始から完了まで継続して排他MDLに近 い強いロックを保持し、DMLも基本的にブ ロックされる。 ロック時間が最も長くなる。 ALGORITHM=INPLACE ① 初期化フェーズ: 共有アップグレード可能 MDLを取得 ② 実行フェーズ: DMLと並走。必要な操作は 実行準備中に短時間取得 ③ 最終フェーズ (commit table definition): 排 他MDLに昇格し常に取得 ALGORITHM=INSTANT データディクショナリのメタデータのみ変 更。 準備・実行中は排他MDLを取らないが、 最終フェーズ (commit table definition phase) に排他MDL取得 重要: アルゴリズムに関わらず MDL は必ず発生する。「オンラインDDL = ゼロロック」は誤り。 ※ docs: dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html
11. メタデータロック（ MDL）とは テーブル定義の整合性を守るためのロック MDLの種類 共有MDL (Shared) SELECT / DML 実行時に自動取得 他の共有MDLとは競合しない 排他MDL (Exclusive) ALTER TABLE / DROP TABLE 時 すべての共有MDLをブロック MDL 待機の発生フロー DML（SELECT/UPDATE） 共有MDL取得 ↓ ALTER TABLE 実行 排他MDLを要求 → 待機 ↓ 後続クエリ (SELECT/UPDATEなど） 共有MDLを取れずキューがたまる (アクセス数が多いプロダク トだとこれが大きな問題になることがある ) 💡 クエリが「詰まった」ように見えるのはこれが原因
12. Aurora リードレプリカ特有の挙動 Vanilla MySQLとは別の動きをする Vanilla MySQL（RDS MySQL含む） Aurora MySQL DDL反映方法 SQLスレッドで順次適用 クラスターボリューム共有 → ほぼ即時反映 リードレプリカの挙動 Waiting for table metadata lock が溜まって待機 実行中のクエリが強制終了される （Lost connection...） ロック待ち レプリカ側でMDL待ちが ズラッと並ぶ MDL待ちは溜まらない が単発エラーが発生
13. Aurora レプリカの lost connection への対応 MDL発生時にSELECTがエラーになるなら、再実行できるようにする Aurora MDL時の挙動 ALTER TABLE 実行タイミングで Readレプリカで実行中のSELECTが 「Lost connection to MySQL server during query」エラーで落ちる 弊社の対応方針 Rails の7.1から導入された自動リトライ機能を活用し、 lost connection エラー発生時に 一定回数クエリを自動リトライするよう設定。 (リトライするかは一定の 条件あり) ALTER 完了後にリトライが成功するため アプリ側にはリカバリされる。
14. 22: 外部キー制約追加時に注意
15. 外部キー追加の罠①： COPY アルゴリズムになる 🚨 「インデックス追加と同じ感覚で FOREIGN KEY を追加したら、テーブルコピーが走ってしまった」 外部キー制約追加時のアルゴリズム ❌ foreign_key_checks = 1（デフォルト） ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); → ALGORITHM=COPY が強制される テーブル全体をコピー その間 INSERT / UPDATE / DELETE がすべてブロック ✅ foreign_key_checks = 0 を先にセット SET SESSION foreign_key_checks = 0; ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); → ALGORITHM=INPLACE になる DML を止めずにオンラインで実行できる テーブルコピーが走らない 📖 MySQL 公式ドキュメントより： 「外部キー制約の追加: INPLACE アルゴリズムは foreign_key_checks が無効な場合にサポートされます。それ以外 の場合は COPY アルゴリズムのみがサポートされます」 ⚠ 注意: foreign_key_checks=0 にすると制約チェックをスキップするため、整合性違反データが混入するリスクがある。実行前にデータ整合性が保 たれていることを確認すること。
16. 外部キー追加の罠②：参照先テーブルにも S ロックがか かる Migration 中に別テーブルへの更新 API が突然タイムアウトする なぜ起きるか：COPY アルゴリズムでは既存データを新テーブルへコピーする際、参照先テーブルの整合性チェックが走る。 InnoDB はそ の間、参照先テーブルに S ロック（共有ロック）をかける。 テーブル構成 users（親テーブル） id PK name email FK → orders（子テーブル・ FK追加対象） id PK user_id ← FK追加 amount Migration 中のロック状態 orders → COPY で完全ブロック users → S ロック UPDATE / INSERT / DELETE がブロックされる Migration 中に何が起きるか ① Migration 開始 ALTER TABLE orders ADD FOREIGN KEY ... が実行される COPY 開始 ② 整合性チェック InnoDB が orders の全行を走査し users.id の存在を確認 → この間ずっと users に S ロックをか け続ける S Lock ③ ユーザー退会 API が呼ばれる UPDATE users SET deleted_at=NOW() WHERE id=123 ← X ロックが必要だが S ロックと競合 → Lock wait timeout exceeded TIMEOUT ✅ 対策： foreign_key_checks=0 で INPLACE にすれば子テーブルの COPY が走らず、参照先テーブルへの S ロックも発生しない。
17. CHAPTER 02 罠②：外部キーを持つテーブルへの DDL操作 がMDLによるデッドロックを引き起こす 「安全なはず」が止まる・落ちる・見えないデッドロックになる
18. 前提知識: MySQLのレイヤー構造を理解する 「サーバーコア」と「InnoDB」はどう違う？ 🔷 MySQL サーバーレイヤー（ SQL Layer） ・SQL パーサー ・クエリオプティマイザー ・メタデータロック（MDL）管理 ・スレッド/接続管理 ・レプリケーション制御 🟢 InnoDB ストレージレイヤー バッファプール : データ・インデックスのメモリキャッシュ 行レベルロック (InnoDB): レコードロック・ギャップロック・ネクスト キーロック MVCC / Undo ログ: 複数バージョン同時実行制御 ・RollbackSegment トランザクション管理 : ACID保証、デッドロック検出 <= よく発生す るデッドロックはこっち 一般的にイメージするデッドロックはストレージレイヤーのものですが今回対象としているのはサーバーレ イヤーのデッドロック
19. SHOW ENGINE INNODB STATUSに出ないデッドロック Migrationでデッドロックが起きたのに Datadogに何も来ない… 🚨 起きたこと Migration(Drop Table) 実行中にデッドロックが大量発生。 通常ならデッドロックが発生した場合 Aurora → CloudWatch → firehose → Datadog にロックモニター情報が届くはずが、今回は何も来 なかった。 CloudWatch の生ログをたどってもロックモニター自体が出力されておらず、 Aurora 側でそもそも検知されていない状態だった。 なぜ検知されなかったのか InnoDB ストレージエンジン層のロック （検知できる） InnoDB ストレージエンジン層のロック競合を検知する 対象：行ロック / ギャップロック / ネクストキーロック （SHOW ENGINE INNODB STATUS / innodb_print_all_deadlocks） MDL(サーバーコア ) による デッドロック（検知できない） MDL（メタデータロック）はサーバーレイヤーで管理される → InnoDB ロックモニターの対象外 → Datadog / CloudWatch に何も出ない 💡 「ログが来ない = デッドロックじゃない」は誤り。 MDLデッドロックはロックモニターに出てこない。
20. MDLによるデッドロックの発生メカニズム 「DROP TABLE 中間テーブル」が親テーブルの排他 MDLを取りにいく orders 親テーブル customers 親テーブル customers_orders 子テーブル（FK あり） タイムライン＆ロック状態 時刻 セッション① アプリ セッション② Migration orders MDL状態 customers MDL状態 T1 START TX; SELECT * FROM orders; ー> ordersに共有MDL S (①) なし T2 DROP TABLE customers_orders; → customers_orders 排他MDL ✅ → customers 排他MDL ✅ → orders 排他MDL …待機⏸ S(①) ↑X(②)待 X (②) T3 SELECT * FROM customers; → customers 共有MDL …待機⏸ （②が排他MDL保持中） orders の排他MDL 待機中… S(①) ↑X(②)待 X(②) ↑S(①)待 T3 時点：①は customers 待ち ／ ②は orders 待ち　⇄ 相互待機 → デッドロック ❌
21. MDLデッドロックの再発防止策 問題のあるマイグレーション -- 外部キー制約がある状態でいきなり DROP DROP TABLE customers_orders; -- → orders, customers の排他MDL競合が起きる → 改善 改善後のマイグレーション -- 先に外部キー制約を削除してから DROP ALTER TABLE customers_orders DROP FOREIGN KEY fk_customers_orders_order_id; ALTER TABLE customers_orders DROP FOREIGN KEY fk_customers_orders_customer_id; DROP TABLE customers_orders; -- → 排他MDLの競合対象が最小化される 学んだこと 1 サーバーコア管理の MDL: InnoDB ロックモニターでは検知されない。Performance Schema の metadata_locks テーブルで確認 可能 2 外部キーはロック範囲を広げる : DROP TABLE 前に外部キー制約を削除しておくことで競合リスクを大幅低減(CREATE TABLEも 同様の事象が発生します) 3 Migration前の確認を徹底する : 本番環境では参照先テーブルへのDMLが走っていないことを確認してから実行
22. CHAPTER 03 罠③：Auroraの場合レプリカの重いクエリ がWriterに影響する Undo ログと RollbackSegmentHistoryListLength
23. 「レプリカなら安心」は本当か？ Aurora のクラスターボリューム共有の罠 Aurora アーキテクチャ Writer （Primary） Reader （Replica） 共有クラスターボリューム （Undo ログ共有） Redash（数時間の集計クエリ） → Reader経由 何が起きたか Reader上で数時間クエリ実行 Redashによる長時間トランザクション ↓ Undo ログがパージできない 古い行バージョンが蓄積 → RollbackSegmentHistoryListLength↑ ↓ Writer クエリ劣化 共有ボリュームを通じてWriter側に影響
24. 対策：トランザクション分離レベルを活用する 集計クエリには(分析基盤では) 分離レベルをREAD COMMITTED を設定することで Undo の肥大化を抑制できる -- セッション単位でトランザクション分離レベルを下げる SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- その後、集計クエリを実行 SELECT SUM(amount) FROM orders WHERE ... トレードオフの整理 分離レベル Non-repeatable read Phantom read Undoログ蓄積 REPEATABLE READ（デ フォルト） 防ぐ 防ぐ（製品によるがMysqlの 場合は防ぐ） 多い ⚠ READ COMMITTED（集計 向け） 起きうる 起きうる 少ない ✅ 💡 集計系はリアルタイム整合性より安定稼働が重要。 READ COMMITTED で十分なケースがほとんど。
25. CHAPTER 04 罠④：同時リクエストによる デッドロック ギャップロック・ロック昇格
26. パターン①：ギャップロック デッドロック パターン②： S→X昇格 デッドロック
27. パターン①：ギャップロックとは何か 「存在しないレコードの隙間」にかかるロック ギャップロック（Gap Lock） ＝ インデックス上の「レコードとレコードの間の隙間」に対してかかるロック SELECT ... FOR UPDATE でレコードが存在しなかった場合、その「検索した範囲」に他のトランザクションが INSERT できないよう封鎖する。 インデックスイメージ（ email カラム） email 1 email 5 email 1 ～ email 5 の「ギャップ」（ email3 など存在しない） ギャップロックの特性 🔍 例えばSELECT FOR UPDATE で検索ヒットしない時などに発生 WHERE email='email3' で検索して行が存在しなければ、 InnoDB はそのギャップ範囲をロックする。「念のため封鎖しておく」動作。 🤝 Gap Lock 同士は競合しない 同じギャップに対して複数のトランザクションが Gap Lock を取れてしまう。「お互いに封鎖できた」状態になる。これが落とし穴。 💥 INSERT は Gap Lock と競合する ギャップ内への INSERT は、そのギャップに Gap Lock を持つどのトランザクションとも競合する。 → 相互ブロックが生まれやすい。
28. パターン①：ギャップロック デッドロックの流れ Gap Lock 同士は取れる → そのまま両方 INSERT しようとする シナリオ：Tx A と Tx B が同時に「email3 を検索して、なければ INSERT する」という処理を実行する Tx A Tx B ① SELECT FOR UPDATE → email3 が存在しない → Gap Lock 取得 SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock ✅ Gap Lock SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock ✅ Gap Lock 💡 Gap Lock 同士は競合しない → 両方が「取れた」と思っている ② INSERT email3 を試みる → 相手の Gap Lock に阻まれる INSERT INTO users (email) VALUES('email3'); -- Tx B の Gap Lock に阻まれる -- → 待機 ⏸ WAIT INSERT INTO users (email) VALUES('email3'); -- Tx A の Gap Lock に阻まれる -- → 待機 ⏸ WAIT ③ Tx A は Tx B の待ち、Tx B は Tx A の待ち → 相互待機 → デッドロック 💥
29. パターン①：なぜ起きるか・どう防ぐか 「存在チェック → INSERT」という処理パターンが引き起こす なぜ起きるか -- 「なければ INSERT する」という処理を複数スレッドが同時に実行すると … SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock INSERT INTO users (email) VALUES ('email3'); -- 相手の Gap Lock に阻まれる → WAIT 対策 ✅ ① INSERT IGNORE または INSERT ... ON DUPLICATE KEY UPDATE 「存在チェックしてから INSERT」という2ステップをやめて、1クエリで 完結させる。 Gap Lock が取れない→そもそも競合しない。 INSERT IGNORE INTO users (email) VALUES ('email3'); -- または INSERT INTO users (email) VALUES ('email3') ON DUPLICATE KEY UPDATE updated_at = NOW(); ✅ ② FOR UPDATE をやめる（存在確認だけなら共有ロック or ロックなし） 「INSERT するかどうか未定」の段階で FOR UPDATE（X ロック）を取 るから Gap Lock が問題になる。 本当に X ロックが必要か設計を見直す。 -- 存在確認だけなら FOR UPDATE は不要 SELECT * FROM users WHERE email='email3'; -- INSERT するときに UNIQUE 制約に任せる
30. パターン②： S→X昇格 デッドロック
31. パターン②： Sロック・Xロックとは何か 「共有ロックは競合しない」が落とし穴になる ロックの種類 S ロック（共有ロック） 取得方法：SELECT ... LOCK IN SHARE MODE 「読み取り中。他が更新するのは待ってほしい」 S ロックを持っている間、他の S ロックは許可される。 ただし X ロックはブロックされる。 X ロック（排他ロック） 取得方法：SELECT ... FOR UPDATE / UPDATE / DELETE 「更新中。誰も触らないでほしい」 X ロックは他のいかなるロック（ S も X も）とも競合する。 X ロックを取るには全ての S ロックが解放される必要がある。 ロック互換性 S ＋ S 競合しない ✅ 両方が同時に読める S ＋ X 競合する ❌ 読んでいる間は更新できない X ＋ X 競合する ❌ 更新中は誰も触れない 💥 落とし穴：「S ロックを持ったまま X ロックに昇格しようとする」と、相手の S ロックが邪魔になる → 相互ブロック
32. パターン②：テーブル構成 reviews（親）← FK — review_activities（子）を同一 TX 内で操作する テーブル構成 reviews（親テーブル） PK id bigint offer_id bigint NOT NULL reviewer_id bigint NOT NULL status varchar 'pending' etc. created_at datetime updated_at datetime review_activities（子テーブル） PK id bigint FK review_id bigint → reviews.id action varchar 'submitted' etc. performed_by_id bigint NOT NULL created_at datetime updated_at datetime
33. パターン②： S→X昇格 デッドロックの流れ FK INSERT で暗黙の S ロック → 同 TX 内で UPDATE → 相互ブロック シナリオ：Tx A・Tx B がほぼ同時に「子テーブルへ INSERT → 親テーブルを UPDATE」という処理を実行する テーブル： reviews（親） ←FK— review_activities（子） Tx A Tx B ① FKがあるため review_activities に INSERT → InnoDB が reviews の参照行に S ロックを自動取得（ S 同士は競合しないので両方成功） INSERT INTO review_activities (review_id, action) VALUES (1, 'submitted'); -- InnoDB: reviews.id=1 に -- S ロック自動取得 ✅ S Lock INSERT INTO review_activities (review_id, action) VALUES (1, 'checked'); -- InnoDB: reviews.id=1 に -- S ロック自動取得 ✅ -- （S同士は競合しない） S Lock 💡 S ロック同士は競合しない → Tx A・Tx B どちらも取得成功 ② reviews を UPDATE → X ロックが必要 → 相手の S ロックに阻まれて待機 UPDATE reviews SET status = 'approved' WHERE id = 1; -- X ロック必要だが -- Tx B の S ロックが邪魔 -- → 待機 ⏸ WAIT UPDATE reviews SET status = 'rejected' WHERE id = 1; -- X ロック必要だが -- Tx A の S ロックが邪魔 -- → 待機 ⏸ WAIT ③ Tx A は Tx B の S ロック待ち、Tx B は Tx A の S ロック待ち → 相互待機 → デッドロック 💥
34. パターン②：なぜ起きるか・どう防ぐか 根本原因 # 同一 TX 内で FK 子テーブルに INSERT してから親テーブルを UPDATE すると… review_activity = ReviewActivity.create!(review_id: id, ...) # → reviews に S ロック（暗黙） review.update!(status: 'approved') # → reviews に X ロックが必要 → 相手の S ロックと競合 対策 ✅ ① UPDATE を先に実行する（処理順の入れ替え） ← 最もシンプル 先に X ロックを取ってしまえば、後続の FK INSERT の S ロック取 得は同じ TX が X を保持しているため成功する。 別の TX から S ロックを取られる前に X を確保できる。 # UPDATE（X ロック）を先に実行 review.update!(status: 'approved') # その後 INSERT → S ロック取得は # 自 TX が X を持っているので成功 ReviewActivity.create!(review_id: id, ...) ✅ ② INSERT 前に SELECT FOR UPDATE で先に X ロックを取る 処理順を変えられない場合の代替手段。 FOR UPDATE で reviews の X ロックを先取りしておくことで、FK INSERT 時の S ロック取得が「昇格」ではなく同一ロックの再確認 になる。 # 先に X ロックを取得 review = Review.lock.find(id) # FOR UPDATE # 以降の INSERT・UPDATE は順番待ちになり # 相互ブロックが発生しない ReviewActivity.create!(review_id: id, ...) review.update!(status: 'approved')
35. デッドロックとの向き合い方
36. AIコードレビューでデッドロックが発生しやすい実装を指摘するように している(シフトレフト )
37. デッドロック発生を即座に検知・分析できる仕組み (シフトライト ) Aurora エラーログ → CloudWatch → Firehose → Datadog 前提設定： Aurora クラスターパラメーターグループで innodb_print_all_deadlocks = 1 を設定する 監視パイプライン Aurora Error Log デッドロック発生時に ロックモニター情報を出力 CloudWatch Logs エラーログを リアルタイム収集 Subscription Filter フィルタリング Kinesis Firehose ログを ストリーム配信 Datadog Logs クエリとロック情報を すぐに確認できる Datadog で確認できること どのクエリとどのクエリでデッドロックしたか ロックモニターに 2 つのトランザクションのクエリが出力される どのロックを持っていて、何を待っていたか HOLDS THE LOCK / WAITING FOR THIS LOCK で保持・待機ロックが確認でき る どちらがロールバックされたか WE ROLL BACK TRANSACTION (N) で被害トランザクションを特定できる いつ・何件発生しているか ログのタイムスタンプと件数でデッドロックの頻度を把握できる
38. ロックモニターの読み方 *** (1) TRANSACTION: ← ① TX番号・どのクエリかを確認 TRANSACTION 421, ACTIVE 0 sec inserting MySQL thread id 10, query id 500 app INSERT INTO users (email,name) VALUES ('email3@example.com','Alice') ← クエリA *** (1) HOLDS THE LOCK(S): ← ② 保持しているロック index idx_email of table 'app'.'users' trx id 421 lock_mode X locks gap before rec ← ギャップロック(X) を保持中 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ← ③ 待っているロック index idx_email of table 'app'.'users' trx id 421 lock_mode X locks gap before rec insert intention waiting ← INSERT をブロックされている *** (2) TRANSACTION: ← ④ もう一方のTXも同じ構造を確認 TRANSACTION 422, ACTIVE 0 sec inserting MySQL thread id 11, query id 501 app INSERT INTO users (email,name) VALUES ('email3@example.com','Bob') ← クエリB *** (2) HOLDS THE LOCK(S): index idx_email of table 'app'.'users' trx id 422 lock_mode X locks gap before rec ← B も同じギャップロックを保持 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: index idx_email of table 'app'.'users' trx id 422 lock_mode X locks gap before rec insert intention waiting ← A のギャップロックに阻まれている *** WE ROLL BACK TRANSACTION (2) ← ⑤ TX(2)=クエリBがロールバック対象 読み方まとめ : ① どのクエリか → ② 何を持っているか → ③ 何を待っているか → ④ 相手も同じ構造か（＝相互待機） → ⑤ どちらが ロールバックされたか
39. CHAPTER 05 罠⑤：意外に広い ロック範囲 UPDATEのネクストキーロック・外部キーの Sロック
40. パターン①： UPDATE でテーブル全体がロックされる パターン②：外部キー制約で親テーブルに Sロックが伝播す る
41. パターン①： UPDATE でテーブル全体がロックされる 「1行だけ更新したかった」のに … ❌ インデックスなしのUPDATE UPDATE orders SET status = 'shipped' WHERE user_id = 123; -- user_id にインデックスなし！ 何が起きるか ⚠ テーブル全体をスキャンして user_id=123 を探す ⚠ スキャンしたすべての行に排他ネクストキーロック ⚠ 本来1ユーザーだけ更新したいのにテーブル全体が ロック ⚠ 他トランザクションの INSERT/UPDATE/DELETE がほぼ停 止 ✅ 適切なインデックス付き -- インデックスを追加！ CREATE INDEX idx_orders_user_id ON orders(user_id); UPDATE orders SET status = 'shipped' WHERE user_id = 123; -- user_id=123 の行だけロック インデックスがある場合 ✓ インデックスで対象行を特定 → 最小限のスキャン ✓ ロック範囲が劇的に狭まる ✓ 他トランザクションへの影響を最小化
42. パターン②：外部キー制約で親テーブルに Sロックが伝播す る
43. 前提：テーブル構造と外部キー制約 どのテーブルがどうつながっているか テーブル定義（ DDL） -- 親テーブル① CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) ); -- 親テーブル② CREATE TABLE groups ( id INT PRIMARY KEY, name VARCHAR(100) ); -- 子テーブル（中間テーブル） CREATE TABLE group_users ( user_id INT NOT NULL, group_id INT NOT NULL, PRIMARY KEY (user_id, group_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (group_id) REFERENCES groups(id) ); テーブルの関係 users 🔑 id (PK) name email groups 🔑 id (PK) name group_users 🔑 user_id (FK) 🔑 group_id (FK) なぜSロックが親テーブルにかかるのか group_users に INSERT / DELETE すると、InnoDB は 参照整合性チェックのため 参照先の親行を読みにいく。 このとき自動で 共有ロック（Sロック） を取得する。 コードに書かなくても、外部キーがある限り必ず発生する。
44. バッチ処理と APIが競合する group_users の操作が users の UPDATE をブロックする シナリオ : グループ移動バッチ（ group_users を大量 DELETE→INSERT）が動いている間に、ユーザー退会 API（users を UPDATE）が実行され る トランザクション① グループ移動バッチ BEGIN; -- group_users を削除 DELETE FROM group_users WHERE user_id=123, group_id=1; -- → InnoDB が users.id=123 に S ロック自動取得 -- 新グループに追加 INSERT INTO group_users VALUES(123, 2); -- → users.id=123, groups.id=2 に S ロック -- ループ継続（大量件数を COMMITせず処理） -- S ロックを保持し続ける ... トランザクション② ユーザー退会 API BEGIN; UPDATE users SET deleted_at = NOW() WHERE id = 123; -- X ロックを取得したいが -- TX① が users.id=123 の S ロックを -- 保持中 → 待機 -- → Lock wait timeout exceeded -- ユーザー退会が失敗する 🔴 S ロック（共有）は複数取れるが、 X ロック（排他）は S ロックが全部解放されるまで取れない → 退会・更新系 API がすべてブロックされる ✅ 対策: バルク処理を小さいバッチ（例： 100件ごと）に分割して途中で COMMIT → Sロックの保持時間を短縮する
45. CHAPTER 06 罠⑥：急成長でじわじわ悪化する スロークエリ リリース時は無害だったクエリが、データ増加 ×トラフィック増加で詰まる
46. 「リリース時は問題なかった」のに詰まる罠 データ量 × アクセス数の掛け算で顕在化する ❌ 典型的なパターン リリース直後 データ数が少ないのでフルスキャンでも数ms。問題な し。 数ヶ月後 データが数百万件に増加。同じクエリが数秒に。 急成長期 そのエンドポイントへのアクセスも急増。処理が詰まり はじめる。 障害 タイムアウト多発・接続枯渇・DB負荷急上昇。 なぜ気づきにくいか ・ リリース時のレビューでは少量データでテストしており問題が見えな い ・ データ増加は緩やかなため、劣化が少しずつ進み気づくのが遅れ る ・ コード変更なしで突然遅くなるため、原因特定に時間がかかる ・ AIコードレビューでもクエリの実行計画まではチェックできない データ量の増加 × アクセス数の増加 → 「無害だったクエリ」が障害の引き金になる 💡 急成長サービスでは「今動いている」は「将来も動く」を意味しない。データ量を見越したクエリ・インデックス設計が必要。
47. Datadog Database Monitoring で各チームが自律的に改善 できるようにしている 各開発チームがトレースから実行計画まで自力で辿れる仕組み 発見〜改善のフロー トレースで検知 🔍 遅いエンドポイントを トレースで特定 ↓ どのクエリが遅いか → 実行計画を確認 📋 Datadog DB Monitoring で EXPLAIN を確認 ↓ どこにコストがかかるか → AI が仮説を提示 🤖 Datadog の AI 機能が 実行計画を解析し インデックス案を提案 → チームが修正 ✅ 各開発チームが 自律的にインデックスを 追加して改善 Datadog Database Monitoring で何ができるか スロークエリの検出 実行時間・頻度でランキング。問題クエリを一目で特定できる 実行計画の可視化 EXPLAIN の結果をUIで視覚的に確認。 Full Scan / 非効率な結合を検知 AI による改善提案 実行計画をもとに適切なインデックス構成を AI が自動で提案 チームの自律改善 SRE を介さず各開発チームが自分たちのクエリを改善できる
48. せっかくなので実際の Datadog画面をチラ見せします
49. DBMとトレース連携するとトレースから遅いクエリを特定し、その実行計画を確認できる
50. 実行計画がビジュアライズされているので問題箇所が知識がなくても特定し やすい(①でビジュアライズ切り替え可能 )
51. さらにAIによる改善提案も見れる (②をクリックすると見れる )
52. CHAPTER 08 罠⑦：データ増加で Buffer Poolが 足りなくなる 「キャッシュが効いている」はいつまでも続かない
53. InnoDB Buffer Pool とは MySQL の性能を最も左右するメモリ領域 Buffer Pool = テーブルのデータページ・インデックスページをメモリにキャッシュする領域 クエリ実行時、MySQL はまず Buffer Pool を確認する。ヒットすれば Buffer Pool(メモリ)から返す。ミスすればストレージから読み込む buffer poolに保存する。 クエリ実行フロー ① クエリ実行 ② Buffer Pool を確認 → → ✅ ヒット → メモリから返す（高速・低コスト） ❌ ミス → ストレージから読込（低速・ I/O 発生） Aurora での Buffer Pool サイズ innodb_buffer_pool_size = DBInstanceClassMemory × 3/4 インスタンスサイズに応じて自動計算。スケールアップすると Buffer Pool も比例して拡張される。 (調整も可能) 💡 「どの程度のデータを Buffer Poolに載せられているか」 がクエリ性能を大きく左右する
54. 急成長でBuffer Poolが足りなくなる 「ヒットしていたはず」のデータがキャッシュから溢れる 急成長期に起きること 📈 データ量の増加 テーブルの行数・サイズが増え、ワーキングセットが Buffer Pool を超えはじめる。 一度読み込んだデータが追い出され、次のクエリでまたストレージから読み直す。 🗂 インデックスの増加 機能追加でインデックスが増えるたびに Buffer Pool を消費する。 インデックス自体もページとしてキャッシュされるため、無計画な追加は逆効果になりうる。 🐌 結果：毎クエリでストレージから読み直しが発生 → Read I/O 急増 → クエリが急激に遅くなる
55. 監視すべきメトリック Buffer Pool Hit Ratio CloudWatch / Aurora メトリクス キャッシュヒット率。ここが下がるとストレージ読み 取りが急増する Read IOPS / Read Latency CloudWatch / Aurora メトリクス I/O 読み取りの頻度と遅延。 Buffer Pool ミスが増え ると連動して悪化 チューニングのアプローチ インスタンスサイズアップ パラメータチューニングでbuffer poolサイズを増や す Buffer Pool 使用率 Performance Schema 100% 張り付きはワーキングセットが Buffer Poolに 収まりきれていないサイン 不要なindexを削除する
56. CHAPTER 07 まとめ：どう向き合うか 改善策と運用プラクティス
57. SREとしてどう向き合うか レイヤーを意識する サーバーレイヤー（ MDL）なのかInnoDB（行ロック）なのかで観 測方法・対策が全く異なる。「どのレイヤーで起きているか」を 最初に問う習慣を持つ 仕組みを理解してから運用する 「InnoDB バッファプール」「 MVCC/Undoログ」「メタデータロッ ク」の概念を知っているだけでトラブルシューティングの精度 が格段に上がる 高トラフィックは「通常では起きない」競合を日常化 する 理論上起きにくい競合も、リクエスト数が増えれば確率論的 に必ず発生する。設計段階から競合を想定したロック設計・イ ンデックス設計を行う 改善を積み重ねる 単発の対応で終わらせず、ポストモーテム →再現実験→予防 策→モニタリング追加のサイクルを回す。 Aurora運用知識そ のものがプロダクトの継続性に直結する