[pgpool-general: 2279] Re: PGPool and backend CPU usage

[Stelios Limnaios]

Hi,

You are correct, we're using PostgreSQL replication.

We upgraded PGPool to version 3.3.1, but we still are getting 'backend mismatch' errors.
I think that CPU overload is not the cause of the problem anymore.

Do you have any idea how can we track what is causing backends to loose synchronisation?
PGPool logs only the action of degeneration:
read_kind_from_backend: 0 th kind C does not match with master or majority connection kind D
kind mismatch among backends. Possible last query was: "SELECT name,id,type,creator FROM notus_aqa.tree_node LEFT JOIN notus_aqa.branch ON id=child_id WHERE parent_id=$1 AND lower(name)=$2" kind details are: 0[C] 1[D] 2[D]
degenerate_backend_set: 0 fail over request from pid 22833
starting degeneration. shutdown host 158.255.228.43(5433)
Restart all children
execute command: /usr/local/etc/failover_r.sh 0 "158.255.228.43" 5433 /var/lib/pgsql/9.1/data_5433 1 0 "158.255.228.54" 0

On the Postgre side, we get this log message:
LOG:  unexpected EOF on client connection

Finally Java reports:
org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
Caused by: java.io.EOFException

So, maybe we need to understand what is causing this EOF on client connection.
Does PGPool drop the connection to Postgres for some reason? 
Is it a timeout that we need to configure? Is network latency a factor to this problem?

Many thanks for your interest and you time.

Regards,
Stelios

-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at postgresql.org] 
Sent: 09 November 2013 08:57
To: Stelios Limnaios
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 2263] PGPool and backend CPU usage

How do you set up pgpool replication? Are you using streaming replication of PostgreSQL?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi all,
> 
> We're  facing a situation that would like some expertise to be solved.
> 
> First of all, our setup consists of 3 databases Postgres 9.1, PGPool 3.2 STABLE checked out from the repository (rev 6dde744) and PGPoolAdmin 3.2.0.
> We have setup replication and load balancing, along with scripts for online recovery.
> The cluster works fine in terms of replicating and load balancing data.
> 
> But, the servers host other applications expect of the databases.
> Some applications require a lot of CPU when running.
> We found recently that when CPU is high in a backend, PGPool fails to update the data on this server and that causes a 'kind mismatch among backends' situation.
> 
> I know that there is a replication_stop_on_mismatch parameter in pgpool conf, but that will disconnect the database from the cluster and we will have to re-attach it (this could happen several times).
> 
> So I was wondering, if there is a more permanent workaround for this.
> Is it something we can configure in PGPool?
> Are there any minimum memory requirements that we could set to avoid this problem?
> Would that be in Postgres?
> Maybe the best option is to dedicate the server to run the database only (?).
> 
> Thanks in advance for your help and your time.
> 
> Regards,
> Stelios