MySQL Bugs: #55038: InnoDB: performance drop in subselect_exists/subselect

Bug #55038	InnoDB: performance drop in subselect_exists/subselect_in test-cases
Submitted:	6 Jul 2010 21:48	Modified:	17 Oct 2010 0:21
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

Description:
Performance drop was observed for 'subselect_exists' and 'subselect_in' queries from mysql-bench suite. These queries use a bit different access methods but pattern of performance drop is very similar and likely cause of problem is the same for both queries.

subselect_in
------------
SELECT * FROM bench1 WHERE id IN (SELECT id FROM bench2)

5.5.3: 1716.55 sec
5.5.4: 1895.8  sec  
5.5.5: 2022.27 sec

EXPLAIN is the same for 5.5.3/5.5.4/5.5.5:

 explain SELECT * FROM bench1 WHERE id IN (SELECT id FROM bench2);
+----+--------------------+--------+-----------------+---------------+---------+---------+------+-------+-------------+
| id | select_type        | table  | type            | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+--------------------+--------+-----------------+---------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY            | bench1 | ALL             | NULL          | NULL    | NULL    | NULL | 29862 | Using where |
|  2 | DEPENDENT SUBQUERY | bench2 | unique_subquery | PRIMARY       | PRIMARY | 4       | func |     1 | Using index |
+----+--------------------+--------+-----------------+---------------+---------+---------+------+-------+-------------+

subselect_exists
-----------------
SELECT * FROM bench1 WHERE EXISTS (SELECT * FROM bench2 WHERE bench2.id=bench1.id)

5.5.3: 2001.64 sec
5.5.4: 2296.92 sec
5.5.5: 2449.91 sec

EXPLAIN is the same for 5.5.3/5.5.4/5.5.5:
mysql> explain SELECT * FROM bench1 WHERE EXISTS (SELECT * FROM bench2 WHERE bench2.id=bench1.id);
+----+--------------------+--------+--------+---------------+---------+---------+----------------+-------+-------------+
| id | select_type        | table  | type   | possible_keys | key     | key_len | ref            | rows  | Extra       |
+----+--------------------+--------+--------+---------------+---------+---------+----------------+-------+-------------+
|  1 | PRIMARY            | bench1 | ALL    | NULL          | NULL    | NULL    | NULL           | 29862 | Using where |
|  2 | DEPENDENT SUBQUERY | bench2 | eq_ref | PRIMARY       | PRIMARY | 4       | test.bench1.id |     1 | Using index |
+----+--------------------+--------+--------+---------------+---------+---------+----------------+-------+-------------+

How to repeat:
- start server with default settings
- run attached test case(tables are the same for both queries)

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

perl  bug_sub.pl  --db-socket=/tmp/555.sock --subselect_in  --range-count=250
MySQL 5.5.5 m3, ENGINE: InnoDB
Generating random keys
Creating table bench1
Inserting 10000 rows in order
Time for insert_in_order (10000): 1.45908 wallclock secs ( 0.09 usr  0.15 sys +  0.00 cusr  0.00 csys =  0.24 CPU)
Inserting 10000 rows in reverse order
Time for insert_reverse_order (10000): 1.45579 wallclock secs ( 0.08 usr  0.11 sys +  0.00 cusr  0.00 csys =  0.19 CPU)
Inserting 10000 rows in random order
Time for insert_random_order (10000): 1.52148 wallclock secs ( 0.18 usr  0.14 sys +  0.00 cusr  0.00 csys =  0.32 CPU)

Inner table size: 100
Time for subselect_in (count=250:rows=25000): 31.0069 wallclock secs ( 0.09 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.11 CPU)

b) to run query only 
perl  bug_sub.pl  --db-socket=/tmp/555.sock --subselect_exist --skip-create --range-count=250
MySQL 5.5.5 m3, ENGINE: InnoDB
Inner table size: 100
Time for subselect_exists (count=250:rows=25000): 38.0808 wallclock secs ( 0.08 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.08 CPU)

Test case for the issue

Attachment: bug_sub.pl (application/octet-stream, text), 6.37 KiB.

=== 5.5.4 ===

perl  bug_sub.pl  --db-socket=/tmp/mysql.sock --subselect_in
MySQL 5.5.4 m3, ENGINE: InnoDB 
Generating random keys
Creating table bench1
Inserting 10000 rows in order
Time for insert_in_order (10000): 1.75878 wallclock secs ( 0.09 usr  0.15 sys +  0.00 cusr  0.00 csys =  0.24 CPU)
Inserting 10000 rows in reverse order
Time for insert_reverse_order (10000): 1.89959 wallclock secs ( 0.10 usr  0.13 sys +  0.00 cusr  0.00 csys =  0.23 CPU)
Inserting 10000 rows in random order
Time for insert_random_order (10000): 1.92451 wallclock secs ( 0.17 usr  0.14 sys +  0.00 cusr  0.00 csys =  0.31 CPU)

Inner table size: 100
Time for subselect_in (count=250:rows=25000): 20.7792 wallclock secs ( 0.07 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.07 CPU)

=== 5.5.5 ===

perl  bug_sub.pl  --db-socket=/tmp/mysql.sock --subselect_in
MySQL 5.5.5 m3, ENGINE: InnoDB 
Generating random keys
Creating table bench1
Inserting 10000 rows in order
Time for insert_in_order (10000): 2.19597 wallclock secs ( 0.10 usr  0.18 sys +  0.00 cusr  0.00 csys =  0.28 CPU)
Inserting 10000 rows in reverse order
Time for insert_reverse_order (10000): 1.82821 wallclock secs ( 0.10 usr  0.13 sys +  0.00 cusr  0.00 csys =  0.23 CPU)
Inserting 10000 rows in random order
Time for insert_random_order (10000): 1.83678 wallclock secs ( 0.13 usr  0.14 sys +  0.00 cusr  0.00 csys =  0.27 CPU)

Inner table size: 100
Time for subselect_in (count=250:rows=25000): 20.2798 wallclock secs ( 0.10 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.10 CPU)

I don't see any regression. Actually a minor improvement in the 5.5.5 tree.

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".

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".

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".