Bug #55036 InnoDB: performance regression in select_subq_sel_corr test
Submitted: 6 Jul 2010 21:00 Modified: 17 Oct 2010 0:19
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.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[6 Jul 2010 21:00] Alexey Stroganov
Description:
Performance drop was observed for select_subq_sel_corr query from mysql-bench suite:

query:
select avg((select max(b) from bench5 where bench5.b=bench6.b)) from bench6

perl bug_sub_sel_corr.pl --db-socket=/tmp/554.sock --skip-create
MySQL 5.5.4 m3, ENGINE: InnoDB
Correlated
Time for select_subq_sel_corr (count=100:rows=100): 31.6383 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

dl360-g5-c:/data0/ranger/2 # perl bug_sub_sel_corr.pl --db-socket=/tmp/555.sock --skip-create
MySQL 5.5.5 m3, ENGINE: InnoDB
Correlated
Time for select_subq_sel_corr (count=100:rows=100): 35.4435 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

explain looks identically for both 5.5.4 and 5.5.5:

explain select avg((select max(b) from bench5 where bench5.b=bench6.b)) from bench6;
+----+--------------------+--------+-------+---------------+----------+---------+---------------+-------+--------------------------+
| id | select_type        | table  | type  | possible_keys | key      | key_len | ref           | rows  | Extra                    |
+----+--------------------+--------+-------+---------------+----------+---------+---------------+-------+--------------------------+
|  1 | PRIMARY            | bench6 | index | NULL          | bench6i  | 5       | NULL          | 10480 | Using index              |
|  2 | DEPENDENT SUBQUERY | bench5 | ref   | bench6i1      | bench6i1 | 5       | test.bench6.b |    50 | Using where; Using index |
+----+--------------------+--------+-------+---------------+----------+---------+---------------+-------+--------------------------+

How to repeat:
- Start server with default settings
- run attached test case:

a) to create/populate tables and run query
perl bug_sub_sel_corr.pl --db-socket=/tmp/555.sock 

b) just to run query 
perl bug_sub_sel_corr.pl --db-socket=/tmp/555.sock --skip-create

c) you can adjust number of loops(default=100)
perl bug_sub_sel_corr.pl --db-socket=/tmp/555.sock --skip-create --loops=10
[6 Jul 2010 21:01] Alexey Stroganov
Test case for the issue

Attachment: bug_sub_sel_corr.pl (application/octet-stream, text), 4.15 KiB.

[8 Jul 2010 15:34] Omer Barnir
triage: setting tag to SR55RC (D2 - performance regression)
[14 Jul 2010 0:02] Sunny Bains
=== 5.5.5 ===
perl bug_sub_sel_corr.pl --db-socket=/tmp/mysql.sock --skip-create --loops=10
MySQL 5.5.5 m3, ENGINE: InnoDB 
Correlated
Time for select_subq_sel_corr (count=10:rows=10): 2.48343 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

perl bug_sub_sel_corr.pl --db-socket=/tmp/mysql.sock --skip-create
MySQL 5.5.5 m3, ENGINE: InnoDB 
Correlated
Time for select_subq_sel_corr (count=100:rows=100): 24.1013 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)

=== 5.5.4 ===
perl bug_sub_sel_corr.pl --db-socket=/tmp/mysql.sock --skip-create
MySQL 5.5.4 m3, ENGINE: InnoDB 
Correlated
Time for select_subq_sel_corr (count=100:rows=100): 27.3021 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)

**** Second run on warmed up cache ****
perl bug_sub_sel_corr.pl --db-socket=/tmp/mysql.sock --skip-create
MySQL 5.5.4 m3, ENGINE: InnoDB 
Correlated
Time for select_subq_sel_corr (count=100:rows=100): 27.2793 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

perl bug_sub_sel_corr.pl --db-socket=/tmp/mysql.sock --skip-create --loops=10
MySQL 5.5.4 m3, ENGINE: InnoDB 
Correlated
Time for select_subq_sel_corr (count=10:rows=10): 2.77811 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)

I don't see any regression, in fact the opposite, 5.5.5 is faster.
[14 Aug 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".
[16 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".
[17 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".