Re: Builtin connection polling

+1 to the concept... A lot of user could benefit if we did this in a good
way.

On Wed, Jan 17, 2018 at 8:09 AM, Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> 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 which devalues 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
>
>

--
Ivan Novick, Product Manager Pivotal Greenplum
inovick(at)pivotal(dot)io -- (Mobile) 408-230-6491
https://www.youtube.com/GreenplumDatabase