[Pgpool-general] Replication and number of postgres processes

[John Andre Taule]

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?

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?

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
>> 
>