[ome-devel] OMERO 5 deletion performance

[Yanling Liu]

Hello Josh,

A little update on this issue:

After bumping kernel.shmmax to 8GB and Postgresql max_locks_per_transaction
to 256, I can now do bulk deletion for 910 images in 25 datasets at one
time.

However, I have got the same error message when trying to delete a lager
set of images after import failure. Tried to increase
max_locks_per_transaction to 2048 and waiting for deletion to finish, which
took couple hours...

My system has 8GB total memory, I would think it doesn't make sense to
increase kernel.shmmax to beyond 8GB, right?

What I am also interested in knowing the common practice for OMERO memory
configuration. What would be recommend memory configuration for OME if we
have couple thousand images per project?

I'll start a new thread regarding my import errors...

Thanks,
Yanling


On Thu, Jun 5, 2014 at 7:39 PM, Josh Moore <josh at glencoesoftware.com> wrote:

> Hi Yanling,
>
> On Jun 4, 2014, at 2:14 PM, Yanling Liu wrote:
>
> > I am using postgres 9.1. And I got same error message after increasing
> > kernel.shmmax to 128MB.
>
> Sorry that you're still having troubles. Unfortunately, the entire OME team
> is currently at the annual users' meeting:
>
>
> http://www.openmicroscopy.org/site/community/minutes/meetings/9th-annual-users-meeting-june-2014
>
> so our responses will be somewhat slower than usual. Until your PostgreSQL
> installation
> is no longer showing the error, though, comparing deletion times won't be
> useful.
>
> Have you restarted PostgreSQL after the change? If that didn't work, your
> host?
>
> You might want to read through
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> to find out more about changing these settings.
>
> > ...
> > In case you might wonder why I need to delete files. It is simply because
> > there's import error so I need to delete whatever imported into OMERO
> > before doing import again.
>
> Makes complete sense.
>
>
> > Not to mention import performance in OMERO is also low...
>
> Can you quantify this for us? What file format are you trying to import?
> How many individual files and GB per fileset?
> How long did import take? Could you send us the import log?
>
> Also note that part of the import times will be dependent on the
> performance of your PostgreSQL installation, so until we've handled the
> first issue, we may not want to compare import times).
>
>
> > ...
> > Thanks,
> > Yanling
>
>
> Cheers,
> ~Josh.
>
>
> > On Tue, Jun 3, 2014 at 6:48 PM, Roger Leigh <rleigh at dundee.ac.uk> wrote:
> >
> >> On 03/06/14 19:25, Josh Moore wrote:
> >>
> >>> Hi Yanling,
> >>>
> >>> On Jun 3, 2014, at 7:28 PM, Yanling Liu wrote:
> >>>
> >>> Made mistake in outputting Blitz-0.log file. Here is what I found:
> >>>>
> >>>> 2014-06-03 08:43:19,924 WARN  [
> o.h.h.ast.exec.MultiTableDeleteExecutor]
> >>>> (2-thread-3) unable to drop temporary id table after use [ERROR:
> current
> >>>> transaction is aborted, commands ignored until end of transaction
> block]
> >>>> 2014-06-03 08:43:19,927 ERROR [
>  ome.services.delete.Deletion]
> >>>> (2-thread-3) Failure during DeleteHandle.steps :
> >>>> ...
> >>>>
> >>>> From my limited knowledge about java and postgres, this looks like a
> >>>> postgres issue. Shall I change max_locks_per_transaction? If so what
> >>>> would
> >>>> be optimal value?
> >>>>
> >>>> ...
> >>>
> >>> On Tue, Jun 3, 2014 at 9:29 AM, Yanling Liu <vrnova at gmail.com> wrote:
> >>>>
> >>>>> ...
> >>>>> But I do found something in postgres log file:
> >>>>>
> >>>>> 2014-06-03 08:43:19 EDT WARNING:  out of shared memory
> >>>>> 2014-06-03 08:43:19 EDT ERROR:  out of shared memory
> >>>>> 2014-06-03 08:43:19 EDT HINT:  You might need to increase
> >>>>> max_locks_per_transaction.
> >>>>>
> >>>>> ...
> >>>
> >>> In postgresql.conf I have following settings:
> >>>>>
> >>>>> max_connections = 100
> >>>>> #max_locks_per_transaction = 64         # min 10
> >>>>>
> >>>>> Mote the max_locks_per_transaction is commented out by default. I am
> not
> >>>>> an expert on postgres and I would appreciate if you may provide some
> >>>>> hints
> >>>>> on how to set postgres parameters properly for OMERO.
> >>>>>
> >>>>
> >> http://www.postgresql.org/docs/9.3/static/runtime-config-locks.html
> >> There shouldn't be a need to fiddle with these settings unless you see
> >> problems specifically relating to them.
> >>
> >>
> >> Since you likely still have available resources on your system with
> >>> 8 GB, one solution would be to give increase the shared memory
> >>> setting of your system. See the "Linux" section of
> >>> http://www.postgresql.org/docs/9.1/static/kernel-resources.html
> >>>
> >>
> >> As a caveat, do make sure to check the documentation for the version of
> >> PostgreSQL you are using.  More recent versions of PostgreSQL (>= 9.3)
> >> no longer use SYSV SHM significantly and so these settings might not
> >> apply to you.  See e.g.
> >> http://www.postgresql.org/docs/9.3/static/kernel-resources.html and
> >> http://rhaas.blogspot.co.uk/2012/06/absurd-shared-memory-limits.html.
> >> In the case of PostgreSQL 9.3 and later you can increase shared_buffers
> >> as high as you like providing you have the system memory (and swap
> >> space) to back it.
> >>
> >>
> >> Regards,
> >> Roger
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openmicroscopy.org.uk/pipermail/ome-devel/attachments/20140610/6ba8fea2/attachment.html>