[ome-users] excessive time to upgrade database

[Mark Carroll]

Dear Carnë,

> 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?

> To be more exact, this is during the 5.0 to 5.1 upgrade, using the
> "sql/psql/OMERO5.1__1/OMERO5.0__0.sql" database upgrade script.
>
> I did notice that more than 8 hours were spent on a single step (I can't
> tell which from the output of the upgrade but it was something that updated
> 50183812 rows).

That's a lot of rows: perhaps that size alone gives a clue as to which
table, it's probably rather more than the number of images or files you
have in the system. If the large, slow update was among a few others
then my guess is that the culprit was the 5.1DEV__13 DML statements from
line 1609 in the SQL script where we fill in the new "unit" column for
various quantities to give their values the default unit for that
property. Perhaps the deduplication from line 2206 may also take a while
but that's not an update.

> 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.

Cheers,

Mark

The University of Dundee is a registered Scottish Charity, No: SC015096