mariadb-connectorの接続でmysql_pingを発行するとLostConnectionになる

Question

現在、オフコンからOpen系へのCOBOLのリプレースのシステムを作っており、
opensource-cobolからRDBへの接続をするため
C言語でmariadb-connectorを使ってmariadbからデータを取得する部分の
コーディングをしております。

そこで、長時間帳票印刷等で、データベースアクセスがなくなることで、
データベースへの接続が切れてしまうことへの対応をしています。

複数スレッドにし、サブのスレッドから、
mysql_pingの関数で定期的に接続を確保する仕様を検討しているのですが、
LostConnection,または,Server has gone awayとエラーがでて、
突然途中で落ちてしまいます。

原因がどこにあるか？
または、原因を調査するポイントはどこか？
を教えていただければと思います。

環境は下記のような構成です。
CentOS release 6.8
gcc (GCC) 4.4.7
MariaDB 10.0.27
opensource-cobol-1.3.1J

Answer 1

DBへの接続については、「切れないようにする」よりも、「切れていたら再接続する」という実装にしたほうが柔軟性が高いです。たとえば、アプリケーションサーバが暇な時にDBサーバをリブートするなどの運用が可能になりますし、何かの障害でDBサーバが落ちることにも対応できます。

mysql_ping もそのために用意された関数だと思います。 MYSQL_OPT_RECONNECT を指定しておけば、古い DBの接続を利用し始める前に mysql_ping を１回呼び出すことで、切れていたら自動的に再接続されます。

原因がどこにあるか？

おそらく、サーバ側の無通信タイムアウトだと思います。長時間アクセスがない接続を強制的に切断する機能がDBサーバ側で動作しているのではないでしょうか。以前も同様の質問があったのですが、このタイムアウト時間を伸ばすことは根本的な解決になりません。前述のとおり、「切れていたら、再接続する」ように仕様を変更することをおすすめします。

（未検証です。ドキュメントを見て回答しております。間違っていたらごめんなさい。）

Comments

[yamame]

回答ありがとうございます

その方がいいのは分かっておりますが、
トランザクションが残ったままになるケースがあり再接続するとテーブルにロックがかかってしまいます。

また、時間が短くてもLost connectionになることがあるためタイムアウトでは無いようです

[mit0223]

> タイムアウトでは無いようです

あ、そうですか。すると、別スレッドから mysql_ping しても対策にはならないですよね。接続が切れているのにトランザクションが残ったままになるということは、クライアント側のプログラムの問題の可能性があると思います。サーバ側の都合で強制切断しているのであれば、トランザクションはアボートするはずですから。

サーバ側のログには何も出ていないのでしょうか？

[mit0223]

パケットが大きすぎるということはあるでしょうか？
http://dev.mysql.com/doc/refman/5.6/ja/packet-too-large.html
問い合わせ結果が1GBtyes を超えるようなことがあれば、これに該当するようです。

あと、mysql サーバとアプリケーションサーバの間にロードバランサ、ファイアウォールルータなどが入っていませんか？入っているのであれば、その機器により、接続が廃棄されている可能性があります。機器のログをチェックしてはいかがでしょうか。

[yamame]

パケットのサイズや、間に入っているファイヤウォールルータなどは、調整してみましたが、
問題ないことを確認しておりました。

皆様の回答で解決がなされないので、こちらをベストアンサーとさせていただきます。

Answer 2

直接の回答ではありませんが、
汎用機からオープン系に移行したシステムのチューニングに何度か呼ばれたことがあります。

正規化がいい加減、結果SQLがとっても複雑。
ほとんどの SELECT文のFROM句にTABLE, VIEW か SYNONYM が1つしか書いていない。(NoSQLではない)
SELECTの並びから呼ばれるFUNCTIONにSELECT文が実行されているのが多数。
そのためディスクへのI/Oがボトルネックに。

SQLを理解しているメンバーを使わなかったのが原因ですが、どれもきちんと直すにはあまりにも
時間も費用もかかるので、いちばんボトルネックになっている部分をちょっと直して終わりました。

Comments

[yamame]

返事が遅くなってすみません。

おっしゃる通りで、
お客様からの要望で、
「現在の構造を変えるな」との条件付きのため正規化はほぼ出来ていない状況です。
また、I/Oがボトルネックになっており、
DBチューニングを下記の項目をしてみたり、
HDDをSSDに変えたりしているなかで
今回の質問のような現象になっています。
>いちばんボトルネックになっている部分をちょっと直して終わりました。
とありますが、どのような項目でしょうか？
教えていただけると助かります。

DBチューニングの設定変更した項目は主に下記のものです。
innodb_buffer_pool_size
read_buffer_size
sort_buffer_size
query_cache_size