DatabaseBusyException

[bernhard]

Hi!

We are facing some issues with the Omero server under some load using our own client application. There are concurrent threads and processes which perform reading and writing of data from and to the server independently. I can't exactly say how many connections are established but my guess is that there are less than 30 open in parallel.

The select and update queries work on the same tables, mainly the screen, plate, well, wellsample and image tables. It often happens that we see DatabaseBusyExceptions in our client that is using the OmeroCpp interface.

Do you have any suggestions how to tune the system, in particular the postgres database in order to get rid of this errors?

Thanks! Bernhard

[jmoore]

Hi Bernhard,

Some questions first: do you think you can quantify what each of the threads is doing in terms of INSERTs, UPDATEs, SELECTs, and DELETEs? How many simultaneous connections does your postgres allow? What does

Code: Select all

select * from pg_stat_activity;


look like under heavy load? Do you see any "IDLE in transactions" in your "ps auxw" output?

Something you can do right away is to use the "backOff" field of the concurrency exceptions (slice) to try again.

[bernhard]

Hi Josh!

We did not change any of the default postgresql settings, thus 100 connections are allowed. The client sits on the same machine and looking at the output of lsof -i I don't see more than 10 established connections.

Select statements are loading data from the image table and references, like channel, pixels etc, also screen, screenacquisition, plate, well and wellsample tables are queried to load data that is needed for an analysis of images of a complete plate aquisistion. Concurrently another client application (can also be the same) is uploading data and writing to the same tables.

Could you elaborate a little bit more on the backOff field of the DatabaseBusyException, from the docs I can't see the field and not sure what you meant.


It was a bit difficult to reproduce the load on our system, but here's the best I could get in the meantime:

Code: Select all

$ postgres@server> ps uwwx
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  5531  0.0  0.0  81756  3904 ?        Ss   Jul21   0:27 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres  5548  0.0  0.0  43528  1060 ?        Ss   Jul21   0:00 postgres: logger process
postgres  5555  0.0  0.1  81888 34616 ?        Ss   Jul21   0:00 postgres: writer process
postgres  5556  0.0  0.0  43524  1048 ?        Ss   Jul21   0:00 postgres: stats collector process
postgres  5984 11.9  0.0  90004 32184 ?        Rs   14:25   1:09 postgres: columbus omero4 127.0.0.1(45838) SELECT
postgres  6298  6.2  0.0  89920 32576 ?        Rs   14:26   0:34 postgres: columbus omero4 127.0.0.1(47541) SELECT
postgres  6299  3.4  0.0  89916 31792 ?        Rs   14:26   0:18 postgres: columbus omero4 127.0.0.1(43695) idle
postgres  6302  2.4  0.0  89920 31564 ?        Rs   14:26   0:13 postgres: columbus omero4 127.0.0.1(39078) SELECT
postgres  6303  1.9  0.0  89060 29744 ?        Rs   14:26   0:10 postgres: columbus omero4 127.0.0.1(57831) BIND
postgres  6897  0.5  0.0  89184 28392 ?        Ss   14:28   0:02 postgres: columbus omero4 127.0.0.1(57434) idle in transaction
postgres  8152  0.0  0.0  85148 10212 ?        Rs   14:32   0:00 postgres: columbus omero4 127.0.0.1(59862) BIND
postgres  8326  0.0  0.0  85416  5416 ?        Ss   14:34   0:00 postgres: columbus omero4 127.0.0.1(45734) idle
postgres  8361  0.0  0.0  82572  2648 ?        Ss   14:35   0:00 postgres: columbus omero4 127.0.0.1(59055) idle
postgres 18632  1.6  0.0  89228 31520 ?        Ss   12:30   2:07 postgres: columbus omero4 127.0.0.1(44515) idle
postgres 29625  0.0  0.0  15224  2280 pts/1    S    13:53   0:00 bash
postgres 31625  0.0  0.0  83216  7604 ?        Ss   14:06   0:00 postgres: columbus omero4 [local] idle


The output of the pg_stat_activity table query is attached.

Thanks for your help! Bernhard