MySQL – ごく単純なUPDATEがなぜか遅い時がある

先日不可解な事象に直面しました、ごく単純な1行のUPDATE文が稀に非常に遅くなる時があったのです。稀に、といいますが検証してみたところ、50回実行して1～2回程度の発生頻度。

軽く「UPDATE 遅い」などで調べてみたところ、どれにも該当しないように見えました。

ググった情報	当方の状況
サブクエリなどを駆使してSQLが複雑である	WHERE句にPRIMARY KEYを指定して、1行1カラムをUPDATEするのみ
indexが効いていない	該当テーブルのindexはPRIMARY KEYの1カラムのみ、explainで見ても間違いなく効いている
データ量が多すぎる	1万レコード程度

といった感じです。

うーん、わからん！ということで検証して調査しました。

 

検証方法

わたしの環境の具体的な名称などはぼかしますが、問題が発生していたSQLは、ほぼ以下のようなモノでした

UPDATE `table_name` 
SET
  `col_name` = 'ランダムな文字列' 
WHERE
  `id` = 'PRIMARYなID'

 
カラム`id`はPRIMARYです、1カラムで一意に特定できるのでクッソ早いはず。

検証では’ランダムな文字列’をphp側で毎回生成し、’PRIMARYなID’は対象を1レコードに絞って、UPDATEを1回するごとにsleepを1秒挟みつつ、50回繰り返すようにしてみました。何度か試しましたが、だいたい、50回実行するごとに1～2回程度めちゃくちゃ遅延することがありました！

以下、結果を一部抜粋（sleepを1秒挟んでいるので、早い時は0.001秒程度で終わる）

・・・
31番目の処理に掛かった時間：1.0006639957428秒 
32番目の処理に掛かった時間：1.00084400177秒 
33番目の処理に掛かった時間：1.0007209777832秒 
34番目の処理に掛かった時間：12.763210058212秒 
35番目の処理に掛かった時間：1.0010838508606秒 
36番目の処理に掛かった時間：1.0006799697876秒 
37番目の処理に掛かった時間：1.0010631084442秒
・・・ 

 

原因が判明！

うーん、なんでや～、と嘆いていたのですが、検証phpを実行中になんとなくMySQL側でshow processlistを見ていました。正常時に実行すると以下のような情報が出てきます

+----+------+-----------------+------------+---------+------+-------+------------------+
| Id | User | Host            | db         | Command | Time | State | Info             |
+----+------+-----------------+------------+---------+------+-------+------------------+
|  1 | root | localhost:50793 | local_work | Query   |    0 | init  | show processlist |
+----+------+-----------------+------------+---------+------+-------+------------------+

遅延が発生していないときは、上記のshow processlistのプロセス以外にsleepで待ちのプロセスが居ました、おそらくphpが実行中でMySQL側で何も実行されていない時にコネクションだけ残っているものと思います。

が！

何回かshow processlistを実行していたら、変なやつが登場してきました。
その名も「Waiting for query cache lock」！

こんなん↓

Timeのところ、10秒とか経過しているではありませんか！なんや！？貴様！！

と、コイツでググってみたところすぐに出てきました。

どうやら、MySQLサーバのプロセスで1つ持っているクエリキャッシュ領域が更新されてロックしているので、それを待っている状態とのこと。

クエリキャッシュ領域はDISKよりも高速な記憶領域で、実行されたSQLの結果を記憶しておいて、その後に全く同じSQLが実行されたら、遅いDISKから再度読み込みなおすのではなく、アクセスが速いキャッシュから結果を返しまっせ、というモノ。

ググってすぐに出てきたことから、どうやら結構コイツに引っかかってしまうことがある模様です。

 

解決方法

じゃあ、それは分かったけど、解決策はどないすんねん、というところですが、結論から言うとわたしはまだ解決できておりません・・・

ざっくり方法は2つ

 

クエリキャッシュを無効にする

正直、既に稼働しているシステムで影響が分からないものを無効にする気にはあまりなれなかったのですが、クエリキャッシュを使っていて、むしろ遅延してしまうシステムの場合は無効にするのもアリなようです。

遅延してしまうシステム～、というとボヤっとしていますが、参照系よりも更新系が多いシステムなんかが該当するようです。クエリキャッシュが使える状況というのは、

• 1度実行されたSQLと全く同じSQLが実行される
• SELECTの結果が全く同じである（1度目のSQL実行からDBが更新されていない）

です、まあ当然といえば当然の挙動。なので、更新されるばっかりだと2個目の条件が成立しないので、キャッシュは使われず、SQLは再度実行されることになります。

まあ、ものは試し！改善されてから影響は考えればいいや～ということで無効にしてみようとしました。

が、なんとわたしが使っているレンタルサーバでは、クエリキャッシュを無効にできず・・・
変更は以下の構文で行ったのですが、アクセス権限がないってことではじかれてしまいました。。。

SET GLOBAL query_cache_size=0;
SET GLOBAL query_cache_type=0;

うーん、まあ無理ならどうしようもないっすねぇ～、とりあえずレンタルサーバ運営に問い合わせましたが、結果はまだ来ておりません。続報がきたら更新します。

 

SQL_NO_CACHEを使う

MySQLのプロセス全体に影響を及ぼさないで、対応する方法もあるようです。SQLを以下のようにするとキャッシュせずに実行できる模様

SELECT SQL_NO_CACHE `col_name` FROM `table_name`

と思ったんですが、うーむ、どうやらコイツはSELECT文にだけ有効なようで。
それもあらためて考えると当たり前で、クエリキャッシュはもともと

「1度実行したSQLの結果をキャッシュしておいて、次に全く同じSQLがきたらキャッシュから結果を返す」

というモノ、UPDATEの時はキャッシュするとかしないとかではない。

UPDATEするということはDBの値が変わるということなので、変更前の値がキャッシュから返されてしまうとマズイ！なので、UPDATEの時は該当のキャッシュがクリアされるような挙動になるはず。

なんで、SQL_NO_CACHEで対策する場合には、該当テーブルを参照しているSQLすべてにSQL_NO_CACHEをつけなければいけないことに（たぶん）なるでしょう。

そんなことはやってられんばい～ヽ( ´∇`)ﾉ

 

最終的に・・・

クエリキャッシュが無効にできるかはまだ分かりませんが、そもそもの対策として、

OLTP系のシステムで頻繁にUPDATEするような処理はなくす

という切ない結論になりそうです。

今回はセッション系の情報をカラムに持たせていて、pvのたびに更新が発生していたのですが、まあそもそもコイツはいけてなかった。。。
更新頻度は毎回じゃなくてもよかったので、極端にUPDATEされる回数を減らすことにしました。

もしくはインメモリ系のミドルとかがあれば、そっちで持たせたりとかで対応すべきなんでしょう、わたしが使っているレンタルサーバにはありませんが。

パリっとしない結末になりましたが、自サーバで設定変更ができる方は試してみてください～