I have a PostgreSQL 9.4 server that seems to be limiting connections to fewer than what I have defined in max_connections.
I have max_connections set to 300.
SHOW max_connections;
max_connections
-----------------
300
However, I have discovered that I start getting new connections timing out as soon as my active connections hits 100, as measured by SELECT count(*) from pg_stat_activity;
So long as that number is below 100, new connections work. Once it hits 100, new connections fail: PG::ConnectionBad: could not connect to server: Connection timed out
(Ruby pg library)
There seems to be an exception for superuser connections (ie. me connecting via PgAdmin), as I can exceed the 100 threshold that way, though apps not using superusers continue to time out.
I am at a loss as to figuring out exactly what the root cause may be.
Some config settings:
max_connections = 300
superuser_reserved_connections = 2
shared_buffers = 1024MB
effective_cache_size = 2048MB
work_mem = 8MB
maintenance_work_mem = 256MB
I have tried tinkering with these values a bit, to no useful effect.
Other details:
- Ubuntu 14.04 64-bit
- 4GB Linode VPS
- PostgreSQL 9.4.1
- Did not have this problem with essentially the same configuration on PostgreSQL 9.2.
- Databases were migrated from Postgres 9.2 via pg_dumpall
- Memory usage doesn't seem to be an issue,
free
shows 2.1 GB free, 3.5 GB including buffers/cache. - The connections are for many different roles to many different databases, no individual DB has more than 5 or 6 active connections, and each DB is being accessed by its own separate role. Does not seem to be running afoul of any connection limits on individual DBs or roles.
Have you raised the kernel max segment size too?
Try:
Is it possible that you have limited the max user connection?Role privileges
You can change this with