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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Dec 25 17:42:32 JST 2018


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


More information about the pgpool-general mailing list