[pgpool-general: 7293] Re: pgpool appears to stop working for a few minutes

[Tatsuo Ishii]

> We have been recently having intermittent problems with our pgpool setup
> that causes service outages for usually 5-20 minutes, and we cannot figure
> out what the problem is.
> 
> [image: server_stats.jpg]
> https://i.imgur.com/JodLFhl.jpg
> 
> 
> As you can see from the graph, incoming TCP connections jump up very high
> (usually up to num_init_connections * backlog multiplier) and then we start
> seeing the listen queue socket overflows. We have somaxxcon set quite high
> on the server 8000 and the num_init_connections * listen_backlog_multiplier
> is 150 * 10
> 
> pgpool doesn't show any errors, but doesn't seem to be working correctly.
> If I run netstat -n, I see a lot of ESTABLISHED connections (a lot more
> than num_init_connetions * max_pool) from the PHP front-end web servers to
> pgpool.

I think this is normal, since afeter an incoming connection is
accepted in the listen queue, the state will be ESTABLISHED.

> I see the correct number (num_init_connections * backlog
> multiplier) of ESTABLISHED connections from pgpool to the back-end postgres
> servers, and there is no major load on these servers. Also, I can see there
> are available connections on the back-end postgres servers.
> 
> This problem is more likely to happen during a period of high volume, but
> we do also see it happen during quieter periods. The pgpools logs don't
> show any errors or issues.
> If we restart pgpool, everything seems to go back to normal and working
> correctly again.
> 
> Attached is our configuration, and we would love to get some assistance on
> this.
> We are running pgpool v4.1.3 on Ubuntu 16.04.3
> 
> I have attached the netstat output for when the server is operating
> correctly, and when it is erroring and overflowing. 10.0.0.17 and 10.0.0.18
> are our front-end PHP servers. 10.0.0.10 and 127.0.0.1 are our back-end
> postgres servers.
> 
> If anyone can help, I would much appreciate it.

My guess is, postgres's query processing speed is not just high enough
to process client requests. In this case reserved_connections = 1 (or
so) could mitigate the situation. If this parameter is set to more
than 0, pgpool will not accept incoming connections if there's already
(num_init_children - reserved_connections). However this will give an
error to PHP side ("Sorry too many connections already").

Another possible solution is turning on serialize_accept. We usually
recommend this to avoid "thundering herd problem". However you said
that the server pgpool runs on does not show high load, which is the
Singh of "thundering herd problem". So this setting may not give any
effect.

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