| Bug #13917 | 5.0 is slower than 4.1 on many queries | ||
|---|---|---|---|
| Submitted: | 11 Oct 2005 8:54 | Modified: | 9 Feb 2008 13:41 |
| Reporter: | Vadim Tkachenko | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.0.14-rc, 5.0.27 | OS: | Linux (Linux, RedHat AS 3) |
| Assigned to: | CPU Architecture: | Any | |
[11 Oct 2005 8:54]
Vadim Tkachenko
[16 Aug 2006 15:16]
Igor Babaev
I move this bug to 'Open' as it does not contain a single test case that can be easily verified.
[26 Sep 2006 5:52]
Valeriy Kravchuk
Vadim, How many rows should we have in sbtest table to see the real difference? Do we need to run concurrent threads or single thread is enough?
[6 Oct 2006 16:09]
Arnaud LE CORF
Hi, I have this test case that maybe can help you. MySQL version: 5.0.22 We use engine InnoDB After some hours (or day) with the database up, I launch the request (see below), which take more than 3 hours to be completed. This was the only query in the database at that time. Then I do "analayze table" on all tables. And the the request was really quicker (23 minutes) I can forward you other informations (the request itself, show table status;show innodb status;show variables;mysqldump, etc...) But I can't attach file to his bug. You can contact me at: alecorf@exaprotect.com the explain: +----+-------------+--------------------+--------+----------------------------------------------------------+----------+---------+-------------------------------------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+--------+----------------------------------------------------------+----------+---------+-------------------------------------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | file | index | PRIMARY | PRIMARY | 4 | NULL | 2053 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lnk_target_file | ref | alert_id,file_id | file_id | 4 | eas_EXABENCH3.file.file_id | 1052 | Using index | | 1 | SIMPLE | lnk_target_userid | ref | alert_id,userid_id | alert_id | 8 | eas_EXABENCH3.lnk_target_file.alert_id | 1 | Using where | | 1 | SIMPLE | target_userid | eq_ref | PRIMARY | PRIMARY | 4 | eas_EXABENCH3.lnk_target_userid.userid_id | 1 | Using index | | 1 | SIMPLE | alert | eq_ref | PRIMARY,create_date,classification_id,kind,create_date_2 | PRIMARY | 8 | eas_EXABENCH3.lnk_target_file.alert_id | 1 | Using where | | 1 | SIMPLE | lnk_source_userid | ref | alert_id,userid_id | alert_id | 8 | eas_EXABENCH3.alert.alert_id | 1 | Using where | | 1 | SIMPLE | source_userid | eq_ref | PRIMARY | PRIMARY | 4 | eas_EXABENCH3.lnk_source_userid.userid_id | 1 | Using index | | 1 | SIMPLE | classification | eq_ref | PRIMARY,name | PRIMARY | 4 | eas_EXABENCH3.alert.classification_id | 1 | Using where | | 1 | SIMPLE | lnk_alert_analyzer | ref | PRIMARY,analyzer_id | PRIMARY | 8 | eas_EXABENCH3.lnk_target_userid.alert_id | 1 | Using where | | 1 | SIMPLE | analyzer | eq_ref | PRIMARY,analyzerid,evt_col_id | PRIMARY | 4 | eas_EXABENCH3.lnk_alert_analyzer.analyzer_id | 1 | Using where | | 1 | SIMPLE | event_collector | eq_ref | PRIMARY,domain_id | PRIMARY | 4 | eas_EXABENCH3.analyzer.evt_col_id | 1 | Using where | +----+-------------+--------------------+--------+----------------------------------------------------------+----------+---------+-------------------------------------------------+------+-----------------------------------------------------------+ I launch the request, it tooks more 11262 seconds (3 hours 7 min 42 sec) Then I do "analyze table <table>" on all table (I use a script for that) Then the explain (quite different) +----+-------------+--------------------+--------+--------------------------------------------+-------------+---------+-------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+--------+--------------------------------------------+-------------+---------+-------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | analyzer | ALL | PRIMARY,analyzerid,evt_col_id | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | event_collector | eq_ref | PRIMARY,domain_id | PRIMARY | 4 | eas_EXABENCH3.analyzer.evt_col_id | 1 | Using where | | 1 | SIMPLE | lnk_alert_analyzer | ref | PRIMARY,analyzer_id | analyzer_id | 4 | eas_EXABENCH3.analyzer.analyzer_id | 2240 | Using index | | 1 | SIMPLE | lnk_source_userid | ref | alert_id,userid_id | alert_id | 8 | eas_EXABENCH3.lnk_alert_analyzer.alert_id | 1 | Using where | | 1 | SIMPLE | source_userid | eq_ref | PRIMARY | PRIMARY | 4 | eas_EXABENCH3.lnk_source_userid.userid_id | 1 | Using index | | 1 | SIMPLE | lnk_target_file | ref | alert_id,file_id | alert_id | 8 | eas_EXABENCH3.lnk_alert_analyzer.alert_id | 2 | Using where | | 1 | SIMPLE | file | eq_ref | PRIMARY | PRIMARY | 4 | eas_EXABENCH3.lnk_target_file.file_id | 1 | Using index | | 1 | SIMPLE | lnk_target_userid | ref | alert_id,userid_id | alert_id | 8 | eas_EXABENCH3.lnk_target_file.alert_id | 1 | Using where | | 1 | SIMPLE | target_userid | eq_ref | PRIMARY | PRIMARY | 4 | eas_EXABENCH3.lnk_target_userid.userid_id | 1 | Using index | | 1 | SIMPLE | alert | eq_ref | PRIMARY,create_date,classification_id,kind | PRIMARY | 8 | eas_EXABENCH3.lnk_target_file.alert_id | 1 | Using where | | 1 | SIMPLE | classification | eq_ref | PRIMARY,name | PRIMARY | 4 | eas_EXABENCH3.alert.classification_id | 1 | Using where | +----+-------------+--------------------+--------+--------------------------------------------+-------------+---------+-------------------------------------------+------+----------------------------------------------+ I launch the request after that, and it's faster: 15000 rows in set (22 min 57.17 sec)
[21 Nov 2006 16:46]
Valeriy Kravchuk
Arnaud, You results are expected if you deleted many rows (or changed table substantially). You have to perform ANALYZE TABLE after big changes in the table to get optimal plans from the optimizer.
[18 Aug 2007 1:41]
Igor Babaev
- There is no mangeable test case for this problem. By the above reason I move the bug entry to 'Open'
[8 Dec 2007 19:58]
Valeriy Kravchuk
Vadim, Can you give examples of queries that has different execution plans in 5.0.x vs. 4.1.x and runs slower becase of that? Arnaud, Please, upload your test case to ouse FTP server: ftp://ftp.mysql.com/pub/mysql/upload/ (with 13917 in a file name, if possilbe), and send a comment with file name when done.
[9 Jan 2008 0: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".
[10 Feb 2008 0: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".
