Re: Built-in connection pooler

Hi

On 12.11.2019 10:50, ideriha(dot)takeshi(at)fujitsu(dot)com wrote:
> Hi.
>
>> From: Konstantin Knizhnik [mailto:k(dot)knizhnik(at)postgrespro(dot)ru]
>>
>> New version of builtin connection pooler fixing handling messages of extended
>> protocol.
>>
> Here are things I've noticed.
>
> 1. Is adding guc to postgresql.conf.sample useful for users?
Good catch: I will add it.

>
> 2. When proxy_port is a bit large (perhaps more than 2^15), connection failed
> though regular "port" is fine with number more than 2^15.
>
> $ bin/psql -p 32768
> 2019-11-12 16:11:25.460 JST [5617] LOG: Message size 84
> 2019-11-12 16:11:25.461 JST [5617] WARNING: could not setup local connect to server
> 2019-11-12 16:11:25.461 JST [5617] DETAIL: invalid port number: "-22768"
> 2019-11-12 16:11:25.461 JST [5617] LOG: Handshake response will be sent to the client later when backed is assigned
> psql: error: could not connect to server: invalid port number: "-22768"
Hmmm, ProxyPortNumber is used exactly in the same way as PostPortNumber.
I was able to connect to the specified port:

knizhnik(at)knizhnik:~/dtm-data$ psql postgres -p 42768
psql (13devel)
Type "help" for help.

postgres=# \q
knizhnik(at)knizhnik:~/dtm-data$ psql postgres -h 127.0.0.1 -p 42768
psql (13devel)
Type "help" for help.

postgres=# \q

> 3. When porxy_port is 6543 and connection_proxies is 2, running "make installcheck" twice without restarting server failed.
> This is because of remaining backend.
>
> ============== dropping database "regression" ==============
> ERROR: database "regression" is being accessed by other users
> DETAIL: There is 1 other session using the database.
> command failed: "/usr/local/pgsql-connection-proxy-performance/bin/psql" -X -c "DROP DATABASE IF EXISTS \"regression\"" "postgres"
Yes, this is known limitation.
Frankly speaking I do not consider it as a problem: it is not possible
to drop database while there are active sessions accessing it.
And definitely proxy has such sessions. You can specify
idle_pool_worker_timeout to shutdown pooler workers after  some idle time.
In this case, if you make large enough pause between test iterations,
then workers will be terminated and it will be possible to drop database.

> 4. When running "make installcheck-world" with various connection-proxies, it results in a different number of errors.
> With connection_proxies = 2, the test never ends. With connection_proxies = 20, 23 tests failed.
> More connection_proxies, the number of failed tests decreased.
Please notice, that each proxy maintains its own connection pool.
Default number of pooled backends is 10 (session_pool_size).
If you specify too large number of proxies then number of spawned backends =
 session_pool_size * connection_proxies can be too large (for the
specified number of max_connections).

Please notice the difference between number of proxies and number of
pooler backends.
Usually one proxy process is enough to serve all workers. Only in case
of MPP systems with large number of cores
and especially with SSL connections, proxy can become a bottleneck. In
this case you can configure several proxies.
But having more than 1-4 proxies seems to be bad idea.

But in case of check-world the problem is not related with number of
proxies.
It takes place even with connection_proxies = 1
There was one bug with handling clients terminated inside transaction.
It is fixed in the attached patch.
But there is still problem with passing isolation tests under connection
proxy: them are using pg_isolation_test_session_is_blocked
function which checks if backends with specified PIDs are blocked. But
as far as in case of using connection proxy session is no more bounded
to the particular backend, this check may not work as expected and test
is blocked. I do not know how it can be fixed and not sure if it has to
be fixed at all.

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