MySQL障害からの復旧と書き込み性能の安定化

まとめ

発生概要

日中（業務時間帯）に本番DBサーバが異常終了。アプリケーション全体でレスポンスが数分間停止。

直後に自動再起動が行われたが、トランザクション再送による一時的な負荷上昇が発生。

初動対応（可用性確保）

リードレプリカの導入

読み取り系トラフィックをAuroraのリードレプリカへ分散し、Primaryへの負荷を軽減。
障害発生時には、アプリケーションレベルでリードレプリカへのフェイルオーバーが可能な構成としたことで、再発時の業務影響を最小化。

フェイルオーバー設定の追加

Auroraの自動フェイルオーバー機能を有効化。
Primary障害時に数十秒以内でリードレプリカへ自動切り替え可能とし、システム停止時間を最小限に抑制。

メトリクス・ログ収集の強化

CloudWatch Metrics にて以下を常時収集：

• CPU利用率、メモリ使用率、I/O Wait、接続数、クエリレイテンシ
• DatabaseConnections, Deadlocks, FreeableMemory, DiskQueueDepth などのAurora固有メトリクス

加えて、slow_query_log および performance_schema を有効化し、SQLレベルでのボトルネック分析を可能に。
これにより、障害の再現性確認や再発防止に向けたボトルネック特定の精度を向上。

調査・測定フェーズ

フェイルオーバー発生状況の確認

• Auroraイベントログ を CloudWatch Logs 経由で取得し、過去1ヶ月間のフェイルオーバー発生日時を特定。
• boto3スクリプトによりログストリームを自動走査し、フェイルオーバー発生日時を検出。
• 発生は業務時間帯に集中しており、再現性のある負荷イベントであることを確認。

# Auroraイベントログ（フェイルオーバーイベント）の取得
import boto3, datetime
logs = boto3.client('logs')

end = int(datetime.datetime.utcnow().timestamp())
start = end - 60 * 60 * 24 * 60  # 過去60日分

resp = logs.filter_log_events(
    logGroupName='/aws/rds/instance/my-aurora-instance/error',
    startTime=start * 1000,
    endTime=end * 1000,
    filterPattern='RDS-EVENT-0169'
)
for e in resp['events']:
    print(e['timestamp'], e['message'])

リソースメトリクスの分析

• CloudWatch Metrics でフェイルオーバー直前のリソース状況を確認。
• FreeableMemory の急減が見られ、メモリ枯渇がフェイルオーバーのトリガーになっていた可能性を特定。
• 同時に、CPU使用率・I/O Wait も上昇しており、I/O負荷集中が疑われた。

# CloudWatchメトリクス（メモリ・CPUなど）の取得
cw = boto3.client('cloudwatch')
metrics = cw.get_metric_statistics(
    Namespace='AWS/RDS',
    MetricName='FreeableMemory',
    Dimensions=[{'Name': 'DBInstanceIdentifier', 'Value': 'my-aurora-instance'}],
    StartTime=datetime.datetime.utcnow() - datetime.timedelta(minutes=10),
    EndTime=datetime.datetime.utcnow(),
    Period=60,
    Statistics=['Average', 'Minimum']
)
print(metrics['Datapoints'])

スロークエリログの収集と相関分析

• Auroraでは slow_query_log が CloudWatch Logs に自動転送されているため、boto3で同一のロググループからスロークエリを抽出。
• フェイルオーバー発生直前の数分間に発行されたクエリを解析し、実行時間・テーブル名・DML種別を抽出してメトリクスと照合。
• 解析の結果、複数の DELETE および INSERT クエリが同一テーブルに対して同時発行されていたことを確認。
• 特に、月初に実行される広告データの全件入れ替えバッチ処理が該当時間帯で動作していた。

# スロークエリログ（slowqueryロググループ）の抽出
resp = logs.filter_log_events(
    logGroupName='/aws/rds/instance/my-aurora-instance/slowquery',
    startTime=start * 1000,
    endTime=end * 1000,
    filterPattern='"Query_time:"'
)
for e in resp['events']:
    print(e['timestamp'], e['message'])

根本原因の特定

• 該当バッチ処理は 全件DELETE → 全件INSERT の形式でデータを再構築しており、
  Auroraのバッファプールと一時領域を大量に消費。
• これによりメモリが枯渇し、Auroraがフェイルオーバーを自動トリガー。

検証フェーズ

スロークエリの特定と原因分析

• 障害発生時に日中実行されていた 広告ログ集計バッチ処理 を特定。
• この処理では、毎月1日〜前日までの大量データに対して DELETE → INSERT を繰り返すDML操作が行われていた。
• 大量のDMLにより一時領域のメモリ使用量が急増し、I/O競合およびパフォーマンス遅延を誘発。
• ログ解析の結果、処理中に空きメモリが逼迫していたことを確認。

改善方針と設計

• MySQLの月単位パーティション化 を提案・実装。
  • 各月の広告ログを独立したパーティションに分割し、アクセス頻度の高い最新月のみをスキャン対象とする構成へ変更。
  • 従来の DELETE → INSERT を、TRUNCATE → INSERT（DMLからDDLへの置き換え） に変更。
  • メモリ使用量とトランザクションログ肥大化を防止。
  • 目的：
    • インデックス効率の向上
    • I/O局所性の改善
    • DMLオーバーヘッドの削減

-- 広告ログテーブルのパーティション構成（例：月単位）
CREATE TABLE ad_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ad_id VARCHAR(32) NOT NULL,
  click_count INT NOT NULL,
  impression_count INT NOT NULL,
  log_date DATE NOT NULL
)
ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p202509 VALUES LESS THAN (TO_DAYS('2025-10-01')),
  PARTITION p202510 VALUES LESS THAN (TO_DAYS('2025-11-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

• INSERT処理の最適化
  • データ再投入バッチのINSERTを、1行ずつではなく Multi-row INSERT 形式に変更。
  • トランザクションあたりの書き込み回数を削減し、Auroraへのネットワーク往復とログI/Oを軽減。
  • 検証環境では、1件ずつのINSERTに比べて高速化を確認。
  • 一部の検証では LOAD DATA LOCAL INFILE による直接投入も試験し、バルクロード時の性能向上を実証。

ステージング環境での検証

本番データをサンプリングし、パーティション化およびINSERT最適化の効果を検証。比較対象は「適用前（DELETE+INSERT方式）」と「適用後（TRUNCATE+Multi-row INSERT方式）」。

検証項目

• フルスキャン時のレスポンス時間
• メモリ使用量・I/O待ち時間
• バックアップ・レプリケーションへの影響

検証結果

• 処理時間のかかる DELETE 処理が、TRUNCATE により 1秒未満で完了。
• Multi-row INSERT により再投入時間を短縮。
• DML操作時のメモリ消費を大幅に削減し、I/O待機発生率も低下。
• Auroraフェイルオーバーのリスクを解消し、安定稼働を確認。

移行計画と再発防止

移行対象と優先順位付け

• 対象テーブルは数十件におよぶため、データサイズ・更新頻度・業務影響度の3軸で優先順位を定義。
• 特にサイズが大きく、月次集計や広告配信ログなどでI/O負荷が集中していたテーブルから段階的に移行を実施。
• 各テーブルのパーティション化有無・インデックス構成・スキーマ互換性を事前に棚卸しし、
  依存関係を明示化した上で移行順序を確定。

段階的な移行方針

• バッチ単位の移行：まずはバッチ処理で参照・更新されるテーブルを対象に、
  DELETE → INSERT 方式から TRUNCATE → Multi-row INSERT 方式へ段階的に切り替え。
• 段階的デプロイ：フェイルオーバー検知を有効化した状態でステージング → 本番の順に展開。
• ロールバックプラン：異常検知時には旧構成へ即時復元できるよう、Auroraスナップショットを取得。また、必要に応じて移行作業中に変更するDB設定を戻せるようパラメータグループをコピーしてバックアップ。

切替時のリスク管理

• 切替タイミングはアクセス負荷の低い深夜帯に限定。
• フェイルオーバー発生を検知した場合、即座に自動復旧（フェイルバック）を行う設定を適用。
• 移行直後は CloudWatch Logs Insights でslowquery と error ログを5分間隔でモニタリングし、クエリパフォーマンスの異常やリソース逼迫を早期検知。

継続監視と再発防止体制

• CloudWatchメトリクス監視の自動化
  • FreeableMemory, CPUUtilization, DiskQueueDepth, Deadlocks を常時監視対象に設定。
  • しきい値を超過した際はSlack通知と同時にスナップショットを自動取得。
• 週次レポート運用
  • boto3スクリプトで主要メトリクスとスロークエリ件数を定期収集。
  • 異常傾向を自動グラフ化し、性能劣化を定量的に追跡。
• 月次メンテナンスサイクル
  • パーティション追加・古いデータのDROP・インデックス再構築を定期実施。
  • バッチ処理の実行計画を分析し、クエリチューニングを継続。
• これらの仕組みにより、再発防止をシステム的に担保する体制を確立。

結果・成果

定量的成果

以下は、これまでの内容（リードレプリカ導入／自動フェイルオーバー／メトリクス強化／パーティション化＆TRUNCATE＋Multi-row INSERT など）から
定量成果として追加可能な指標案です読むポイントと計測方法を添えています。

定性的成果

• フェイルオーバー発生の根本原因を可視化し、再現性のある分析体制を確立。
• パーティション設計・バッチ構造・監視設計を一体的に見直すことで、運用チームが障害傾向を「数値で理解・判断」できる仕組みに転換。
• 開発・運用双方でパフォーマンスチューニングに関する知見を共有し、負荷試験・監視・チューニングを一連の開発工程に組み込む流れを形成。
• データ基盤チームが月次でのリスクレビューを定例化し、フェイルオーバー予兆を事前に検出・対処する運用プロセスを確立。

考察・学び

技術的観点

• フェイルオーバーは単なる障害現象ではなく、システム全体の負荷設計が臨界点を超えた結果として発生していた。
  メトリクスとスロークエリログの相関分析を通じて、「CPUやメモリ」ではなく「DML設計」が根本要因であると特定できた。

運用・組織的観点

• 障害対応を「個別事象の解決」ではなく、観測・仮説・検証のループとして整理したことで、チーム全体が共通の分析フレームワークで再発防止に取り組めるようになった。
• CloudWatch・boto3・Auroraログを連携させた観測基盤により、人依存のトラブルシュートから脱却。障害対応プロセスを自動化・可視化する第一歩となった。

今後の展開

• バッチ処理の非同期化やキューイング（SQS, Lambda連携）によって、トラフィックピーク時でも安定したスループットを維持できる構成を目指す。
• 定期的なパフォーマンスレビューを通じて、“監視のための運用”から“改善のための監視”へと発展させる。