pgbouncer连接池设置与压力测试的最大连接数测试

pgbouncer中间件，通过设置so_reuseport=1，配置两个pgbouncer的systemctl服务，启动两个pgbouncer的进程。
如下两个进程的配置文件分别是pgbouncer.ini和pgbouncer2.ini，default_pool_size=30，意味着最多支持60个客户端连接，超过这个连接数之后就开始排队（即便是数据库的最大连接数远超60），验证一下这个设置。

可以看到当线程数超过64的时候，出现了NOTICE:  No server connection available in postgres backend, client being queued异常。
但是这里的No server connection available并不是达到了数据库的最大连接数的设置的值，也不是报错，而是受前端的pgbouncer连接池中设置的最大值限制，没有连接可用，然后排队等待可用连接。

root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c8 -j8
Password:
pgbench (16.4)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 76925
number of failed transactions: 0 (0.000%)
latency average = 6.239 ms
initial connection time = 11.821 ms
tps = 1282.170948 (without initial connection time)
statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.000           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
         0.534           0  BEGIN;
         0.709           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.737           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.846           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.261           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.624           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.527           0  END;
root@ubuntu12:/usr/local/pgbouncer/bin#
root@ubuntu12:/usr/local/pgbouncer/bin#
root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c32 -j32
Password:
pgbench (16.4)
starting vacuum...end.
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 32
number of threads: 32
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 93419
number of failed transactions: 0 (0.000%)
latency average = 20.549 ms
initial connection time = 28.982 ms
tps = 1557.281163 (without initial connection time)
statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.000           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
         2.492           0  BEGIN;
         1.250           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         1.657           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         3.656           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         7.347           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.015           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         3.126           0  END;
root@ubuntu12:/usr/local/pgbouncer/bin#
root@ubuntu12:/usr/local/pgbouncer/bin#
root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c64 -j64
Password:
pgbench (16.4)
starting vacuum...end.
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
NOTICE:  No server connection available in postgres backend, client being queued
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 64
number of threads: 64
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 93579
number of failed transactions: 0 (0.000%)
latency average = 41.024 ms
initial connection time = 67.205 ms
tps = 1560.068618 (without initial connection time)
statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.000           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
        23.016           0  BEGIN;
         1.245           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         1.818           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         3.676           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         7.185           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.111           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         2.951           0  END;
root@ubuntu12:/usr/local/pgbouncer/bin#