[Pgcluster-general] Lifecheck in pgreplicate issues

[Anatoly Savchenkov]

Hi,

I'm testing pgcluster 1.7rc8 and in normal conditions it works fine.
But when I test cluster behavior in case of node unavailability
(caused by either abrupt shutdown or network connectivity problems) I
encounter several issues:

1. If some node is excluded from the list of available nodes by life
checking in pgreplicate it never comes back again until pgreplicate is
restarted. I detect node exclusion by 'port(XXXX) host:XXXX error'
line in pgreplicate.sts file. I reviewed the source code
(src/pgcluster/pgrp/lifecheck.c) and found that once dead node is
never involved in life check, so it will never be put into 'use'
state. From one point of view this looks correct because dead node
needs to be synchronized before including it back to cluster. But this
makes crash recovery more complex, pgreplicate should be restarted
manually on all cluster nodes.

The patch for this is trivial: just remove 'if (host_ptr->useFlag != DB_TBL_USE){...}'
condition from while loop inside of lifecheck_loop() routine. But I'm
in doubt whether this cause some side effect.

2. LifeCheck_Timeout parameter of pgreplicate.conf does not work as
expected. I expect to detect node failures in 'LifeCheck_Timeout'
seconds (or X * LifeCheck_Timeout, where X is known number of
retries). Life checking is performed just by issuing simple query on a
remote node and PGRcreateConn() is used to establish DB connection.
But implementation of PGRcreateConn() relies on blocking
PQsetdbLogin() which can hang for up to minutes depending on TCP stack
configuration of host OS. I suggest to rewrite PGRcreateConn() to use
PQconnectdb() with connect_timeout option set to LifeCheck_Timeout.
This will eliminate use of alarm() and will give more predictable
timings. Let me know if this is reasonable and will not introduce any
issues and I'll provide a patch. 

3. Query issued during a time interval between some node failure and
pgreplicate detected this failure causes cluster node which receives
the query to hang. Sometimes the node hangs for less than a minute,
sometimes it may take more that 10 minutes, sometimes it is forever
(or at least for more than an hour). It is important to note that I
use DBD::Pg with prepared queries and placeholders. psql queries works
fine. Behavior does not depend on type of query being issued. It is
observed even on selects.

This looks very similar to previously discussed problem with
deadlocking when using DBD::Pg with placeholders and simultaneous DB
write (insert/update/delete) operations for the same table into two or
more nodes. That deadlock could be eliminated by either setting
'auto_lock_table' to false or switching to cybercluster. But actually
this is the same solution because according to the source code
cybercluster does not support 'auto_lock_table' (source code is simply
excluded by #ifdef 0). However 'auto_lock_table' is very useful
feature because it prevents cluster desync in case of parallel inserts
with SERIAL columns (or perhaps in other cases as well).

I'm not experienced with postgres internals. Do someone have any ideas
which module could be responsible for hangs in case of DBD::Pg?

Thank you.

-- 
Best regards,
 Anatoly                          mailto:Anatoly.Savchenkov at arc.com 
Unless otherwise expressly stated, this message does not create or vary any contractual relationship between you and ARC International. The contents of this e-mail may be confidential and if you have received it in error, please delete it from your system, destroy any hard copies and telephone the above number. Incoming emails to ARC may be subject to monitoring other than by the addressee. SJ