[ome-devel] OMERO 5 deletion performance

Tue Jun 10 17:00:37 BST 2014

On Jun 10, 2014, at 5:25 PM, Yanling Liu wrote:

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

How long did that take?

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

How many did you try to increase to?

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

Definitely not.

> 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 think the only time we've run into this, we upgraded to 9.3 which doesn't have the issue as Roger mentioned. Otherwise, we haven't had any hard & fast response regarding what to set. Or on which platforms/configurations it's going to be necessary. Sorry.

In general though, until we have a new implementation of delete which does not use PostgreSQL rollbacks, it will be necessary to do the deleting in batches.

Cheers,
~Josh

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