Mysql – InnoDB query duration inconsistent

innodbmyisamMySQL

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 updates will take 20ms, and the 21st update will suddenly take 200ms. I've set up an incredibly basic test:

CREATE TABLE `test` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I insert a single row into the table, and then I have a C# console program that does a series of updates:

for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   MySqlCommand cmd = new MySqlCommand();
   cmd.Connection = conn;
   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}

This is the output I see from the program:

MySQL version : 5.5.17
Duration: 36
Duration: 30
Duration: 16
Duration: 26
Duration: 152
Duration: 47
Duration: 71
Duration: 77
Duration: 46
Duration: 28
Duration: 21
Duration: 25
Duration: 17

If I run "SHOW profile FOR QUERY" with the 152ms time, both the "Updating" and "query end" values are abnormally high.

If I switch the table to MyISAM, the query duration is perfect, but I don't want table-locking. Does anyone have a guess as to what is making InnoDB act this way?

Best Answer

ASPECT #1 : InnoDB

What immediately catches my eye is the fact that the table has no explicit PRIMARY KEY. This means that an internal rowID Clustered Index (gen_clust_index) is being used.

With each UPDATE command, you may encounter moments when the row is actually locked in conjunction with the Clustered Index. Consequently, the data page gets locked.

Here is a question: Is it possible for the PRIMARY KEY (or rowID) of two different rows to deadlock? Surprisingly, the answer is Yes. Rather than explain why, I will refer you to three questions asked by one person: RedBlueThing.

I managed to answer his three questions about deadlocking pages:

It is possible that two or more rows occupy the same data page. In fact, the rowID-based PRIMARY KEY coexists with the data on the same data page. While crisscrossing rows, perhaps some miscellaneous garbage collection (data structures within the InnoDB Buffer Pool that are released and reallocated) may be happening.

ASPECT #2 : Your C# Code

Inside the loop, you are also doing garbage collection and reallocation for the DB Connector. That needs to be reorganized, perhaps placing it outside the loop.

SUGGESTION

Try setting up the test using the transaction paradigm with BeginExecuteNonQuery()

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.BeginExecuteNonQuery();
for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}
cmd.EndExecuteNonQuery();

Give it a Try !!!

Related Question