518073 - Backport checksetup.pl performance enhancements from Bugzilla trunk

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: General, defect)

Description

[Dave Miller [:justdave]]

checksetup.pl upgrade of 3.2 to 3.4 takes several hours to run.  There's been a lot of performance enhancements to the schema altering code on the trunk which will probably speed this up a *lot*.  In the interest of keeping downtime minimal in the upgrade, we should backport these to bmo-3.4 so they're in place when we upgrade production.

Comment 1

[Reed Loden [:reed]]

Why can't we just do this upstream? Is there a reason these fixes aren't getting backported to 3.4?

Comment 2

[Frédéric Buclin]

(In reply to comment #1)
> Why can't we just do this upstream? Is there a reason these fixes aren't
> getting backported to 3.4?

Because we are not fixing bugs here, we are only improving performance. And in case we regress something, we don't want to take the risk to break 3.4.x.

Comment 3

[Reed Loden [:reed]]

(In reply to comment #2)
> (In reply to comment #1)
> > Why can't we just do this upstream? Is there a reason these fixes aren't
> > getting backported to 3.4?
> 
> Because we are not fixing bugs here, we are only improving performance. And in
> case we regress something, we don't want to take the risk to break 3.4.x.

Yes, and we've taken many a perf fix on prior branches just for that reason. So, why not these? How much risk is there in possibly regressing something? So many of the big bugzillas are still on earlier releases because upgrades are so difficult. We should be trying to help them out by making downtime minimal.

Comment 4

[Gervase Markham [:gerv]]

Why don't we backport it to the b.m.o. code and run the upgrade? If it runs without trouble, then that's a very useful data point to take into consideration when thinking about whether to take it on the 3.4 branch.

But I think Reed is right - doing work on the 3.4 branch to minimise downtime when migrating to it will make the upgrade smoother, and so big sites will be happier to do it. Which is good for them, and for us.

Gerv

Comment 5

[Dave Miller [:justdave]]

------------------------------------------------------------
revno: 6698
author: Max Kanat-Alexander <[e-mail]>
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 15:10:52 -0500
message:
  Req 11 / Bug 518073: Part 1: Bug 405970: checksetup.pl is slow when there's nothing to do (we attempt to rederive groups every time instead of only when the group deriviation process is updated).
------------------------------------------------------------
revno: 6699
author: Max Kanat-Alexander <[e-mail]>
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 16:30:08 -0500
message:
  Req 11 / Bug 518073: Part 2: Bug 507969: Only remove indexes in prep for MyISAM->InnoDB conversion if we actually have tables to convert.  Only set up default statuses if they haven't already been defined.
------------------------------------------------------------
revno: 6700
author: Max Kanat-Alexander <[e-mail]>
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 16:33:58 -0500
message:
  Req 11 / Bug 518073: Part 3: Bug 508018: only drop broken column defaults if they're actually broken
------------------------------------------------------------
revno: 6701
author: Max Kanat-Alexander <[e-mail]>
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 16:41:20 -0500
message:
  Req 11 / Bug 518073: Part 4: Bug 508023: Speed up MySQL's bz_alter_column for when we're just adding or removing a DEFAULT
------------------------------------------------------------
revno: 6702
author: Max Kanat-Alexander <[e-mail]>
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 16:44:04 -0500
message:
  Req 11 / Bug 518073: Part 5: Bug 508032: Speed up initializing of email_setting values
------------------------------------------------------------
revno: 6703
committer: David Miller <[e-mail]>
branch nick: bmo3.4
timestamp: Sat 2009-11-14 16:54:36 -0500
message:
  Bugfix for bug 507969: ignore VIEWs when looking for MyISAM tables to convert.

Comment 6

[Dave Miller [:justdave]]

And the results?

before committing this stuff, checksetup.pl took 4.5 hours to convert bmo 3.2 to bmo 3.4.

after committing this stuff, it takes 65 minutes.

w00t!!

Comment 7

[Max Kanat-Alexander]

Wow, nice! :-)

Comment 8

[Max Kanat-Alexander]

By the way, for certain tables it's also faster to:

1) Rename the table
2) Have checksetup re-create the table
3) Copy data from the old table to the new table in a single INSERT statement.

However, this requires intimate knowledge of Bugzilla::Install::DB in order to know for certain that (a) this is safe and (b) won't break checksetup.

Comment 9

[Reed Loden [:reed]]

(In reply to comment #7)
> Wow, nice! :-)

... and this is why all these changes should be backported from 3.6 to 3.4.