postgresql 一个好的 PgPool II 配置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10362032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A good PgPool II configuration
提问by Murali_dharan_raju
I have been trying to configure PgPool to accept a requests of about 150. Postgres server is configured to accept only 100 connections. Anything beyond 100 need to be pooled by PgPool. I don't seem to get that. I only require PgPool to queue the requests, my current configuration does not do that. From my JMeter test, when I try to get connection beyond 100, postgres gives me an error saying PSQL error: sorry, too many clients.
我一直在尝试将 PgPool 配置为接受大约 150 个请求。Postgres 服务器配置为仅接受 100 个连接。超过 100 的任何内容都需要由 PgPool 合并。我似乎不明白。我只需要 PgPool 将请求排队，我当前的配置不这样做。从我的 JMeter 测试中，当我尝试获得超过 100 的连接时，postgres 给我一个错误，说 PSQL error: sorry, too many clients。
I only have configured PGPool with the following parameters :
我只使用以下参数配置了 PGPool：
listen_address = 'localhost'
port = 9999
backend_hostname0 = 'localhost'
backend_port0 = 5432
num_init_children = 100
max_pool = 4
child_life_time =120
child_max_connections = 0
connections_life_tome = 120
client_idle_limit = 0
Since I only require PgPool to Queue the extra connections requests, is the above configuration correct? Please advise on the proper configuration.
由于我只需要 PgPool 来排队额外的连接请求，上面的配置是否正确？请建议正确的配置。
回答by user2606721
The 'child_max_connections' in pgpool is NOT the maximum allowed connections to the DB. It is the number of times a pooled connection can be used before it terminates and restarts. It is there to recycle connection threads and stop memory leaks.
pgpool 中的“child_max_connections”不是数据库的最大允许连接数。它是池连接在终止和重新启动之前可以使用的次数。它用于回收连接线程并阻止内存泄漏。
The formula of max_pool x num_init_children describes the maximum number of connections that pgpool will make to Postgresql. Obviously, this needs to be less than the 'max_connections' set in postgresql, otherwise pgpool marks the DB as an unavailable backend. And if you have some DB connections reserved for admin use, you need to reduce the number of pgpool connections further.
max_pool x num_init_children 的公式描述了 pgpool 与 Postgresql 建立的最大连接数。显然，这需要小于 postgresql 中设置的“max_connections”，否则 pgpool 会将数据库标记为不可用的后端。如果您保留了一些数据库连接供管理员使用，则需要进一步减少 pgpool 连接的数量。
So, what I am saying is that the 'max_connections' in the formula is the parameter set in postgresql.conf. Setting 'child_max_connections' to 100 in the comment above just means that the pgpool connection is closed and reopened every 100 times it is used.
所以，我说的是公式中的'max_connections'是postgresql.conf中设置的参数。在上面的注释中将 'child_max_connections' 设置为 100 仅意味着 pgpool 连接每使用 100 次就会关闭并重新打开。
回答by kgrittn
The first thing is to figure out what you want as your maximum pool size. PostgreSQL performance (both in terms of throughput and latency) is usually best when the maximum number of active connections is somewhere around ((2 * number-of-cores) + effective-spindle-count). The effective spindle count can be tricky to figure -- if your active data set is fully cached, count it as zero, for example. Don't count any extra threads from hyperthreading as cores for this calculation. Also note that due to network latency issues, you may need a pool slightly larger than the calculated number to keep that number of connections active. You may need to do some benchmarks to find the sweet spot for your hardware and workload.
第一件事是弄清楚您想要什么作为最大池大小。当最大活动连接数约为 ((2 * number-of-cores) + Effective-spindle-count) 时，PostgreSQL 性能（在吞吐量和延迟方面）通常是最好的。有效主轴计数可能很难计算——例如，如果您的活动数据集已完全缓存，则将其计为零。不要将来自超线程的任何额外线程视为此计算的核心。另请注意，由于网络延迟问题，您可能需要一个比计算出的数量略大的池，以保持该数量的连接处于活动状态。您可能需要进行一些基准测试才能找到适合您的硬件和工作负载的最佳位置。
The setting you need to adjust is child_max_connections, with num_init_childrenkept less than or equal to that.
您需要调整的设置是child_max_connections，num_init_children保持小于或等于。
