MySQL Forums
Forum List  »  Performance

Write Operations very slow in InnoDB engine when compared to MyIsam
Posted by: MURALI NARAYANASWAMY
Date: November 28, 2014 07:07AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Write Operations very slow in InnoDB engine when compared to MyIsam
1667
November 28, 2014 07:07AM


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.