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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Dec 25 15:35:46 JST 2018


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