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

[Tatsuo Ishii]

If you are using PostgreSQL's streaming replication, your porblem
shouldn't happen because read only SELECTs should be sent to only one
node and the kind mismach error will never happen. I think pgpool
thinks that lower() is a writing function and the SELECT needed to be
sent to all nodes. This could be solved by teaching pgpool which
function is not read only by using black_black_function_list in
pgpool.conf.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general