Write Operations very slow in InnoDB engine when compared to MyIsam
Hi,
I have created two test tables, one with InnoDB Engine and other with MyIsam Engine
public void creatTable(Connection con, String tableName,String dbEngine){
Statement stmt = null;
String query = "CREATE TABLE "+tableName+"( \n" +
"`id` int(7) NOT NULL AUTO_INCREMENT, \n" +
"`Model` varchar(100) DEFAULT NULL, \n" +
"`Make` varchar(100) DEFAULT NULL, \n" +
"`Blade` varchar(100) DEFAULT NULL, \n" +
"PRIMARY KEY (`id`)\n" +
") ENGINE="+dbEngine+";";
try{
stmt = con.createStatement();
stmt.execute(query);
System.out.println("Table :" + tableName + " Created Successfully with " + dbEngine + "DB Engine");
}catch(SQLException sql){
sql.printStackTrace();
}
}
Passed table name and DB engine as parameters.During insertion of 10000 rows
public void insertRecords(Connection con, String tableName){
String query;
Statement stmt = null;
long startTime = System.currentTimeMillis();
try{
stmt = con.createStatement();
//max = 10000
for (int i=1; i<max;i++){
query = "insert into "+tableName+" values ("+i+",'Model"+i+"', 'Make"+i+"', 'Blade"+i+"')";
stmt.executeUpdate(query);
}
}
catch(SQLException sql){
sql.printStackTrace();
}
finally{
closeResources(stmt);
}
long endTime = System.currentTimeMillis();
long diffTime = endTime - startTime;
System.out.println("Insert Query Time taken :" + diffTime/1000);
}
InnoDB Engine returns : Insert Query Time taken :22 Secs
Time taken for Select Query - 1 Sec
MyIsam Engine returns : Insert Query Time taken : 0 Sec
The test is done in Windows 64 bit with 8 Gig memory. The max cpu usage was found to be at 58%.
Please advice how to tune the InnoDB engine as my SQL tables need to be in transactional state, so that I can commit or roll back based on the txn results.
Thanks much in advance.
Muralidharan N
Subject
Views
Written By
Posted
Write Operations very slow in InnoDB engine when compared to MyIsam
1667
November 28, 2014 07:07AM
703
November 28, 2014 06:59PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.