+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.knizh...@postgrespro.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 inov...@pivotal.io -- (Mobile) 408-230-6491 https://www.youtube.com/GreenplumDatabase
