Closed
Bug 286625
Opened 19 years ago
Closed 15 years ago
./collectstats.pl --regenerate is enormously slow
Categories
(Bugzilla :: Query/Bug List, defect)
Tracking
()
RESOLVED
FIXED
Bugzilla 3.4
People
(Reporter: shane.h.w.travis, Assigned: mkanat)
Details
(Keywords: perf)
Attachments
(1 file, 5 obsolete files)
7.18 KB,
patch
|
LpSolit
:
review+
|
Details | Diff | Splinter Review |
Doing most anything with the bugs_activity table is interminably slow, because the existing indices don't cover the added/removed fields, which are what really need help the most. For example: running 'collectstats.pl --regenerate' on my local database with these indices took under 5 hours; before putting the indices on, I let it run for 35 hours before I stopped it, and it did not yet look to be more than halfway done. Patch to follow.
Reporter | ||
Comment 1•19 years ago
|
||
Adds indices to existing and new tables; tip version
Attachment #177773 -
Flags: review?
Reporter | ||
Comment 2•19 years ago
|
||
Adds indices to existing and new tables; 2.18 version
Attachment #177774 -
Flags: review?
Reporter | ||
Updated•19 years ago
|
Status: NEW → ASSIGNED
Flags: blocking2.20?
Flags: blocking2.18.1?
Target Milestone: --- → Bugzilla 2.18
Updated•19 years ago
|
Attachment #177773 -
Flags: review? → review?(bugreport)
Updated•19 years ago
|
Attachment #177774 -
Flags: review? → review?(bugreport)
Comment 3•19 years ago
|
||
Comment on attachment 177773 [details] [diff] [review] Code patch for tip r=joel by inspection
Attachment #177773 -
Flags: review?(bugreport) → review+
Comment 4•19 years ago
|
||
Comment on attachment 177774 [details] [diff] [review] Code patch for 2.18 r=joel by inspection
Attachment #177774 -
Flags: review?(bugreport) → review+
Reporter | ||
Updated•19 years ago
|
Flags: approval?
Flags: approval2.18?
Updated•19 years ago
|
Flags: blocking2.20?
Flags: blocking2.20+
Flags: blocking2.18.1?
Flags: blocking2.18.1+
Flags: approval?
Flags: approval2.18?
Flags: approval2.18+
Flags: approval+
Comment 5•19 years ago
|
||
travis, assuming you are on vacation this week, I'm commiting this patch myself. ;) Tip: Checking in checksetup.pl; /cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v <-- checksetup.pl new revision: 1.376; previous revision: 1.375 done Checking in Bugzilla/DB/Schema.pm; /cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema.pm,v <-- Schema.pm new revision: 1.15; previous revision: 1.14 done 2.18 branch: Checking in checksetup.pl; /cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v <-- checksetup.pl new revision: 1.289.2.29; previous revision: 1.289.2.28 done
Status: ASSIGNED → RESOLVED
Closed: 19 years ago
Resolution: --- → FIXED
Comment 6•19 years ago
|
||
Comment 7•19 years ago
|
||
DBD::mysql::db do failed: Duplicate key name 'bugs_activity_idfield' at ./checksetup.pl line 3873
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Comment 8•19 years ago
|
||
Comment 9•19 years ago
|
||
Both patche have been backed out. checksetup.pl fails with the following error messages: Tip: Adding more indexes for bugs_activity table. DBD::mysql::db do failed: Duplicate key name 'bugs_activity_idfield' at ./checksetup.pl line 3873 2.18: Adding more indexes for bugs_activity tables. DBD::mysql::db do failed: Duplicate key name 'bugs_activity_idfield' at ./checksetup.pl line 4531. DBD::mysql::db do failed: Duplicate key name 'bugs_activity_added' at ./checksetup.pl line 4532. DBD::mysql::db do failed: Duplicate key name 'bugs_activity_removed' at ./checksetup.pl line 4533. DBD::mysql::db do failed: Duplicate key name 'bugs_activity_added_removed' at ./checksetup.pl line 4534. Moreover, I observe a index name inconsistency: +$dbh->do("ALTER TABLE bugs_activity ADD INDEX bugs_activity_idfield (bug_id, fieldid)"); + bugs_activity_bug_field_idx => [qw(bug_id fieldid)],
Comment 10•19 years ago
|
||
Comment on attachment 177773 [details] [diff] [review] Code patch for tip per my previous comment
Attachment #177773 -
Flags: review-
Updated•19 years ago
|
Attachment #177774 -
Flags: review-
Updated•19 years ago
|
Flags: approval2.18+
Flags: approval+
Assignee | ||
Comment 11•19 years ago
|
||
Comment on attachment 177773 [details] [diff] [review] Code patch for tip Also, could you explain why you're adding three more fulltext indexes? Fulltext indexes will cause us problems in the future, because we can't have transactional (InnoDB) tables with fulltext indexes in them. I don't fully understand why we would be *searching* on added and removed, but I'm sure there's some good reason?
Attachment #177773 -
Flags: review-
Comment 12•19 years ago
|
||
I can see why you would want an index on (fieldid, added) and (fieldid, removed), but I cannot see why those would need to be fulltext. Please try some benchmarks using (fieldid, added) and (fieldid, removed) or, possibly, (fieldid, added, bugid)(fieldid, removed, bugid) or (fieldid, leftmost 10 chars of added), etc....
Assignee | ||
Comment 13•19 years ago
|
||
You know, looking over the general Bugzilla code, and the regenerate option of collectstats, it looks like we just need the following multi-column index to handle the regenerate stuff: bugs_activity(bug_id, bug_when, fieldid) We could then also drop the bug_id index, or just replace it with that one. An examination of Bugzilla code shows me that we should get performance gains elsewhere in Bugzilla with that multi-column index. I don't think that anything else is actually necessary for 2.18.1, at the least. Also, the indexes in the patch on this bug don't include _idx on the end of their name, which they should.
Flags: blocking2.18.2?
Updated•19 years ago
|
Flags: blocking2.18.2?
Flags: blocking2.18.2+
Flags: blocking2.18.1-
Flags: blocking2.18.1+
Assignee | ||
Comment 14•19 years ago
|
||
Oh, actually, actual experimentation with EXPLAIN shows that the index created in this bug is actually correct, not the index that I just stated. So the idea index is: bugs_activity(bug_id, fieldid) And we can eliminate the bug_id index, because we can always use that one instead. But that's the only index needed to fix the collectstats problem.
Comment 15•19 years ago
|
||
"If it's not a regression from 2.18 and it's not a critical problem with something that's already landed, let's push it off." - Dave
Flags: blocking2.20+
Flags: blocking2.18.2+
Updated•19 years ago
|
Whiteboard: [wanted for 2.20]
Updated•19 years ago
|
Flags: blocking2.20-
Comment 16•19 years ago
|
||
Not a security bug, retargetting to 2.20.
Target Milestone: Bugzilla 2.18 → Bugzilla 2.20
Comment 17•19 years ago
|
||
travis has gone, and nothing is broken here, just slow => retargetting
Assignee: shane.h.w.travis → query-and-buglist
Severity: normal → minor
Status: REOPENED → NEW
Whiteboard: [wanted for 2.20]
Target Milestone: Bugzilla 2.20 → Bugzilla 2.24
Updated•18 years ago
|
Severity: minor → enhancement
Comment 18•18 years ago
|
||
We are freezing the code for 3.0 in two weeks and we don't expect this bug to be fixed on time.
Target Milestone: Bugzilla 3.0 → ---
Assignee | ||
Updated•15 years ago
|
Severity: enhancement → minor
Summary: Add more indexes to bugs_activity table → ./collectstats.pl --regenerate is enormously slow
Assignee | ||
Comment 19•15 years ago
|
||
This is a work in progress, including some debugging code. As usual, what is ACTUALLY slow is not what you might think.
Assignee: query-and-buglist → mkanat
Attachment #177773 -
Attachment is obsolete: true
Attachment #177774 -
Attachment is obsolete: true
Attachment #178297 -
Attachment is obsolete: true
Attachment #178299 -
Attachment is obsolete: true
Status: NEW → ASSIGNED
Assignee | ||
Comment 20•15 years ago
|
||
This makes --regenerate take minutes instead of hours or days.
Attachment #395272 -
Attachment is obsolete: true
Attachment #395288 -
Flags: review?(LpSolit)
Assignee | ||
Comment 21•15 years ago
|
||
This patch also fixes a problem where --regenerate wasn't correctly counting the empty resolution. (I believe normal collectstats.pl also has this bug, and we can address it separately somewhere.)
Target Milestone: --- → Bugzilla 3.6
Comment 22•15 years ago
|
||
Comment on attachment 395288 [details] [diff] [review] v1 Nice catch about the empty resolution. Old data is indeed wrong as the number of closed bugs doesn't match the number of bugs having a resolution. Also, the perf improvement is huge. IMO, both are valid reasons to take this patch for 3.4. r=LpSolit
Attachment #395288 -
Flags: review?(LpSolit) → review+
Comment 23•15 years ago
|
||
Let's take it on the 3.4 branch as it fixes a bug. And the huge perf problem can also be seen as a bug.
Updated•15 years ago
|
Severity: minor → normal
Assignee | ||
Comment 24•15 years ago
|
||
tip: Checking in collectstats.pl; /cvsroot/mozilla/webtools/bugzilla/collectstats.pl,v <-- collectstats.pl new revision: 1.70; previous revision: 1.69 done 3.4: Checking in collectstats.pl; /cvsroot/mozilla/webtools/bugzilla/collectstats.pl,v <-- collectstats.pl new revision: 1.68.2.2; previous revision: 1.68.2.1 done
Status: ASSIGNED → RESOLVED
Closed: 19 years ago → 15 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•