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

[Mariel Cherkassky]

Most of the queries in that csv have the same format :
select columns from table_name
where col1 in(..)
and (col2 in (...) or col3 in (...))
and col4in (...)

the difference is in the number of arguments in each query - thats all. The
columns and the table name are identical in all the queries.

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

> Thanks for the detailed info.
>
> In the sheet, query id: 1354301971 is one of the most interest queries
> because withpool_total_time/withoutpool_total_time is as high as 3.53.
> Can you elaborate more on the query?
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > -well it depends on how many arguments are sent. I checked the diff by
> > looking on pg_stat_statements. Before every test I reset all the content
> of
> > it. I attach an excel file(csv format) with the diffs.
> > -pgpool-II version 3.7.7 (amefuriboshi)
> > -Indeed, java app.
> > -I had 16cpus.
> > -The size of 1 row is about 16KB.
> >
> >
> > ‫בתאריך יום ג׳, 25 בדצמ׳ 2018 ב-10:42 מאת ‪Tatsuo Ishii‬‏ <‪
> > ishii at sraoss.co.jp‬‏>:‬
> >
> >> How many rows do these SELECTs return? Also how wide are each row?
> >> These will affect performance of Pgpool-II because the bigger they
> >> are, the more network packets are neccessary.
> >>
> >> Also please share Pgpool-II version, using extended query or not (if
> >> the app is Java application, it's yes), and how many (virtual) cpus
> >> are there.
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
> >> > 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/20181226/4b3da72a/attachment.html>