昨日の MySQL Casual Advent Calendar 2017 は@tikamotoさんの MySQLerのためのウィンドウ関数入門 でした。
この記事は同Advent Calender 10日目、MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその1：バッチ編 の続きです。
前回は「プロパティをあれこれ変えて」と言っておきながら1つしか変えていませんでしたので、ここからが実質的な「本番」です。
MySQL Connector/Jは、ユーザが送信したクエリ・コマンド以外にも SELECT @@… や SET … など、世話焼きのおかんのようによく喋りますが、できるだけ無駄口を叩かないように仕向けるのが今回の検証テーマです。
なお、検証用のコードやDB定義などは、前回のものを流用します。
1. 検証対象のプロパティ
- 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J（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 の設定で効きそうなやつ（その手の平は尻もつかめるさ／もづにおんさん）
但し、すべての組み合わせを試すのは難しいので、
-
rewriteBatchedStatements=trueのみ -
rewriteBatchedStatements=trueに加えてエンコーディング（上2つ）としてutf8を指定 -
rewriteBatchedStatements=trueに加えてオートコミット・トランザクション項目をまとめて変更（通信量が減る方向に） -
rewriteBatchedStatements=trueに加えてサーバ設定をキャッシュ
の4パターンで検証します。
※バッチ2スレッド：非バッチ4スレッド／スレッドあたりINSERT行数20万行／バッチINSERT1,000行単位／COMMIT10,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秒スロークエリログ」を集計してみました。
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)
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)
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)
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）の記事では、プリペアードステートメントのキャッシュ機能について検証します。
明日の MySQL Casual Advent Calendar 2017 は@bringer1092さんの NDB Clusterをmemcachedで簡単にアクセス です。