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

[Tatsuo Ishii]

> 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)?

If you are not sure, leave it as the default. child_life_time is a
kind of life gurard against unwanted problems, such as memory leak. If
pgpool-II would be completely bug-free, you could disable it. But you
know life is not ideal...

> 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?

Yes, correct.

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

> 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