Bug #55035 InnoDB: performance drop in update_of_key_big test-case
Submitted: 6 Jul 2010 19:40 Modified: 12 Oct 2010 0:57
Reporter: Alexey Stroganov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.4,5.5.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[6 Jul 2010 19:40] Alexey Stroganov 
Description:
Performance drop was observed for update_of_key_big scenario from mysql-bench suite:

5.5.3: Time for update_of_key_big (100000):  11.726 wallclock secs
5.5.4: Time for update_of_key_big (100000):  21.897 wallclock secs
5.5.5: Time for update_of_key_big (100000):  23.340 wallclock secs

table definition:
-----------------
CREATE TABLE `bench1` (
  `id` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  `dummy1` char(30) DEFAULT NULL,
  PRIMARY KEY (`id`,`id2`),
  KEY `ix_id3` (`id3`)
) ENGINE=InnoDB

update loop:
------------
for ($i= 0 ; $i < $opt_loop_count ; $i+= $step)
{
  $count++;
  $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i");
}

explain
--------
explain looks the same for 553,554,555.

mysql> explain select * from bench1 where id3 >= 0 and id3 <= 9996;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | bench1 | range | ix_id3        | ix_id3 | 4       | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+

How to repeat:
- Start server with default settings
- run attached test case:
perl bug_update_of_key_big.pl --db-socket=/tmp/555.sock
[6 Jul 2010 19:41] Alexey Stroganov 
Test case for the issue

Attachment: bug_update_of_key_big.pl (application/octet-stream, text), 2.83 KiB.
[8 Jul 2010 15:36] Omer Barnir 
triage: setting tag to SR55RC (D2 - performance regression)
[11 Aug 2010 9:32] Marko Mäkelä 
Is this a duplicate of Bug #54914?
[11 Sep 2010 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Oct 2010 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".