Closed Bug 151817 Opened 22 years ago Closed 17 years ago

Sort order affects bug query performance

Categories

(Bugzilla :: Query/Bug List, enhancement, P2)

2.17
enhancement

Tracking

()

RESOLVED WORKSFORME

People

(Reporter: teilo+bugzilla, Assigned: nobody)

References

Details

(Keywords: perf)

Attachments

(2 files)

Mozilla 1.0 does not complete a search on bugzilla.mozilla.org but hangs at 
the please standby page

The search completes in a few seconds <3 in IE6 on the same computer (windows 
XP)

(all bugs with a CC or comment added by anyone with an email address 
containing @teilo.net)

the specific search is 
http://bugzilla.mozilla.org/buglist.cgi?
bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&b
ug_status=RESOLVED&bug_status=VERIFIED&bug_status=CLOSED&email1=%
40teilo.net&emailtype1=substring&emaillongdesc1=1&email2=&emailtype2=substring&
emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&bugidtype=include&bug_i
d=&changedin=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&short_desc=&short
_desc_type=substring&long_desc=&long_desc_type=substring&bug_file_loc=&bug_file
_loc_type=substring&status_whiteboard=&status_whiteboard_type=substring&keyword
s=&keywords_type=anywords&field0-0-0=noop&type0-0-0=noop&value0-0-
0=&cmdtype=doit&order=Reuse+same+sort+as+last+time
If you put "debug=1" in the list of URL parameters, what does the resulting
debug output look like for IE?
output is,

SELECT DISTINCT bugs.bug_id, bugs.groupset, bugs.bug_severity, bugs.priority,
bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution,
bugs.short_desc FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT
JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid,
longdescs longdescs_, profiles longdescnames_ LEFT JOIN cc selectVisible_cc ON
bugs.bug_id = selectVisible_cc.bug_id AND selectVisible_cc.who = 17149 WHERE
((bugs.groupset & 0) = bugs.groupset OR (bugs.reporter_accessible = 1 AND
bugs.reporter = 17149) OR (bugs.cclist_accessible = 1 AND selectVisible_cc.who =
17149 AND not isnull(selectVisible_cc.who)) OR (bugs.assigned_to = 17149) OR
(bugs.qa_contact = 17149)) AND bugs.assigned_to = map_assigned_to.userid AND
bugs.reporter = map_reporter.userid AND longdescs_.bug_id = bugs.bug_id AND
longdescs_.who = longdescnames_.userid AND (bugs.bug_status = 'UNCONFIRMED' OR
bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR bugs.bug_status =
'REOPENED' OR bugs.bug_status = 'RESOLVED' OR bugs.bug_status = 'VERIFIED' OR
bugs.bug_status = 'CLOSED') AND (INSTR(LOWER(longdescnames_.login_name),
'@teilo.net')) ORDER BY bugs.bug_status, bugs.priority,
map_assigned_to.login_name, bugs.bug_id 
if I leave mozilla "pondering" after a long time it now reports the following error,

Software error:
SELECT DISTINCT bugs.bug_id, bugs.groupset, bugs.bug_severity, bugs.priority,
bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution,
bugs.short_desc FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT
JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid,
longdescs longdescs_, profiles longdescnames_ WHERE ((bugs.groupset & 0) =
bugs.groupset ) AND bugs.assigned_to = map_assigned_to.userid AND bugs.reporter
= map_reporter.userid AND longdescs_.bug_id = bugs.bug_id AND longdescs_.who =
longdescnames_.userid AND (bugs.bug_status = 'UNCONFIRMED' OR bugs.bug_status =
'NEW' OR bugs.bug_status = 'ASSIGNED' OR bugs.bug_status = 'REOPENED' OR
bugs.bug_status = 'RESOLVED' OR bugs.bug_status = 'VERIFIED' OR bugs.bug_status
= 'CLOSED') AND (INSTR(LOWER(longdescnames_.login_name), '@teilo.net')) ORDER BY
bugs.priority, bugs.bug_severity : Server shutdown in progress at globals.pl
line 276.

also should this block bug 150783
Blocks: 150783
For what it's worth, this is reproducable with Moz 1.1 Alpha. Same error message
": Server shutdown in progress at globals.pl line 276." IE 6.0 works fine.
Netscape 4.79 does not have a problem with it either. 

Could this possibly be browser-dependant with that error message?

I tried mine on Win2K
Can someone with Linux Mozilla 1.0 / 1.1 try this?
I upgraded to moz1.1 alpha. before I started getting the error message.  I don't
recall getting the "Server shutdown in progress at globals.pl
line 276." error with 1.0 (just hanging) but I may be mistaken.
Attached file query explains
The problem is related to sort option, not browser.  Sorting the query by
"Assignee" (assignee, status, priority, id) or by Bugzilla's default sort
option (status, priority, assignee, id) works; sorting by "Importance"
(priority, severity) doesn't.

This attachment contains the output of explain for queries with each of those
sort options enabled.  Perhaps something changed between 2.14 and 2.16 that
causes the wrong type of sort option to be chosen in some situations where the
correct one used to be chosen before?
Look at the rows column. bmo thinks that there should only be about one row
returned, in which case sorting via a temp file would be ok.

How many rows are returned?
OK, this is, um 'odd'. From irc:

http://bugzilla.mozilla.org/buglist.cgi?debug=1&order=map_assigned_to.login_name,bugs.bug_id&email1=jmd%40pobox.com&emailtype1=substring&emaillongdesc1=1

works, while:

http://bugzilla.mozilla.org/buglist.cgi?debug=1&order=bugs.bug_id&email1=jmd%40pobox.com&emailtype1=substring&emaillongdesc1=1

fails.

If you make the substring an exact match,they both work. Is the explain different?

The only difference is that the working one references more than one table in
the ORDER clause.

Umm, help? Corrupted indexes? Phase of the moon? mysql bug?
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id, bugs.groupset, bugs.bug_severity,
bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status,
bugs.resolution, bugs.product, bugs.component, bugs.op_sys,
bugs.target_milestone, bugs.short_desc FROM bugs, profiles map_assigned_to,
profiles map_reporter LEFT JOIN profiles map_qa_contact ON bugs.qa_contact =
map_qa_contact.userid, longdescs longdescs_, profiles longdescnames_ WHERE
((bugs.groupset & 0) = bugs.groupset ) AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND
longdescs_.bug_id = bugs.bug_id AND longdescs_.who = longdescnames_.userid AND
(INSTR(LOWER(longdescnames_.login_name), 'jmd@pobox.com')) ORDER BY
map_assigned_to.login_name,bugs.bug_id;
+-----------------+--------+------------------------------+---------+---------+-----------------------+-------+---------------------------------------------+
| table           | type   | possible_keys                | key     | key_len |
ref                   | rows  | Extra                                       |
+-----------------+--------+------------------------------+---------+---------+-----------------------+-------+---------------------------------------------+
| longdescnames_  | ALL    | PRIMARY                      | NULL    |    NULL |
NULL                  | 58286 | where used; Using temporary; Using filesort |
| longdescs_      | ref    | bug_id,who                   | who     |       3 |
longdescnames_.userid |    40 |                                             |
| bugs            | eq_ref | PRIMARY,assigned_to,reporter | PRIMARY |       3 |
longdescs_.bug_id     |     1 | where used                                  |
| map_assigned_to | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.assigned_to      |     1 |                                             |
| map_reporter    | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.reporter         |     1 | Using index; Distinct                       |
| map_qa_contact  | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.qa_contact       |     1 | Using index; Distinct                       |
+-----------------+--------+------------------------------+---------+---------+-----------------------+-------+---------------------------------------------+
6 rows in set (0.00 sec)

mysql> EXPLAIN SELECT DISTINCT bugs.bug_id, bugs.groupset, bugs.bug_severity,
bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status,
bugs.resolution, bugs.product, bugs.component, bugs.op_sys,
bugs.target_milestone, bugs.short_desc FROM bugs, profiles map_assigned_to,
profiles map_reporter LEFT JOIN profiles map_qa_contact ON bugs.qa_contact =
map_qa_contact.userid, longdescs longdescs_, profiles longdescnames_ WHERE
((bugs.groupset & 0) = bugs.groupset ) AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND
longdescs_.bug_id = bugs.bug_id AND longdescs_.who = longdescnames_.userid AND
(INSTR(LOWER(longdescnames_.login_name), 'jmd@pobox.com')) ORDER BY bugs.bug_id;
+-----------------+--------+------------------------------+---------+---------+-------------------+---------+---------------------------------+
| table           | type   | possible_keys                | key     | key_len |
ref               | rows    | Extra                           |
+-----------------+--------+------------------------------+---------+---------+-------------------+---------+---------------------------------+
| longdescs_      | ALL    | bug_id,who                   | NULL    |    NULL |
NULL              | 1415978 | Using temporary; Using filesort |
| bugs            | eq_ref | PRIMARY,assigned_to,reporter | PRIMARY |       3 |
longdescs_.bug_id |       1 | where used                      |
| map_reporter    | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.reporter     |       1 | Using index                     |
| map_qa_contact  | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.qa_contact   |       1 | Using index                     |
| map_assigned_to | eq_ref | PRIMARY                      | PRIMARY |       3 |
bugs.assigned_to  |       1 |                                 |
| longdescnames_  | eq_ref | PRIMARY                      | PRIMARY |       3 |
longdescs_.who    |       1 | where used; Distinct            |
+-----------------+--------+------------------------------+---------+---------+-------------------+---------+---------------------------------+
6 rows in set (0.04 sec)
Err, um. So it uses a differnet key, so that the index can be used for sorting
(I guess). Ug.
Hmm. Maybe we should file this as a mysql bug? myk, how long do all of those
queries take?

IF you do an ANALYZE, does that speed the result up at all?
Keywords: perf
Summary: Search never completes in mozilla 1.0 but ok in IE 6 → Sort order affects bug query performance
OPTIMIZE, which I'm doing per bugu 155451, does ANALYZE.  Making this bug
dependent on that one.
Depends on: 155451
Attached file post-optimize explains
Well, the good news is that after optimizing the tables the queries now take
the same amount of time to run.  The bad news is that now they both time out. 
It looks like the query optimizer thinks it should look at every bug in the
bugs table first instead of finding matching profile records.

Now that the queries take the same amount of time to run, I guess I can resolve
this bug as fixed ;-), but instead I'll leave it open for the optimizer
problem.
Sorry, my comment yesterday was wrong.  The results I got were the result of
index corruption.  After the corruption was fixed I got the same results I got
before: for the working query, the optimizer searches starts with the
longdescnames (profiles) table, while for the failing query it starts with the
longdescs table.

Clearly longdescnames is the right table to start with, since it's the only
table that significantly limits the result set.  This could be an optimizer
problem, but let's wait and see what happens after I run "ANALYZE TABLE" on the
relevant tables sometime in the next few days.

In any case, I don't think this is actually a regression, so removing from the
regressions list.
No longer blocks: 150783
Priority: -- → P1
Target Milestone: --- → Bugzilla 2.18
Assignee: endico → nobody
These bugs appear to be abandoned.  Retargeting to 2.20
Target Milestone: Bugzilla 2.18 → Bugzilla 2.20
I'll take a look at this. I did some re-working of the sort-order stuff already.
Assignee: nobody → mkanat
Priority: P1 → P2
Assignee: mkanat → nobody
Target Milestone: Bugzilla 2.20 → Bugzilla 2.22
*** Bug 156628 has been marked as a duplicate of this bug. ***
Severity: major → enhancement
OS: Windows XP → All
Hardware: PC → All
QA Contact: mattyt-bugzilla → default-qa
Target Milestone: Bugzilla 2.22 → ---
The original search listed now completes on bugzilla.mozilla.org. It's still not very fast, but I've looked at the EXPLAIN output on MySQL 4.1 and there's not much we can do about it.

For what it's worth, this particular query returns almost *instantly* on PostgreSQL, so this is one example of a time when Pg actually performs better than MySQL.
Status: NEW → RESOLVED
Closed: 17 years ago
Resolution: --- → WORKSFORME
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: