[pgpool-general: 4104] Scaling Issues

[Joe Schaefer]

Hi,

Love the product so far but have run into a few bugs that will soon be
showstoppers for us.  Here's what we've found:

Our application provides persistent connections to a web socket app,
potentially supporting hundreds of concurrent users.  At one site we have
num_init_children set to 2048 because we are replicating hundreds of
databases across four different master-slave postgres nodes.  The problem
here isn't really a big deal, but it's something that should be worked out
because no similar server-side software still possesses thundering-herd
type issues like we do with pgpool.  Our typical load averages for the
pgpool master is around 200-400.  CPU idle is fine, so we're not concerned
other than our load average statistics for that host are out of whack.

Secondly, we have two-postgres-node setup with tens of databases and
num_init_children set to 512.  It is basically the same application but for
a different site.  What happens here is far more concerning: when we see
about 60+ concurrrent users (and hence have about 200 pgpool backend
connections) pgpool stops correctly shipping write queries to the master.
Instead it ships a certain percentage to the slave, which breaks our
application completely.

Of the two, my real concern lies with the second situation as users are
impacted by pgpool's behavior during heavy load periods.  This doesn't seem
to happen under light load with less than 20 users and hence less than 100
backend connections.

Anyone seen anything similar and know how to workaround it?  For kicks here
are my whitelist rules for sql functions:

white_function_list = 'random,count,extract,date_part'
                                   # Comma separated list of function names
                                   # that don't write to database
                                   # Regexp are accepted
black_function_list = 'currval,lastval,nextval,setval,.*'
                                   # Comma separated list of function names
                                   # that write to database
                                   # Regexp are accepted
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151010/e9bc14e3/attachment.html>