63249 - Bug counts is very slow for Products with many bugs

Status: VERIFIED FIXED

(Bugzilla :: Reporting/Charting, defect, P1)

Description

[TimMaroney]

Going to the page http://bugzilla.mozilla.org/reports.cgi and asking for 
either "Bug Counts" or "Bug Charts" does not work. "Bug Counts" just 
times out on the Mac, while on Windows it prints the page header but no 
content; "Bug Charts" on either platform shows just a  broken image icon.

Comment 1

[Andreas M. "Clarence" Schneider]

See bug 6682 for bug charts.

Comment 2

[Jesse Ruderman]

Actually, see bug 38631 for bug charts.

Comment 3

[Matthew Tuck [:CodeMachine]]

I don't think those bugs are related.  It's a bugzilla.mozilla.org specific
issue.  If you go to the image page of charts, you get:

Forbidden

You don't have permission to access
/data/mining/-All-_NEW:ASSIGNED:REOPENED:UNCONFIRMED:.gif on this server.

Apache/1.3.12 Server at bugzilla.mozilla.org Port 80

This has been like this for ages, I would have thought it was so obvious it
would have been fixed, but I guess not.

I'll assume the count problems are similar.

Reassigning.

Comment 4

[Gervase Markham [:gerv]]

Resolving because I know what caused this, and the new code will sort it.

Gerv

Comment 5

[TimMaroney]

This is not resolved. These functions still time out. Marking a bug as 
"resolved" because you "know what caused it" and because it will be fixed 
in the future is bizarre behavior. The Resolved state means it is resolved 
now, not that it can be or will be resolved.

Comment 6

[Matthew Tuck [:CodeMachine]]

Please check again.  It is not bizarre that this problem might be fixed yet
bugzilla.mozilla.org aren't running the latest Bugzilla code.  A bug is fixed
when it is fixed, regardless of whether you see it on b.m.o.

Comment 7

[TimMaroney]

The bug is on bugzilla.mozilla.org. It is not fixed. I'm not concerned with 
internal code versions that are not running on bugzilla.mozilla.org; I'm 
concerned with the inability to get bug counts or bug charts on bugs 
related to Mozilla.

Comment 8

[Matthew Tuck [:CodeMachine]]

OK I had a bit of an itchy trigger finger.  When you have to go through lots of
bugs sometimes these things happen.

I did not notice that this was currently assigned to endico, and hence is
already a bugzilla.mozilla.org specific bug (Dawn another reason why we need a
different component).

Normally bugs in the Bugzilla component get closed when they get fixed in
Bugzilla, and we don't have separate bugs for bugzilla.mozilla.org if they
aren't a mozilla.org specific issue, because they would be rather pointless.

Maybe we could have just one update bug for those who are interesting in knowing
when b.m.o updates to the latest version, for things they're waiting to be
fixed.

Comment 9

[Matthew Tuck [:CodeMachine]]

Oh, and I also assumed that at the time you commented, b.m.o was not up to
date.  It definitely is at the moment.

Comment 10

[TimMaroney]

OK, since you said the code has now been updated, I checked it again 
today. Since 7 March, the bug chart has begin to work, but bug counts time 
out.

Comment 11

[Gervase Markham [:gerv]]

Bug counts _do_ work. They are just extremely slow when you ask for -All-, or 
major Products like Browser. To see them working, try a small Product like 
Directory.

Morphing this bug into a performance issue.

Gerv

Comment 12

[TimMaroney]

No, they don't work. The browser times out before they complete.

Comment 13

[Matthew Tuck [:CodeMachine]]

Gerv, you're right.  Given the amount of output it generates for even a
component like mozilla.org, it's not suprising.  Even if there's nothing we can
do to make it faster we should make sure that it is as fully incremental as can
be, ie the first table can appear while the second is being made.

We had all sorts of tomfoolery here because two separate issues were filed on
one bug.  I won't blame the reporter because it was reasonable to assume they
were related, but we all need to be a little more careful.

The fact your browser times out in a browser issue, not a Bugzilla issue. 
There's nothing we can do about that other than to make it faster.

Comment 14

[Gervase Markham [:gerv]]

<sigh>Product "Directory" works for me in about 10 seconds using Navigator 4.x 
on Windows 95. Product Mail/News takes about 2 minutes, if you turn off bug 
links. Product Browser does time out. Product -All- would no doubt do so also.

As I have said, there is a performance problem here. That is why this bug is 
still open. We will get around to fixing it.

Why do you need these reports anyway? :-)

Gerv

Comment 15

[TimMaroney]

Blaming the browser for timing out fails to note that the performance 
problem on the back end does in fact prevent the front end from 
functioning. There's a point at which "too slow" shades into "doesn't work," 
and this behavior is past that point.

Comment 16

[Matthew Tuck [:CodeMachine]]

Regardless, just because it doesn't work in your browser, does not mean it
doesn't work in someone else's with a longer timeout.  And it's all a semantic
quibble that makes no difference to the fix anyway, so let's forget it.

Comment 17

[Gervase Markham [:gerv]]

Having looked at the code, there seems no way to speed this up in Perl. It's
just a very big and complex query. It's already running against the shadowdb. If
anything is going to happen here, we need a SQL guru to look at it and see where
the bottleneck is.

Gerv

Comment 18

[timeless]

fwiw there have been cases where we hit an apache timeout, but i don't think 
this bug is one of them.

Comment 19

[Matthew Tuck [:CodeMachine]]

Well, according to bug #50492 which I just found, which was originally really a
dupe, they think it is.

Comment 20

[John Bafford]

Well, taking a quick glance at the 2.12 code, it seems that the problem isn't 
that it's a big and complex query. It's a whole mess of O(n) queries.

You have queries of the form:

select distinct assigned_to from bugs where target_milestone='foo'
foreach $person in result set
	select count(bug_id) from bugs,profiles where target_milestone='foo' and 
userid=assigned_to and userid='$person'

Which besides being an unnecessary join (if userid=assigned_to, there's no need 
to bring in the users table unless you're extracting additional information from 
the table, which you're not) could be better accomplished in _one_ query:

select assigned_to,count(bug_id) from bugs group by assigned_to;

You also seem to have several instances where you do a select and then have perl 
sort the data. This is inefficient: you should always have the database sort the 
output data.

I would be willing to clean up the mess, however I do not have a system on which 
I can install bugzilla and all of the components it requires, let alone try it on 
a large dataset.

Comment 21

[Gervase Markham [:gerv]]

Well, there is a test system for Bugzilla developers, landfill.tequilarista.org, 
but I don't know what the procedure is for getting accounts on it. You would 
have to ask Tara (the bug assignee of this bug.)

Gerv

Comment 22

[Matthew Tuck [:CodeMachine]]

*** Bug 89678 has been marked as a duplicate of this bug. ***

Comment 23

[Dan Mosedale (:dmosedale, :dmose)]

As far as having perl sort the data, it may be relevant that the database engine
is MySQL, which uses table level locking rather than row-level locking.  So
anything that ties up the database engine for longer risks having locks held
longer and therefore lock contention.  Also possibily relevant is the fact that
the shadow database can't be synced until it is quiescent.

Comment 24

[John Bafford]

That's a valid argument, but in this case, I don't think it really applies. Read
locks in mysql will not block other reads from proceding. In any case, the sort
is the least of the problems; the big problem comes from the inefficient
queries.

Comment 25

[Matthew Tuck [:CodeMachine]]

That query stuff looks right - you just need to modify Perl to interpret the new
style query.

However the one thing I can think of is to ask why it's not being sorted by
"assigned to email address".  I would have expected the output to be, and if
that's the case we would still need to pull in the users table.

In either case, just having one query rather than a lot of them will likely
speed that up.

As for record/table level locking, having a read lock definitely does matter
because it can block writes, but if, as Gerv said, this is running against the
shadowdb then there'll never be any high priority writes.

Comment 26

[Jacob Steenhagen]

-> New Bugzilla Product

Comment 27

[Gervase Markham [:gerv]]

If anyone has SQL suggestions here, that would be great. I doubt I have the
SQL-fu to do much with this.

Perhaps we could stop people running Bug Counts on "All"? Given the diversity of
Products in Bugzilla, it's not really meaningful data anyway.

Gerv

Comment 28

[Matthew Tuck [:CodeMachine]]

Gerv I can take this if you like.

Comment 29

[Gervase Markham [:gerv]]

Sure.

Gerv

Comment 30

[Matthew Tuck [:CodeMachine]]

Attachment: Fix.

Comment 31

[Matthew Tuck [:CodeMachine]]

Wow.  I expected this to be hard, but it was so easy I was wondering what I was
missing.

The queries mentioned above are for the most doomed reports, while this fix is
for bug counts.

Those queries were efficient in comparison.  I haven't tested the perf of this
fix - it's hard to without a large data set.  However, unless I've missed
something, logic dictates a massive perf gain here.

The query was selecting columns that were never used, joined against the
profiles table twice (once for assignee and once for reporter) and only ever
used once (for assignee).  However, the real killer is assumedly the complete
(cross product) join against the versions table (which was unused).

Interestingly, these problems were present from day one.  Looking at the code, I
had expected years of hacking in band aids to have resulted in it, but no, it
was written that way.

Comment 32

[Gervase Markham [:gerv]]

Comment on attachment 54488 [details] [diff] [review]
Fix.

Looks sane to me, but I'm not an SQL expert. The second reviewer should be one. r=gerv.

Gerv

Comment 33

[Jacob Steenhagen]

Comment on attachment 54488 [details] [diff] [review]
Fix.

After figuring out that:
Bug Counts == most_doomed()
Most Doomed == most_doomed_for_milestone()

Then this look sane.  You don't really need to alias the profiles table being that it's
only in there once, but it doesn't hurt anything.

r=jake

Comment 34

[Jacob Steenhagen]

Checking in reports.cgi;
/cvsroot/mozilla/webtools/bugzilla/reports.cgi,v  <--  reports.cgi
new revision: 1.49; previous revision: 1.48
done

Comment 35

[Matthew Tuck [:CodeMachine]]

b.m.o should be updating to HEAD soon, here's hoping we'll see something nice
here.  I expect a 5000% speedup.

Comment 36

[Matthew Tuck [:CodeMachine]]

Tested after b.m.o upgrade, massive speedup, perf now acceptable!

Comment 37

[Matthew Tuck [:CodeMachine]]

*** Bug 106890 has been marked as a duplicate of this bug. ***