[ome-users] excessive time to upgrade database

[Carnë Draug]

On 7 March 2016 at 10:17, Mark Carroll <m.t.b.carroll at dundee.ac.uk> wrote:
>> I have been upgrading our omero installations this weekend and found
>> that the step to upgrade the database takes an excessive amount of
>> time.  It took more than 9 hours and caused a temporary >3x increase on
>> the database size (from 30GB to 100GB but at the end it was down to 64GB).
>
>
> That's interesting. Depending on how or when your VACUUMs run, I wonder
> if it's worth starting a full vacuum on a Friday night to see if that
> helps with the size, though it could run for a long time on a large
> database. Did you already try the "Optimize an upgraded database"
> section from the server upgrade page?

Yes, I did that after the upgrade.  It also took a fair amount of hours.
Should that have been done before?

> [...]
>> I'm asking because the documentation states that this should execute quickly.
>>  From the upgrade instructions [1]:
>>
>>      The 5.1 upgrade script should execute in a short time.
>
>
> Ha, goodness, it does. I shall have to look into where that sentence
> came from.
>
>> Could there be something wrong with our database?  Or is ours just abnormally
>> large?
>
>
> I expect that your database is fine though it might be good to check
> that your largest tables are indeed ones that you would expect to be
> large. We could try to dig up some comparisons if you have any specific
> concerns there.
>

I don't have any specific concern.  Everything else seemed to work fine,
both installations upgraded, and no one noticed anything odd.  I only
got worried because the documentation mentioned a short time but if you
thing that's a documentation issue then it's all good.

>
> A further thought here: While it is a big table, it is also true that
> many distributions' PostgreSQL installations come with fairly slow
> configuration settings by default. On a decent server it can be worth
> increasing various memory settings and suchlike in postgresql.conf. If
> your DB admin hasn't already much tuned your installation then it might
> be worth seeing what other PostgreSQL experts tend to adjust in case the
> same brings benefits for your system. Though I would certainly hesitate
> to make specific suggestions, I've seen some significant performance
> improvements arising from this approach, so it could be worth cautiously
> investigating.
>

Yeah, I probably should look into this.  I've been acting on the assumption
that no one has yet complained about the performance so it's been pretty low
priority.

Carnë