Re: Built-in connection pooling

On 17.01.2018 19:09, Konstantin Knizhnik wrote:
> Hi hackers,
>
> My recent experiments with pthread version of Postgres show that
> although pthread offers some performance advantages comparing with
> processes for large number of connections, them still can not
> eliminate need in connection pooling. Large number even of inactive
> connections cause significant degrade of Postgres performance.
>
> So we need connection pooling.  Most of enterprise systems working
> with Postgres are using pgbouncer or similar tools.
> But pgbouncer has the following drawbacks:
> 1. It is an extra entity which complicates system installation and
> administration.
> 2. Pgbouncer itself can be a bottleneck and point of failure. For
> example with enabled SSL, single threaded model of pgbouncer becomes
> limiting factor when a lot of clients try to simultaneously
> reestablish connection. This is why some companies are building
> hierarchy of pgbouncers.
> 3. Using pool_mode other than "session" makes it not possible to use
> prepared statements and session variables.
> Lack of prepared statements can itself decrease speed of simple
> queries up to two times.
>
> So I thought about built-in connection pooling for Postgres. Ideally
> it should be integrated with pthreads, because in this case scheduling
> of sessions can be done more flexible and easily.
> But I decided to start with patch to vanilla Postgres.
>
> Idea is the following:
> 1. We start some number of normal backends (which forms backend pool
> for serving client sessions).
> 2. When number of connections exceeds number of backends, then instead
> of spawning new backend we choose some of existed backend and redirect
> connection to it.
> There is more or less portable way in Unix to pass socket descriptors
> between processes using Unix sockets:
> for example
> https://stackoverflow.com/questions/28003921/sending-file-descriptor-by-linux-socket/
> (this is one of the places where pthreads Postgres will win). So a
> session is bounded to a backend. Backends and chosen using round-robin
> policy which should guarantee more or less unform distribution of
> sessions between backends if number of sessions is much larger than
> number of backends. But certainly skews in client application access
> patterns can violate this assumption.
> 3. Rescheduling is done at transaction level. So it is enough to have
> one entry in procarray for backend to correctly handle locks. Also
> transaction level pooling eliminates
> problem with false deadlocks (caused by lack of free executors in the
> pool). Also transaction level pooling minimize changes in Postgres
> core needed to maintain correct session context:
> no need to suspend/resume transaction state, static variables, ....
> 4. In the main Postgres query loop in PostgresMain  we determine a
> moment when backend is not in transaction state and perform select of
> sockets of all active sessions and choose one of them.
> 5. When client is disconnected, then we close session but do not
> terminate backend.
> 6. To support prepared statements, we append session identifier to the
> name of the statement. So prepared statements of different sessions
> will not interleave. As far as session is bounded to the backend, it
> is possible to use prepared statements.
>
> This is minimal plan for embedded session pooling I decided to
> implement as prototype.
>
> Several things are not addressed now:
>
> 1. Temporary tables. In principle them can be handled in the same way
> as prepared statements: by concatenating session identifier to the
> name of the table.
> But it require adjusting references to this table in all queries. It
> is much more complicated than in case of prepared statements.
> 2. Session level GUCs. In principle it is not difficult to remember
> GUCs modified by session and save/restore them on session switch.
> But it is just not implemented now.
> 3. Support of multiple users/databases/... It is the most critical
> drawback. Right now my prototype implementation assumes that all
> clients are connected to the same database
> under the same user with some connection options. And it is a
> challenge about which I want to know option of community. The name of
> the database and user are retrieved from client connection by
> ProcessStartupPacket function. In vanilla Posgres this function is
> executed by spawned backend. So I do not know which database a client
> is going to access before calling this function and reading data from
> the client's socket. Now I just choose random backend and assign
> connection to this backend. But it can happen that this backend is
> working with different database/user. Now I just return error in this
> case. Certainly it is possible to call ProcessStartupPacket at
> postmaster and then select proper backend working with specified
> database/user.
> But I afraid that postmaster can become bottleneck i this case,
> especially in case of using SSL. Also larger number of databases/users
> can significantly suffer efficiency of pooling if each backend will be
> responsible only for database/user combination. May be backend should
> be bounded only to the database and concrete role should be set on
> session switch. But it can require flushing backend caches
> whichdevalues idea of embedded session pooling. This problem can be
> easily solved with multithreaded Postgres where it is possible to
> easily reassign session to another thread.
>
> Now results shown by my prototype. I used pgbench with scale factor
> 100 in readonly  mode (-S option).
> Precise pgbench command is "pgbench -S -c N -M prepared -T 100 -P 1
> -n". Results in the table below are in kTPS:
>
> Connections
> Vanilla Postgres
> Postgres with session pool size=10
> 10
> 186
> 181
> 100
> 118
> 224
> 1000
> 59
> 191
>
>
>
> As you see instead of degrade of performance with increasing number of
> connections, Postgres with session pool shows stable performance result.
> Moreover, for vanilla Postgres best results at my system are obtained
> for 10 connections, but Postgres with session pool shows better
> performance for 100 connections with the same number of spawned backends.
>
> My patch to the Postgres is attached to this mail.
> To switch on session polling set session_pool_size to some non-zero
> value. Another GUC variable which I have added is "max_sessions" which
> specifies maximal number of sessions handled by backend. So total
> number of handled client connections is session_pool_size*max_sessions.
>
> Certainly it is just prototype far from practical use.
> In addition to the challenges mentioned above, there are also some
> other issues which should be considered:
>
> 1. Long living transaction in client application blocks all other
> sessions in the backend and so can suspend work of the Postgres.
> So Uber-style programming when database transaction is started with
> opening door of a car and finished at the end of the trip is
> completely not compatible with this approach.
> 2. Fatal errors cause disconnect not only of one client caused the
> problem but bunch of client sessions scheduled to this backend.
> 3. It is possible to use PL-APIs, such as plpython, but session level
> variables may not be used.
> 4. There may be some memory leaks caused by allocation of memory using
> malloc or in top memory context which is expected to be freed on
> backend exit.
> But it is not deallocated at session close, so large number of handled
> sessions can cause memory overflow.
> 5. Some applications, handling mutliple connections inside single
> thread and multiplexing them at statement level (rather than on
> transaction level) may not work correctly.
> It seems to be quite exotic use case. But pgbench actually behaves in
> this way! This is why attempt to start pgbench with multistatement
> transactions (-N) will fail if number of threads (-j) is smaller than
> number of connections (-c).
> 6. The approach with passing socket descriptors between processes was
> implemented only for Unix and tested only at Linux, although is
> expected to work also as MacOS and other Unix dialects. Windows is not
> supported now.
>
> I will be glad to receive an feedback and suggestion concerning
> perspectives of embedded connection pooling.
>
> --
> Konstantin Knizhnik
> Postgres Professional:http://www.postgrespro.com
> The Russian Postgres Company

Attached please find new version of the patch with few fixes.
And more results at NUMA system with 144 cores and 3Tb of RAM.

Read-only pgbench (-S):

#Connections\kTPS
Vanilla Postgres
Session pool size 256
1k
1300 1505
10k
633
1519
100k
- 1425

Read-write contention test: access to small number of records with 1% of
updates.

#Clients\TPS Vanilla Postgres Session pool size 256
100 557232 573319
200 520395 551670
300 511423 533773
400 468562 523091
500 442268 514056
600 401860 526704
700 363912 530317
800 325148 512238
900 301310 512844
1000 278829 554516

So, as you can see, there is no degrade of performance with increased number of connections in case of using session pooling.

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