launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Wed, Oct 6, 2010 at 8:02 PM, Danilo Šegan <danilo@xxxxxxxxxxxxx> wrote:
> У сре, 06. 10 2010. у 23:37 +1300, Robert Collins пише:
>> On Wed, Oct 6, 2010 at 7:52 PM, Stuart Bishop
>> <stuart.bishop@xxxxxxxxxxxxx> wrote:
>> > On Mon, Oct 4, 2010 at 9:42 AM, Edwin Grubbs <edwin.grubbs@xxxxxxxxxxxxx> wrote:
>> >> Hi Stuart,
>> >>
>> >> Do you have the staging db restore time that we use to announce the
>> >> estimated downtime for the Launchpad release?
>> >
>> > This is a tricky one to estimate. There is a large change in the
>> > pipeline that on staging took 3 hours last time it ran. However, this
>> > patch should run much faster on the production hardware (the slowness
>> > is rebuilding indexes on the 600 million rows of the BranchRevision
>> > table).
>> >
>> > So based on this, I would guess up to 5 hours.
>> >
>> > However, we have also upgraded to PG 8.4. We should get a better
>> > timing this weekend under PG 8.4.
>>
>> 5 hours downtime is -huge- right after Ubuntu releases.
>>
>> Lets roll that patch back and discuss alternate approaches, if the
>> indication on Monday is that we're looking at more than an hours total
>> downtime.
>
> I am delaying a TranslationMessage column drop for the similar reason.
> Anyway, the strategy I was going to take was:
>
>  - create new indexes beforehand (use a separate DB patch and get it
> executed on all the DB servers: now sure how workable that is): creating
> an index should take around 20 minutes and there are a few of them
>  - drop the column during rollout ("main" DB patch)
>
> Could this strategy work here as well?  I.e. can we create indexes
> already?  If we have to wait for the rollout (i.e. it strictly depends
> on the DB changes), could we create indexes afterwards when the DB is
> fully operational?

No, it won't unfortunately. The key difference here is we are
replacing the primary key, and there is no way to tell PostgreSQL to
use an existing UNIQUE index when declaring the constraint.

Goal here is to make BranchRevision narrower and faster, reclaim about
17GB of disk on every db replica, improve staging db restore times.

-- 
Stuart Bishop <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/