[Pgpool-general] Replication and number of postgres processes

[Tatsuo Ishii]

> I might be using the wrong terminnology as I've just started experimenting
> with this system a few weeks ago.
> 
> My current setup is something like this:
> 
> client
> - 
> load balance
> -
> Httpd - httpd - httpd ... httpd (current setup have 6 servers here)
> -
> postgres
> 
> What I hoped to achieve with pgpool was something like this:
> Client
> -
> Load balance
> -
> Httpd/pgpool/postgres - httpd/pgpool/postgres - httpd/pgpool/postgres ...
> httpd/pgpool/postgres
> 
> I have this setup running in a test environment already, and that's why I
> wondered if my observation on max_connections for postgresql.conf when
> adding more servers to the httpd/pgpool/postgres layer is correct. I have 3
> httpd/pgpool/postgres servers in my test environment.
> 
> So far I've had to adjust max_connection to roughly 3 x MaxClients on each
> server (and of course some extra for admin stuff). So if I move this setup
> to live, adding the 6 existing servers to the 3 new, I would have to
> increase max_connection on each server to roughly 9 x MaxClients for http?

Yes.

> Our service can run into worst case, that is all clients trying inserts or
> updates at the same time. So my observation so far was that each "update"
> fired 1 connection on each of the 2 other servers. So when all 3 servers had
> 256 clients connected trying an "update", it fired 758 connections on all 3
> servers. I guess this is just what I have to expect with synchronous
> replication?

Yes.

However allowing 758 connections to PostgreSQL does not neccesary mean
you earn best throughput. It is possible that for example:

for each httpd/pgpool/PostgreSQL:

MaxClients = 256 num_init_children = 128 max_connections = 128

This will not allow 256 concurrent DB access, rather only 128. So
129th DB access will be blocked until one of DB connections to pgpool
is closed. This may or may not bring better performance than
num_init_childre = 256 case, depending on your hardware.

I recommend you to tweak num_init_children util you find a "sweat spot".
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Regards
> John Andre Taule 
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
> Sent: 25. januar 2011 01:07
> To: post at johnandre.net
> Cc: pgpool-general at pgfoundry.org
> Subject: Re: [Pgpool-general] Replication and number of postgres processes
> 
>>From pgpool doc:
> 
>   <dt>max_pool</dt>
>   <dd>
>       <p>The maximum number of cached connections in pgpool-II
>       children processes. pgpool-II reuses the cached connection if an
>       incoming connection is connecting to the same database with the
>       same username. If not, pgpool-II creates a new connection to the
>       backend. If the number of cached connections exceeds max_pool,
>       the oldest connection will be discarded, and uses that slot for
>       the new connection.
> 
> max_pool is only related to number of username/database combo. Say, you have
> 2 combos (for example, "user1/mydb" and "user2/mydb") you can assumes it as
> a constant 2 in the fomula repsented in the pgpool doc.
> 
> max_pool*num_init_children <= (max_connections -
> superuser_reserved_connections) (no query canceling needed)
> max_pool*num_init_children*2 <= (max_connections -
> superuser_reserved_connections) (query canceling nee
> 
> BTW  I'm not sure what you mean by "replication servers".
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> Yes, I've seen that, but going from 3 (physical) replication servers 
>> to 5 replication servers, would I need to increase max_pool in that 
>> formula from
>> 4 to 6? Or a more extreme example would going from 2 replication 
>> servers to
>> 128 with an expected 100 simultaneous "UPDATE" queries on each server 
>> result in going from 200 postgres-connections on each server to 12800 
>> postgres-connections on each server?
>> 
>>>From my experiment with 3 servers it looked like the need for 
>>>connections on
>> each server would increase when I add servers. I just want to make 
>> sure that is the right conclusion before making a decision on how to use
> pgpool.
>> 
>> Regars
>> John Andre Taule
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>> Sent: 24. januar 2011 08:39
>> To: post at johnandre.net
>> Cc: pgpool-general at pgfoundry.org
>> Subject: Re: [Pgpool-general] Replication and number of postgres 
>> processes
>> 
>> There is a formula for this in the pgpool documentation.
>> See the "num_init_children" section.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>> 
>>> Will the number of postgres processes needed increase by how many 
>>> servers is used for replication?
>>> Example 100 connections by the frontend application.
>>> 2 replication servers: 200 connections
>>> 3 replication servers: 300 connections N replication servers: n*100 
>>> connections Is my understanding of resources needed correct?
>>> Regards
>>> John Andre Taule
>>> 
>> 
>