Benchmark by changing properties with MySQL Connector / J Part 2: Stop unnecessary talk

Yesterday's MySQL Casual Advent Calendar 2017 was @ tikamoto's Introduction to Window Functions for MySQL / tikamoto / items / a9298442ba0ef45112fe).

This article is a continuation of Benchmark by changing properties with MySQL Connector / J Part 1: Batch on the 10th day of the same Advent Calender.

Last time, I said "change the properties" but changed only one, so this is the actual "production".

In addition to the query commands sent by the user, MySQL Connector / J often speaks like SELECT @@… and SET…, like a caretaker, but it tries to avoid wasting as much as possible. Is the theme of this verification.

For the verification code and DB definition, the ones from Previous will be used.

1. Properties to be verified

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

Of

• characterEncoding Encoding specification * Non-performance item

• characterSetResults Encoding specification for result set * Same as above

• ʻAlwaysSendSetIsolation Always send transaction isolation level (** true **) / not (false`)

• ʻElideSetAutoCommits`` SET autocommit = nonly when the state is different from the server sideSET autocommit = n Send (true) / Always send (** false` **)

• ʻUseLocalSessionState Judge the autocommit transaction isolation level without asking the server (true) / not (** false` **)

• cacheServerConfiguration Cache server settings (true) / not (** false **)

• ** Bold ** is the default

Examine the change in performance when changing from the default.

• The following articles will be helpful.

• ** The one that seems to work with the settings of mysql Connector / J Mr.) **

However, it is difficult to try all combinations, so

• Only rewriteBatchedStatements = true
• Specify utf8 as the encoding (top two) in addition to rewriteBatchedStatements = true
• In addition to rewriteBatchedStatements = true, change all autocommit transaction items (in the direction of reduced traffic)
• Cache server settings in addition to rewriteBatchedStatements = true

We will verify with 4 patterns of.

• Batch 2 threads: Non-batch 4 threads / ʻINSERT number of rows 200,000 lines / batch ʻINSERT 1,000 rows / COMMIT 10,000 rows.

2. Verification result

The result of checking the required time and the number of queries / commands is shown.

2-1. Time required

Only the non-batch duration of the third pattern ** "Change all autocommit transaction items in addition to rewriteBatchedStatements = true "** had a clear effect. Others are within the margin of error.

2-2. Number of queries / commands

I tried to aggregate "0 second slow query log".

rewriteBatchedStatements=true only

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)

rewriteBatchedStatements=true + encoding

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)

rewriteBatchedStatements=true＋SET_Suppression such as 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)

rewriteBatchedStatements=true + server settings cache

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)

There are two ʻINSERT INTOs, the top is ʻINSERT (non-batch) with one row each, and the bottom is ʻINSERT (batch) with multiple rows. The number of executions of the former was not exactly 20,000, but there was no abnormality in the number of rows ʻINSERTed (1.2 million rows in total).

There seems to be some noise in each result, and if the verification is such that SQL is run for a long time, the result will change, but the effect can be clearly seen in the third pattern. On the other hand, the 4th pattern did not produce the expected result, but the number of server setting information inquiries (/ * mysql-connector-java-5.1.44…) in other patterns is small in the first place. With that in mind, it's possible that the test wasn't effective because it was a test that flushed SQL in a short amount of time.

3. Summary

The results were surprising for the server cache, but otherwise as expected, sealing the "talk" seems to have some performance impact. However, using connection pooling may change the results, and if you are in a cluster, problems may occur during failover depending on the specified contents.

• ** Do not leave property specifications at default **
• ** The property to be specified is ** in the real environment ** (logging / capturing the query command that is flowing) ** After investigating and verifying, select the appropriate one ** (verification result of another person) Don't swallow)

I just told you that, and this time it's over.

In the next (12/21) article [https://qiita.com/hmatsu47/items/d3ce39577fb383ec21d2), we will examine the cache function of prepared statements.

Tomorrow's MySQL Casual Advent Calendar 2017 will be @ bringer1092's [Easy access to NDB Cluster with memcached](https://qiita. It is com / bringer1092 / items / dc16da138826134a0ff9).