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

[19 Aug 2009 18:27] Alexey Stroganov
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
[19 Aug 2009 18:28] Alexey Stroganov
Test case for the issue

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

[20 Aug 2009 12:37] Nirbhay Choubey
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
[20 Sep 2009 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".