[pgpool-general: 5713] PGpool Configuration

[Rishi]

Team,

We want to set up a High availability with Postgres and while doing the
research found PGPool is a great tool. But we are finding difficulty to
achieve on what we want.

Current Setup :

App server : RoR with unicorn + nginx
DB : Postgresql Master and Slave
Middleware : PGPool 3.6.2

Configuration :

Webserver : 4cores with 14G memory , 30 unicorn workers and DBpool :800.
Totally 3 servers with Azure load balancer

DB server : 16cores with 28GB Both Master and Slave (DB has 25million
record, mix match of transnational & web application )

PGpool Confugration : 4cores with 28GB memory. configuration are below.

#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------

connection_cache = on
                                   # Activate connection pools
                                   # (change requires restart)

                                   # Semicolon separated list of queries
                                   # to be issued at the end of a session
                                   # The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
                                   # The following one is for 8.2 and before
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'



#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Concurrent session and pool size -

num_init_children = 3000
                                   # Number of concurrent sessions allowed
                                   # (change requires restart)
max_pool = 2
                                   # Number of connection pool caches per
connection
                                   # (change requires restart)

# - Life time -

#child_life_time = 300
child_life_time = 60
                                   # Pool exits after being idle for this
many seconds
child_max_connections = 1000
                                   # Pool exits after receiving that many
connections
                                   # 0 means no exit
connection_life_time = 60
                                   # Connection to backend closes after
being idle for this many seconds
                                   # 0 means no close
client_idle_limit = 60
                                   # Client is disconnected after being
idle for that many seconds
                                   # (even inside an explicit transactions!)
                                   # 0 means no disconnection


Rest as default.


>From the above setup we are trying hit the concurrent users of 4k... But at
some point of time PGpool load got increased and memory is fully occupied
even if i increase the Memory full memory got occupied. This resulting
requests slow down and request per second throughput and failures.


But without PGpool directly connecting to Master works fine without errors
and much better throughput.

We would like to scale 20k concurrent users with PGPool setup. We would
like to know whether PGpool is enterprise standard to work on.. Else we
have to look for other solutions.

Or are we missing anything in Pgpool configuration. Please let us know.

-- 

Regards,
Rishi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170906/1fa69296/attachment.html>