はじめに

この投稿は、アイスタイル Advent Calendar 2024 の18日目の記事です。

アイスタイルのDBREグループに所属している新卒2年目のisomです

アイスタイルでは、さまざまなデータベースを活用していますが、その中の一つとしてAmazon Aurora MySQLを使用しています。

この記事では、稼働中のサービスにメンテナンス時間を設けることなく、Aurora MySQLにインデックスを追加した際の体験談を共有します。

過去のインデックスの作成の事例

以前、オンプレミスのMySQL環境（バージョン：8.0.23）で、メンテナンス時間を設けずにインデックスを追加した際に、メタデータロックが原因で障害が発生したことがありました。

当時はオンラインDDLを使用しているので、問題が起きないと考えていました。

しかし、本番でインデックス作成クエリを実行したところ、メタデータロックの取得時に、実行中の更新クエリーの完了をインデックス作成クエリが待ち、後続のクエリが全て実行待ち状態になる現象が発生してしまいました。その結果、30分ほどユーザーにサービスが提供できない状態が発生してしまいました。

そのため、今回は安全にインデックスの作成ができるように下記の対応を行いました。

今回の対応

障害を防ぐために以下の3つの対応を行いました。

1. lock_wait_timeout の設定

MySQLの設定において、lock_wait_timeout を設定することで、他のプロセスとロックが競合した際に、すぐにインデックスの追加クエリがエラーを返すように変更しました。これにより、ロック待ちのプロセスがシステム全体に影響を及ぼすリスクを軽減できました。
※ドキュメント

lock_wait_timeoutのデフォルト値は、31536000秒で設定されています。
下記のクエリで現在の設定の確認ができます。

lock_wait_timeoutの設定の確認

SHOW VARIABLES LIKE 'lock_wait_timeout';

今回はlock_wait_timeoutを1秒に変更して実行しました。

lock_wait_timeoutの変更

SET SESSION lock_wait_timeout=1;

2. インデックス作成前にロックの確認

インデックスを作成前に下記のクエリを実行し、ロックがないかの確認をおこないました。

ロックの確認

SELECT
ml.OBJECT_TYPE,
ml.OBJECT_SCHEMA,
ml.OBJECT_NAME,
ml.LOCK_TYPE,
ml.LOCK_DURATION,
ml.LOCK_STATUS,
ml.OWNER_THREAD_ID,
t.PROCESSLIST_ID
FROM
performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE
ml.OBJECT_NAME = 'テーブル名' --テーブル名を記載
AND ml.LOCK_STATUS = 'GRANTED';

3. ステージ環境での検証

本番環境と同等の負荷を再現するために、ステージング環境で実際の読み書き負荷をシミュレーションしながら検証を実施しました。

事前に想定されるトラフィックを再現。
メタデータロックの発生や他プロセスへの影響を確認。

これらの検証を通じて、安全にインデックスを追加できることを確認し、本番環境で実行しました。

本番環境でインデックス作成した結果

下記が実際に本番環境で作成した際の結果となります。

サーバ構成

インスタンスクラス：db.r6g.xlarge
台数：更新系1台、参照系1台

作成時間

テーブル名	行数	秒数
Aテーブル	21,249,957行	3分48秒
Bテーブル	43,959,517行	4分45秒
Cテーブル	64,185,737行	7分16秒

メトリクス

CPUUtilization

WriteLatency

WriteIOPS

ReadIOPS

まとめ

上記の対応を経て、サービスを停止することなく本番環境でインデックスの追加を無事に完了することができました。

最後まで読んでいただき、ありがとうございました！
この記事が、同じような課題に取り組む方々のお役に立てれば幸いです