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

[Petr Novak]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160403/b2e3d8e5/attachment.html>