[pgpool-general: 6801] Re: High load average on pgpool server

Bo Peng pengbo at sraoss.co.jp
Fri Dec 27 13:39:44 JST 2019


"max_pool * num_init_children" is the number of connections to PostgreSQL server.
You need to increase "num_init_children" to accept 2000-3000 connections.

On Sun, 22 Dec 2019 12:53:16 +0100
Radosław Szczygieł <radoslaw.szczygiel at interia.pl> wrote:

> I try change this settings. In few months after end of migration process in my pg cluster we predict that number of connections will be 2 times more than now, maybe 2000-3000 connections. Should I increase only max_pool parameter and decrease num init children?RegardsRadosław Szczygieł Temat: Re: [pgpool-general: 6797] Re: High load average on pgpool serverData: 2019-12-22 5:56Nadawca: "Bo Peng" Adresat: "Radosław Szczygieł" ; DW: "pgpool-general at pgpool.net" ; &gt; Hi,
> &gt; 
> &gt; I have checked your conection setting.
> &gt; I found you are setting "num_init_children = 500", and it is a large number.
> &gt; 
> &gt; =====
> &gt; num_init_children = 500
> &gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Number of concurrent sessions allowed
> &gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)
> &gt; =====
> &gt; 
> &gt; You may hit the "thundering herd problem".
> &gt; Try to set "serialize_accept = on" to check if the problem is resolved.
> &gt; 
> &gt; See more details about "serialize_accept":
> &gt; 
> &gt; https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html#GUC-SERIALIZE-ACCEPT
> &gt; 
> &gt; On Fri, 20 Dec 2019 14:02:45 +0100
> &gt; Radosław Szczygieł  wrote:
> &gt; 
> &gt; &gt; Could You check my child and connection life time settings. Maybe there is a problem....Is there a best practice for life time settings?Temat: [pgpool-general: 6797] Re: High load average on pgpool serverData: 2019-12-20 10:51Nadawca: "Radosław Szczygieł" Adresat: "Bo Peng" ; DW: "pgpool-general at pgpool.net" ; I use the latest version of pgpool and pg12. I think that this high load cause after upgrade from 4.0 to 4.1. Before upgrade load average was about 2-5.Temat: Re: [pgpool-general: 6795] High load average on pgpool serverData: 2019-12-20 10:36Nadawca: "Bo Peng" Adresat: "Radosław Szczygieł" ; DW: pgpool-general at pgpool.net; &gt; Hi,
> &gt; &gt; &gt; 
> &gt; &gt; &gt; &gt; QUESTION: What can cause such an increase in server load?
> &gt; &gt; &gt; 
> &gt; &gt; &gt; A large number of connections may increase server load.
> &gt; &gt; &gt; Which version of pgpool do you use?
> &gt; &gt; &gt; 
> &gt; &gt; &gt; On Thu, 19 Dec 2019 13:32:26 +0100
> &gt; &gt; &gt; Radosław Szczygieł  wrote:
> &gt; &gt; &gt; 
> &gt; &gt; &gt; &gt; Hello PGPOOL team,I have a problem regarding pgpool server load. Our cluster consists of 2 pgpool servers (watchdog) and 2 postgresql servers (master-slave). Within a few days of restarting the pgpool service, the average server load is at a high level load average : 15-20 on 15 min load. After restart pgpool load average drops to low values, but every day there is a successive increase in load average (but count of connections is the same all time). At the highest load average, the RAM usage is about 6,5GB, but the processor is used to the max.QUESTION: What can cause such an increase in server load? Currently, our PG cluster has a size of about 600GB, about 1000 connections to several bases, of which in many cases connections are short but there are many at once with high frequency. Pgpool server with centos 7 have Intel(R) Xeon(R) CPU X5650 @ 2.67GHz and 64GB ram.Below I present part of the configuration of pool connection and life time and attach scr
 een from I
> &gt;  cinga moni
> &gt; &gt; &gt;  tor of load average last 2 weeks.# -----listen_backlog_multiplier = 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Set the backlog parameter of listen(2) to&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # num_init_children * listen_backlog_multiplier.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)serialize_accept = off&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
> &gt;  bsp;&amp;nb
> &gt; &gt; &gt;  sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # whether to serialize accept() call to avoid thundering herd problem&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)reserved_connections = 0#-----------num_init_children = 500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Number of concurrent sessions allowed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)max_pool = 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
> &gt;  sp;&amp;nbs
> &gt; &gt; &gt;  p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Number of connection pool caches per connection&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)# - Life time -child_life_time = 300&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; # Pool exits after being idle for this many secondschild_max_connections = 2005&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Pool exits after receiving that many connections&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&
> &gt;  ;&nbsp;
> &gt; &gt; &gt;  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0 means no exitconnection_life_time = 1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Connection to backend closes after being idle for this many seconds&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0 means no closeclient_idle_limit = 4000Regards,Radoslaw Szczygiel 
> &gt; &gt; &gt; 
> &gt; &gt; &gt; 
> &gt; &gt; &gt; -- 
> &gt; &gt; &gt; Bo Peng 
> &gt; &gt; &gt; SRA OSS, Inc. Japan
> &gt; &gt; &gt; @interia.pl&gt;@sraoss.co.jp&gt;
> &gt; 
> &gt; 
> &gt; -- 
> &gt; Bo Peng 
> &gt; SRA OSS, Inc. Japan
> &gt; 

Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan

More information about the pgpool-general mailing list