I’m trying to maximize pooler and DB resources to ensure highest throughput.

Specs:

Postgres DB: 8CPUs 64GB Memory

VM for PGbouncer: 16CPU 64GB Memory

Both servers are on the same location.

As I’m using GCP SQL, max_connections and shared_buffers is already set to optimal value 800 and 22GB

PG bouncer config:

[databases]
reader = host=ip dbname=noname password=nopassword

[pgbouncer]

max_client_conn = 100000
default_pool_size = 40
max_db_connections = 43
pool_mode = "transaction"

Each PGbouncer can have max of 43 connections with DB and all 16instances will max out at 688connections which is well under max db connections.

My PGbouncer VM runs 16 pgbouncer isntances on 1001-1016 ports and I’ve haproxy which distributes load to all pgbouncer instances.

PG bench input file select.sql

SELECT 1;

When I run pgbench I get the following results:

max_client_conn=10000
  -C  |  -T    | TPS
---------------+-------
 500  |   120  | 60k
 1000 |   120  | 56k
 1500 |   120  | 55k

max_client_conn=100000(increased from 10,000 to 100,000)
  -C  |  -T    | TPS
---------------+-------
 500  |   120  | 47k
 1000 |   120  | 62k
 1500 |   120  | 63k
 2000 |   120  | 44k
 2500 |   120  | 38k
 3000 |   120  | 46k
 4000 |   120  | 46k
 5000 |   120  | 44k


increased DB max_buffer size from 21GB to 40GB

   -C  |  -T    | TPS
---------------+-------
 2000 |   120  | 69k
 5000 |   120  | 62k
 7000 |   120  | 60k
 10000|   120  | 64k

Note: There were no failures

My TPS is saturated at around 40-60k. Is there anything else that can be done to improve TPS?
Horizontally scaling pgbouncer instance is an option but you’ll soon run into max_connections limit which is 800.

Pg bouncer held all db-connections and that explains the constant memory. CPU only spikes when I run a stress test.
When running stress test my PG bouncer vm had atmost of 50% CPU used but not much in memory.
Because this is just a SELECT 1 changing pool_mode from transaction to statement wont matter much.

Questions:

1. Is there anything I can do to improve TPS further? I know I can add read replicas and more pgbouncer instances. But is there anything I can do before I scale up?
2. Why did RPS improve when I increased max_buffer size? Anyways all requests