[pgpool-general: 6370] Re: pgpool performance issues

[Mariel Cherkassky]

Well, it is a little bit complicated to explain. Basicly, I monitored all
the queries that are running and I saw that the huge diff in performance is
generated because of the next query :
 select columns from table_name
where col1 in(..)
and (col2 in (...) or col3 in (...))
and col4in (...)

We run this query a lot of times and the diff between performance is about
70% in most of the times.

‫בתאריך יום ג׳, 25 בדצמ׳ 2018 ב-8:35 מאת ‪Tatsuo Ishii‬‏ <‪
ishii at sraoss.co.jp‬‏>:‬

> BTW, x4.5 slowness sounds unusual. What is your application doing?
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > 40% loss is for typical light weight query case. For heavier query,
> > for example something like, SELECT count(*) FROM really_big_table,
> > performance loss will be pretty subtle.
> >
> > But if you want really low performance loss (for example less than
> > 10%) in any query, probably you'd better to look for another
> > solutions.
> >
> > Best regards,
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese:http://www.sraoss.co.jp
> >
> >> The load average was pretty normal in both cases so I dont think that
> >> limited resources are the root cause here. 40% performance loss is
> pretty
> >> big number, isnt there a way to debug it ?
> >>
> >> ‫בתאריך יום א׳, 23 בדצמ׳ 2018 ב-13:49 מאת ‪Tatsuo Ishii‬‏ <‪
> >> ishii at sraoss.co.jp‬‏>:‬
> >>
> >>> Hi,
> >>>
> >>> Pgpool-II needs to store and forward each network packet from clients
> >>> and PostgreSQL.  So usually about 40% performance loss is expected
> >>> comparing with direct connecting to PostgreSQL.  That says, if your
> >>> application's SELECT reads a lot of rows for example, it may take more
> >>> time.
> >>>
> >>> Another point of consideration is, hardware resources especially CPU,
> >>> memory and network. In your test case #1, on node A only applications
> >>> and walreciver use CPU, On the other hand in test case #2, on node A
> >>> the applications, Pgpool-II and walreciver use CPU. So it maybe
> >>> possible Pgpool-II cannot get enough CPU. Have you checked resource
> >>> usage?
> >>>
> >>> Best regards,
> >>> --
> >>> Tatsuo Ishii
> >>> SRA OSS, Inc. Japan
> >>> English: http://www.sraoss.co.jp/index_en.php
> >>> Japanese:http://www.sraoss.co.jp
> >>>
> >>> > I meant that max_connection is set to 500. I did the same text with
> >>> > num_init_children=500 but same performance(because my app doesnt use
> in
> >>> > this architecture more then 200 connections..).
> >>> >
> >>> > ‫בתאריך יום א׳, 23 בדצמ׳ 2018 ב-10:29 מאת ‪Pierre Timmermans‬‏ <‪
> >>> > ptim007 at yahoo.com‬‏>:‬
> >>> >
> >>> >> What do you mean by "My db has 500 max connections" ? If you have
> 500
> >>> >> concurrent connections, then you should set num_init_children to
> 500,
> >>> >> because now you can have no more than 200 concurrent users (with
> pgpool
> >>> a
> >>> >> connection is released from the pgpool only if it the session
> >>> disconnects
> >>> >> from postgres)
> >>> >>
> >>> >> Pierre
> >>> >>
> >>> >>
> >>> >> On Sunday, December 23, 2018, 9:26:17 AM GMT+1, Mariel Cherkassky <
> >>> >> mariel.cherkassky at gmail.com> wrote:
> >>> >>
> >>> >>
> >>> >> Hi,
> >>> >> I'm using pgpool and I'm suffering from very poor performance issues
> >>> when
> >>> >> using the pool. I have the next architecture  :
> >>> >> 1)Node A, contains the application,standby database and pgpool
> service.
> >>> >> 2)Node B contains the primary db and pgpool service that will be
> used
> >>> only
> >>> >> in failover.
> >>> >>
> >>> >> I did the next 2 tests :
> >>> >> -In the first test, the application on node A access the DB on node
> B
> >>> >> directly, without connecting to the pool. I run one of our
> application`s
> >>> >> major procedures and it took 40s.
> >>> >> -In the second test, the application on node A access the pool on
> node A
> >>> >> and it redirects the queries to node B. I run the same procedure
> and it
> >>> >> took 3minutes.
> >>> >>
> >>> >> My db has 500 max connections and the application has only 1 user
> and 1
> >>> db
> >>> >> so I set the num_init_children to be 200 and pool_size to be 1.
> >>> >>
> >>> >> Any idea how can I tune the pgpool ?
> >>> >>
> >>> >> Thanks.
> >>> >> _______________________________________________
> >>> >> 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/20181225/4b71e5f8/attachment.html>