■ 1. 概要
- 発表者はTimeeのプラットフォームエンジニアリングチームに所属する徳富博
- Aurora MySQLを使用するサービスの急成長で顕在化した7つの罠とその対処法を紹介する
- 急成長の文脈として以下の3点が挙げられる
- アクセス数の急増によりボトルネックが顕在化
- データ量の急増によりクエリ処理速度が低下しインデックス設計の重要性が増大
- 開発者増加によりMigration頻度が増加しALTER TABLEのロック影響が無視できなくなった
■ 2. 罠①: DDL実行時の落とし穴
- メタデータロック（MDL）に関する誤解:
- 「オンラインDDLならロックはゼロ」という誤解が存在する
- アルゴリズムに関わらずMDLは必ず発生する
- ALGORITHM=COPYは開始から完了まで排他MDLを継続保持しDMLが完全にブロックされる
- ALGORITHM=INPLACEは初期化・実行・最終フェーズの3段階でMDLを取得する
- ALGORITHM=INSTANTは最終フェーズ（commit table definition phase）でのみ排他MDLを取得する
- MDL待機の発生フロー:
- DMLが共有MDLを取得した状態でALTER TABLEが排他MDLを要求し待機する
- 後続のSELECT/UPDATEが共有MDLを取れずキューに詰まる
- アクセス数が多いプロダクトではこれが大きな問題になる
- Aurora固有の挙動:
- Vanilla MySQLではMDL待ちがレプリカ側に溜まり待機する
- Aurora MySQLではDDL反映がクラスターボリューム共有によりほぼ即時反映される
- Auroraリードレプリカでは実行中のクエリが強制終了（Lost connection）される
- MDL待ちは溜まらないが単発エラーが発生する
- Aurora lost connectionへの対応:
- Rails 7.1から導入された自動リトライ機能を活用する
- lost connectionエラー発生時に一定回数クエリを自動リトライするよう設定する
- ALTER完了後にリトライが成功しアプリ側にはリカバリされる
- 外部キー制約追加時の注意（COPYアルゴリズム強制）:
- foreign_key_checks=1（デフォルト）のまま外部キーを追加するとCOPYアルゴリズムが強制される
- COPYアルゴリズムではテーブル全コピーの間INSERT/UPDATE/DELETEが完全停止する
- migration実行前にSET SESSION foreign_key_checks=0を実行することでINPLACEになりDMLを止めずにオンライン実行できる
- ただしforeign_key_checks=0は整合性チェックをスキップするため実行前にデータ整合性を確認する
- 外部キー追加時の参照先テーブルへのSロック:
- COPYアルゴリズムでは既存データを新テーブルへコピーする際に参照先テーブルの整合性チェックが走る
- InnoDBはその間参照先テーブルにSロック（共有ロック）をかけ続ける
- 親テーブルへのUPDATE/INSERT/DELETEはXロックが必要なためSロックと競合しタイムアウトが発生する
- foreign_key_checks=0でINPLACEにすることで子テーブルのCOPYが走らず参照先テーブルへのSロックも発生しない
■ 3. 罠②: Drop Table中にMDLによるデッドロックが大量発生
- MySQLのレイヤー構造の理解:
- MySQLサーバーレイヤー（SQL Layer）がメタデータロック（MDL）を管理する
- InnoDBストレージレイヤーが行ロック・ギャップロック・ネクストキーロックを管理する
- 一般的にイメージするデッドロックはストレージレイヤーのものだが今回のデッドロックはサーバーレイヤーのもの
- SHOW ENGINE INNODB STATUSに出ないデッドロック:
- MDL（メタデータロック）はサーバーレイヤーで管理されInnoDBロックモニターの対象外
- SHOW ENGINE INNODB STATUSやinnodb_print_all_deadlocksには出力されない
- Datadog/CloudWatchにも何も出ない
- Performance Schemaのmetadata_locksテーブルで確認可能
- MDLデッドロックの発生メカニズム（DROP TABLE + FK）:
- セッション①がordersテーブルをSELECTし共有MDLを保持する
- セッション②がFKを持つ子テーブルcustomers_ordersをDROP TABLEしようとする
- DROP TABLEはcustomers_orders・customers・ordersの排他MDLを順次要求する
- ordersの排他MDLはセッション①の共有MDLが邪魔し待機する
- セッション①がcustomersをSELECTしようとするがセッション②がcustomersの排他MDLを保持中
- セッション①はcustomers待ち・セッション②はorders待ちとなり相互待機（デッドロック）が発生する
- 再発防止策:
- DROP TABLE前に外部キー制約を先に削除することで排他MDLの競合対象を最小化する
- Migration前に本番環境で参照先テーブルへのDMLが走っていないことを確認してから実行する
■ 4. 罠③: Auroraの場合レプリカの重いクエリがWriterに影響する
- 問題の概要:
- Redashによる数時間の集計クエリがReader上で実行されていた
- AuroraはWriter/Reader間でクラスターボリューム（Undoログ）を共有する
- Readerで長時間トランザクションが実行されるとUndoログがパージできなくなる
- 古い行バージョンが蓄積しRollbackSegmentHistoryListLengthが増大する
- 共有ボリュームを通じてWriter側のクエリ劣化が発生する
- 対策（トランザクション分離レベルの活用）:
- 集計クエリには分離レベルをREAD COMMITTEDに設定することでUndoの肥大化を抑制できる
- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED を実行してから集計クエリを実行する
- REPEATABLE READ（デフォルト）はNon-repeatable readとPhantom readを防ぐがUndoログ蓄積が多い
- READ COMMITTEDはNon-repeatable readとPhantom readが起きうるがUndoログ蓄積が少ない
- 集計系はリアルタイム整合性より安定稼働が重要でREAD COMMITTEDで十分なケースがほとんど
■ 5. 罠④: 同時リクエストによるデッドロック
- パターン①: ギャップロックデッドロック:
- ギャップロック（Gap Lock）はインデックス上の「レコードとレコードの間の隙間」に対してかかるロック
- SELECT FOR UPDATEでレコードが存在しない場合その検索範囲に他トランザクションがINSERTできないよう封鎖する
- Gap Lock同士は競合しないため複数トランザクションが同じギャップにGap Lockを取れてしまう
- INSERTはGap Lockと競合するためお互いのGap Lockに阻まれた状態で相互ブロックが生まれやすい
- 発生シナリオ: TxAとTxBが同時に「email3を検索してなければINSERT」を実行する
- 両者がGap Lockを取得後INSERTを試みると相手のGap Lockに阻まれ相互待機（デッドロック）が発生する
- 対策①: INSERT IGNOREまたはINSERT ON DUPLICATE KEY UPDATEを使い1クエリで完結させGap Lockを取らないようにする
- 対策②: FOR UPDATEをやめ存在確認のみなら共有ロックまたはロックなしにしXロックが本当に必要かを設計から見直す
- パターン②: S→X昇格デッドロック:
- SロックはSELECT LOCK IN SHARE MODEで取得し他のSロックとは競合しない
- XロックはSELECT FOR UPDATE/UPDATE/DELETEで取得し全てのロックと競合する
- SロックをXロックに昇格しようとすると相手のSロックが邪魔になり相互ブロックが発生する
- 発生シナリオ: TxAとTxBがFKを持つ子テーブルにINSERT後親テーブルをUPDATEする
- FK INSERTでInnoDBが親テーブルの参照行にSロックを自動取得する（S同士は競合しないので両方成功）
- その後UPDATEでXロックが必要となるが相手のSロックが邪魔で待機する
- TxAはTxBのSロック待ち・TxBはTxAのSロック待ちとなりデッドロックが発生する
- 対策①: UPDATEを先に実行することで先にXロックを取得し後続のFK INSERTのSロック取得を同一TX内のXで成功させる
- 対策②: INSERT前にSELECT FOR UPDATEで先にXロックを取得し順番待ちにすることで相互ブロックを回避する
■ 6. 罠⑤: 意外に広いロック範囲
- パターン①: UPDATEでテーブル全体がロックされる:
- インデックスなしのUPDATEではテーブル全体をスキャンし全行に排他ネクストキーロックがかかる
- 本来1ユーザーだけ更新したいのにテーブル全体がロックされ他トランザクションのINSERT/UPDATE/DELETEがほぼ停止する
- 対策: 適切なインデックスを追加することでロック範囲を劇的に狭める
- パターン②: 外部キー制約で親テーブルにSロックが伝播する:
- group_usersにINSERT/DELETEするとInnoDBが参照整合性チェックのため参照先の親行にSロック（共有ロック）を自動取得する
- バルク処理が大量のSロックを保持し続けると退会・更新系APIのXロック取得がブロックされる
- 対策: バルク処理を小さいバッチ（例: 100件ごと）に分割して途中でCOMMITしSロックの保持時間を短縮する
■ 7. 罠⑥: 急成長でじわじわ悪化するスロークエリ
- 問題の構造:
- リリース直後はデータ数が少なくフルスキャンでも数msで問題なし
- 数ヶ月後にデータが数百万件に増加し同じクエリが数秒に悪化する
- 急成長期にそのエンドポイントへのアクセスも急増し処理が詰まりはじめる
- 最終的にタイムアウト多発・接続枯渇・DB負荷急上昇という障害に至る
- データ量の増加×アクセス数の増加が「無害だったクエリ」を障害の引き金にする
- 気づきにくい理由:
- リリース時のレビューでは少量データでテストしており問題が見えない
- データ増加は緩やかなため劣化が少しずつ進み気づくのが遅れる
- コード変更なしで突然遅くなるため原因特定に時間がかかる
- AIコードレビューでもクエリの実行計画までチェックできない
- Datadog Database Monitoringによる改善体制:
- 発見〜改善のフローは「トレースで検知 → 実行計画を確認 → AIが仮説を提示 → チームが修正」
- スロークエリの検出: 実行時間・頻度でランキングし問題クエリを一目で特定できる
- 実行計画の可視化: EXPLAINの結果をUIで視覚的に確認しFull Scan/非効率な結合を検知する
- AIによる改善提案: 実行計画をもとに適切なインデックス構成をAIが自動提案する
- チームの自律改善: SREを介さず各開発チームが自分たちのクエリを改善できる
■ 8. 罠⑦: データ増加でBuffer Poolが足りなくなる
- InnoDB Buffer Poolとは:
- テーブルのデータページ・インデックスページをメモリにキャッシュする領域
- クエリ実行時MySQLはまずBuffer Poolを確認しヒットすればメモリから返す
- ミスすればストレージから読み込みBuffer Poolに保存する（Read I/O発生）
- AuroraではBuffer Poolサイズ = DBInstanceClassMemory × 3/4で自動計算される
- 急成長期に起きること:
- データ量の増加によりワーキングセットがBuffer Poolを超えはじめる
- 一度読み込んだデータが追い出され次のクエリでまたストレージから読み直す
- インデックスの増加によりBuffer Poolを消費しインデックス自体もページとしてキャッシュされるため無計画な追加は逆効果になりうる
- 結果として毎クエリでストレージから読み直しが発生しRead I/Oが急増しクエリが急激に遅くなる
- 監視すべきメトリック:
- Buffer Pool Hit Ratio: キャッシュヒット率でここが下がるとストレージ読み取りが急増する
- Read IOPS/Read Latency: Buffer Poolミスが増えると連動して悪化する
- Buffer Pool使用率: 100%張り付きはワーキングセットがBuffer Poolに収まりきれていないサイン
- チューニングのアプローチ:
- インスタンスサイズアップ
- パラメータチューニングでbuffer poolサイズを増やす
- 不要なインデックスを削除する
■ 9. まとめ: どう向き合うか
- レイヤーを意識する:
- サーバーレイヤー（MDL）なのかInnoDB（行ロック）なのかで観測方法・対策が全く異なる
- 「どのレイヤーで起きているか」を最初に問う習慣を持つ
- 仕組みを理解してから運用する:
- 「InnoDBバッファプール」「MVCC/Undoログ」「メタデータロック」の概念を知っているだけでトラブルシューティングの精度が格段に上がる
- 高トラフィックは「通常では起きない」競合を日常化する:
- 理論上起きにくい競合もリクエスト数が増えれば確率論的に必ず発生する
- 設計段階から競合を想定したロック設計・インデックス設計を行う
- 改善を積み重ねる:
- 単発の対応で終わらせずポストモーテム→再現実験→予防策→モニタリング追加のサイクルを回す
- Aurora運用知識そのものがプロダクトの継続性に直結する