Help with pgpool settings,

PostgreSQL
1

Hi all, longtime MySQL/ProxySQL guy here trying to come up to speed with PostgreSQL and pgpool. A bit steeper than proxysql was. I’m encountering a few issues and can’t find any discussion as to a situation where on my pool, I see a lot of “pgpool: wait for connection request” on the host. My setup:

7 Percona Postgresql 15, one primary, the rest standbys as backend
1 pgpool-II version 4.5.0 (percona) server

I’m trying to find the optimal settings for connections, timeouts, etc. as we’ve had to do a bunch of testing to handle the connections needed for the app. I’ve ended up setting the pg backends to max connections of 1000, then pgpool, num_init_children 500, max_pool 10, child_max_connections to 1000, client_idle_limit 60. Am I missing something obvious?

Also, tps using pgbench is around 220 throught he pool, but direct to the master is 340. Is this a normal decrease, or yet another thing I’m missing?

yes, I am using memcached query cache.

Thank you!

ps. Boxes are all 32 procs, 128 GB RAM, ubuntu 22.04 bhvye VMs, running on smartOS/smartCloud

2

7 Percona Postgresql 15, one primary, the rest standbys as backend
1 pgpool-II version 4.5.0 (percona) server

I’m trying to find the optimal settings for connections, timeouts, etc. as we’ve had to do a bunch of testing to handle the connections needed for the app. I’ve ended up setting the pg backends to max connections of 1000, then pgpool, num_init_children 500, max_pool 10, child_max_connections to 1000, client_idle_limit 60. Am I missing something obvious?

num_init_children determines the number of concurrent connections you want to support. Setting it to 500 allows for a maximum of 500 concurrent connections. This setting should be based on your application requirements.

The max_pool value depends on the number of distinct [db-name, db-user] pairs the application uses to connect to the database service. Typically, applications use one or two db-users, so this value is usually kept below 5. A higher max_pool value increases the max_connections requirements on the backend.

If you use the settings for max_pool and num_init_children mentioned above, the ideal setting for max_connections on PostgreSQL would be num_init_children * max_pool (500 * 10 = 5000). So, I suggest adjusting the max_pool and num_init_children values or increasing the max_connections on PostgreSQL.

For higher values of num_init_children, you should consider enabling serialize_accept in the Pgpool configuration. However, serialize_accept is very load and system-dependent, so I recommend benchmarking with and without serialize_accept under your expected load. Additionally, you should enable dynamic_process_management mode with these num_init_children settings.

More information on process management mode can be found here.

Regarding client_idle_limit, this depends on your workload and application expectations. If your application does not hold connections and only creates a connection when needed, performs some tasks, and then disconnects, this timeout can be as low as 10 seconds. An idle connection from the application, in this case, would indicate a problem, as it has not released the connection after use.

Lastly, if you are not setting max_connections on PostgreSQL to num_init_children * max_pool, then enable connection_life_time as well.

Also, tps using pgbench is around 220 throught he pool, but direct to the master is 340. Is this a normal decrease, or yet another thing I’m missing?

Pgpool-II performs better than direct connection when database and workload size crosses a certain limit. See the blog for reference

https://www.highgo.ca/2019/09/06/can-you-gain-performance-with-pgpool-ii-as-a-load-balancer/

Thanks
Best regards
Muhammad Usama

3

Thank you Muhammad,

I will try these settings. Also, per our other issue wrt to the bug with pg_catalog queries, I wanted to make sure you saw my reply that 4.3 Postgresql.org’s packages also have this bug. We built 4.5.2 and it doesn’t seem to have the bug.