Yesterday's MySQL Casual Advent Calendar 2017 was @ soudai1025's [Why don't you read SHOW ENGINE INNODB STATUS](http: / /soudai.hatenablog.com/entry/2017/12/20/030013).
It is a continuation of.
This time, we will verify whether the performance can be improved by enabling the cache function of prepared statements.
Of
cachePrepStmts Use (true) / not use (** false **) prepared statement cache
prepStmtCacheSize Number of prepared statements to cache (** 25 **)
prepStmtCacheSqlLimit Maximum number of prepared statements to cache (** 256 **)
** Bold ** is the default
Examine the change in performance when changing from the default.
Similar to Part 2, the following articles will be helpful.
** The one that seems to work with the settings of mysql Connector / J Mr.) **
Also, as referred to in the article above, the recommended value for HikariCP (fast connection pooling) is shown here.
This time, I modified the code used in Part 1 (a new table is prepared).
COMMIT in units of 10,000 linesIn the form of, the following three patterns are compared and verified.
The result of checking each required time is shown.
| Verification pattern | Time required(ms) |
|---|---|
| Only the properties used in Part 2 | 38,057 |
Property used in Part 2 +cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=2048 |
39,907 |
Property used in Part 2 +cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=2048 |
30,027 |
I found out.
prepStmtCacheSize) of 69 and 70.To be honest, I was surprised that client-based prepared statements would have a caching effect. I would like to check the source code once and see what kind of processing it is doing.
However, if the number of prepared statements used on a daily basis exceeds even one cache upper limit, the effect may become zero (in some cases, negative) (the cache algorithm is not very good). (Maybe), I feel that it is quite difficult to use.
At the very least, ** HikariCP recommends 250, so let's keep it at 250 **, as it can cause unexpected troubles due to application growth, so it is better to stop it.
Tomorrow's MySQL Casual Advent Calendar 2017 will try @ huato's docker-compose to easily build a test environment for group replication. .
The base is the one used in Part 1. The following are added / changed.
DbInsert.Java (code added from line 66)
private static final int PSMT_ALL_COLUMN = 70;
private static final int PSMT_STORE_COLUMN = 10;
private static final String PSMT_MEMO = "1234567890";
public void psmtInsert(int totalline, int commitline, String option) {
try (
Connection con = new DbConnection().getConnectionForTest(JDBC_URL + option);
) {
con.setAutoCommit(false);
for (int i = 0; i < totalline; i++) {
StringBuilder sb_pfx = new StringBuilder("INSERT INTO psmt_test.psmt_test (");
StringBuilder sb_sfx = new StringBuilder(" VALUES (");
for (int j = 1; j <= PSMT_STORE_COLUMN; j++) {
sb_pfx.append("memo" + (Integer.toString((i % PSMT_ALL_COLUMN) + j) + ((j == PSMT_STORE_COLUMN) ? ")" : ", ")));
sb_sfx.append("?" + ((j == PSMT_STORE_COLUMN) ? ")" : ", "));
}
try (
PreparedStatement psmt = con.prepareStatement(sb_pfx.toString() + sb_sfx.toString());
) {
for (int j = 1; j <= PSMT_STORE_COLUMN; j++) {
psmt.setString(j, PSMT_MEMO);
}
psmt.execute();
if (((i + 1) % commitline == 0) || ((i + 1) == totalline)) {
con.commit();
}
} catch (SQLException e) {
throw e; }
}
} catch (ClassNotFoundException e) {
System.out.println("[Error] Driver not found.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("[Error] Invalid DB access.");
e.printStackTrace();
}
}
PsmtInsert.java (new addition)
package site.hmatsu47.conjtest;
public class PsmtInsert extends Thread {
private int totalline = 0;
private int commitline = 0;
private String option;
private String title;
public PsmtInsert(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().psmtInsert(totalline, commitline, option);
long endtime = System.nanoTime();
System.out.println("[Psmt] " + title + " : " + String.valueOf((endtime - starttime) / (1000 * 1000)) + " msec.");
}
}
Main.java (change)
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 = "";
private static final int DEFAULT_PSMT_THREAD = 0;
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]);
final int psmtthread = (args.length < 7 ? DEFAULT_PSMT_THREAD : Integer.valueOf(args[6]).intValue());
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);
}
for (int i = 1; i <= psmtthread; i++) {
PsmtInsert pi = new PsmtInsert(totalline, commitline, option, String.valueOf(i));
pi.start();
Thread.sleep(1000);
}
}
catch (Exception e) {
System.out.println("[Error]");
e.printStackTrace();
}
}
}
Additional DB / table definition
CREATE DATABASE psmt_test;
CREATE TABLE psmt_test.psmt_test
(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
memo1 VARCHAR(10),
memo2 VARCHAR(10),
memo3 VARCHAR(10),
memo4 VARCHAR(10),
memo5 VARCHAR(10),
memo6 VARCHAR(10),
memo7 VARCHAR(10),
memo8 VARCHAR(10),
memo9 VARCHAR(10),
memo10 VARCHAR(10),
memo11 VARCHAR(10),
memo12 VARCHAR(10),
memo13 VARCHAR(10),
memo14 VARCHAR(10),
memo15 VARCHAR(10),
memo16 VARCHAR(10),
memo17 VARCHAR(10),
memo18 VARCHAR(10),
memo19 VARCHAR(10),
memo20 VARCHAR(10),
memo21 VARCHAR(10),
memo22 VARCHAR(10),
memo23 VARCHAR(10),
memo24 VARCHAR(10),
memo25 VARCHAR(10),
memo26 VARCHAR(10),
memo27 VARCHAR(10),
memo28 VARCHAR(10),
memo29 VARCHAR(10),
memo30 VARCHAR(10),
memo31 VARCHAR(10),
memo32 VARCHAR(10),
memo33 VARCHAR(10),
memo34 VARCHAR(10),
memo35 VARCHAR(10),
memo36 VARCHAR(10),
memo37 VARCHAR(10),
memo38 VARCHAR(10),
memo39 VARCHAR(10),
memo40 VARCHAR(10),
memo41 VARCHAR(10),
memo42 VARCHAR(10),
memo43 VARCHAR(10),
memo44 VARCHAR(10),
memo45 VARCHAR(10),
memo46 VARCHAR(10),
memo47 VARCHAR(10),
memo48 VARCHAR(10),
memo49 VARCHAR(10),
memo50 VARCHAR(10),
memo51 VARCHAR(10),
memo52 VARCHAR(10),
memo53 VARCHAR(10),
memo54 VARCHAR(10),
memo55 VARCHAR(10),
memo56 VARCHAR(10),
memo57 VARCHAR(10),
memo58 VARCHAR(10),
memo59 VARCHAR(10),
memo60 VARCHAR(10),
memo61 VARCHAR(10),
memo62 VARCHAR(10),
memo63 VARCHAR(10),
memo64 VARCHAR(10),
memo65 VARCHAR(10),
memo66 VARCHAR(10),
memo67 VARCHAR(10),
memo68 VARCHAR(10),
memo69 VARCHAR(10),
memo70 VARCHAR(10),
memo71 VARCHAR(10),
memo72 VARCHAR(10),
memo73 VARCHAR(10),
memo74 VARCHAR(10),
memo75 VARCHAR(10),
memo76 VARCHAR(10),
memo77 VARCHAR(10),
memo78 VARCHAR(10),
memo79 VARCHAR(10),
memo80 VARCHAR(10)
);
Recommended Posts