はじめに

この投稿は、アイスタイル 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';