<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<div name="messageReplySection" style="font-size: 14px; font-family: -apple-system, BlinkMacSystemFont, sans-serif;">On Feb 27, 2019, 10:51 AM -0800, Stephen Eilert <contact@stepheneilert.com>, wrote:<br />
<blockquote type="cite" style="margin: 5px 5px; padding-left: 10px; border-left: thin solid #1abc9c;">
<div name="messageReplySection" style="font-size: 14px; font-family: -apple-system, BlinkMacSystemFont, sans-serif;">On Feb 26, 2019, 6:27 PM -0800, Tatsuo Ishii <ishii@sraoss.co.jp>, wrote:<br />
<blockquote type="cite" style="margin: 5px 5px; padding-left: 10px; border-left: thin solid #e67e22;">
<blockquote type="cite" style="margin: 5px 5px; padding-left: 10px; border-left: thin solid #3498db;">Hi,<br />
<br />
Apologies if this has been discussed before, but if so I can’t find a relevant thread.<br /></blockquote>
<br />
I think this has not been discussed before. Thank you for the report.<br />
<br />
<blockquote type="cite" style="margin: 5px 5px; padding-left: 10px; border-left: thin solid #3498db;">We are experiencing some strange behavior on our PGPool nodes. In some of them, load average is well in excess of 100 – although CPU is over 70% idle (rest spent in system), so this pointed to I/O. Checking on AWS, we see that there is a lot of disk activity, including writes. This was surprising, as we did not expect the pgpool machines to have any significant disk usage, nor we can find anything in the documentation that would indicate it to be the case.<br />
<br />
After some sysdig usage, the only file getting heavily written to appears to be /var/log/postgresql/pgpool_status<br />
<br />
It is multiple 6 bytes writes a second, all with the same data, by pgpool. So this shouldn’t be a problem as it is not too much data. Except that it seems to be getting flushed right away (fsync?).<br />
<br />
Is this behavior expected? The PG node status have not changed at any time, there are two nodes, always up. Why would the file get rewritten dozens (even hundreds, depending on load) of times per second?<br />
<br />
Would this be caused by a misconfiguration?<br /></blockquote>
<br />
I appears that every time a client connects to Pgpool-II, and<br />
Pgpool-II creates new connections to PostgreSQL node, it issues<br />
fsync(). Maybe you disabled connection cache or set very short life<br />
time for connection cache?<br />
<br />
Anyway, I think current behavior of Pgpool-II is not best optimized<br />
and I would like to propose attached patch to enhance it. It eliminate<br />
the necessity of fsync(): instead of every time a connection is<br />
created to PostgreSQL, fsync() will be issued only once for the first<br />
time a connection is created to PostgreSQL.<br />
<br />
The patch should be able to applied to 3.7.<br />
<br /></blockquote>
<blockquote type="cite" style="margin: 5px 5px; padding-left: 10px; border-left: thin solid #e67e22;"><br /></blockquote>
<div class="matchFont"><br /></div>
<div class="matchFont">Hi,</div>
<div class="matchFont"><br /></div>
<div class="matchFont">That would explain it.</div>
<div class="matchFont"><br style="font-family: -apple-system, BlinkMacSystemFont, sans-serif !important;" /></div>
<div class="matchFont">Our clients are a bunch of microservices running in Kubernetes. There are a lot of them. Their behavior is, whenever they need to perform any database operation, they will establish a new connection, perform any queries needed, then close the connection as soon as they are done. This is so because we experienced issues in the past with holding connections for longer periods. And not all of them require constant database communication(they may be idle waiting for API calls themselves), so it was also wasteful</div>
<div class="matchFont"><br style="font-family: -apple-system, BlinkMacSystemFont, sans-serif !important;" /></div>
<div class="matchFont">I don't have enough understanding of pgpool's internals yet, but it looks to me that the patch would solve this issue.</div>
<div class="matchFont"><br style="font-family: -apple-system, BlinkMacSystemFont, sans-serif !important;" /></div>
<div class="matchFont">Thanks for the response. We have increased resources in the pgpool machine for now while we wait a patch. Load is still very high, but manageable.</div>
<div class="matchFont"><br style="font-family: -apple-system, BlinkMacSystemFont, sans-serif !important;" /></div>
<div class="matchFont">– Stephen</div>
</div>
_______________________________________________ <br /></blockquote>
<br />
<div>It seems that I missed one thing and jumped the gun. </div>
<div><br /></div>
<div>From what you have explained, it is not only when new client connections are created to PGPool, but they need to ALSO be created to PostgreSQL itself, for this behavior to manifest, right?</div>
<div><br /></div>
<div>Then this doesn’t fully explain it. Connection cache is on (attached pgpool config file).</div>
<div><br /></div>
<div>We do reach the configured 500 connection limit on pgpool processes quite often (after which pgpool will spawn another process). Could this be related?</div>
<div><br /></div>
<div><br /></div>
<div>– Stephen</div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div></div>
</div>
</body>
</html>