** MySQL Casual Adventskalender ** Dies ist der erste Artikel, Teil 1.

Gestern @ atsuizos Verwenden Sie "MAX_EXECUTION_TIME", um die SELECT-Anweisung durch Zeitüberschreitung zwangsweise zu beenden? .

Mein Artikel ist wahrscheinlich lang für seinen Inhalt, daher werde ich ihn in drei Wochen senden, damit das Lesen nicht langweilig wird.

0. Verifizierungsumgebung

Da ich es beiläufig mache, höre ich auf, es zum Server zu bringen, und versuche, es auf der IDE des PCs auszuführen. Daher sollten sich die Ergebnisse erheblich ändern, wenn sich die Umgebung ändert.

PC-Spezifikationen usw.

IDE: Eclipse 4.7.1a (Plejaden japanische Lokalisierung)
MySQL Community Server: Windows (x64) Version 5.7.20
MySQL Connector / J: 5.1.44 (vom Maven-Repository bezogen)
Es ist ein alter Mann zum Zeitpunkt des Schreibens ...
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] > "

Da ich nicht an die Windows-Version gewöhnt bin, ** MySQL Community Server 5.7.19 - Installation unter Windows 10 (@ rubytomato @ github) ** Ich bezog mich auf. Vielen Dank.

1. Details zur Überprüfung

In MySQL Connector / J gibt es mehrere leistungsbezogene Eigenschaften.

5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J（MySQL Connector/J 5.1 Developer Guide）
Beziehen Sie sich hauptsächlich auf ** Leistungserweiterungen. ** in der Mitte der Seite.

Von diesen ist das erste, was mir einfällt, wenn mehrere Datensätze gleichzeitig "EINFÜGEN", "rewriteBatchedStatements".

** Optimieren einer großen Anzahl von INSERTs in MySQL Connector / J (SH2-Tagebuch) **

Obwohl es ein Standardartikel ist, der in aufgenommen wird

Was ist, wenn ich vergesse, "rewriteBatchedStatements" zu aktivieren?
Im vorherigen Artikel von SH2 ** "100 Datensätze INSERT und 1.000 Datensätze COMMIT" **, aber was passiert, wenn Sie dieses Gerät wechseln?
Was ist die Änderung, wenn in mehreren Threads ausgeführt wird?

Lassen Sie uns die drei Punkte untersuchen.

2. Überprüfungsergebnis

Der Bestätigungscode, die DB / Tabellendefinition usw. werden gegen Ende veröffentlicht. Als Argument bei der Ausführung von Java-Bestätigungscode

Anzahl der nicht stapelbaren "INSERT" -Threads ("VALUES" nicht verkettet), die ausgeführt wurden
Anzahl der Thread-Ausführungen im Stapel INSERT (mit VALUES-Verkettung)
INSERT Anzahl der Zeilen
Verkettungseinheit VALUES (Anzahl der Zeilen) für Batch INSERT
"COMMIT" -Einheit (Anzahl der Zeilen)
Optionaler Parameter für MySQL Connector / J URL (Eigenschaft angeben)

In Form einer Angabe wurden die Threads ausgeführt, während der Start um 1 Sekunde in der Reihenfolge "Nicht-Batch-INSERT" -Thread-> Batch-INSERT-Thread "verzögert wurde, und die für jeden Thread erforderliche Zeit wurde gemessen.

Die Werte (Millisekunden) in der Tabelle geben "den Durchschnittswert ohne die Maximal- und Minimalwerte von 5 Ausführungen" an.

2-1. Was passiert, wenn ich vergesse, "rewriteBatchedStatements" zu aktivieren?

Dies ist einer der Fälle, die tatsächlich an einem vertrauten Ort passiert sind (wurden).

Wenn rewriteBatchedStatements ** ungültig ** in Batch INSERT ( addBatch → executeBatch)

Wenn nicht Batch INSERT (execute ())
Wenn rewriteBatchedStatements ** gültig ist ** in batch INSERT (addBatch ()→executeBatch ())

Vergleichen mit.

Die Anzahl der "INSERT" -Zeilen beträgt 200.000, die Batch-Einheit "INSERT" 100 und die Einheit "COMMIT" 1.000. Es wird in einem Thread ausgeführt.

Überprüfungsmuster	Benötigte Zeit(ms)
Stapel·`rewriteBatchedStatements`Ungültig	49,115
Nicht chargenweise	84,231
Stapel·`rewriteBatchedStatements`Wirksamkeit	19,845

Es dauert zwar mehr als doppelt so lange, bis "rewriteBatchedStatements" gültig ist, es ist jedoch schneller als "non-batch". Seien wir vorsichtig.

2-2. Was passiert, wenn die Einheit `INSERT` / `COMMIT` geändert wird?

Batch ** INSERT 100 Zeilen ** Einheiten / ** COMMIT 1.000 Zeilen ** Einheiten
Batch ** INSERT 1.000 Zeilen ** Einheiten / ** COMMIT 1.000 Zeilen ** Einheiten
Batch ** INSERT 1.000 Zeilen ** Einheiten / ** COMMIT 10.000 Zeilen ** Einheiten

In 3 Mustern ausführen (INSERT Anzahl der Zeilen beträgt 200.000 Zeilen, rewriteBatchedStatements ist gültig).

Überprüfungsmuster	Benötigte Zeit(ms)
Charge 100 /`COMMIT`1,000	19,845
Charge 1,000／`COMMIT`1,000	4,464
Charge 1,000／`COMMIT`10,000	3,385

In SH2s Artikel gab es eine Beschreibung, dass ** "Es wird nicht wachsen, selbst wenn Sie es erhöhen" **, aber es scheint, dass es noch wahrscheinlicher wachsen wird, weil sich die Zeiten geändert haben (ich denke, es hängt vom Inhalt von `INSERT`` ab. ). Beachten Sie jedoch, dass ** Heap-Speichermangel ** flackert, wenn die Einheit der Anzahl der neu geschriebenen Zeilen größer wird.

Wenn nicht nur "INSERT", sondern auch "UPDATE" und "DELETE" beteiligt sind, achten Sie auf eine Verlangsamung aufgrund von Verriegelung und Deadlock.

2-3. Was ist die Änderung, wenn sie in mehreren Threads ausgeführt wird?

Selbst wenn nur ein Thread schneller wird, sind andere Threads betroffen ... also habe ich es versucht. Lassen Sie uns zunächst ** mit 4 parallelen Threads ** überprüfen, während Sie das Verhältnis von ** Batch: Nicht-Batch ändern.

Jeder Thread hat INSERT 200.000 Zeilen / Batch INSERT 1.000 Zeilen / COMMIT 10.000 Zeilen / rewriteBatchedStatements ist gültig.

Überprüfungsmuster	Stapelzeit erforderlich(ms)	Dauer ohne Charge(ms)
Charge 1: Nicht-Charge 3	6,974	38,753
Charge 2: Nicht-Charge 2	5,814	35,005
Charge 3: Nicht-Charge 1	5,131	42,453

Für Batch-INSERT-Threads ** mehr Batches = schneller als weniger Nicht-Batch **. Auf der anderen Seite sind Nicht-Batch-INSERT-Threads ** langsamer, wenn die Anzahl der Threads ** Batch> Non-Batch ist.

Als nächstes überprüfen wir mit ** 8 Thread parallel ** (die Anzahl der INSERT-Zeilen usw. ist dieselbe wie zuvor).

Überprüfungsmuster	Stapelzeit erforderlich(ms)	Dauer ohne Charge(ms)
Charge 1: Nicht-Charge 7	22,398	85,643
Charge 3: Nicht-Charge 5	15,025	64,833
Charge 5: Nicht-Charge 3	11,508	45,332
Charge 7: Nicht-Charge 1	7,926	47,137

Bei Batch-INSERT-Threads sind ** mehr Batches = schneller, wenn der Nicht-Batch-Wert abnimmt ** wie zuvor. Nicht-Batch-INSERT-Threads sind ** nur dann langsam, wenn die Anzahl der Threads 1 beträgt. Der ** Tropfen ist jedoch nur **.

3. Zusammenfassung

Vergessen Sie nicht "rewriteBatchedStatements = true", wenn Sie "addBatch ()" + "executeBatch ()" ausführen
Es ist notwendig, vorsichtig zu sein, wenn nicht genügend Speicher zur Verfügung steht, aber addBatch () ＋ executeBatch () (wenn das Update-System nur INSERT ist) ist positiv, ohne sich in gewissem Maße um andere Threads zu kümmern. Es kann in Ordnung sein, es zu benutzen

Nächster (17.12.) Artikel unterdrückt die Anzahl der Abfragen, die hauptsächlich zum Überprüfen der Umgebung zum Zeitpunkt der Verbindung ausgegeben wurden. Lassen Sie uns überprüfen).

Wenn Sie das Ergebnis mit ein wenig Flug zeigen,

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

Das Ergebnis (8 Threads / Charge 7: Nicht-Charge 1, gleiche Bedingungen wie im letzten Testfall mit Ausnahme der Eigenschaften) war ** "Charge 7.581 ms / Nicht-Charge 33.145 ms" **.

Morgen ist @ tom - bo's Ich habe mit Anomalien für jede Isolationsstufe von MySQL (innodb) experimentiert.

4. Code zur Überprüfung usw.

`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();
		}
	}
}

`Benutzer- / DB- / Tabellendefinition`


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)

[JAVA] Benchen durch Ändern der Eigenschaften mit MySQL Connector / J Teil 1: Batch

0. Verifizierungsumgebung

my.ini

1. Details zur Überprüfung

2. Überprüfungsergebnis

2-1. Was passiert, wenn ich vergesse, "rewriteBatchedStatements" zu aktivieren?

2-2. Was passiert, wenn die Einheit INSERT / COMMIT geändert wird?

2-3. Was ist die Änderung, wenn sie in mehreren Threads ausgeführt wird?

3. Zusammenfassung

4. Code zur Überprüfung usw.

pom.xml

DbConnection.java

DbInsert.java

SimpleInsert.java

BatchInsert.java

Main.java

Benutzer- / DB- / Tabellendefinition