<p dir="ltr">Hi,</p>
<p dir="ltr">>> Let's say<br>
>><br>
>> - health_check_period = 10<br>
>> - health_check_max_retries = 11<br>
>> - health_check_timeout =10<br>
>> - health_check_retry_delay = 1<br>
>><br>
>> If at time 0 master goes down and health_check is started, after 11 tries<br>
>> that takes 10+1 seconds each, failover is triggered at time 121</p>
<p dir="ltr">>That's the worst case. Most of error checking will return far before<br>
>timeout, so usually the failover trigger time would be time 11 * 1 = 11.</p>
<p dir="ltr">the problem here is that I need that failover does not happen before 120s, but obviously neither I would like it to happen after a lot of time. Best option for me would be to failover after exactly 120s.</p>
<p dir="ltr">There are also 2 timeout to be considered: connection_timeout (10s) and health_check_timeout (let's say 10 seconds).</p>
<p dir="ltr">I've made a small test using psql and two different cases:<br>
* trying to connect to a node that is up but postgresql service is down<br>
* trying to connect to a node that is down</p>
<p dir="ltr">Having a look at strace, the behaviour is quite different.<br>
In case 1. an error is returned almost instantly by the connect() and the poll().<br>
In case 2, the connect() instead hits a timeout after about 30s.</p>
<p dir="ltr">I think that in case postgresql is down for upgrade but the node is up, pgpool behaviour may be similar to case 1, so probably the health checks fail quickly.</p>
<p dir="ltr">On the other hand if the node is down or there are network issues pgpool probably will end up hitting connect_timeout or health_check_timeout.</p>
<p dir="ltr">Since my nodes are located in the same place and network between them is reliable, I should probably reduce both timeout to a few seconds and wait a little more between tries.</p>
<p dir="ltr">If I choose:<br>
* health_check_timeout and connection timeout = 3<br>
* health_check_retry_delay = 8<br>
* health_check_max_retries = 15</p>
<p dir="ltr">I should probably obtain failover not before 15 * 8 = 120s if postgresql is down (case 1, quick failures) and not after (8 + 3) * 15 = 165s if the node is down or unrechable (case 2, connection timeout).</p>
<p dir="ltr">I think that 3 seconds may be enough for an health test even after heavy load. Moreover those tests are allowed to fail occasionally without triggering failover.</p>
<p dir="ltr">May you please comment about my parameter choice?</p>
<p dir="ltr">Thank you and best regards,</p>
<p dir="ltr">Gabriele Monfardini <br></p>
<p dir="ltr">-----<br>
Gabriele Monfardini<br>
LdP Progetti GIS<br>
tel:<a href="tel:0577.531049"> 0577.531049</a><br>
email:<a href="mailto:monfardini@ldpgis.it"> monfardini@ldpgis.it</a></p>
<p dir="ltr">On Fri, May 20, 2016 at 4:30 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br>
</p>
<blockquote><p dir="ltr">><br>
</p>
</blockquote>
<p dir="ltr">> ></p>
<p dir="ltr">> > Hi all,<br>
> ><br>
> > I have a setup with two pgpools in HA and two backends in streaming<br>
> > replication.<br>
> > The problem is that, due to unattended upgrade, master has been restarted<br>
> > and master pgpool has correctly started failover.<br>
> ><br>
> > We would like to prevent this, playing with health_check parameters, in<br>
> > order for pgpool to cope with short master outage without performing<br>
> > failover.<br>
> ><br>
> > I've found an old blog post of Tatsuo Ishii,<br>
> ><a href="http://pgsqlpgpool.blogspot.it/2013/09/health-check-parameters.html"> http://pgsqlpgpool.blogspot.it/2013/09/health-check-parameters.html</a>, in<br>
> > which the following statement is made:<br>
> ><br>
> > Please note that "health_check_max_retries *<br>
> >> (health_check_timeout+health_check_retry_delay)" should be smaller than<br>
> >> health_check_period.<br>
><br>
> Yeah, this is not a best advice.<br>
><br>
> > Looking at the code however it seems to me that things are a little<br>
> > different (probably I'm wrong).<br>
> ><br>
> > 1. in main loop health check for backends is performed<br>
> > (do_health_check), starting from 0 to number_of_backends<br>
> > 2. suppose that i-th backend health check fails because of timeout. The<br>
> > process is interrupted by the timer.<br>
> > 3. if (current_try <= health_check_max_retries) =><br>
> > sleep health_check_retry_delay<br>
> > 4. we're back in main loop, the health check restart from i, the backend<br>
> > for which health_check failed<br>
> > 5. suppose that health_check fails again and again<br>
> > 6. when (current_try > health_check_max_retries) => set backend down<br>
> > 7. we're back in main loop, the health check restart from i, the backend<br>
> > for which health_check failed, but now its state is DOWN so we continue to<br>
> > next backend<br>
> > 8. in main loop when do_health_check exits, all backend are down or all<br>
> > backend currently not down are healthy<br>
> > 9. then we sleep health_check_period in main loop before starting again<br>
> > the check from the beginning.<br>
> ><br>
> ><br>
> > If I understand it correctly, health_check_period is slept unconditionally<br>
> > at the end of the check so it is not needed to set it as high as per the<br>
> > formula in the blog.<br>
><br>
> Correct.<br>
><br>
> > Moreover if there are many backends and many failures last backend may be<br>
> > checked again after a long time, in the worst case after about<br>
> ><br>
> > (number_of_backends-1) * health_check_max_retries *<br>
> > (health_check_timeout+health_check_retry_delay) + health_check_period<br>
><br>
> Again, correct. To enhance this, we need to create separate health<br>
> check process, and each process performs health check for each<br>
> PostgreSQL concurrently.<br>
><br>
> > Suppose that I choose that is acceptable that master may goes down for at<br>
> > max 120 seconds before failover.<br>
> ><br>
> > Since I have only two backends, I should probably set<br>
> ><br>
> > health_check_max_retries * (health_check_timeout+health_check_retry_delay)<br>
> > + health_check_period<br>
> ><br>
> > to about 120s.<br>
> ><br>
> > Let's say<br>
> ><br>
> > - health_check_period = 10<br>
> > - health_check_max_retries = 11<br>
> > - health_check_timeout =10<br>
> > - health_check_retry_delay = 1<br>
> ><br>
> > If at time 0 master goes down and health_check is started, after 11 tries<br>
> > that takes 10+1 seconds each, failover is triggered at time 121<br>
><br>
> That's the worst case. Most of error checking will return far before<br>
> timeout, so usually the failover trigger time would be time 11 * 1 = 11.<br>
><br>
> > In case all health checks returns OK in negligible time, that should<br>
> > happens almost always, health_check_period assures that no checks are done<br>
> > for next 10 seconds.<br>
><br>
> Right.<br>
><br>
> > Can you please confirm my findings or correct me?<br>
><br>
> Thank you for your analysis!<br>
><br>
> Best regards,<br>
> --<br>
> Tatsuo Ishii<br>
> SRA OSS, Inc. Japan<br>
> English:<a href="http://www.sraoss.co.jp/index_en.php"> http://www.sraoss.co.jp/index_en.php</a><br>
> Japanese:<a href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a><br>
><br>
> > Best regards,<br>
> ><br>
> > Gabriele Monfardini<br>
> ><br>
> > -----<br>
> > Gabriele Monfardini<br>
> > LdP Progetti GIS<br>
> > tel:<a href="tel:0577.531049"> 0577.531049</a><br>
> > email:<a href="mailto:monfardini@ldpgis.it"> monfardini@ldpgis.it</a><br>
> _______________________________________________<br>
> pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">> pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general">> http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</p>