例によって気になったので意訳。
Checking for a live database connection considered harmful on MySQL Performance Blog
顧客のデータベースでよく見かけて注意するのだが、クエリーを送信する前にデータベース接続がアクティブかどうかをチェックするのは大きなオーバーヘッドになる。これは、次のような擬似コードで書かれるデザインパターンに由来する。
function query_database(connection, sql)
if !connection.is_alive() and !connection.reconnect() then
throw exception
end
return connection.execute(sql)
end
多くの開発環境やフレームワークで、こういうコードになっている。これには、実際には期待したとおりには動かないと言うことと、大きなパフォーマンスのオーバーヘッドがあると言う２点で間違っている。
実はちゃんと動かない
このコードはレースコンディション(競合状態)によって動作しない。もしチェックしたときに接続がアクティブだとしても、connection.execute(sql)を実行するときにアクティブだとは保証されない。さらにもし非アクティブで再接続したとしても、同様にアクティブであるとは保証されない。
チェックして実行するのは実用的ではない。代わりに、次のように書き換えるべきだ。
function query_database(connection, sql, retries=1)
while true
try
result=connection.execute(sql)
return result
catch InactiveConnectionException e
if retries> 0 then
retries = retries - 1
connection.reconnect()
else
throw e
end
end
end
end
is_active()が無くなったのに気付いただろうか。接続がアクティブであればクエリーが実行され、そうでなければ失敗して再接続し、再度実行しようとする。
このコードだと、必要に応じてロック待ちのタイムアウトやデッドロックのときに再試行することができるようになっている。私の経験上、多くのアプリケーションで有効である。ほとんどのアプリケーションでは、こう言うときには単純に再試行するだけで、ちゃんと扱おうとはしていない。
パフォーマンスのオーバーヘッド
アクティブチェックには大抵の場合、MySQLのプロトコルレベルのコマンドであるpingかstatisticsの呼び出しか、SELECT 1のような自明なクエリーが実行される。前者のコマンドはSHOW GLOBAL STATUSで表示されるCom_admin_commandsをインクリメントし、後者のクエリーは診断を難しくする。これは多くのアプリケーションで非常に高コストとなる。ここには２つのコストがある。１つはネットワーク通信とクエリー実行時間のアプリケーションへのコストで、もう１つはデータベースサーバの負荷上昇。このデータベースサーバへの負荷はかなり大きい。何日か前に、「管理コマンドのstatistics」を使うRuby on Railsアプリケーションを見たが、全クエリー実行時間の40%がこのコマンドだった。この不必要な接続チェックを削除したところ、データベースの負荷が半分程度に削減できた。これは普通じゃない！
アプリケーションのクエリーが長いとき、追加のクエリーはノイズの中で消えてしまう。しかし高トラフィックアプリケーションはクエリー実行時間を短くするのに途方もない努力を費やし、いくつかのチューニングしたアプリケーションでは、クエリーの実行時間がミリ秒より長くならないかドキドキしている。もしデータベースで毎秒20,000クエリー走っているなら、コネクションチェックも毎秒20,000回行われていることになる。これらpingやstatisticsと言うクエリーは、アプリケーションで実行すべきクエリーと同じぐらいコストがかかっているんだ。
これはデータベースサーバへの負荷であった。アプリケーション側では、クエリー実行時間の２倍の遅延があるのがわかるだろう。クエリーを実行するときには、そのアプリケーションフレームワークがチェックのためにネットワーク通信をして、さらに別のネットワーク通信でクエリーを実行する。これもやっぱり問題だ。
問題なのは、さっきの擬似コードがレアケースに気を取られて一般的な場合にペナルティーを課しているところ。普通は接続は生きていて、確認したり再接続したりはしなくてもいい。良い方法は、レースコンディションを解決したコードを使うことだ。もし接続が切れていても、クエリーを実行するときに探せばいいだけだ。そのときまでは、全てがOKで、クエリーを実行できる。
問題となるライブラリのアップストリームメンテナが、この問題を見つけ出して解決することを願っている。アプリケーションが成長するときの大きな手助けになるからだ。ラボではうまくいっていて、現場でもそうであったとしても、パフォーマンスはすぐに問題となる。そしてそれはすごく目立つんだ。
追記
この馬鹿げた結果を見て欲しい。
# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ====== ======== ===============
# 1 0x5E796D5A4A7D1CA9 10651.0708 73.1% 120487 0.0884 ADMIN STATISTICS
# 2 0x85FFF5AA78E5FF6A 1090.0772 7.5% 23621 0.0461 BEGIN
# 3 0x6E85B9A9C9FF813E 868.0335 6.0% 6923 0.1254 UPDATE scores
# 4 0xA3A0423749EC0E37 851.0152 5.8% 6020 0.1414 UPDATE user_datas
# 5 0x813031B8BBC3B329 822.0041 5.6% 23299 0.0353 COMMIT
# 6 0xA873BBC4583C4C85 278.4533 1.9% 6985 0.0399 SELECT users user_devices
That's right, 73% of the server's load is consumed by checking to see if the connection is still alive
まとめ
これが本当だとすると、Rubyで使われているMySQLライブラリや、RailsのMySQLアダプタとかをちゃんと調べないとやばいんじゃないか？と言うわけで調査にはいります。
