[pgpool-general: 4607] Re: Using Amazon RDS Replication with pgpool - bad performance though

[Roman D]

Thank you for the response.
Performance measured by running load tests on web application simulating hundreds of simultaneous users and requests.
“Round trip” meaning time from start of SQL request until response received.

Are there any insights/advice about connecting to pgpool-II on application level? It seems like it’s not advisable to use connection pool in front of pgpool-II?
We have Java application that uses c3p0 connection pool. Comparing direct connection java-> postgres with java -> pgpool-II -> postgres makes Java application slower by 6-8 times, most time spent in connection pool code. In all places there is large number of available connections, more than needed.

Thanks.

> On 3 Apr 2016, at 6:20 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
> Strange. I have never tried with RDS but I have tried pgpool-II with
> PostgreSQL on EC2 instances and have not seen such a performance
> degradation. How do you measure performance? pgbench?
> 
> The term "round trip" is not very clear for me...
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Hello,
>> 
>> Amazon RDS Master and Read replica.
>> The goal is to have Read queries balanced to Read Replica using pgpool, while Replication is provided by RDS.
>> I thought that my case is pretty common, but there’s no mention anywhere on the net or in this list of someone ever done this.
>> 
>> But surprisingly this configuration works!
>> Using combination of these parameters:
>> replication_mode = off
>> load_balance_mode = on
>> master_slave_mode = on
>> master_slave_sub_mode = ‘stream'
>> 
>> pgpool 3.3.4
>> Master configured in pgpool.paswd as 0 and replica as 1
>> 
>> Here’s the conf:
>> 
>> backend_hostname0 = ‘master’
>> backend_port0 = 5432
>> backend_weight0 = 1
>> backend_flag0 = ‘DISALLOW_TO_FAILOVER’
>> 
>> backend_hostname1 = ’slave’
>> backend_port1 = 5432
>> backend_weight1 = 1
>> backend_flag1 = ‘DISALLOW_TO_FAILOVER'
>> 
>> enable_pool_hba = on
>> pool_passwd = ‘pool_passwd'
>> ssl = off
>> 
>> num_init_children = 200
>> max_pool = 4
>> child_life_time = 300
>> child_max_connections = 2000
>> connection_life_time = 300
>> client_idle_limit = 0
>> listen_backlog_multiplier = 5
>> 
>> connection_cache = on
>> reset_query_list = ‘ABORT; DISCARD ALL'
>> 
>> replication_mode = off
>> load_balance_mode = on
>> master_slave_mode = on
>> master_slave_sub_mode = 'stream'
>> sr_check_period = 0
>> 
>> parallel_mode = off
>> health_check_period = 0
>> use_watchdog = off
>> memory_cache_enabled = off
>> 
>> 
>> The problem is that pgpool is up to 4 times slower then direct connection to database.
>> Without pgpool database roundtrip is 10 msec. With pgpool it’s 30-40 msec.
>> 
>> pgpool is hosted on separate machine form the application and database. PGPool machine CPU is at 5% ( net.core.somaxconn set to 5000)
>> application CPU/memory is at 50% and database CPU&memory is at 50% load
>> so basically pgpool is being the bottleneck, doing something that slows down the database access.
>> 
>> I tried changing every possible parameter, changing num_init_children, max_pool - that does not improve anything.
>> 
>> I am about to give up. Please help! Is there anything else I can look into ?It would be amazing if this worked without adding such significant time overhead. 
>> 
>> Thank you!
>> 
>> 
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general