Simple UPDATE query takes too much time in InnoDB than MyISAM

Question

Quick description:

In my application (VC++/Windows 8) I am issuing simple update queries to increase value of a field in a InnoDB table in MySQL database. It takes way too long than the same with MyISAM table.

Details:

I am creating a table DEMO_TABLE having columns MyID and MyCounter (both integers) and engine MyISAM

pStatement->execute("CREATE TABLE IF NOT EXISTS DEMO_TABLE(MyID int NOT NULL PRIMARY KEY, MyCounter int) ENGINE=MyISAM");

I then added to the table a row having MyID value equal to 0. Then I am issuing UPDATE query in a loop:

time_t dwTime1 = time(&dwTime1);
for (int i=0; i<500; i++)
{
    char strUpdateRequest[256];
    sprintf_s(strUpdateRequest, 256, "UPDATE DEMO_TABLE SET MyCounter = (MyCounter + 1) WHERE ThreadID = 0");
    pStatement->executeUpdate(strUpdateRequest);
}
time_t dwTime2 = time(&dwTime2);
std::cout << "\nTime difference: " << (dwTime2 - dwTime1);

It ran quickly and the output was:

Time difference: 0

Means it has consumed less than a second.

But when I deleted table and repeated all this exercise again with InnoDB engine.

pStatement->execute("CREATE TABLE IF NOT EXISTS DEMO_TABLE(ThreadID int NOT NULL PRIMARY KEY, MyCounter int) ENGINE=InnoDB");

However, this time to my surprise it took much longer and the output was:

Time difference: 17

It has consumed 17 seconds.

(Despite, in both the cases above I checked table containts and found MyCounter column value has been populated correctly (500))

UPDATE:

I also have observed there is lot of disk activity in these 17 seconds.

Question:

In many discussions and even in MySQL documents it has been mentioned that in case of updates InnoDB performs much better than MyISAM. But I observed exactly opposite.

Can someone please clarify this behavior? Am I doing anything wrong?

Answer 1

I found the issue was here in my.ini (which can be located in C:\ProgramData\MySQL\MySQL Server 5.6 or similar folder based on installation)

There is parameter innodb_flush_log_at_trx_commit which has value defaulted to zero causing 1 MB of innodb_log_buffer_size to be written to disk at each commit. This was resulting in major performance penalty.

So I made innodb_flush_log_at_trx_commit=2 after reading its description and then restarted MySQL server, then performance boosted a lot.

If set to 1, InnoDB will flush (fsync) the transaction logs to the disk at each commit, which offers full ACID behavior. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 or 2 to reduce disk I/O to the logs. Value 0 means that the log is only written to the log file and the log file flushed to disk approximately once per second. Value 2 means the log is written to the log file at each commit, but the log file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=2