Re: Built-in connection pooler

I did some testing with the latest patch [1] on a small local VM with 1 CPU and 2GB RAM with the intention of exploring pg_pooler_state().

Configuration:

idle_pool_worker_timeout = 0 (default)
connection_proxies = 2
max_sessions = 10 (default)
max_connections = 1000

Initialized pgbench w/ scale 10 for the small server.

Running pgbench w/out connection pooler with 300 connections:

pgbench -p 5432 -c 300 -j 1 -T 60 -P 15 -S bench_test
starting vacuum...end.
progress: 15.0 s, 1343.3 tps, lat 123.097 ms stddev 380.780
progress: 30.0 s, 1086.7 tps, lat 155.586 ms stddev 376.963
progress: 45.1 s, 1103.8 tps, lat 156.644 ms stddev 347.058
progress: 60.6 s, 652.6 tps, lat 271.060 ms stddev 575.295
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 300
number of threads: 1
duration: 60 s
number of transactions actually processed: 63387
latency average = 171.079 ms
latency stddev = 439.735 ms
tps = 1000.918781 (including connections establishing)
tps = 1000.993926 (excluding connections establishing)


It crashes when I attempt to run with the connection pooler, 300 connections:

pgbench -p 6543 -c 300 -j 1 -T 60 -P 15 -S bench_test
starting vacuum...end.
connection to database "bench_test" failed:
server closed the connection unexpectedly
       This probably means the server terminated abnormally
       before or while processing the request.

In the logs I get:

WARNING:  PROXY: Failed to add new client - too much sessions: 18 clients, 1 backends. Try to increase 'max_sessions' configuration parameter.

The logs report 1 backend even though max_sessions is the default of 10.  Why is there only 1 backend reported?  Is that error message getting the right value?

Minor grammar fix, the logs on this warning should say "too many sessions" instead of "too much sessions."

Reducing pgbench to only 30 connections keeps it from completely crashing but it still does not run successfully.

pgbench -p 6543 -c 30 -j 1 -T 60 -P 15 -S bench_test
starting vacuum...end.
client 9 aborted in command 1 (SQL) of script 0; perhaps the backend died while processing
client 11 aborted in command 1 (SQL) of script 0; perhaps the backend died while processing
client 13 aborted in command 1 (SQL) of script 0; perhaps the backend died while processing
...
...
progress: 15.0 s, 5734.5 tps, lat 1.191 ms stddev 10.041
progress: 30.0 s, 7789.6 tps, lat 0.830 ms stddev 6.251
progress: 45.0 s, 8211.3 tps, lat 0.810 ms stddev 5.970
progress: 60.0 s, 8466.5 tps, lat 0.789 ms stddev 6.151
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 30
number of threads: 1
duration: 60 s
number of transactions actually processed: 453042
latency average = 0.884 ms
latency stddev = 7.182 ms
tps = 7549.373416 (including connections establishing)
tps = 7549.402629 (excluding connections establishing)
Run was aborted; the above results are incomplete.

Logs for that run show (truncated):


2019-08-07 00:19:37.707 UTC [22152] WARNING:  PROXY: Failed to add new client - too much sessions: 18 clients, 1 backends. Try to increase 'max_sessions' configuration parameter.
2019-08-07 00:31:10.467 UTC [22151] WARNING:  PROXY: Failed to add new client - too much sessions: 15 clients, 4 backends. Try to increase 'max_sessions' configuration parameter.
2019-08-07 00:31:10.468 UTC [22152] WARNING:  PROXY: Failed to add new client - too much sessions: 15 clients, 4 backends. Try to increase 'max_sessions' configuration parameter.
...
...


Here it is reporting fewer clients with more backends. Still, only 4 backends reported with 15 clients doesn't seem right.  Looking at the results from pg_pooler_state() at the same time (below) showed 5 and 7 backends for the two different proxies, so why are the logs only reporting 4 backends when pg_pooler_state() reports 12 total?

Why is n_idle_clients negative?  In this case it showed -21 and -17.  Each proxy reported 7 clients, with max_sessions = 10, having those n_idle_client results doesn't make sense to me.


postgres=# SELECT * FROM pg_pooler_state();
 pid  | n_clients | n_ssl_clients | n_pools | n_backends | n_dedicated_backends | n_idle_backends | n_idle_clients | tx_bytes | rx_bytes | n_transactions

-------+-----------+---------------+---------+------------+----------------------+-----------------+----------------+----------+----------+---------------
-
25737 |         7 |             0 |       1 |          5 |                    0 |               0 |            -21 |  4099541 |  3896792 |          61959
25738 |         7 |             0 |       1 |          7 |                    0 |               2 |            -17 |  4530587 |  4307474 |          68490
(2 rows)


I get errors running pgbench down to only 20 connections with this configuration. I tried adjusting connection_proxies = 1 and it handles even fewer connections.  Setting connection_proxies = 4 allows it to handle 20 connections without error, but by 40 connections it starts having issues.

While I don't have expectations of this working great (or even decent) on a tiny server, I don't expect it to crash in a case where the standard connections work.  Also, the logs and the function both show that the total backends is less than the total available and the two don't seem to agree on the details.

I think it would help to have details about the pg_pooler_state function added to the docs, maybe in this section [2]?

I'll take some time later this week to examine pg_pooler_state further on a more appropriately sized server.  

Thanks,


[1] https://www.postgresql.org/message-id/attachment/103046/builtin_connection_proxy-16.patch
[2] https://www.postgresql.org/docs/current/functions-info.html

 
Ryan Lambert