MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその2：余計なお喋りを止める編

昨日の [MySQL Casual Advent Calendar 2017](https://qiita.com/advent-calendar/2017/mysql-casual) は@tikamotoさんの [MySQLerのためのウィンドウ関数入門](https://qiita.com/tikamoto/items/a9298442ba0ef45112fe) でした。

この記事は同Advent Calender 10日目、[MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその1：バッチ編](https://qiita.com/hmatsu47/items/0a3b48c86abdcd2cc884) の続きです。

前回は「プロパティをあれこれ変えて」と言っておきながら1つしか変えていませんでしたので、ここからが実質的な「本番」です。

MySQL Connector/Jは、ユーザが送信したクエリ・コマンド以外にも `SELECT @@…` や `SET …` など、世話焼きのおかんのようによく喋りますが、できるだけ無駄口を叩かないように仕向けるのが今回の検証テーマです。

なお、検証用のコードやDB定義などは、[前回](https://qiita.com/hmatsu47/items/0a3b48c86abdcd2cc884)のものを流用します。

# 1. 検証対象のプロパティ

 * **[5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html)（MySQL Connector/J 5.1 Developer Guide）**

のうち、

 * `characterEncoding` エンコーディングの指定　※非パフォーマンス項目
 * `characterSetResults` リザルトセット用のエンコーディング指定　※同上
 * `alwaysSendSetIsolation` トランザクション分離レベルを常に送信する（**`true`**）／しない（`false`）
 * `elideSetAutoCommits` サーバ側の状態と違う時にだけ `SET autocommit=n`
 を送る（`true`）／常に送る（**`false`**）
 * `useLocalSessionState` オートコミット・トランザクション分離レベルをサーバに問い合わせずに判断する（`true`）／しない（**`false`**）
 * `cacheServerConfiguration` サーバ設定をキャッシュする（`true`）／しない（**`false`**）

※**`太字`**がデフォルト

について、デフォルトから変更したときのパフォーマンスの変化を検証します。

※以下の記事が参考になります。

 * **[mysql Connector/J の設定で効きそうなやつ](http://moznion.hatenadiary.com/entry/2015/12/25/102241)（その手の平は尻もつかめるさ／もづにおんさん）**

但し、すべての組み合わせを試すのは難しいので、

 * `rewriteBatchedStatements=true` のみ
 * `rewriteBatchedStatements=true` に加えてエンコーディング（上2つ）としてutf8を指定
 * `rewriteBatchedStatements=true` に加えてオートコミット・トランザクション項目をまとめて変更（通信量が減る方向に）
 * `rewriteBatchedStatements=true` に加えてサーバ設定をキャッシュ

の4パターンで検証します。

※バッチ2スレッド：非バッチ4スレッド／スレッドあたり`INSERT`行数20万行／バッチ`INSERT`1,000行単位／`COMMIT`10,000行単位。

# 2. 検証結果

所要時間とクエリ／コマンド数を確認した結果を示します。

## 2-1. 所要時間

| 検証パターン | バッチ所要時間 (ms) | 非バッチ所要時間 (ms) |
|:--|--:|--:|
| `rewriteBatchedStatements=true` のみ | 11,691 | 50,299 |
| `rewriteBatchedStatements=true`＋エンコーディング | 12,173 | 51,042 |
| `rewriteBatchedStatements=true`＋`SET autocommit` 等抑制 | 11,334 | 31,277 |
| `rewriteBatchedStatements=true`＋サーバ設定キャッシュ | 11,911 | 52,384 |

3パターン目の**「`rewriteBatchedStatements=true` に加えてオートコミット・トランザクション項目をまとめて変更」**の、非バッチ所要時間のみ明らかな効果がありました。
ほかは誤差の範囲です。

## 2-2. クエリ／コマンド数

「0秒スロークエリログ」を集計してみました。

```text:rewriteBatchedStatements=trueのみ
root@localhost [(none)] > SELECT LEFT(sql_text, 80) sqltext, COUNT(*) cnt FROM mysql.slow_log GROUP BY sql_text ORDER BY cnt DESC;
+----------------------------------------------------------------------------------+-------+
| sqltext                                                                          | cnt   |
+----------------------------------------------------------------------------------+-------+
| select @@session.tx_read_only                                                    | 59232 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       | 20151 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       |   400 |
| commit                                                                           |   120 |
| /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e2000 |     3 |
| SET autocommit=1                                                                 |     1 |
| SET autocommit=0                                                                 |     1 |
| rollback                                                                         |     1 |
+----------------------------------------------------------------------------------+-------+
8 rows in set (18.27 sec)
```

```text:rewriteBatchedStatements=true＋エンコーディング
root@localhost [(none)] > SELECT LEFT(sql_text, 80) sqltext, COUNT(*) cnt FROM mysql.slow_log GROUP BY sql_text ORDER BY cnt DESC;
+----------------------------------------------------------------------------------+-------+
| sqltext                                                                          | cnt   |
+----------------------------------------------------------------------------------+-------+
| select @@session.tx_read_only                                                    | 60793 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       | 20680 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       |   400 |
| commit                                                                           |   120 |
| SET autocommit=0                                                                 |     3 |
| /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e2000 |     2 |
| Quit                                                                             |     2 |
| SET NAMES utf8                                                                   |     1 |
| SET autocommit=1                                                                 |     1 |
| select @@version_comment limit 1                                                 |     1 |
+----------------------------------------------------------------------------------+-------+
10 rows in set (18.85 sec)
```

```text:rewriteBatchedStatements=true＋SET_autocommit等抑制
root@localhost [(none)] > SELECT LEFT(sql_text, 80) sqltext, COUNT(*) cnt FROM mysql.slow_log GROUP BY sql_text ORDER BY cnt DESC;
+----------------------------------------------------------------------------------+-------+
| sqltext                                                                          | cnt   |
+----------------------------------------------------------------------------------+-------+
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       | 21179 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       |   400 |
| commit                                                                           |   120 |
| /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e2000 |     3 |
| SET character_set_results = NULL                                                 |     1 |
| rollback                                                                         |     1 |
| select @@version_comment limit 1                                                 |     1 |
| select USER()                                                                    |     1 |
+----------------------------------------------------------------------------------+-------+
8 rows in set (3.00 sec)
```

```text:rewriteBatchedStatements=true＋サーバ設定キャッシュ
root@localhost [(none)] > SELECT LEFT(sql_text, 80) sqltext, COUNT(*) cnt FROM mysql.slow_log GROUP BY sql_text ORDER BY cnt DESC;
+----------------------------------------------------------------------------------+-------+
| sqltext                                                                          | cnt   |
+----------------------------------------------------------------------------------+-------+
| select @@session.tx_read_only                                                    | 60414 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       | 20353 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890１２３４５６       |   400 |
| commit                                                                           |   120 |
| SET autocommit=0                                                                 |     1 |
| SET character_set_results = NULL                                                 |     1 |
| Quit                                                                             |     1 |
| rollback                                                                         |     1 |
+----------------------------------------------------------------------------------+-------+
8 rows in set (18.50 sec)
```

`INSERT INTO`が2つありますが、上が1行ずつの`INSERT`（非バッチ）、下が複数行`INSERT`（バッチ）です。
前者の実行回数はきっちり2万回になっていませんが、`INSERT` された行数に異常はありませんでした（全体で120万行）。

それぞれの結果に多少のノイズが入っていそうですし、長時間SQLを流すような検証であれば結果も変わってくるのでしょうが、はっきりと効果が見て取れるのは3パターン目でした。
一方、4パターン目は期待通りの結果になりませんでしたが、他パターンでサーバ設定情報の問い合わせ（`/* mysql-connector-java-5.1.44…`）が流れている数自体がそもそも少ないことを考えると、短時間でSQLを一気に流すテストだったために効果が出なかった可能性もあります。

# 3. まとめ

サーバのキャッシュについては意外な結果となりましたが、それ以外は予想通りで、「お喋り」を封印するとそれなりにパフォーマンスに影響が出るようです。
但し、コネクションプーリングを使うと結果が変わってくる可能性がありますし、クラスタを組んでいる場合、指定内容によってはフェイルオーバー時に不具合が生じる可能性もあります。

 * **プロパティ指定はデフォルトのままにしない**
 * **指定するプロパティは実環境で**（流れているクエリ・コマンドをロギング／キャプチャするなどして）**調査・検証してから適切なものをチョイスする**（他人の検証結果を鵜呑みにしない）

ということだけお伝えして、今回はおしまいです。

[次回（12/21）の記事](https://qiita.com/hmatsu47/items/d3ce39577fb383ec21d2)では、プリペアードステートメントのキャッシュ機能について検証します。

明日の [MySQL Casual Advent Calendar 2017](https://qiita.com/advent-calendar/2017/mysql-casual) は@bringer1092さんの [NDB Clusterをmemcachedで簡単にアクセス](https://qiita.com/bringer1092/items/dc16da138826134a0ff9) です。