Closed Bug 442882 Opened 14 years ago Closed 14 years ago

Populating bugs_fulltext can be very slow on large installations

Categories

(Bugzilla :: Installation & Upgrading, defect, P1)

3.1.4
defect

Tracking

()

RESOLVED FIXED
Bugzilla 3.2

People

(Reporter: mkanat, Assigned: mkanat)

References

Details

Attachments

(1 file, 1 obsolete file)

reed reported that on the staging site for upgrading bugzilla.mozilla.org, the bugs_fulltext populating code was running for hours and hours and was only 14% done. I think there's something wrong with their staging server for it to be *that* slow, but we could special-case MySQL and use GROUP_CONCAT to do this all server-side.
Attached patch Hack to speed up things on MySQL (obsolete) — Splinter Review
This is somewhat of a hack, but it works.
Assignee: installation → mkanat
Status: NEW → ASSIGNED
We encountered an issue today with this patch on our specific installation. When migrating a 2.18 database to 3.2 using checksetup.pl the patch fails because when it runs, the longdescs.comment_id columns does not yet exist. If I add the column manually and then run then it works better.
Okay. That could be fixed. Does this get you a noticeable performance improvement?
yes, about 1/10 of the time required by the old method :)
(In reply to comment #4)
> yes, about 1/10 of the time required by the old method :)
> 

I mean just for the fulltext conversion part. The rest still takes a while (dropping/adding indexes, email prefs, moving attachment data, etc.)

Okay! That's enough of a benefit for me!

We can create a sql_group_concat in specific DB drivers that can support it, and then call $dbh->can('sql_group_concat') when we need it.
Priority: -- → P1
Target Milestone: --- → Bugzilla 3.2
Attached patch v2 (non-hack)Splinter Review
This version can actually go upstream. It does still contain one hack for MySQL, but that's OK for now. I'm OK with hacks for clear performance gains, and this is the only place we use GROUP_CONCAT right now.
Attachment #327580 - Attachment is obsolete: true
Attachment #331863 - Flags: review?
Attachment #331863 - Flags: review? → review?(dkl)
Running now on our internal snapshot database. Will let you know how it goes. Code wise looks good to me.
Comment on attachment 331863 [details] [diff] [review]
v2 (non-hack)

Works good. Converted 283155 bugs in 19mins.

r=dkl
Attachment #331863 - Flags: review?(dkl) → review+
Flags: approval3.2+
Flags: approval+
tip:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.65; previous revision: 1.64
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.54; previous revision: 1.53
done

3.2:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.60.2.3; previous revision: 1.60.2.2
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.51.2.1; previous revision: 1.51
done
Status: ASSIGNED → RESOLVED
Closed: 14 years ago
Resolution: --- → FIXED
Blocks: 480001
You need to log in before you can comment on or make changes to this bug.