[pgpool-general: 4106] Re: Scaling Issues

[Tatsuo Ishii]

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

The thundering-herd problem has been discussed recently (see
[pgpool-general: 3934] accept() scalability issues)
http://www.pgpool.net/pipermail/pgpool-general/2015-August/003992.html

Next major version of pgpool-II will have a parameter to overcome the
problem.

http://www.pgpool.net/pipermail/pgpool-committers/2015-October/002711.html

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

This is new to me. Is there any test case to reproduce the problem?

> 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

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp