MySQL Bugs: #46814: myisam: performance regression in subselect

Bug #46814	myisam: performance regression in subselect_exists test on x64
Submitted:	19 Aug 2009 18:27	Modified:	20 Sep 2009 20:54
Reporter:	Alexey Stroganov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.85	OS:	Linux (x64)
Assigned to:		CPU Architecture:	Any

Description:
While analyzing MyISAM results of mysql-bench test for 5.0.85/x64 we found that results for subselect_exists operation is worse on ~20% than in previous release.

5.0.84/MyISAM/x64: subselect_exists 507.40 sec
5.0.85/MyISAM/x64: subselect_exists 621.15 sec

In case of i686 binary results for 5.0.85 is a bit better than for 5.0.84:
5.0.84/MyISAM/i686: subselect_exists 653.16
5.0.85/MyISAM/i686: subselect_exists 617.16

query:
SELECT * FROM bench1 WHERE EXISTS (SELECT * FROM bench2 WHERE bench2.id=bench1.id)

execution plans for both servers for query above are the same.

we extracted test-case for this exact query and run it:

x32:
----
MySQL 5.0.84 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 29.4552 wallclock secs 

MySQL 5.0.85 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 29.3288 wallclock secs 

x64:
----
MySQL 5.0.84 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 24.6794 wallclock secs 

MySQL 5.0.85 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 29.2516 wallclock secs 

How to repeat:
1. unpack 5.0.84/5.0.85 x64 packages 
2. start servers with defaults like:
cd 5.0.84
./bin/mysqld --no-defaults --basedir=. --datadir=data/ --socket=/tmp/5084.sock --port=5084
cd 5.0.85
./bin/mysqld --no-defaults --basedir=. --datadir=data/ --socket=/tmp/5085.sock --port=5085

3. run attached test-case

perl bug-exists.pl  --db-socket=/tmp/5084.sock
perl bug-exists.pl  --db-socket=/tmp/5085.sock

Test case for the issue

Attachment: bug-exists.pl (application/octet-stream, text), 3.96 KiB.

Re-verified with the re-build mysql-enterprise-5.0.85-linux-x86_64-glibc23.tar.gz package, by running the attached test case and comparing the results with that of 5.0.84.

Issue still persists.

x64:
----
MySQL 5.0.84 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 23.8825 wallclock secs

MySQL 5.0.85 enterprise, ENGINE: myisam
Time for subselect_exists (500:5000): 29.9352 wallclock secs

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