FLUSH TABLES WITH READ LOCKの速度について

Tweet気になったので、How fast is FLUSH TABLES WITH READ LOCK?の意訳。

FLUSH TABLES WITH READ LOCKってどれだけ時間がかかる？

ちょっとまえのMySQLカンファレンスでバックアップソフトのベンダーのとこに行ったんだ。で、バックアップソフトについて話をし始めたんだけど、それはデータベース全体のロックを取得する、FLUSH TABLES WITH READ LOCKを使ってるということだった。でも彼は、ロックは「数ミリ秒」しかかからないと誇らしげに語ってた。バックアップソフトベンダーはそう思っているようだが、これは大きな勘違いだ。

実際にはこのコマンドがロックする時間はわからない。研究室のテスト環境ではそりゃミリ秒で終わるかも知れないが、実際にはもっと時間がかかる。数分から下手すれば数時間もかかることがあるだろう。そしてこの間、サーバは完全にロックされる(readonlyでもないんだ！)。なぜそうなのか、このコマンドが何をしてるか見てみよう。このコマンドには、いくつか重要な処理が含まれている。

ロックのリクエスト

FLUSH TABLES WITH READ LOCKコマンドは、すぐにグローバルロックを要求する。すると、そのロックが許可される前に、システムの中で動作している全てのプロセスがロックアウトされる。理論上、結局は読み出しのロックを取得するだけなのだから、このことは悪いようには見えない。他の読み出しロックだけが必要なコマンドとは、共存できるはずだ。

でも実際には、多くのテーブルは読み書きされている。最初の書き込みクエリーがグローバルロックによってブロックされると、それに続く読み出しクエリーは、その前の書き込みクエリーが要求したテーブルロックによってブロックされる。結局、実質的にテーブルは排他ロックされてしまい、新しいリクエストは全てブロックされてしまう。読み出しのクエリーでさえも！

ロックを待つ

FLUSH TABLES WITH READ LOCKがロックを取得する前に、ロックを持っている実行中のものは全て終わらなければならない。要するに、SELECTも含めて、全てのクエリーが終わる必要がある。もしすごい時間のかかるクエリーが動いていたり、テーブルロックするトランザクションがあったりすると、FLUSH TABLES WITH READ LOCKは全ての処理が終わってロックが解放されるまでブロックされることになる。これには結構な時間がかかるんじゃないかと思う。僕にとっては顧客のサーバにログインして走ってるクエリーを見ることなんて珍しくないこと。FLUSH TABLES WITH READ LOCKの前に、こういうクエリーが走っていたりすると、結果はさんざんなことになる。

このプロセスが動いているときにシステムがどう見えるかについて、例を示そう。

Id 6の接続はログインできていないことがわかる。これはMySQLのコマンドラインクライアントが-Aオプション付きで起動されていて、テーブルとカラムのリストを取り出そうとタブ補完しようとしたところだ。また「Flushing tables」と言うのも間違っている。まだテーブルをフラッシュしていない。ロックを得ようと待ってるだけなんだ。

テーブルのフラッシュ

FLUSH TABLES WITH READ LOCKがようやくロックを取得すると、データのフラッシュが始まる。でも全てのストレージエンジンに適用される訳じゃない。MyISAMは通常処理中ではディスクにデータをフラッシュしようとはしないにもかかわらずだ。MyISAMはOSが適切なときにディスクにフラッシュすることを当てにしている。結果的に、MyISAMが多いとOSのバッファにダーティーなのが多くなってしまい、フラッシュするのに結構な時間がかかってしまう。この間、システムはロックされたままになってしまい、全てが終わってようやくFLUSH TABLES WITH READ LOCKは完了し、レスポンスを返すことになる。

ロックの保持

このコマンドの最後の部分は、ロックが保持される時間。このロックはUNLOCK TABLESや他のいくつかのコマンドで解放される。FLUSH TABLES WITH READ LOCKを使っているたいていのバックアップシステムでは、ロックの間の処理は、ファイルシステムのスナップショットの初期化のような比較的短い時間で終わる。だから実際には処理の中で非常に短い時間を費やすだけのことが多い。

結論

実環境で動作するバックアップシステムは、FLUSH TABLES WITH READ LOCK willが瞬時に完了すると仮定するべきではない。非常に時間がかかる場合があるからだ。例えばMyISAMとInnoDBが混在している場合がそう。でも多くの場合は混在させたりしないし、バックアップシステムがグローバルロックを回避するように設定できるはずだ。InnoDBのみの場合だと、ロックを取得する必要はないはずだ。だからロックフリーなバックアップをとれる。バックアップベンダーはこのことを考慮して製品を開発すべきだ。

まとめ

MyISAM使っていると、FLUSH TABLES WITH READ LOCK willが瞬時に終わらない場合があるので注意。あとInnoDBだとそもそもロック取得しなくてもいいから、別の方法を使うべき。要調査。