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

[Tatsuo Ishii]

I assume "set session characteristics read-only/read-write" or "set
transaction level isolation" queries are originated from applications,
not pgpool-II.

In this case the queries are sent to all the DB nodes which take more
time than other queries.

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

> Hi Roman
> 
> we've recently experienced similar problems to which you're referring. It
> was the case, when the machine with pgpool had larger network latencies to
> the postgres server (~30ms rtt, pgpool and db were in different
> datacenters).
> I've enabled logging all queries on the database, while debugging the issue
> and discovered that the slowdown was caused by queries like "set session
> characteristics read-only/read-write" or "set transaction level isolation",
> which was done for every query. There were several more type of such
> queries, but the bottom line is, it was done for every query sent to the
> database prior executing the query itself. When the pgpool is on the same
> network, the penalty is negligible, but when the latencies get in the way,
> it causes problems.
> 
> Don't know whether your problem is the same, but maybe it can help you
> trace the issue.
> 
> Cheers.
> Petr
> On Apr 3, 2016 14:13, "Roman D" <dostick at gmail.com> wrote:
> 
>> 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
>>
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>