On Sat, Feb 25, 2017 at 4:19 AM, lisandro <rostagnolisandro@xxxxxxxxx> wrote:
Hi there! Please tell me if this isn't the place to post my question, I'm new
in the list.
I'm using PostgreSQL 9.3, I have around 150 databases, and I use pgBouncer
for connection pooling.
My server is a VPS with 8cpus and 24gb of RAM.
My current postgreSQL configuration (resumed) is this:
listen_addresses = '*'
port = 6543
max_connections = 250
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 10485kB
maintenance_work_mem = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
In the other hand, my pgBouncer configuration (resumed) is this:
listen_addr = localhost
listen_port = 5432
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 10
min_pool_size = 2
server_idle_timeout = 30
...
Note that pgBouncer pool size is per user/database pair. With these settings and 150 databases I'm actually surprised that you aren't running out of connections more often. Perhaps there are per-database settings that haven't been shown. We are also missing info on reserve_pool_timeout, max_db_connections, etc. which could all play a role, here.
Cheers,
Steve
