Experiments: MySQL performs batch performance tests using JDBC

Keywords: SQL MySQL Java JDBC

Test preparation

Database: MySQL Server 5.5.40

System: Windows 7, Core (TM) i3-4160@3.6HZ, Arch x64

Database driver: mysql-connector-java.5.1.9

JDK environment JDK 1.7.0_75 x64

 

Experimental requirements

(1) Insert 10,000 pieces of data at a time, then delete them, and execute them 10 times in total.

(2) Using MyISAM and InnoDB engines to test respectively

 

Experimental reference

Test the precompiled effect of sql statements in mysql

Performance optimization of MySQL bulk insertion (I)

Performance optimization of MySQL bulk insertion (2)

Performance optimization of MySQL bulk insertion (3)

Batch updates of Statement and PreredStatement

Test code

Build table code

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User primary key',
  `name` varchar(32) NOT NULL COMMENT 'User name',
  `password` varchar(128) NOT NULL COMMENT 'Password',
  `gender` varchar(8) NOT NULL COMMENT 'Gender',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

Unit test code


package com.thin.mysql;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Random;

public class StatementTestCase {

	public static StatementTestCase instance;
	public static String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true&useServerPrepStmts=true";
	public Connection conn;
	public static String DriverClass = "com.mysql.jdbc.Driver";
	int MODE = 1000;

	public static StatementTestCase shareInstace() {
		if (instance == null) {
			synchronized (StatementTestCase.class) {
				if (instance == null) {
					instance = new StatementTestCase();
				}
			}
		}
		instance.openConnection();
		return instance;
	}

	private Driver findDriver(String driverName) {
		Enumeration<Driver> drivers = DriverManager.getDrivers();
		if (drivers != null) {
			do {
				if (!drivers.hasMoreElements()) {
					break;
				}
				Driver driver = drivers.nextElement();
				if (driver.getClass().getName().equals(driverName)) {
					return driver;
				}
			} while (true);
		}
		return null;
	}

	private synchronized void openConnection() {
		try {
			Driver driver = findDriver(DriverClass);
			if (driver == null) {
				Class.forName(DriverClass);
			}
			if (conn == null || conn.isClosed()) {
				conn = DriverManager.getConnection(url, "root", "root");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void executeStatement01() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = statSql + nextVal + ";";
			stmt.execute(sql);

		}
		stmt.execute("delete from user where 1=1;");
		conn.commit();
		stmt.close();
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executeStatement02() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = statSql + nextVal + ";";
			stmt.addBatch(sql);

		}
		stmt.addBatch("delete from user where 1=1;");
		stmt.executeBatch();
		conn.commit();
		stmt.close();
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executeStatement03() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		sql = statSql;
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = sql + nextVal + ",";

		}
		if (!sql.equalsIgnoreCase(statSql) && sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
			stmt.addBatch(sql);
		}
		stmt.addBatch("delete from user where 1=1;");
		stmt.executeBatch();
		conn.commit();
		stmt.close();
		System.out.println(sql);
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executeStatement04() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		sql = statSql;
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = sql + nextVal + ",";
			if (i % 500 == 0) {
				if (!sql.equalsIgnoreCase(statSql) && sql.endsWith(",")) {
					sql = sql.substring(0, sql.length() - 1);
					stmt.execute(sql);
				}
				sql = statSql;
			}

		}
		if (!sql.equalsIgnoreCase(statSql) && sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
			stmt.execute(sql);
		}
		stmt.execute("delete from user where 1=1;");
		conn.commit();
		stmt.close();
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executePrepareStatement01() throws SQLException {
		long startTime = System.currentTimeMillis();
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;

		String[] genders = new String[] { "male", "female" };
		sql = "REPLACE INTO user (id,name,password,gender) VALUE (?,?,?,?)";
		PreparedStatement pstat = conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		Random rnd = new Random(System.currentTimeMillis());
		for (int i = 1; i <= 10000; i++) {
			int intRand = Math.abs(rnd.nextInt() % 2);
			name = "zhangsan" + i;
			password = "pass" + intRand;
			intRand = Math.abs((intRand) % 2);

			gender = genders[intRand];

			pstat.clearParameters();
			pstat.setInt(1, i);
			pstat.setString(2, name);
			pstat.setString(3, password);
			pstat.setString(4, gender);
			pstat.execute();

		}
		pstat.execute("delete from user where 1=1;");
		conn.commit();
		pstat.close();

		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));
	}

	public void executePrepareStatement02() throws SQLException {
		long startTime = System.currentTimeMillis();
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;

		String[] genders = new String[] { "male", "female" };
		sql = "REPLACE INTO user (id,name,password,gender) VALUE (?,?,?,?)";
		PreparedStatement pstat = conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		Random rnd = new Random(System.currentTimeMillis());
		for (int i = 1; i <= 10000; i++) {
			int intRand = Math.abs(rnd.nextInt() % 2);
			name = "zhangsan" + i;
			password = "pass" + intRand;
			intRand = Math.abs((intRand) % 2);

			gender = genders[intRand];

			pstat.setInt(1, i);
			pstat.setString(2, name);
			pstat.setString(3, password);
			pstat.setString(4, gender);
			pstat.addBatch();

		}
		pstat.addBatch("delete from user where 1=1;");
		pstat.executeBatch();
		conn.commit();
		pstat.close();

		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));
	}

	public void executeStatement04_2() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		sql = statSql;
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = sql + nextVal + ",";
			if (i % MODE == 0) {
				if (!sql.equalsIgnoreCase(statSql) && sql.endsWith(",")) {
					sql = sql.substring(0, sql.length() - 1);
					stmt.execute(sql);
				}
				sql = statSql;
			}
		}
		if (!sql.equalsIgnoreCase(statSql) && sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
			stmt.execute(sql);
		}
		stmt.execute("delete from user where 1=1;");
		conn.commit();
		stmt.close();
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executeStatement02_2() throws SQLException {
		long startTime = System.currentTimeMillis();
		Statement stmt = conn.createStatement();
		conn.setAutoCommit(false);
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;
		String[] genders = new String[] { "male", "female" };
		Random rnd = new Random(System.currentTimeMillis());
		String statSql = "REPLACE INTO user(id,name,password,gender) VALUES ";
		for (int i = 1; i <= 10000; i++) {
			int intRand = rnd.nextInt();
			name = "'" + "zhangsan" + i + "'";
			password = "'" + "pass" + intRand + "'";
			intRand = Math.abs((intRand) % 2);
			gender = "'" + genders[intRand] + "'";
			String nextVal = "(" + i + "," + name + "," + password + ","
					+ gender + ")";
			sql = statSql + nextVal + ";";
			stmt.addBatch(sql);
			if (i % MODE == 0) {
				stmt.executeBatch();
			}

		}
		stmt.addBatch("delete from user where 1=1;");
		stmt.executeBatch();
		conn.commit();
		stmt.close();
		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));

	}

	public void executePrepareStatement02_02() throws SQLException {
		long startTime = System.currentTimeMillis();
		String sql = null;
		String name = null;
		String password = null;
		String gender = null;

		String[] genders = new String[] { "male", "female" };
		sql = "REPLACE INTO user (id,name,password,gender) VALUE (?,?,?,?)";
		PreparedStatement pstat = conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		Random rnd = new Random(System.currentTimeMillis());
		for (int i = 1; i <= 10000; i++) {
			int intRand = Math.abs(rnd.nextInt() % 2);
			name = "zhangsan" + i;
			password = "pass" + intRand;
			intRand = Math.abs((intRand) % 2);

			gender = genders[intRand];

			pstat.setInt(1, i);
			pstat.setString(2, name);
			pstat.setString(3, password);
			pstat.setString(4, gender);
			pstat.addBatch();
			if (i % MODE == 0) {
				pstat.executeBatch();
			}

		}
		pstat.addBatch("delete from user where 1=1;");
		pstat.executeBatch();
		conn.commit();
		pstat.close();

		System.out.println("final : "
				+ (System.currentTimeMillis() - startTime));
	}

}

Test call code instance

 public static void main( String[] args )
    {
       
    	try {
    		System.out.println("executeStatement04_2");
    		for (int i = 0; i < 10; i++) {
    			StatementTestCase.shareInstace().executeStatement04_2();
            }
    		
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    }

test result

executeStatement01
final : 1616
final : 1124
final : 995
final : 1213
final : 1055
final : 1245
final : 1339
final : 1055
final : 1506
final : 1305
executeStatement02
final : 1504
final : 1282
final : 1416
final : 1136
final : 1186
final : 1171
final : 1130
final : 1776
final : 1369
final : 1105
executeStatement03
final : 4700
final : 5141
final : 4223
final : 4308
final : 4200
final : 4257
final : 4459
final : 4935
final : 4198
final : 4274
executeStatement04
final : 4271
final : 4310
final : 4167
final : 4581
final : 4159
final : 4259
final : 4657
final : 4384
final : 4166
final : 4217
executePrepareStatement01
final : 1161
final : 1522
final : 1067
final : 998
final : 982
final : 946
final : 1005
final : 886
final : 1306
final : 896
executePrepareStatement02
final : 1149
final : 1024
final : 1701
final : 1137
final : 892
final : 873
final : 964
final : 1006
final : 1188
final : 948

Summary of results

Above all, we use InnoDB transaction support engine. If we do not use transaction executeStatement03 and executeStatement04 cases, the speed is the fastest, but it is also around 4200 milliseconds and 4200 milliseconds. It is too time-consuming to test without transaction, so we save the result without transaction.

(2) We can conclude that executeStatement 03 and executeStatement 04 should be the fastest in theory, but the effect is not ideal after using jdbc driver. We have reason to doubt the way of jdbc implementation.

In the database, by executing the SQL statements spliced by executeStatement04, the efficiency is 155 milliseconds, while the other statements are slower.

(3) Transaction can improve efficiency.

(4) Prepare Statement can obviously improve the efficiency.

jdbc's addBatch & executeBatch execution efficiency is not stable, but overall higher than unused (not obvious)

The first execution of the preprocessing statement is slow.

 

Improvement test

In addition, according to other conclusions, the batch processing efficiency can be improved appropriately. We modified the batch processing methods with better performance under three different conditions.

executeStatement02

executeStatement04

executePrepareStatement02

1. Let's first set max_allowed_packet = 8M

set global max_allowed_packet = 8*1024*1024

2. Improved code to control batch granularity with MODE

public void executeStatement04_2() throws SQLException
	{
		 long startTime = System.currentTimeMillis();
		 Statement stmt = conn.createStatement();
		 conn.setAutoCommit(false);
		 String sql = null;
		 String name = null;
		 String password = null;
		 String gender = null;
		 String[] genders = new String[]{"male","female"};
		 Random rnd = new Random(System.currentTimeMillis());
		 String statSql="REPLACE INTO user(id,name,password,gender) VALUES ";
		 sql = statSql;
		 for (int i=1;i<=10000;i++)
		 {
			 int intRand = rnd.nextInt();
			 name = "'"+"zhangsan"+i+"'";
			 password = "'"+"pass"+intRand+"'";
			 intRand = Math.abs((intRand)%2);
			 gender = "'"+genders[intRand]+"'";
			 String nextVal =  "("+i+","+name+","+password+","+gender+")";
			 sql =  sql +  nextVal+",";
			 if(i%MODE==0)
			 {
				 if(!sql.equalsIgnoreCase(statSql) && sql.endsWith(","))
				 {
					 sql = sql.substring(0,sql.length()-1); 
					 stmt.execute(sql);
				 }
				 sql = statSql;
			 }
		 }
		 if(!sql.equalsIgnoreCase(statSql) && sql.endsWith(","))
		 {
			 sql = sql.substring(0,sql.length()-1); 
			 stmt.execute(sql);
		 }
		 stmt.execute("delete from user where 1=1;");
		 conn.commit();
		 stmt.close();
		 System.out.println("final : "+(System.currentTimeMillis()-startTime));
		
	}
	
	public void executeStatement02_2() throws SQLException  
	{
		 long startTime = System.currentTimeMillis();
		 Statement stmt = conn.createStatement();
		 conn.setAutoCommit(false);
		 String sql = null;
		 String name = null;
		 String password = null;
		 String gender = null;
		 String[] genders = new String[]{"male","female"};
		 Random rnd = new Random(System.currentTimeMillis());
		 String statSql="REPLACE INTO user(id,name,password,gender) VALUES ";
		 for (int i=1;i<=10000;i++)
		 {
			 int intRand = rnd.nextInt();
			 name = "'"+"zhangsan"+i+"'";
			 password = "'"+"pass"+intRand+"'";
			 intRand = Math.abs((intRand)%2);
			 gender = "'"+genders[intRand]+"'";
			 String nextVal =  "("+i+","+name+","+password+","+gender+")";
			 sql =  statSql +  nextVal +";";
			 stmt.addBatch(sql);
			 if(i%MODE==0)
			 {
				 stmt.executeBatch();
			 }
			
		 }
		 stmt.addBatch("delete from user where 1=1;");
		 stmt.executeBatch();
		 conn.commit();
		 stmt.close();
		 System.out.println("final : "+(System.currentTimeMillis()-startTime));
		
	}
	
	public void executePrepareStatement02_02() throws SQLException
	{
		 long startTime = System.currentTimeMillis();
		 String sql = null;
		 String name = null;
		 String password = null;
		 String gender = null;
		 
		 String[] genders = new String[]{"male","female"};
		 sql="REPLACE INTO user (id,name,password,gender) VALUE (?,?,?,?)";
		 PreparedStatement pstat = conn.prepareStatement(sql);
		 conn.setAutoCommit(false);
		 Random rnd = new Random(System.currentTimeMillis());
		 for (int i=1;i<=10000;i++)
		 {
			 int intRand = Math.abs(rnd.nextInt()%2);
			 name = "zhangsan"+i;
			 password = "pass"+intRand;
			 intRand = Math.abs((intRand)%2);
			 
			 gender = genders[intRand];
			 
			 pstat.setInt(1, i);
			 pstat.setString(2, name);
			 pstat.setString(3, password);
			 pstat.setString(4, gender);
			 pstat.addBatch();
			 if(i%MODE==0)
			 {
				 pstat.executeBatch();
			 }
			
		 }
		 pstat.addBatch("delete from user where 1=1;");
		 pstat.executeBatch();
		 conn.commit();
		 pstat.close();
		 
		 System.out.println("final : "+(System.currentTimeMillis()-startTime));
	}

test result

MODE=500

executeStatement02_2
final : 1558
final : 1228
final : 1070
final : 1477
final : 1002
final : 1024
final : 1003
final : 1129
final : 1415
final : 1306
executeStatement04_2
final : 464
final : 511
final : 538
final : 523
final : 568
final : 396
final : 555
final : 429
final : 672
final : 449
executePrepareStatement02_02
final : 1434
final : 968
final : 991
final : 975
final : 1015
final : 1099
final : 1180
final : 1001
final : 889
final : 1291

MODE=1000

executeStatement02_2
final : 1860
final : 1420
final : 1502
final : 1195
final : 1395
final : 1135
final : 1139
final : 1652
final : 1200
final : 1433
executeStatement04_2
final : 642
final : 888
final : 562
final : 603
final : 853
final : 556
final : 595
final : 820
final : 637
final : 596
executePrepareStatement02_02
final : 1663
final : 1281
final : 1131
final : 1381
final : 1341
final : 1230
final : 1189
final : 1227
final : 1585
final : 1231

CONCLUSION AFTER REFORMATION

(1) The efficiency of addBatch & executeBatch is not improved, but slightly decreased by controlling granularity and submitting in batches by MODE under the above conditions.

(2) We find that the efficiency of executeStatement 04 has been improved by 7-8 times, but with the increase of MODE, the efficiency decreases slightly.

 

Final conclusion

Through the above experiments and the modified experiments, the final conclusions are as follows:

(1) Multi-Values (e.g. executeStatement 04_2) splicing in non-transactional state is more efficient than general SQL, which can increase efficiency by tens of times.

(2) The use of transactions can increase efficiency dozens of times

(3) Transaction + Multi-Values has the highest batch processing efficiency in granularity 500-1000

(4) The response of addBatch & executeBatch to granularity control is not obvious, so it is suggested that executeBatch be executed once.

Pre-compiled SQL is generally efficient, but its first execution is very inefficient.

Posted by hyd_guy on Sun, 14 Apr 2019 10:54:31 -0700

Hot Keywords

©2024 Programmer Group