** MySQL Casual Advent Calendar ** Ceci est le premier article d'entrée, partie 1.

Hier, @ atsuizo's Utilisez-vous "MAX_EXECUTION_TIME" pour mettre fin de force à l'instruction SELECT en expirant? .

Mon article est susceptible d'être long pour son contenu, je vais donc l'envoyer dans trois semaines pour qu'il ne devienne pas fastidieux à lire.

0. Environnement de vérification

Puisque je vais le faire avec désinvolture, je vais arrêter de le prendre sur le serveur et essayer de l'exécuter sur l'IDE du PC. Par conséquent, les résultats devraient changer considérablement lorsque l'environnement change.

Spécifications du PC, etc.

IDE: Eclipse 4.7.1a (localisation japonaise pléiades)
MySQL Community Server: Windows (x64) version 5.7.20
MySQL Connector / J: 5.1.44 (obtenu à partir du référentiel Maven)
C'est un vieil homme au moment de la rédaction ...
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] > "

Parce que je ne suis pas habitué à la version Windows, ** MySQL Community Server 5.7.19 --Installation sous Windows 10 (@ rubytomato @ github) ** Je l'ai mentionné. Merci beaucoup.

1. Détails de la vérification

Il existe plusieurs propriétés liées aux performances dans MySQL Connector / J.

5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J（MySQL Connector/J 5.1 Developer Guide）
Se référer principalement à ** Extensions de performances. ** à partir du milieu de la page

Parmi ceux-ci, la première chose qui vient à l'esprit quand il y a un processus de traitement par lots de plusieurs enregistrements ʻINSERTestrewriteBatchedStatements`.

** Réglage d'un grand nombre d'INSERTs dans MySQL Connector / J (journal SH2) **

Bien que ce soit un élément standard qui est repris dans

Et si j'oublie d'activer rewriteBatchedStatements?
Dans l'article précédent de SH2, ** "100 enregistrements ʻINSERT et 1000 enregistrements COMMIT`" **, mais que se passe-t-il si vous changez cette unité?
Quel est le changement lors de l'exécution dans plusieurs threads?

Examinons les trois points.

2. Résultat de la vérification

Le code de vérification, la définition de DB / table, etc. seront affichés vers la fin. En tant qu'argument lors de l'exécution du code de vérification Java,

Non-batch ʻINSERT (VALUES` pas de concaténation) nombre d'exécutions de threads
Batch ʻINSERT(avec concaténationVALUES`) Nombre d'exécutions de threads
ʻINSERT` nombre de lignes
Unité de concaténation VALUES (nombre de lignes) au lot ʻINSERT`
Unité COMMIT (nombre de lignes)
Paramètre facultatif MySQL Connector / J URL (spécifier la propriété)

Sous la forme de la spécification de "thread non-batch ʻINSERT-> Batch ʻINSERT thread", les threads ont été exécutés en retardant le démarrage d'une seconde, et le temps requis pour chaque thread a été mesuré.

Les valeurs (millisecondes) du tableau indiquent "la valeur moyenne excluant les valeurs maximum et minimum de 5 exécutions".

2-1. Que se passe-t-il si j'oublie d'activer `rewriteBatchedStatements`?

C'est l'un des cas qui s'est réellement produit (a été fait) dans un endroit familier.

Lorsque rewriteBatchedStatements ** invalide ** dans le lot ʻINSERT (ʻaddBatch → ʻexecuteBatch`)

Quand non-batch ʻINSERT(ʻexecute ())
Lorsque rewriteBatchedStatements ** est valide ** dans le lot ʻINSERT (ʻaddBatch () → ʻexecuteBatch () `)

Comparer avec.

Le nombre de lignes ʻINSERT est de 200 000, l'unité du lot ʻINSERT est de 100 lignes et l'unité de COMMIT est de 1 000 lignes. Il est exécuté dans un thread.

Modèle de vérification	Temps requis(ms)
lot·`rewriteBatchedStatements`Invalide	49,115
Non-batch	84,231
lot·`rewriteBatchedStatements`Efficacité	19,845

Bien que cela prenne plus de deux fois plus de temps pour que rewriteBatchedStatements soit valide, il est plus rapide que le non-batch, donc il y a un piège de **" Je ne remarque pas oublier de spécifier rewriteBatchedStatements" ** (histoire d'expérience). Soyons prudents.

2-2 Que se passe-t-il si je change l'unité ʻINSERT`/`COMMIT`?

Batch ** ʻINSERT100 lignes ** unités / **COMMIT` 1000 lignes ** unités
Batch ** ʻINSERT1000 lignes ** unités / **COMMIT` 1000 lignes ** unités
Batch ** ʻINSERT1000 lignes ** unités / **COMMIT` 10000 lignes ** unités

Exécuter en 3 modèles (le nombre de lignes ʻINSERTest de 200 000,rewriteBatchedStatements` est valide).

Modèle de vérification	Temps requis(ms)
Lot 100 /`COMMIT`1,000	19,845
Lot 1,000／`COMMIT`1,000	4,464
Lot 1,000／`COMMIT`10,000	3,385

Dans l'article de SH2, il y avait une description que ** "ça ne grandira pas même si vous l'augmentez" **, mais il semble que ça augmentera encore plus probablement parce que les temps ont changé (je pense que cela dépend du contenu de ʻINSERT`). ). Cependant, veuillez noter que le ** manque de mémoire du tas ** clignote à mesure que l'unité du nombre de lignes réécrites devient plus grande.

Aussi, si non seulement ʻINSERT mais aussi ʻUPDATE et DELETE sont impliqués, faites attention au ralentissement dû au verrouillage et au dead lock.

2-3. Quel est le changement lorsqu'il est exécuté dans plusieurs threads?

Même si un seul thread devient plus rapide, les autres threads seront affectés ... alors je l'ai essayé. Tout d'abord, vérifions ** avec 4 threads parallèles ** tout en modifiant le ratio de ** batch: non-batch.

ʻINSERT 200 000 lignes par thread / lot ʻINSERT 1 000 lignes / COMMIT 10 000 lignes / rewriteBatchedStatements est valide.

Modèle de vérification	Temps de lot requis(ms)	Durée hors lot(ms)
Lot 1: Non-lot 3	6,974	38,753
Lot 2: Non-lot 2	5,814	35,005
Lot 3: Non-lot 1	5,131	42,453

Pour les threads «INSERT» par lots, ** plus de lots = plus rapide que moins de non-lots **. D'autre part, les threads ʻINSERT` non-batch sont ** lents ** lorsque le nombre de threads est batch> non batch.

Ensuite, vérifions avec ** 8 thread parallel ** (le nombre de lignes ʻINSERT`, etc. est le même que précédemment).

Modèle de vérification	Temps de lot requis(ms)	Durée hors lot(ms)
Lot 1: Non-lot 7	22,398	85,643
Lot 3: Hors lot 5	15,025	64,833
Lot 5: Non-lot 3	11,508	45,332
Lot 7: Non-lot 1	7,926	47,137

Pour les threads «INSERT» par lots, ** plus de lots = plus rapide que moins de non-lots ** comme auparavant. Les threads ʻINSERT` non par lots sont ** lents uniquement lorsque le nombre de threads est 1. Cependant, la ** baisse n'est que **.

3. Résumé

N'oubliez pas rewriteBatchedStatements = true lorsque vous faites ʻaddBatch ()＋ ʻexecuteBatch ()
Il faut faire attention à ne pas manquer de mémoire, mais ʻaddBatch () ＋ ʻexecuteBatch () (lorsque le système de mise à jour est seulement ʻINSERT`) est positif sans se soucier des autres threads dans une certaine mesure. Il peut être bon de l'utiliser

Dans Article suivant (12/17), le nombre de requêtes émises principalement pour vérifier l'environnement au moment de la connexion est supprimé. Vérifions).

De plus, si vous montrez le résultat avec un peu de vol,

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

Le résultat (8 threads / lot 7: non-lot 1, mêmes conditions que le dernier cas de test sauf pour les propriétés) était ** "batch 7,581ms / non-batch 33.145ms" **.

Demain, c'est @ tom - bo's j'ai expérimenté des anomalies pour chaque niveau d'isolement de MySQL (innodb).

4. Code utilisé pour la vérification, 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();
		}
	}
}

`Définition utilisateur / base de données / table`


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] Benching en modifiant les propriétés avec MySQL Connector / J Partie 1: Batch

0. Environnement de vérification

my.ini

1. Détails de la vérification

2. Résultat de la vérification

2-1. Que se passe-t-il si j'oublie d'activer rewriteBatchedStatements?

2-2 Que se passe-t-il si je change l'unité ʻINSERT/COMMIT`?

2-3. Quel est le changement lorsqu'il est exécuté dans plusieurs threads?

3. Résumé

4. Code utilisé pour la vérification, etc.

pom.xml

DbConnection.java

DbInsert.java

SimpleInsert.java

BatchInsert.java

Main.java

Définition utilisateur / base de données / table