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

[Tatsuo Ishii]

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