[pgpool-general: 3829] Re: EXTERNAL: Re: Connection Failures Through pgpool Only

[Hill, Michael E]

Thanks for the tips.  I have two follow up questions. First, for child_life_time are you suggesting it should be increased, decreased or disabled (after looking at the manual I couldn't tell what was needed)?  Second, I used the defaults for num_init_children and max_connections. So, for pgpool num_init_children = 32 and max_pool = 4.  And for postgresql.conf I have max_connections = 100.  Looking at the manual I see:

max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)

So I'm thinking:  4 *32*2 = 256 for max_connections.  Did I get this right?

Mike
-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at postgresql.org] 
Sent: Saturday, June 20, 2015 5:27 AM
To: Hill, Michael E; hillsangels2 at verizon.net
Cc: pgpool-general at pgpool.net
Subject: EXTERNAL: Re: [pgpool-general: 3821] Connection Failures Through pgpool Only

> Hi pgpoolers,
> 
> After a good unit test, I am having fundamental problems implementing 
> pgpool into integration-test where the real apps are. My system is not 
> connected to the internet in any way and I cannot cut and paste any 
> information, but I will try to fill in the gap by hand.Please help!
> 
> Problem 1Applications frequently get error 08006 (connection_failure) 
> and 08003 (connection_does_not_exist); they
>                              occasionally get messages about threads no longer connected to
>                              database.None of these transactions get thru.

It is possible due to this in pgpool.conf (which is default).

child_life_time = 300

You need to fix this or your application to reconect to pgpool-II.

> Problem 2pgpool instances sometimes lockup/crash and cannot be 
> accessed from psql or pgp commands

Too vague problem report. Please show us what's in the pgpool log file when the issue occurs.

Another possiblity for "lockup" problem is misconfiguration of num_init_children vs. max_connections in postgresql.conf (you did not show us postgresql.conf and this is just my guess). See manual for proper configuration.

> Problem 3The standby nodes show up mostly as status 1 and 3, and only 
> occasionally as 2; the primaries are generally at 2,
>                              except for early startup which is 1.

"3" means pgpool-II detects that either 3rd PostgreSQL is down for pgpool-II cannot reach to the server. Showing the pgpool log may reveals the cause.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Generally the applications all work fine when connecting directly to 
> the primary PG DB servers at 5432, and all databases can be accessed 
> using psql thru the db servers at port 5432, and thru the 
> pgppol/tomcat servers at 9999._The errors occur only when I change the 
> application data sources to go thru the pgpool servers at 9999. 
> _Nothing in the PG or pgpool logs show obvious authentications errors.
> 
> Background/details:
> 
> Systems Each test environment consists of 4 rhel6 HP blades, 8GB of 
> RAM each
> 
> PgpoolTwo instances of v3.4 running independently on 2 severs shared 
> w/ Tomcat (2GB dedicated; no watchdog; installed w/ RPMs
> 
> Potgresv9.3 on dedicated servers using PG streaming synchronous 
> replication; installed w/ tar files
> 
> My exp. lev.3m pgpool, 6m PG, 20+ yrs DBA; set up 9 sets of 4 servers 
> so far; 2 sets to go for the $!
> 
> Key settings:
> 
> Pg_hba.conf (as open as I can get it!):
> 
> 
> # TYPEDATABASEUSERADDRESSMETHOD
> 
> localallalltrust
> 
> hostallall0.0.0.0/0trust
> 
> hostallpostgresql0.0.0.0/0trust
> 
> hostreplicationallxx.xx.xx.xx/32trust --> (only b/c PG insisted)
> 
> pgpool.conf:
> 
> 
> listen_addresses = '*'
> 
> port = 9999
> 
> pcp_port = 9898
> 
> backend_hostname0 = 'abcdefgh'
> 
> backend_port1 = 5432
> 
> backend_weight0 = 1
> 
> backend_data_directory0 = '/data/ABCDFF1'
> 
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_hostname1 = 'abcdefgh'
> 
> backend_port1 = 5432
> 
> backend_weight1 = 1
> 
> backend_data_directory1 = '/data/ABCDEF1'
> 
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> 
> enable_pool_hba = off
> 
> replication_mode = off
> 
> load_balance_mode = on
> 
> master_slave_mode = on
> 
> master_slave_sub_mode = 'stream' (warning msg in log says system is 
> not streaming)
> 
> parallel_mode = off
> 
> Loaded these into db01:
> 
> psql -h localhost -U postgres -W -f pgpool-recovery.sql dbname
> 
> psql -h localhost -U postgres -W -f pgpool-regclass.sql dbname
> 
> Thanks.
> 
> Mike Hill
> Lockheed Martin Corp.
> Herndon, VA
> michael.e.hill at lmco.com
> 
> 
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus