LinuxからSQL Serverに接続して時間がかかるクエリーを実行するとセッションが切れてしまうときのこと

SQL Serverに接続するLinux上のRailsアプリケーションで、時間がかかるクエリーを実行したら

だいたい40秒後ぐらいに

CONNECTION RETRY: ActiveRecord::ConnectionAdapters::SQLServerAdapter 
retry #0.
TinyTds::Error: DBPROCESS is dead or not enabled: EXEC sp_executesql N'*******; SELECT @@ROWCOUNT AS AffectedRows'

のようなエラー出たのです。

ちなみに接続先は、SQL Server 2005で、接続にはfreetds 0.91を利用しています。
railsからはtiny_tdsとactiverecord_sqlserver_adapterを利用しています。

同じクエリーをwindows上ののSQLServer Management Studioから実行すれば、時間はかかるけどきちんと結果がかえってくるので、タイムアウトの設定かなぁと思って、
database.ymlにtimeoutを記述してみたものの、タイムアウトを短くする分には効くのですが、長く設定してもダメでした。

Railsの方からどうにかすれば何とかなるかなぁと思っていたのですが、
Railsを使わずにlinux上でSQL serverに接続するtsqlを使ってみたら、こちらでもエラーが出るのがわかったので、どうもRailsは関係なさそうです。

こんな感じで接続して
tsql -S server -U uuu -P ppp

クエリーを実行したら、やはり40秒後ぐらい以下のエラーが出てクエリーが失敗したのです。

Error 20004 (severity 9):
        Read from the server failed
        OS error 104, "接続が相手からリセットされました"

freetdsは、接続情報をダンプできるので、ダンプさせたところ以下のように出ました。

(util.c:156):Changed query state from READING to DEAD
(util.c:331):tdserror(0x1585150, 0x15853b0, 20004, 104)
(util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
(util.c:384):tdserror: returning TDS_INT_CANCEL(2)
(token.c:555):processing result tokens.  marker is  0()
(token.c:122):tds_process_default_tokens() marker is 0()
(token.c:125):leaving tds_process_default_tokens() connection dead

ダンプ内容を見てもさっぱりわからなかったので、tcpdumpを以下のような感じで実行してみました。

tcpdump host server and port 1433 -Xn

すると、SQL Server側からクエリー実行30秒後ぐらいから1秒おきに10回ぐらいパケットがきて、最後にサーバー側から切断要求が来ていることがわかりました。

tsqlで出たエラーメッセージの内容どおりということでした。

でSQL Serverから出ているのはなんだろう？と思って調べていたらkeep aliveでした。

SQL Serverのkeep aliveを調べていたら以下がありました。
http://msdn.microsoft.com/ja-jp/library/ms190771.aspx

Microsoft SQL Server 構成マネージャでkeep aliveの設定を見ると確かに30秒と1秒間隔が設定されていました。

なので、こちらの値を変えれば住むのですが、値を変更したらSQL Serverのサービスの再起動が必要になるようで、ちょっと再起動がすぐにはできないので、この方法は見送ります。


keep aliveに対して、linux側が返答をしないのがいけないので、返答するようにする方法を調べていたら、以下のページを見つけました。
http://permalink.gmane.org/gmane.comp.db.tds.freetds/13296

どうもlinux側のkeep alive設定をどうのこうのとあったので、ためしにSQL Server側のkeep alive設定とあわせるように以下のように設定してみました。

sysctl -w net.ipv4.tcp_keepalive_time=30
sysctl -w net.ipv4.tcp_keepalive_intvl=1

すると切断されないようになりました。
tcpdumpで見てもきちんとkeep aliveに対して返答をかけていました。

ただデフォルト値が
net.ipv4.tcp_keepalive_time=7200
net.ipv4.tcp_keepalive_intvl=75
なので今回の設定は小さくしすぎな気がします。

SQL Serverだけ見ればよいのですが、他への影響が心配なので、この方法も見送ります。


SQL Serverだけに対してkeep aliveの値を変えたいなぁと思ってたどりついたのが、freetdsのソースを変更することでした。
以下にページでsocketにkeep aliveを設定する方法が書いてあったので、これを真似します。
http://d.hatena.ne.jp/iww/20081030/setsockopt

socketのopen処理は
src/tds/net.c
で書かれているのを見つけました。SO_KEEPALIVEだけはifdefでくくられて記載されていたのでifdefを取って以下のように書き換えてコンパイルしました。

len = 1;
setsockopt(tds->s, SOL_SOCKET, SO_KEEPALIVE, (const void *) &len, sizeof(len));
len = 30;
setsockopt(tds->s, IPPROTO_TCP, TCP_KEEPIDLE, (const void *) &len, sizeof(len));
len = 1;
setsockopt(tds->s, IPPROTO_TCP, TCP_KEEPINTVL, (const void *) &len, sizeof(len));
len = 10;
setsockopt(tds->s, IPPROTO_TCP, TCP_KEEPCNT, (const void *) &len, sizeof(len));

これでkeep aliveに対してちゃんと返答を返すようになりました。


といろいろ調べましたが、実際に取った対応は、遅かったクエリーをチューニングして速くする対応を行いました。

遅いSQLに対してはチューニングがするのが正しい対応だと思うのですが、
railsのmigraionをsql serverに対して実行するときに、大きな既存テーブルに対して何かしらの変更を加えるとやはり問題が起こることがあるのがわかったので、そのうちfreetdsの方の変更もしないとダメかなぁと思っています。