Java
MySQL

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

昨日の MySQL Casual Advent Calendar 2017@tikamotoさんの MySQLerのためのウィンドウ関数入門 でした。

この記事は同Advent Calender 10日目、MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその1:バッチ編 の続きです。

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

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

なお、検証用のコードやDB定義などは、前回のものを流用します。

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

のうち、

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

太字がデフォルト

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

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

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

  • 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=trueSET autocommit 等抑制 11,334 31,277
rewriteBatchedStatements=true+サーバ設定キャッシュ 11,911 52,384

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

2-2. クエリ/コマンド数

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

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 ('1234567890123456       | 20151 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890123456       |   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)
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 ('1234567890123456       | 20680 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890123456       |   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)
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 ('1234567890123456       | 21179 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890123456       |   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)
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 ('1234567890123456       | 20353 |
| INSERT INTO insert_test.insert_test (memo) VALUES ('1234567890123456       |   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で簡単にアクセス です。