How to configure postgres+pgbouncer to allow large number of connections

Question

I am in the process of stress testing pgbouncer with 5000 connections. The purpose of the test is to multiplex the connection using pgbouncer i.e. 5000 client connection to 500 db connections.

However, I am unable to achieve the target of active 500 connection mark.

My setup is pretty straight forward

(client set using pgbench) ----->pgbouncer + psql

|______multiple boxes______||______1 box_________|

pgbouncer and postgres are present in the same box.

pgbouncer = 1 core (expectation)

postgres = 15 cores (expectation)

• machine configuration:

  16 cores

  ulimit -n 10000

• Postgres configuration:

  max_connections = 500

  shared_buffers = 1GB

  work_mem = 100kB

• pgbouncer configuration:

  pool_mode = transaction

  server_lifetime=6000

  server_idle_timeout=2000

  server_connect_timeout=30

  default_pool_size=500

  pool_size=500

  pkt_buf=4096

  server_login_retry=2

• client configuration(8 boxes each 8 cores):

  Each client box mimic as a set for clients using pgbench For an 8 core box I set 16 threads to fire the queries

  pgbench -h -p 6541 -c -j 16 -d -f pgbench_Script.sql -T 360 -U postgres test

  pgbench_Script.sql

  \setrandom delta 0 100000 insert into t4.emplog values(nextval('t4.employeeSeq'),:delta);

Active queries on postgres:

select count(*) from pg_stat_activity where state like 'active';
count
-------
40

My expectation is to have close to 500 active connections to the database via the connection pooler. Problem: I only see a handfull of connections ~ 40 active connections

OBSERVATION: I see multiple postgres process in 'idle' state although pgbouncer has all the client being serviced. Suggesting that the pgbouncer is unable to perform to the best. However, I cannot pin point what is the exact bottle neck.

Potential bottleneck:

client request: pgbench makes each thread a master of a set on connection. how can I mimic a large set of active concurrent connections?

pgbouncer : is my configuration of pgbouncer flawed?

postgres : is my configuration of postgres unable to handle large connection?

Answer 1

Are you setting '-c' as blank or is it a typo in the question? The '-c' sets the number of clients for the pgbench command. Start by something from 100 and slowly move to 1000 for each instance running pgbench.

pgbench -h -p 6541 -c 100 -j 16 -d -f pgbench_Script.sql -T 360 -U postgres test