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

[Nikolay Mastilo (HM)]

Ok, I understood. Thanks a lot!

Best regards,
Nikolay

05.02.2022 9:58, 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