Re: Built-in connection pooler

Hi Ryan,

On 07.08.2019 6:18, Ryan Lambert wrote:
> Hi Konstantin,
>
> 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
>

Sorry, looks like there is misunderstanding with meaning of max_sessions
parameters.
First of all default value of this parameter is 1000, not 10.
Looks like you have explicitly specify value 10 and it cause this problems.

So "max_sessions" parameter specifies how much sessions can be handled
by one backend.
Certainly it makes sense only if pooler is switched on (number of
proxies is not zero).
If pooler is switched off, than backend is handling exactly one session/

There is no much sense in limiting number of sessions server by one
backend, because the main goal of connection pooler is to handle arbitrary
number of client connections with limited number of backends.
The only reason for presence of this parameter is that WaitEventSet
requires to specify maximal number of events.
And proxy needs to multiplex connected backends and clients. So it
create WaitEventSet with size max_sessions*2 (mutiplied by two because
it has to listen both for clients and backends).

So the value of this parameter should be large enough. Default value is
1000, but there should be no problem to set it to 10000 or even 1000000
(hoping that IS will support it).

But observer behavior ("server closed the connection unexpectedly" and
hegative number of idle clients) is certainly not correct.
I attached to this mail patch which is fixing both problems: correctly
reports error to the client and calculates  number of idle clients).
New version also available in my GIT repoistory:
https://github.com/postgrespro/postgresql.builtin_pool.git
branch conn_proxy.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company