[ome-users] excessive time to upgrade database

Carnë Draug carandraug+dev at gmail.com
Mon Mar 7 17:59:04 GMT 2016


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ë


More information about the ome-users mailing list