Closed Bug 63249 Opened 22 years ago Closed 22 years ago
Bug counts is very slow for Products with many bugs
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
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.
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
*** 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.
Assignee: gerv → matty
Status: NEW → ASSIGNED
QA Contact: matty → jake
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 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 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
Status: ASSIGNED → RESOLVED
Closed: 22 years ago → 22 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. ***
You need to log in before you can comment on or make changes to this bug.