Recently had to do an investigation about MySql server performance. So what I basically did was: created a winXP wmWare image, created a script for generating populating sql; installed db server version on that machine and kept track of times while executing sql statements; reverted to the original virgin machine state; start again. The versions I tested on were MySql community server 5.1, 5.5 and the development verion of 5.6. Here is the chart that represents my findings[the smaller the better]:
As a quick summary – interestingly enough I do not see any speed improvement since 5.1:) there is one between 5.5 and 5.6 but it’s nowhere near the 5.1.
Now just a quick overview of what I used to actually get this chart.
The testTable:
The queries:
| Q1 |
| Q2 |
| Q3 |
| Q4 |
| Q5 |
| Q6 |
| Q7 |
| Q8 |
| Q9 |
| Q10 |
| Q11 |
| Q12 |
| Q13 |
| Q14 |
| Q15 |
| Q16 |
Absolute times and machine spec can be found here.
Of course this is the developer machine setup and ran on one virtual machine, but still I thought that would be interesting for other to know. Ireally looking forward to making some more production-like tests now…
One more thing in case interested – the code snippet I used for generating the data:
public class CreateTestTableSql {
private static final int QUERY_ROWS = 10 * 1000 * 1000;
private static final int BATCH_SIZE = 10 * 1000;
private static final boolean USE_BATCH = true;
static Random r = new Random();
static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm");
public static void main(String[] args) throws Exception {
StringBuilder spotTradeSQLRow = new StringBuilder();
File f = new File(USE_BATCH ? "batchmysql.sql" : "mysql.sql");
if (f.exists()) {
f.delete();
}
assert f.createNewFile();
BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f)));
System.out.println("Start");
int batchCounter = 0;
for (int i = 0; i < QUERY_ROWS; i++) {
if (USE_BATCH) {
if (batchCounter == BATCH_SIZE) {
spotTradeSQLRow.append(";n");
batchCounter = 0;
}
if (batchCounter == 0) {
spotTradeSQLRow.append("INSERT INTO test.testtable VALUES(");
} else {
spotTradeSQLRow.append(",(");
}
} else {
spotTradeSQLRow.append("INSERT INTO test.testtable VALUES(");
}
spotTradeSQLRow.append(i + ","); //id
spotTradeSQLRow.append((r.nextBoolean() ?( "'" +getRandomString() + "'"): "NULL") + ","); //testVarchar
spotTradeSQLRow.append((r.nextBoolean() ? r.nextInt() : "NULL") + ","); //testInt
spotTradeSQLRow.append((r.nextBoolean() ? r.nextDouble() : "NULL") + ","); //testInt
spotTradeSQLRow.append((r.nextBoolean() ? ("'" + getRandomDate() + "'") : "NULL") +
")"); //testInt
if (!USE_BATCH) {
spotTradeSQLRow.append(";");
}
out.write(spotTradeSQLRow.toString());
out.write("n");
spotTradeSQLRow.delete(0, spotTradeSQLRow.length());
batchCounter++;
}
if (USE_BATCH) {
out.write(";");
}
out.close();
System.out.println("end");
}
private static String getRandomDate() {
int rand = r.nextInt(10);
rand *= (r.nextBoolean() ? -1 : 1);
final Calendar cal = Calendar.getInstance();
cal.add(Calendar.YEAR, rand);
cal.add(Calendar.DAY_OF_YEAR, rand);
cal.add(Calendar.HOUR_OF_DAY, rand);
return sdf.format(cal.getTime());
}
private static String getRandomString() {
final int rand = r.nextInt(10);
if (rand < 3) {
return "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";
} else if (rand >= 3 && rand < 6) {
return "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB";
} else {
return "CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC";
}
}
}