Re: pgpool-II (max_pool and num_init_children)

Tatsuo Ishii wrote:
>> I'm trying to get a handle on sane values for these two parameters. I
>> assume that they should somehow correlate to my existing
>> max_connections in my postgresql.conf file. Anyone using pgpool-II care
>> to comment?
>>
>> I'm sure it's a balancing act between how many connections you want
>> verses how many cached connections you have, but not sure how to
>> properly assess this issue.
>>
>> (I've tried posting to the pgpool list, but it's apparently unavailable
>> at this time)
>
> Here is an recommended rule:
>
> max_pool*num_init_children <= (max_connections - superuser_reserved_connections)
>
> Because num_init_children defines the max concurrent connections
> pgpool-II can accept, you might want to keep num_init_children as
> large as possible. In this case you could lower max_pool to as low as
> 1, which may cause performance degration because of low effect of
> connection cache if you would have more than 1 user/database
> combinations. However lower max_pool will not any error or any
> problems except performance.

We have a complicated set up. We have 13 databases with 12 postmasters.
So that means 2 databases share a postmaster, the rest have their
own. Consequently, separate pools for each postmaster. We will
frequently have 20-30 or more users connecting to any one database,
although some databases are much lower. This is further complicated by
a web application that also connects to the databases.

To complicate matters even further, we will likely have multiple pools
for each database, in order to give priority to local users over the web
access. In other words, a large pool of connections for local users,
and a smaller pool for web access.

That being said, I'm assuming:

(max_pool1*num_init_children) + (max_pool2*num_init_children) <=
(max_connections - superuser_reserved_connections)

> If you want to use query cancelation frequetntly, the formula above
> will become:
>
> max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections)
>
> because you need extra connection to use query cancelation.

I don't believe we'll have frequent query cancellations.

> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson