[ome-devel] OMERO 5 deletion performance

[Roger Leigh]

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


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