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

[Stelios Limnaios]

Hi Tatsuo,

Thank you very much for your response.

We do have configured the white and the black list:
White: get*,has*
Black: nextval,setval,update*
We recently found that there are more custom functions that we should add in the lists.

There is a point of confusion around the lists.
We came across this page:
http://comments.gmane.org/gmane.comp.db.postgresql.pgpool.general/3136
and "Only one of these two lists can be filled in a configuration."
Is that correct? It is not very obvious in documentation http://www.pgpool.net/docs/latest/pgpool-en.html

Also, does all this explain the I/O exception to the backend?
Is this exception a result of functions not being in the black list?

Thank you again,
Regards,
Stelios

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

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