作者
digoal
日期
2019-09-22
标签
PostgreSQL , postgrespro
背景
postgrespro 企业版支持内置连接池功能，目前正在合并到pg的社区版本，很快大家都可以用上了。
https://commitfest.postgresql.org/24/2067/
设计可参考： 《PostgresPro buildin pool(内置连接池)版本 原理与测试》
现在的版本已经改良，例如lister port只有一个了，不需要配置多个（侵入应用）。
类似oracle的shared server，少数worker进程服务于更多的客户端并发。
When establishing a connection, PostgreSQL spawns a separate backend process for each client. For a large number of clients, this model can cause high consumption of system resources and lead to significant performance degradation, especially on multicore systems. The reason is high contention for PostgreSQL resources between backends. Besides, the size of many PostgreSQL internal data structures is proportional to both the complexity of algorithms for these structures and the number of active backends.
Most production Postgres Pro installations reduce the number of spawned backends using external tools, such as J2EE, odyssey, or pgbouncer, one of the most popular connection poolers for Postgres Pro. However, external connection poolers require additional efforts for installation, configuration, and maintenance. If the pooler is single-threaded, like pgbouncer, you also have to launch multiple pooler instances as it can otherwise cause a bottleneck on high-load systems.
To address these challenges, Postgres Pro Enterprise provides an experimental built-in connection pooler. Unlike external solutions, it does not require any additional maintenance and does not introduce any limitations for clients. With built-in connection pooling enabled, clients can continue using session configuration parameters, prepared statements, and temporary tables as if there is no proxy.
术语
pool worker：负责桥接client和pool worker进程的工作进程（监听client请求，根据database/user并使用pool的scheduler算法选中shared backend进程对接client。）。相当于负责了监听+路由的工作。
shared backend process：每个database/user组合，最多允许开启多少个shared backend进程。
dedicated backend process：默认情况下template0, template1, postgres几个库都是dedicated_databases。也就是说这几个库的连接，每个连接都会fork一个dedicated backend process。
参数
max_sessions (integer)
The maximum number of client sessions that can be handled by one backend when connection pooling is switched on. This parameter does not add any memory or CPU overhead, so specifying a large max_sessions value does not affect performance. If the max_sessions limit is reached, the backend stops accepting connections. Until one of the connections is terminated, attempts to connect to this backend result in an error.
The default value is 1000. This parameter can only be set at server start.
一个shared backend进程同时允许被多少个会话连接。
session_pool_size (integer)
Enables connection pooling and defines the maximum number of backends that can be used by client sessions for each database/user combination.
The default value is zero, so connection pooling is disabled.
每个database/user组合，最多允许开启多少个shared backend进程。
connection_pool_workers (integer)
这个设计改良了，之前每一个shared server都需要一个监听port，现在port只有一个，postmaster，然后重定向到pool worker(listener)，再由pool worker来调度到backend process.
这是之前的设计。 《PostgresPro buildin pool(内置连接池)版本 原理与测试》
Number of connection listeners used to read client startup packages. If connection pooling is enabled, Postgres Pro Enterprise server redirects all client startup packages to a connection listener. The listener determines the database and user that the client needs to access and redirects the connection to an appropriate backend, which is selected from the pool in accordance with the session_schedule policy. This approach allows to avoid server slowdown if a client tries to connect via a slow or unreliable network.
The default value is 2.
负责桥接client和pool worker进程的工作进程（监听client请求，根据database/user并使用pool的scheduler算法选中shared backend进程对接client。）。
相当于负责了监听+路由的工作。指这个工作进程的个数。
The max_sessions parameter specifies the maximal number of sessions that can be handled by a single backend. Thus, the maximal number of connections for one database/user is limited by the session_pool_size * max_sessions value. The max_sessions setting affects only the potential size of the queue on each backend and does not cause any essential negative impact on resource consumption. The default value is 1000.
一个shared backend process最多同时处理max_sessions个并发会话，一个database/user对最多允许多少个shared backend process取决于参数session_pool_size。
dedicated_databases (string)
Specifies the list of databases for which connection pooling is disabled, regardless of the session_pool_size value. For such databases, a separate backend is forked for each connection. By default, connection pooling is disabled for template0, template1, and postgres databases.
默认情况下template0, template1, postgres几个库都是dedicated_databases。也就是说这几个库的连接，每个连接都会fork一个dedicated backend process。
restart_pooler_on_reload (boolean)
Restart connection pool workers once pg_reload_conf() is called. The default value is false.
是否在收到reload信号时，重启shared backend。
如果要关闭空闲的shared backend process，可以设置restart_pooler_on_reload为true，然后执行pg_reload_conf()，将释放空闲的shared backend process.
hold_prepared_transactions (boolean)
Do not reschedule the backend while the current session has unfinished prepared transactions. Scheduling several sessions with conflicting prepared transactions on the same backend can cause undetectable deadlocks.
The default value is off.
当设置为on时(建议实际使用时确认)：当前会话(即client)使用了两阶段事务时，如果会话发起的两阶段事务未结束（commit or rollback），那么这个会话对应的shared backend进程将不会被其他会话使用（占有shared backend进程，直到2pc结束）。
If you are going to use prepared transactions (2PC) in pooled sessions, make sure to enable the hold_prepared_transactions configuration parameter, which forbids rescheduling the backend to another session until all prepared transactions in the current session are committed or rolled back. It prevents conflicts between prepared transactions of several sessions on the same backend, which can cause undetectable deadlocks.
session_schedule (enum)
Specifies scheduling policy for assigning a session to a backend if connection pooling is enabled:
-
round-robin — cyclically scatter sessions between pooled backends.
-
random — choose a backend in the pool at random.
-
load-balancing — choose the backend with the lowest load average. The backend load average is estimated by the number of ready events at each reschedule iteration.
The default policy is round-robin.
idle_pool_worker_timeout (integer)
Terminate an idle connection pool worker after the specified number of milliseconds. The default value is 0, so pool workers are never terminated.
当一个shared backend空闲超过一定时间（毫秒）时（指这段时间都没有收到客户请求），自动terminate。
Releasing Pooled Resources
Once started, pooled backends continue running even if all its clients get disconnected. While it allows to reuse the same backends for future connections, it may sometimes be required to shut down a backend that is no longer in use. For example, you cannot drop a database or a user while at least one backend in the corresponding connection pool is still running.
To terminate backends that are no longer required without a server restart, do the following:
-
Set the restart_pooler_on_reload variable to true.
-
Call the pg_reload_conf() function to reload the server configuration.
Alternatively, you can set the idle_pool_worker_timeout configuration parameter to automatically terminate unused backends and release system resources after the specified timeout.
参考
https://postgrespro.com/docs/enterprise/11/runtime-config-connection#RUNTIME-CONFIG-CONNECTION-SETTINGS
https://postgrespro.com/docs/enterprise/11/connection-pooling
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
