425618 - Canned activity searches are slow

Status: RESOLVED INVALID

(bugzilla.mozilla.org :: General, defect)

Description

[Bradley Baetz (:bbaetz)]

this is bmo specific because this feature is bmo specific, but that code just copied from Bug.pm. I also need some timings from justdave, so.... Any fix is likely to be upstream, or at least should be applied to the duplicate code

This query is slow - 8 second from 2008-03-01 to first output, and 33 seconds from 2008-01-01 (time wget, ctrl-C after some output). Its 90 seconds w/o a date limit.

Using me as the user its 2 seconds for this month (2 bugs changed this month), so that the baseline.

Dave, can you please do an explain and get me the query times for:

SELECT count(bugs_activity.bug_id) FROM bugs_activity
  LEFT JOIN attachments ON attachments.attach_id = bugs_activity.attach_id
  LEFT JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'

(Run that twice in a row, just to prime up the caches for the first time)

SELECT count(bugs_activity.bug_id) FROM bugs_activity
  LEFT JOIN attachments ON attachments.attach_id = bugs_activity.attach_id
 INNER JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'
       AND COALESCE(attachments.isprivate, 0) = 0

SELECT count(bugs_activity.bug_id) FROM bugs_activity
  LEFT JOIN attachments ON attachments.attach_id = bugs_activity.attach_id
 INNER JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'

SELECT count(bugs_activity.bug_id) FROM bugs_activity
 INNER JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'

SELECT count(bugs_activity.bug_id) FROM bugs_activity
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'

Comment 1

[Bradley Baetz (:bbaetz)]

Err, sorry. Also:

SELECT count(bugs_activity.bug_id) FROM bugs_activity
  LEFT JOIN attachments ON attachments.attach_id = bugs_activity.attach_id
  LEFT JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND
bugs_activity.bug_when <= '2008-03-27 23:59:59'
       AND COALESCE(attachments.isprivate, 0) = 0

which is the actual original query

Comment 2

[Bradley Baetz (:bbaetz)]

The big this is the left join for fielddefs - this can be an inner join, and dave has confirmed that there aren't any non matching entries, which is a good sign that there aren't any historical bits. Anyone have an idea why its a left join? Custom fields only delete from fielddefs if its never been used, so thats not it. If this is changed, corresponding FK should be added.

Comment 3

[Max Kanat-Alexander]

Yeah, bbaetz is right about the INNER JOIN. Also, possibly adding a multi-column index on who,bug_when would help. (That wouldn't be needed upstream.)

Comment 4

[Bradley Baetz (:bbaetz)]

The multicolumn index may help for frequent contributers, I guess. Lets see what the explains show.

Bug 425647 for adding FKs

Comment 5

[Bradley Baetz (:bbaetz)]

and one more:

SELECT count(bugs_activity.bug_id) FROM bugs_activity
 INNER JOIN fielddefs ON bugs_activity.fieldid = fielddefs.id
 INNER JOIN profiles ON profiles.userid = bugs_activity.who
  LEFT JOIN attachments ON attachments.attach_id = bugs_activity.attach_id
     WHERE bugs_activity.who = '[e-mail]'
       AND bugs_activity.bug_when >= '2008-03-01 00:00:00' AND bugs_activity.bug_when <= '2008-03-27 23:59:59'
       AND COALESCE(attachments.isprivate, 0) = 0

Comment 6

[Bradley Baetz (:bbaetz)]

FWIW, the left join and colaesce dates back to terry trying to manually reconstruct data after a botched schema update just over 8 years ago. See bug 425647 comment 1.

Comment 7

[Max Kanat-Alexander]

Well, the attachments left join makes sense--bugs_activity.attach_id is frequently NULL.

Comment 8

[Bradley Baetz (:bbaetz)]

Yeah, but I'm trying to work out where the slow part is - it may be that it just has to be slow.

Comment 9

[Dave Miller [:justdave]]

Attachment: SQL + explain output

Here's your output from running the requested queries on mrdb06 (the production slave DB).  Each query was run twice, then run with EXPLAIN in front.

Comment 10

[Bradley Baetz (:bbaetz)]

Bah, its getting lost in cache effects, I think. Hmm.

Dave, once more, without the bugs_activity.bug_when constraints?

Comment 11

[Dave Miller [:justdave]]

Attachment: SQL + explain output 2

again as requested

Comment 12

[Bradley Baetz (:bbaetz)]

Sigh. Its cache effects again  8 seconds for the first, nothing for the rest

But even 8 seconds is < 60

Oh. Looking deeper, after the query it calls can_see_bug on each bug. Thats where the time is going, I suspect...

So this is a bmo specific issue. The Search.pm code should be reused, and if you really want to know how many hidden changes there are put the constraint into the SELECT bit rather than the WHERE clause, and just sum up as you go (possibly even in the template)

Comment 13

[Max Kanat-Alexander]

The hidden changes isn't all that important, so we could dump that.

We could also have a can_see_bugs subroutine instead, which might be simpler than trying to re-use the Search.pm API (which currently requires a CGI object).

Comment 14

[Bradley Baetz (:bbaetz)]

Not necessarily reuse the whole code, but at least split that bit off somehow to avoid duplication of the security stuff.

It shouldn't require a CGI object, just something that you can call ->params on - it was designed that way for email-based queries and the like, as well as the xmlrpc stuff. But yeah, I'm not suggesting that for this. Although being able to search for all changes made by a user on a bug in certain products would be cute ;)

Comment 15

[Dave Miller [:justdave]]

Rearranging this component to not have me as the default assignee, so that it doesn't appear like I'm intending to work on bugs that other people could be taking care of if they didn't think I was already doing them.  If this bug is a software issue on b.m.o and you'd like to fix it, the modified source is now available for the patching (see bug 373688).  Reassigning to the new default owner.

Comment 16

[:glob ✱]

this customisation no longer exists.