PostgreSQL shows too many clients error with a minimal setup
Description
When you spin up a clean install, the PostgreSQL shows in the logs the
The maximum connections are set to 100
The opennms-datasources.xml configures the connection pool by default to 50.
We seem to leak SQL connections that aren’t limited by the data source's configuration. We have by default 50 idle sessions from the opennms user against the opennms database and 50 idle sessions from the postgres user against template1 which already fills 100 connections.
When you lookup the connection you see 106 active connections after I bumped the max_connections in the postgresql.conf.
I’m seeing exactly 50 idle connections from the opennms user against the opennms database and 50 idle connections from the postgres user against the template1 database.
When you increase the connection pool configuration from 50 to 60, you will see exactly 60 idle connections for both users (total 120) the opennms user and the postgres user. So that means the connection pool configuration requires exactly twice the maximum connections for the PostgreSQL server.
Ideally, you would configure a connection pool size for the opennms and postgresql user individually. The behavior we have here is not very obvious. The connection pool settings are for each user in the data sources.
This was introduced in (by 73b8a6f1322d350ec1831dcd03043ef217af374e).
Jeff Gehlbach August 2, 2023 at 3:56 PM
I’m encountering this problem in a develop snapshot as well.
Ronny Trommer July 28, 2023 at 9:34 PM
You can use it, you need to bump the max_connections to something like 200. The PostgreSQL default value with max_connections=100 isn’t enough. Nonetheless, we need to investigate this issue, cause it’s definitely a bug somewhere. Thank you for taking the time and provide feedback here. It’s always great to have others helping to verify certain behavior.
Devesh Chaturvedi July 21, 2023 at 3:17 PM
Is there any update on this? This is still present on 32.0.1 and makes OpenNMS virtually unusable within 15 minutes of startup.
When you spin up a clean install, the PostgreSQL shows in the logs the
The maximum connections are set to 100
The opennms-datasources.xml configures the connection pool by default to 50.
We seem to leak SQL connections that aren’t limited by the data source's configuration. We have by default 50 idle sessions from the opennms user against the opennms database and 50 idle sessions from the postgres user against template1 which already fills 100 connections.
When you lookup the connection you see 106 active connections after I bumped the max_connections in the postgresql.conf.
I’m seeing exactly 50 idle connections from the opennms user against the opennms database and 50 idle connections from the postgres user against the template1 database.
When you increase the connection pool configuration from 50 to 60, you will see exactly 60 idle connections for both users (total 120) the opennms user and the postgres user. So that means the connection pool configuration requires exactly twice the maximum connections for the PostgreSQL server.
Ideally, you would configure a connection pool size for the opennms and postgresql user individually. The behavior we have here is not very obvious. The connection pool settings are for each user in the data sources.