** MySQL Casual Advent Calendar ** This is the first entry article, part 1.

Yesterday, @atsuizo's Are you using "MAX_EXECUTION_TIME" that forcibly terminates the SELECT statement by timeout? .

My article is likely to be long for its content, so I'll send it in three weeks so that it doesn't get tedious to read.

0. Verification environment

Since I will do it casually, I will stop taking it to the server and try running it on the IDE of the PC. Therefore, the results should change considerably when the environment changes.

PC specifications, etc.

IDE: Eclipse 4.7.1a (pleiades Japanese)
MySQL Community Server: Windows (x64) version 5.7.20
MySQL Connector / J: 5.1.44 (obtained from Maven repository)
It's one old guy at the time of writing ...
my.ini

`my.ini`


[mysqld]
basedir = C:\\dev\\mysql-5.7.20-winx64
datadir = C:\\dev\\mysql-5.7.20-winx64\\data
tmpdir = C:\\dev\\mysql-5.7.20-winx64\\tmp

max_connections = 30

explicit_defaults_for_timestamp = 1

innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_status_output = 1
innodb_status_output_locks = 1

character_set_server = utf8
collation_server = utf8_general_ci

general_log = 1
general_log_file = C:\\dev\\mysql-5.7.20-winx64\\logs\\general_query_all.log
log_error = C:\\dev\\mysql-5.7.20-winx64\\logs\\mysqld_error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = C:\\dev\\mysql-5.7.20-winx64\\logs\\slow_query.log

[mysql]
default-character-set = utf8
show-warnings

prompt = "\u@\h [\d] > "

Because I am not used to the Windows version, ** MySQL Community Server 5.7.19 --Installing on Windows 10 (@ rubytomato @ github) ** I referred to. Thank you very much.

1. Verification details

There are several performance-related properties in MySQL Connector / J.

5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J（MySQL Connector/J 5.1 Developer Guide）
Mainly refer to ** Performance Extensions. ** from around the middle of the page.

Of these, the first thing that comes to mind when there is a process to batch ʻINSERTmultiple records isrewriteBatchedStatements`.

** Tuning of mass INSERT in MySQL Connector / J (SH2 diary) **

Although it is a standard item that is taken up in

What if I forget to enable rewriteBatchedStatements?
In the previous article by SH2, ** "100 records ʻINSERT and 1,000 records COMMIT`" **, but what happens if you change this unit?
What is the change when running in multiple threads?

Let's examine the three points.

2. Verification result

The verification code, DB / table definition, etc. will be posted near the end. As an argument when executing Java verification code,

Non-batch ʻINSERT (VALUES` no concatenation) thread execution count
Batch ʻINSERT(withVALUES` concatenation) Thread execution count
ʻINSERT` number of rows
VALUES concatenation unit (number of rows) at batch ʻINSERT`
COMMIT unit (number of lines)
MySQL Connector / J URL Optional Parameter (Specify Property)

In the form of specifying, the threads were executed while delaying the start by 1 second in the order of "non-batch ʻINSERT thread-> batch ʻINSERT thread", and the time required for each thread was measured.

The values (milliseconds) in the table indicate "the average value excluding the maximum and minimum values of 5 executions".

2-1. What happens if I forget to enable `rewriteBatchedStatements`?

This is one of the cases that actually happened (has been done) in a familiar place.

When rewriteBatchedStatements ** invalid ** in batch ʻINSERT (ʻaddBatch → ʻexecuteBatch`)

When non-batch ʻINSERT (ʻexecute ())
When rewriteBatchedStatements ** is valid ** in batch ʻINSERT (ʻaddBatch () → ʻexecuteBatch ()`)

Compare with.

The number of ʻINSERT rows is 200,000, the unit of batch ʻINSERT is 100 rows, and the unit of COMMIT is 1,000 rows. Execution in one thread.

Verification pattern	Time required(ms)
batch·`rewriteBatchedStatements`Invalid	49,115
Non-batch	84,231
batch·`rewriteBatchedStatements`Effectiveness	19,845

While it takes more than twice as long as rewriteBatchedStatements is valid, it is faster than non-batch, so there is a pitfall of **" I don't notice forgetting to specify rewriteBatchedStatements" ** (experience story). Let's be careful.

2-2. What happens if I change the ʻINSERT`/`COMMIT` unit?

Batch ** ʻINSERT100 rows ** units / **COMMIT` 1,000 rows ** units
Batch ** ʻINSERT1,000 rows ** units / **COMMIT` 1,000 rows ** units
Batch ** ʻINSERT1,000 rows ** units / **COMMIT` 10,000 rows ** units

Execute with 3 patterns of (ʻINSERTnumber of rows is 200,000 rows,rewriteBatchedStatements` is valid).

Verification pattern	Time required(ms)
Batch 100 /`COMMIT`1,000	19,845
Batch 1,000／`COMMIT`1,000	4,464
Batch 1,000／`COMMIT`10,000	3,385

In SH2's article, there was a description that ** "It will not grow even if you increase it" **, but it seems that it will grow more because the times have changed (I think it depends on the content of ʻINSERT`). ). However, please note that ** Heap memory shortage ** will flicker as the unit of the number of rewrite lines becomes larger.

Also, if not only ʻINSERT but also ʻUPDATE and DELETE are involved, be careful of slowdown due to lock and deadlock.

2-3. What is the change when executing with multiple threads?

Even if only one thread becomes faster, other threads will be affected ... so I tried it. First, let's verify ** with 4 threads parallel ** while changing the ratio of ** batch: non-batch **.

ʻINSERT 200,000 rows per thread / batch ʻINSERT 1,000 rows / COMMIT 10,000 rows / rewriteBatchedStatements is valid.

Verification pattern	Batch time required(ms)	Non-batch duration(ms)
Batch 1: Non-batch 3	6,974	38,753
Batch 2: Non-batch 2	5,814	35,005
Batch 3: Non-batch 1	5,131	42,453

For batch ʻINSERT threads, ** more batches = faster as less non-batch **. On the other hand, non-batch ʻINSERT threads are ** slow ** when the number of threads is batch> non-batch.

Next, let's verify with ** 8 thread parallel ** (ʻINSERT` number of rows etc. is the same as before).

Verification pattern	Batch time required(ms)	Non-batch duration(ms)
Batch 1: Non-batch 7	22,398	85,643
Batch 3: Non-batch 5	15,025	64,833
Batch 5: Non-batch 3	11,508	45,332
Batch 7: Non-batch 1	7,926	47,137

As for the batch ʻINSERT thread, ** more batches = faster as less non-batch ** as before. Non-batch ʻINSERT threads are ** slow only when the number of threads is 1. However, the ** drop is only **.

3. Summary

Don't forget rewriteBatchedStatements = true when doing ʻaddBatch () ＋ ʻexecuteBatch ()
It is necessary to be careful about running out of memory, but ʻaddBatch () ＋ ʻexecuteBatch () (when the update system is only ʻINSERT`) is positive without worrying about other threads to some extent. It may be okay to use it

In Next (12/17) article, the number of queries issued mainly for checking the environment at the time of connection is suppressed in other properties. Let's verify).

In addition, if you show the result with a little flying,

rewriteBatchedStatements=true&characterEncoding=utf8&characterSetResults=utf8&alwaysSendSetIsolation=false&elideSetAutoCommits=true&useLocalSessionState=true&cacheServerConfiguration=true

The result (8 threads / batch 7: non-batch 1, same conditions as the last test case except for properties) was ** "batch 7,581ms / non-batch 33,145ms" **.

Tomorrow is @ tom--bo's Experimented anomaly for each isolation level of MySQL (innodb).

4. Code used for verification, etc.

`pom.xml`


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>ConJTest</groupId>
  <artifactId>ConJTest</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.7.0</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
  	<dependency>
  		<groupId>mysql</groupId>
  		<artifactId>mysql-connector-java</artifactId>
  		<version>5.1.44</version>
  	</dependency>
  </dependencies>
</project>

`DbConnection.java`


package site.hmatsu47.conjtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbConnection {

	private static final String DRIVER_NAME	= "com.mysql.jdbc.Driver";
	private static final String JDBC_USER	= "testuser";
	private static final String JDBC_PASS	= "T35+U53r";

	public Connection getConnectionForTest(String url) throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER_NAME);
		return DriverManager.getConnection(url, JDBC_USER, JDBC_PASS);
	}
}

`DbInsert.java`


package site.hmatsu47.conjtest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DbInsert {

	private static final String JDBC_URL	= "jdbc:mysql://testdb:3306/insert_test";
	private static final String TEST_MEMO	= "1234567890１２３４５６７８９０1234567890１２３４５６７８９０1234567890１２３４５６７８９０1234567890１２３４５６７８９０1234567890１２３４５６７８９０";

	public void batchInsert(int totalline, int batchline, int commitline, String option) {

		try (
			Connection 			con		= new DbConnection().getConnectionForTest(JDBC_URL + option);
			PreparedStatement	psmt	= con.prepareStatement("INSERT INTO insert_test.insert_test (memo) VALUES (?)")
		) {
			con.setAutoCommit(false);
			psmt.clearBatch();

			for (int i = 1; i <= totalline; i++) {
				psmt.setString(1, TEST_MEMO);
				psmt.addBatch();

				if ((i % batchline == 0) || (i == totalline)) {
					psmt.executeBatch();
					psmt.clearBatch();

					if ((i % commitline == 0) || (i == totalline)) {
						con.commit();
					}
				}
			}
		} catch (ClassNotFoundException e) {
			System.out.println("[Error] Driver not found.");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("[Error] Invalid DB access.");
			e.printStackTrace();
		}
	}

	public void simpleInsert(int totalline, int commitline, String option) {

		try (
			Connection 			con		= new DbConnection().getConnectionForTest(JDBC_URL + option);
			PreparedStatement	psmt	= con.prepareStatement("INSERT INTO insert_test.insert_test (memo) VALUES (?)")
		) {
			con.setAutoCommit(false);

			for (int i = 1; i <= totalline; i++) {
				psmt.setString(1, TEST_MEMO);
				psmt.execute();

				if ((i % commitline == 0) || (i == totalline)) {
					con.commit();
				}
			}
		} catch (ClassNotFoundException e) {
			System.out.println("[Error] Driver not found.");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("[Error] Invalid DB access.");
			e.printStackTrace();
		}
	}
}

`SimpleInsert.java`


package site.hmatsu47.conjtest;

public class SimpleInsert extends Thread {

	private int 	totalline	= 0;
	private int 	commitline	= 0;
	private String option;
	private String title;

	public SimpleInsert(int totalline, int commitline, String option, String title) {
		this.totalline	= totalline;
		this.commitline	= commitline;
		this.option		= option;
		this.title		= title;
	}

	public void run() {
		long	starttime	= System.nanoTime();

		new DbInsert().simpleInsert(totalline, commitline, option);

		long	endtime	= System.nanoTime();

		System.out.println("[Simple] " + title + " : " + String.valueOf((endtime - starttime) / (1000 * 1000)) + " msec.");
	}
}

`BatchInsert.java`


package site.hmatsu47.conjtest;

public class BatchInsert extends Thread {

	private int 	totalline	= 0;
	private int 	batchline	= 0;
	private int 	commitline	= 0;
	private String option;
	private String title;

	public BatchInsert(int totalline, int batchline, int commitline, String option, String title) {
		this.totalline	= totalline;
		this.batchline	= batchline;
		this.commitline	= commitline;
		this.option		= option;
		this.title		= title;
	}

	public void run() {
		long	starttime	= System.nanoTime();

		new DbInsert().batchInsert(totalline, batchline, commitline, option);

		long	endtime	= System.nanoTime();

		System.out.println("[Batch]  " + title + " : " + String.valueOf((endtime - starttime) / (1000 * 1000)) + " msec.");
	}
}

`Main.java`


package site.hmatsu47.conjtest;

public class Main {

	private static final int		DEFAULT_SIMPLE_THREAD	= 0;
	private static final int		DEFAULT_BATCH_THREAD		= 1;
	private static final int		DEFAULT_TOTAL_LINE		= 1000000;
	private static final int		DEFAULT_BATCH_LINE		= 100;
	private static final int		DEFAULT_COMMITL_LINE		= 1000;
	private static final String	DEFAULT_JDBC_OPTION		= "";

	public static void main(String args[]) {
		try {
			final int		simplethread	= (args.length < 1 ? DEFAULT_SIMPLE_THREAD : Integer.valueOf(args[0]).intValue());
			final int		batchthread	= (args.length < 2 ? DEFAULT_BATCH_THREAD : Integer.valueOf(args[1]).intValue());
			final int		totalline		= (args.length < 3 ? DEFAULT_TOTAL_LINE : Integer.valueOf(args[2]).intValue());
			final int		batchline		= (args.length < 4 ? DEFAULT_BATCH_LINE : Integer.valueOf(args[3]).intValue());
			final int		commitline		= (args.length < 5 ? DEFAULT_COMMITL_LINE : Integer.valueOf(args[4]).intValue());
			final String	option			= (args.length < 6 ? DEFAULT_JDBC_OPTION : args[5]);

			for (int i = 1; i <= simplethread; i++) {
				SimpleInsert si = new SimpleInsert(totalline, commitline, option, String.valueOf(i));
				si.start();
				Thread.sleep(1000);
			}

			for (int i = 1; i <= batchthread; i++) {
				BatchInsert bi = new BatchInsert(totalline, batchline, commitline, option, String.valueOf(i));
				bi.start();
				Thread.sleep(1000);
			}
		}
		catch (Exception e) {
			System.out.println("[Error]");
			e.printStackTrace();
		}
	}
}

`User / DB / table definition`


root@localhost [(none)] > CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'T35+U53r';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] > GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] > CREATE DATABASE insert_test;
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)] > USE insert_test;
Database changed

root@localhost [insert_test] > CREATE TABLE insert_test (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, memo VARCHAR(200)) ENGINE InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.13 sec)

Benchmark by changing properties with MySQL Connector / J Part 1: Batch

0. Verification environment

my.ini

1. Verification details

2. Verification result

2-1. What happens if I forget to enable rewriteBatchedStatements?

2-2. What happens if I change the ʻINSERT/COMMIT` unit?

2-3. What is the change when executing with multiple threads?

3. Summary

4. Code used for verification, etc.

pom.xml

DbConnection.java

DbInsert.java

SimpleInsert.java

BatchInsert.java

Main.java

User / DB / table definition