View Issue Details

IDProjectCategoryView StatusLast Update
0005693SOGoBackend Calendarpublic2023-03-01 13:05
Reportertobixen Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status newResolutionopen 
Platform[Server]OSArchlinuxOS Version2023
Product Version5.8.0 
Summary0005693: Server eats up all available postgresql connections
Description

I've set up SOGo server on Archlinux, with postgresql as database backend and auth through postgresql (using local socket). One test user has been set up in said table.

Next step is to run the test suite of my python caldav client library towards SOGo.

Now the test code should be optimized - it's almost silly, I have a global list of all calendars, event UID etc that are in use by at least one of the tests. For every test I do cleanup both before and after the test has run, going through every object on that global list, and ensure to delete it from the server if it exists on the server. The result is that when running the tests, the server gets bombarded with requests. But it's all serial, single-user, one request at the time.

So, like half of the tests run without problems, but then the rest fails on 501s from the server.

Digging more into it, the problem is that postgresql has run out of connection slots. It has hundred connection slots, I think that ought to suffice.

Digging even more into it, it seems that for every CalDAV query I'm doing from the tests, the SOGo server will open a new connection towards postgresql, but this connection is never closed.

(This may possibly be a duplicate of 0004083)

Steps To Reproduce

I did set up SOGo on Archlinux, with those settings:

SOGoProfileURL = "postgresql://%2Fvar%2Frun%2Fpostgresql/sogo/sogo_user_profile";
OCSFolderInfoURL = "postgresql://%2Fvar%2Frun%2Fpostgresql/sogo/sogo_folder_info";
OCSSessionsFolderURL = "postgresql://%2Fvar%2Frun%2Fpostgresql/sogo/sogo_sessions_folder";

SOGoUserSources = (
{
type = sql;
canAuthenticate = YES;
id = foo;
viewURL = postgresql://%2Fvar%2Frun%2Fpostgresql/sogo/users;
}
);

Then checked out the master branch of https://github.com/python-caldav/caldav/, made caldav/tests/conf_private.py to point to my SOGo and ran the test suite there (pytest)

nginx is used as a front-end to SOGo, but that really shouldn't matter.

Additional Information

I'm intending to do more research on this one when I get time:

  • Trying to reproduce the issue with simple requests rather than running my test suite
  • Check up what queries are being done
  • Checking if there exists any work-around (installing pgbouncer or pgpool would probably be useful)
TagsNo tags attached.

Activities

tobixen

tobixen

2023-02-11 16:34

reporter   ~0016652

Here is the error message:

Feb 11 17:33:23 srv4.rl-tobias.c.bitbit.net sogod[121095]: 2023-02-11 17:33:23.768 sogod[121095:121095] EXCEPTION: <PostgreSQL72CouldNotOpenChannelException: 0x5647ffc59370> NAME:NoMoreConnections REASON:cannot open a additional connection ! INFO:(null)

tobixen

tobixen

2023-02-11 17:08

reporter   ~0016653

So there seems to be some kind of garbage collection going on after all. If I wait some time, the connection count drops off again. Meaning that this is not a major bug, possibly a minor bug. One workaround is to increase the max connections, another is to reduce the traffic, a more proper workaround in a production setting would be to use pgbouncer or pgpool.

sebastien

sebastien

2023-03-01 13:05

administrator   ~0016707

Hi there,

So this is more an issue related to postgresql ?

Sebastien

Issue History

Date Modified Username Field Change
2023-02-11 16:26 tobixen New Issue
2023-02-11 16:34 tobixen Note Added: 0016652
2023-02-11 17:08 tobixen Note Added: 0016653
2023-03-01 13:05 sebastien Note Added: 0016707