Closed Bug 63249 Opened 22 years ago Closed 22 years ago

Bug counts is very slow for Products with many bugs

Categories

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

All
Other
defect

Tracking

()

VERIFIED FIXED
Bugzilla 2.16

People

(Reporter: tim, Assigned: CodeMachine)

References

()

Details

(Keywords: perf, Whiteboard: SQL Perf)

Attachments

(1 file)

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.
See bug 6682 for bug charts.
Actually, see bug 38631 for bug charts.
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.
Assignee: tara → endico
Resolving because I know what caused this, and the new code will sort it.

Gerv
Status: NEW → RESOLVED
Closed: 22 years ago
Resolution: --- → WORKSFORME
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.
Status: RESOLVED → REOPENED
Resolution: WORKSFORME → ---
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.
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.
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.
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.
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.
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
Assignee: endico → tara
Severity: major → normal
Status: REOPENED → NEW
Keywords: perf
Summary: neither bug counts or bug charts works → Bug counts is very slow for Products with many bugs
Target Milestone: --- → Bugzilla 2.16
No, they don't work. The browser times out before they complete.
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.
<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
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.
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.
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
fwiw there have been cases where we hit an apache timeout, but i don't think 
this bug is one of them.
Whiteboard: SQL Perf
Well, according to bug #50492 which I just found, which was originally really a
dupe, they think it is.
Priority: -- → P2
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.
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
Priority: P2 → P1
*** Bug 89678 has been marked as a duplicate of this bug. ***
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.
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.
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.
Assignee: tara → gerv
Component: Bugzilla → Reporting/Charting
Product: Webtools → Bugzilla
Version: other → unspecified
-> New Bugzilla Product
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
Gerv I can take this if you like.
Sure.

Gerv
Assignee: gerv → matty
Status: NEW → ASSIGNED
QA Contact: matty → jake
Attached patch Fix.Splinter Review
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.
Keywords: patch, review
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
Attachment #54488 - Flags: review+
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
Attachment #54488 - Flags: review+
Status: ASSIGNED → RESOLVED
Closed: 22 years ago22 years ago
Resolution: --- → FIXED
Checking in reports.cgi;
/cvsroot/mozilla/webtools/bugzilla/reports.cgi,v  <--  reports.cgi
new revision: 1.49; previous revision: 1.48
done
b.m.o should be updating to HEAD soon, here's hoping we'll see something nice
here.  I expect a 5000% speedup.
Tested after b.m.o upgrade, massive speedup, perf now acceptable!
Status: RESOLVED → VERIFIED
*** Bug 106890 has been marked as a duplicate of this bug. ***
QA Contact: jake → default-qa
You need to log in before you can comment on or make changes to this bug.