その手の平は尻もつかめるさ

ギジュツ的な事をメーンで書く予定です

mysql Connector/J の設定で効きそうなやつ

mysql Connector/J を使う時に色々効きそう (あるいは効かなそう) な設定項目を調べたのでまとめておくこととします.なお,対象としている mysql のバージョンは5.6 (以降),Connector/J のバージョンは5.1.34 (以降) です.
Connector/J は設定できる項目が多くて迷ってしまうんですが,道標となる資料も幾つかあるのでそれについてもまとめておきます.

何か追記すべき事項があったら適宜追加してゆきたいと思っています.「これも入れたほうが良いのでは無いか」というようなご指摘ありましたらご一報ください.

設定項目

characterEncoding

おなじみの文字コード.要設定.
ちなみに以下の様なテクがある.

Driver doesn't support utf8mb4 for servers 5.5.2 and newer. The driver now auto-detects servers
configured with character_set_server=utf8mb4 or treats the Java encoding "utf-8" passed via
"characterEncoding=..." as utf8mb4 in the "SET NAMES=" calls it makes when establishing the connection.

MySQL Bugs: #54175: Connector/J cannot handle supplemental characters supported by utf8mb4

mysqld のサーバサイドで "utf8mb4" の文字コード設定をしておいた上で,この項目 (characterEncoding) に "utf8" の設定を渡してやると,ドライバがサーバの設定を自動検出して,コネクションを確立するときに "SET NAMES=utf8mb4" を実行してくれるということみたい.

characterSetResults

なぜか忘れられがちな方の文字コードの設定だがこちらも設定した方が良さそう.

alwaysSendSetIsolation

この項目が true になっていると常にトランザクション分離レベルをサーバに送信することになる.
これは非効率的っぽいので false にすると良さそう.

elideSetAutoCommits

この項目が true になっている場合,Connection.setAutoCommit(boolean) が実行された場合に,サーバとドライバの autocommit の state が異なるときにだけ set autocommit=n を発行するようになる.
つまり,Connector/J はデフォルトだと set autocommit=n をついつい発行しがちなのだけれど,この項目が true になっているとむやみにそれを発行することを抑制することが出来るということのよう.

useServerPrepStmts

この項目が true になっていると server side prepared statement を使うようになる.
MySQL (Connector/J) の server side prepared statement はあんま意味が無さそうな上に正しく扱わないと危険 (memory leak にも似た症状が出る) なので有効にするなら自己責任で,という感じがある.
ちなみに Connector/J のデフォルト値は false (昔のデフォルト値は true だったが途中で「アカン」となって false になったという過去があるとのこと).

See Also:

なお, true にした場合,prepared statement が close されずに leak するというバグがあるとのこと.


cacheServerConfiguration

これを true ににすると,mysql サーバの設定をキャッシュするようになる (SHOW VARIABLESSHOW COLLATION の結果をキャッシュしている様子).
これが false だと connection を確立する時に何個かクエリを飛ばしてサーバの状態を確認するようになってしまうので,connection establish のコストを安く済ませる為に true にすると良さそう.
ちなみにこれを有効にするのは Java 界では常識とのこと!

See also:
MySQL Connector/J を利用するときは cacheServerConfiguration=true を設定する - tokuhirom blog

useLocalSessionState

true にすると autocommit やトランザクション分離レベルをサーバに問い合わせずにローカルの情報を元に判断するようになる.
コストを安くするために true にしておくと良さそう.

maintainTimeStats

サーバへの接続が失敗した時などに、より詳細なエラーメッセージを出力する為に,ドライバはアイドル時間の計算などをしており,そのためにさまざまな内部タイマなどが走っている.
この項目を false にするとそれらの処理をバイパスすることができ,スループットが向上するとのこと.

useUnbufferedInput

true にするとサーバからのレスポンスを読むときに BufferedInputStream を使わなくなる.
false にすると recv() システムコールが減るという記述があるが,ホイホイ false にしてしまって良いものかちょっと判断がつかない.攻めてる気がする.

useReadAheadInput

true にすると,サーバからのレスポンスを読むときに最適化されたノンブロッキングな buffered input stream を使うようになる.
useUnbufferedInput と同じく false にすると recv() システムコールが減るという記述があるが,ホイホイ false にしてしまって良いものかちょっと判断がつかない.攻めてる気がする.

jdbcCompliantTruncation

JDBC Connection Overheads
ここに書いているような感じで,false に設定してやると要らんクエリ発行が減る様子.

jdbcCompliantTruncation のドキュメント読んでると *1,false にしてしまったが最後,ヤベークエリが飛んでもスルーされるようになってしまいそうだけれど,STRICT_TRANS_TABLES が有効になっているとその点は防げる感じで,結果的に要らんクエリが減るだけとなった.めでたしめでたし.
STRICT_TRANS_TABLES が有効になっている場合は false にしても良いんじゃないでしょうか.

rewriteBatchedStatements

true になっていると,マルチステートメントの INSERT/UPDATE をまとめるというやつ.
PreparedStatement の addBatch() や executeBatch() を使ってクエリを作っている時に役に立つ.それ以外の時は関係がない.
そうした機能を使うのであれば有効にすると良さそう.ちなみにこの機能を有効にするためには,同時に useServerPrepStmts が false である必要がある (Connector/J のレイヤで Prepared Statement を構築しなければならないため).

See also:
MySQL Connector/Jにおける大量INSERTのチューニング - SH2の日記

cachePrepStmts

HikariCP が有効にすることを推奨している.true に設定することで Prepared Statement のキャッシュを有効になる.

prepStmtCacheSize

HikariCP が設定することを推奨している.Prepared Statement を最大何個キャッシュするかを設定.HikariCP オススメは250.

prepStmtCacheSqlLimit

HikariCP が設定することを推奨している.Prepared Statement の SQL クエリの文字長 (バイト数) の最大.HikariCP オススメは2048.

参考になる資料

MySQL :: MySQL Connector/J Developer Guide :: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

Connector/J の設定可能項目が全て載っているドキュメンテーション.基本的にこれを読んでいくことになる.

MySQL :: MySQL Connector/J Developer Guide :: 5.1.1 Properties Files for the useConfigs Option

同じく Connector/J のドキュメント.ざっくりとした設定例みたいなのが載ってて参考になる.鵜呑みにするとかなり攻めた設定になりそう.

MySQL Configuration · brettwooldridge/HikariCP Wiki · GitHub

HikariCP を使う上で有効にすべき設定が載っている.

JDBC Connection Overheads

Connector/J がウッカリ発行してしまう不要なクエリを減らす法などが書いている.

http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf

Connector/J を使う上でパフォーマンス改善に役立ちそうな情報がまとまっている pdf.Sun 時代の資料だが参考になる.

*1:Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings (MySQL 4.1.0 and newer)? This property has no effect if the server sql-mode includes STRICT_TRANS_TABLES.