[ome-users] excessive time to upgrade database

[Mark Carroll]

On 03/07/2016 05:59 PM, Carnë Draug wrote:
> On 7 March 2016 at 10:17, Mark Carroll <m.t.b.carroll at dundee.ac.uk> wrote:
(snip)
>> 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?

After is good. The upgrade will expand the size of your database with
the UPDATEs (given how PostgreSQL does MVCC) and the FULL part of the
VACUUM should reclaim the space for the old table rows.

It can be worth making sure that some ongoing maintenance is occurring
too. PostgreSQL installations typically have the autovacuum daemon
running, though I don't know if anything periodically redoes an ANALYZE.
On a rainy day it's probably worth looking into what's recommended and
if that's actually happening.

(snip)
> 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.

Great, that makes perfect sense, I just wanted to make sure. I've now
opened a PR to fix that sentence:
https://github.com/openmicroscopy/ome-documentation/pull/1424. Some
upgrades will run quickly but I suspect that's the minority, as they
typically bundle a bunch of smaller changes and there's a fair chance
that at least some sites will find a couple of the changes to be slow.
It's difficult for us to predict timings in advance because the nature
of different sites' data varies: some may have many large images, others
small ones but with many planes, some may have plates with many wells,
some may have thousands of ROIs on their images, some may heavily use
annotations, etc., so, even given an overall idea of the database size,
how long a specific upgrade may take still depends a lot on which tables
and columns are changing and how much of that one has. We do try to
write SQL to improve the common case, though: for example, the UPDATEs
that I think were slow for you here at least update multiple columns in
one go.

>> On a decent server it can be worth
>> increasing various memory settings and suchlike in postgresql.conf.
>
> 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.

Ah, you /might/ have some wins awaiting you here then. Especially for
older PostgreSQL the default configuration often assumes a fairly
resource-poor host. For other settings like synchronous_commit I don't
have the expertise to confidently say, yes go ahead and turn it off for
OMERO, but others may be able to comment there. But even as your server
is now its usage pattern may be such that the database side is already
performing adequately for typical day-to-day operation: during the
working day maybe just keep an eye on the different processes and IO to
see if you notice any obvious bottlenecks, before deciding to spend some
time optimizing something.

-- Mark

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