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

[Tatsuo Ishii]

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
>> >>
>>