MySQL JDBC ドライバ(MySQL Connector/J)、Java で MySQL といえばまずコレだが、これまた地味に罠が多い(そして多くの人が踏んで苦しむ)のでまとめてみた。

Summary

以下、いずれもプログラム設計時に理解しておかないと、開発中は大丈夫そうでも実用した途端に苦しまされれてしかも設計から治す羽目になる要注意な罠である:

• SELECT 結果は全部メモリに載ってしまう (デフォルト設定で)

  • 大量 SELECT する場合は FetchSize, ResultSetType を要設定
    • 利用時には制約があるので、設計段階から考慮しなければならない (後述)

• idle 時間の「合計で」コネクションが切られる

  • Connector/J 限定ではないが, これも設計時点で要考慮

• クライアントサイド プリペアドステートメントになっている (デフォルト設定で)

  • パフォーマンス向上目的に使えるものではない
  • ただし SQL インジェクション対策というセキュリティ目的では使うべき

• 裏で勝手にスレッドが走っている -> そしてメモリリーク

  • tomcat など Servlet 環境で、デプロイ時のリソースリークの原因に

SELECT 結果は全部メモリに載ってしまう

デフォルトにより、ResultSets は完全に摘出され、メモリに保存されます。 ほとんどの場合において、これは最も効果的な操作方法であり、MySQL の設計により、ネットワーク プロトコルはより簡単に実装できます。 MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.2 JDBC API 実装についての注記

MySQL Connector/J は、デフォルトで SELECT 結果をすべてメモリに格納してしまう。

そのため、大量のデータを処理するために SELECT するような実装を普通に書くと、確実に OutOfMemory の憂き目に・・・。

これを防ぐためには、

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); フェッチのサイズが Integer.MIN_VALUE の、前進専用、読み取り専用のコンビネーションは、行ごとに結果セットをストリームするようドライバに指示する信号として機能します。この後、このステートメントで作成された結果セットは行ごとに摘出されます。

の通りに ResultSetType や FetchSize を設定してやる必要がある。

さらに、追加の罠として、

接続にクエリを発行する前に、結果セットのすべての行を読まなければならず、さもなければ例外が投入されます。

すなわち「SELECT 結果を読み終わるか ResultSet を閉じるまでは、そのコネクションでは別のクエリを発行できないよ」制限もあるという・・・。

なので、

• 大量データを SELECT する
• かつ、同一トランザクションで SELECT 中に INSERT なり UPDATE なりしたい

みたいな設計は MySQL Connector/J では実現不能なので、設計段階から要注意。

このことを知らずに実装すると、全件オンメモリで SELECT して 1 件 1 件を読み取りながら INSERT/UPDATE するような実装をしてしまい、後から ResultSetType や FetchSize を直すと今度は上記制約で設計自体が破綻し...という憂き目に遭って修正者の胃が痛くなる (実体験)。

もし上記のような設計をしたくなった場合、テーブル構造を見なおすなり、一時ファイルを使うなり、あるいはヒープ容量が十分であることを確かめて使うなり、考えなおさなければならない。

idle 時間の「合計で」コネクションが切られる

Connector/J 限定ではなく MySQL そのものの仕様だが、コネクションが何もクエリ処理していない時間の合計が一定時間を超えると MySQL サーバー側から接続を一方的に切られてしまう。

"mysql wait_timeout" を日本語 google でぐぐって真っ先に出てくるページが

この設定、接続のアイドル状態が一定時間続くと MySQL 側から自動的に接続を切る設定らしい。 MySQL の wait_timeout を変更。 - Qiita

と書いているため誤解されていることが多いが、接続のアイドル時間の合計であり、一定時間続くと、ではない。

• How and Why You Should Change MySQL's wait_timeout and interactive_timeout Variables - Club Orbisius | Club Orbisius
• MySQL wait_timeoutの挙動 - kotaroito's notes

よくある例として、「このバッチは断続的にクエリを発行し続けるから何時間走っても大丈夫！」とか思い込んで実装 -> 本番で wait_timeout を食らって死亡！　という流れをとても良く見かけるが、実際には死んでしまう。そして、そういった実装は得てしてコネクション 1 本であること前提(トランザクションをずっと貼ってたりとか)であるため、コネクション再接続に耐えうる実装に治すためには大手術が必要になり修正者の怒りと憎しみを駆り立てる (実体験)。

また別のパターンとして、tomcat などで C3PO コネクションプールを普通に使っているとこの wait_timeout による断が検知されず、「本番稼働し始めて半日ほどすると謎の JDBC エラーが時々出る」みたいな事態になってしまい、コネクション死活監視の設定とテストを休日返上で羽目になったりする (実体験*1 )。

このような事態にならぬよう、サーバー側 wait_timeout を大きくする*2か、アプリ側でコネクションの死活チェックと再接続を前提とした設計にするよう考えておくべきである。

なお、普通は wait_timeout グローバル変数値が使われるが、interactive 扱いの接続では interactive_timeout 値が使われるので注意。どちらにせよ、コネクションはいつか切られる前提で設計・テストしたほうが良いが...。

クライアントサイド プリペアドステートメント

MySQL Connector/J 3.1.0 からは、サーバ側プリペアド ステートメントおよびバイナリ エンコードされた結果セットは、サーバがそれらをサポートする場合に使用されます。 MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.2 JDBC API 実装についての注記

公式の記述が罠だが、最新の Connector/J (少なくとも 5.x) は、デフォルトでクライアントサイド プリペアドステートメントを使う設定になっている。

Connector/J 5.0/5.1のデフォルト設定では、server-sideではなくclient-sideのPrepared Statementが使用されるようになっている。 Connector/J 3.1.0にてデフォルト値はtrueで登場したものの、Connector/J 5.0.5/5.1にてfalseとなった。 Connector/J 5.1とServer Side Prepared Statement - mir the developer

そもそも、プリペアドステートメントの元々のメリットは、DB サーバー側で SQL を予め解析しておいてクエリ実行時に毎度 SQL の解析処理を行わないようにすることでクエリ速度の顕著な向上が得られる点である。しかし、クライアントサイド プリペアドステートメントは言ってしまえば Connector/J がクライアント側で SQL を組み立てているだけなので、この性能上のメリットは全くない。

なので、例えばコネクションプール機構で頑張って PreparedStatement インスタンスをキャッシュ・再利用する実装をしても速度は向上しないどころかキャッシュ処理している分だけメモリの無駄かつ低速になる(実体験)。

なお、useServerPrepStmts なるパラメタを使うことでサーバーサイド プリペアドステートメントにすることもできるが、ある程度の負荷を掛けると謎の例外が頻発するなどの有り様で中々不安定である*3。Connector/J デフォルトでサーバーサイド プリペアドステートメントを使わない設定になっているのもむべなるかな。

このような事情であるので、MySQL Connector/J では PreparedStatement を性能向上のために使おうとは思わないほうが良い。

ただし、これは重要だが、PreparedStatement を使うことはセキュリティ面では大変好ましい。SQL を自力の文字列処理で生成することは SQL インジェクション脆弱性の温床になるので、PreparedStatement のパラメタ機能を使うことはセキュリティのための至極基本的な処置である (性能面ではメリットがなくても)。

裏で勝手にスレッドが走っている -> そしてメモリリーク

この件: http://bugs.mysql.com/bug.php?id=68556

本件は日本語情報があまりないのだが、実は MySQL Connector/J は裏で "AbandonedConnectionCleanupThread" なるスレッドを勝手に走らせている。このスレッドは(ぱっとソースを見た限り)放置された Connection を close してくれる。

この親切機能の何が問題であるかというと、tomcat 環境で

1. 勝手に AbandonedConnectionCleanupThread が起動する (WebApp クラスローダーで)
2. アプリの war なりをデプロイし直す (別の WebApp クラスローダーが作られる)
3. 1 で起動した AbandonedConnectionCleanupThread は元気に走り続ける
4. 古い WebApp クラスローダーは GC されない (1 のスレッドから参照されるため)
5. デプロイ解除(配備解除)前のアプリの全ての static オブジェクトも GC されない (クラスローダーから参照)
6. 激しくメモリリーク

という事態を招くためである。

表面的に見える事象としては、

• アプリの再デプロイ後すぐに or しばらくして OutOfMemoryError
• war を配備解除するたびにメモリが減っている
• 大量のオブジェクトがリークしている

という厄介な状態であり、しかも上記の理由でクラスローダーまるごとリークしているため、真犯人の Connector/J 以外のオブジェクトが目立ってしまい、原因究明もしづらい (特にヒープのダンプだけ見ていると原因究明がとてもむずかしい)。

tomcat への war デプロイ後に「とりあえず tomcat 再起動しないとメモリが足りなくなる」現象が起きている場合、本件を疑う価値があるであろう。

なお、MySQL 公式のバグチケットは Tomcat 7 の JreMemoryLeakPreventionListener を使えということで close されているが*4、この解決策を使うと MySQL JDBC ドライバが Common クラスローダーでロードされるため、tomcat 上の全アプリが同じ Connector/J を使うことになる (Connector/J の持っている static 変数もアプリをまたいで共有することになる)。

公式のバグチケット: http://bugs.mysql.com/bug.php?id=68556

そのため、JreMemoryLeakPreventionListener で使うということは、tomcat 上の Connector/J を使う全アプリが運命共同体になることを意味する*5。

それを許容できる、あるいはそもそも tomcat 上に war が 1 つしかないなら良いが、そうでないならば JreMemoryLeakPreventionListener を使うのではなく、

class MyServlet extends Servlet {
    @Override public void destroy(){
        // このアプリの classloader で走っている MySQL Connector/J のスレッドを停止させる
        // これによってこのアプリの classloader がまるごとメモリリークすることを防ぐ
        // 注: これを使う場合はこのアプリ自身で Connector/J をロードしていること、tomcat 全体で Connector/J をロードしている場合ここで止めてはいけない
        // http://saiya-moebius.hatenablog.com/entry/2014/08/20/230445
        AbandonedConnectionCleanupThread.shutdown();
    }
}

するべきであろう *6。