> First of all default value of this parameter is 1000, not 10.
Oops, my bad! Sorry about that, I'm not sure how I got that in my head last night but I see how that would make it act strange now. I'll adjust my notes before re-testing. :) Thanks, *Ryan Lambert* On Wed, Aug 7, 2019 at 4:57 AM Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > 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 > >