[ome-devel] OMERO 5 deletion performance

[Josh Moore]

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