[pgpool-general: 8016] Re: connection is not available

[Tatsuo Ishii]

>> I hardly understand your system configuration. So I cannot tell which
>> is (or any other) better. Can you clarify from the perspective of
>> PostgreSQL server? I mean:
>>
>> 1) I assume you have 1 PostgreSQL primary and some number of standby
>>    servers in streaming replication configuration. They have exactly
>>    same database cluster. Am I correct?
>>
>> 2) If #1 is correct, how many databases and database users do you
>> have? My wild guess is each "service" is related to each database. So
>> you have 5 databases? Number of database users is same as "connection
>> users"? If so, probably number of database users is 100?
> 
> [NM]: Yes you are right. 5 services-applications. Each connects to its
> own dedicated database.
> 
> So there are 5 services-applications and 5 databases.
> 
> Each services-application connects to its database with only one(!)
> database username.
> 
> Total number of application consumers - 100. They work with all
> services-applications.
> 
> The main question - since the application connects to the database
> with only one username, it probably makes no sense to set the max_pool
> parameter to more than 1. Right?

No. So there are 5 databases (suppose d1 to d5) and one username
(suppose u1). There 5 database and username pairs (d1, u1), (d2, u1),
(d3, u1), (d4, u1) and (d5, u1). Since each connection is distingushed
by (database, username) pair, there will be 5 connection pools will be
in each pgpool process in a long run. This means if max_pool is lower
than 5, certain connection is not cached (the oldest entry is reused),
which may lead to performance loss.

> Total number of application consumers - 100. They work with all
> services-applications.

If num_init_children is 100 (I assume 100 application consumers
connect to the system concurrently) and max_pool is 5, then 5*100 =
500 connections will be made to a PostgreSQL server. So you need to
set max_connections parameter of PostgreSQL to 500 at least.

However you can reduce max_connections by lowering max_pool. This will
increase the chance to create new connection from pgpool to
PostgreSQL, which is not good. However this may increase the
PostgreSQL server's performance because less max_connections consume
less resource.  So there's a chance to tune the balance between
max_pool and max_connections.

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