QA@IT

[mysql] テーブルAのindexを追加したらテーブルBの更新処理でLock wait timeout exceededが発生するようになってしまいました

2690 PV

youtubeに似たようなような動画サイトを運営しております。
あるテーブルのクエリがフルテーブルスキャンになっていたため本番環境でindexを追加したら、別のテーブルの更新処理がロック待ちになってしまいアプリケーションが動かなくなってしまったのでindexを削除しました。
削除したら更新処理のロック待ちはなくなったので問題は解消しました。

環境
DB: mysql-5.5.27
アプリケーションフレームワーク: ruby on rails 4.0.2 on ruby 2.0.0p353
アプリケーションサーバ: Passenger-4.0.37

background
具体的には下記3つのテーブルを定義していて、チャンネル(channelsテーブル)毎に複数の動画(videosテーブル)があり1対多の関係です。また動画再生回数を日別に集計するための再生回数(playcountsテーブル)も定義しています。

  • videosテーブル

    | videos | CREATE TABLE `videos` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `channel_id` int(11) NOT NULL DEFAULT '0',
    `title` varchar(255) NOT NULL DEFAULT '',
    `streaming_url` varchar(128) DEFAULT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=53810 DEFAULT CHARSET=utf8 |
    
  • channelsテーブル

    | channels | CREATE TABLE `channels` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `channel_name` varchar(255) NOT NULL DEFAULT '',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=utf8 |
    
  • playcountsテーブル

    | playcounts | CREATE TABLE `playcounts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `content_id` int(11) DEFAULT NULL,
    `date` date DEFAULT NULL,
    `count` int(11) DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=937360 DEFAULT CHARSET=utf8 |
    

Action
チャンネル毎の動画を表示するときにフルテーブルスキャンになっていたためvideosテーブルのchannel_idにインデックスを貼るようにしました。処理時間は1分くらいで終わりました。

alter table videos add index idx_channel(channel_id);

チャンネル毎の動画をselectするクエリは高速になったのですが、前述したとおり全く別のテーブルの更新処理でロックtimeoutが発生するようになりました。

# Lock wait timeout exceeded; try restarting transactionが出るようになった
update playcounts set count = 100 where video_id = 10 and date = 2014-03-14;

再生回数の更新処理のリクエストに時間がかかりすぎてアプリケーションサーバのプロセスがその処理で止まってしまうので、
すぐにindexを削除しました。処理時間は10分くらいかかりました。

alter table videos drop index idx_channel;

indexを削除したら更新処理は元の速度で行えるようになりサービスは元に戻りました。

原因を探るためにstaging環境などで同じ事をして試しても更新処理は遅くならなかったため、
再度本番を同じ状態にしてshow engine innodb status;などを叩くしかなさそうですがそれはちょっとできないなと思い悩んでいます。

videosテーブルとplaycountsテーブルに外部キー制約などの関連はないのでなぜロック待ちになってしまうのか原因がわからないのですが、どなたか助言をいただけると嬉しいです。

よろしくお願いいたします。

  • stagingとproductionでは件数などは同程度なんでしょうか。
    インデックス作成後の処理の停止を確認した後、データベースの再起動はためされましたか?
    原因まではわかりませんがロックも原因はいろいろなのでそのupdate文が原因ではないかもしれません(別の処理によってロックが獲得できなかっただけかもしれない)ので、その手前で行っている処理などにも気を配ってみてみるといいかもしれません。
    -
  • playcounts に playcounts.date で テーブルパテーション を作成とかすると速度が上がるかもしれない。 -
  • 様々な助言ありがとうございます。

    playcountの更新を止めてからインデックスを追加したらロックなどかからずにALTER TABLEを行うことができました。
    どうもALTER TABLE中の更新処理がたまりすぎてロックがかかるようになってしまったようでした。
    playcountsはパーティショニングテーブルするかindexを追加しようと思います。

    本当にありがとうございました。
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。