<div dir="rtl"><div dir="rtl"><div dir="ltr">-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.</div><div dir="ltr"><div dir="ltr">-pgpool-II version 3.7.7 (amefuriboshi)</div><div>-Indeed, java app. </div><div>-I had 16cpus.</div><div>-The size of 1 row is about 16KB.</div></div><div dir="ltr"><br></div></div></div><br><div class="gmail_quote"><div dir="rtl">בתאריך יום ג׳, 25 בדצמ׳ 2018 ב-10:42 מאת Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>>:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">How many rows do these SELECTs return? Also how wide are each row?<br>
These will affect performance of Pgpool-II because the bigger they<br>
are, the more network packets are neccessary.<br>
<br>
Also please share Pgpool-II version, using extended query or not (if<br>
the app is Java application, it's yes), and how many (virtual) cpus<br>
are there.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Well, it is a little bit complicated to explain. Basicly, I monitored all<br>
> the queries that are running and I saw that the huge diff in performance is<br>
> generated because of the next query :<br>
> select columns from table_name<br>
> where col1 in(..)<br>
> and (col2 in (...) or col3 in (...))<br>
> and col4in (...)<br>
> <br>
> We run this query a lot of times and the diff between performance is about<br>
> 70% in most of the times.<br>
> <br>
> בתאריך יום ג׳, 25 בדצמ׳ 2018 ב-8:35 מאת Tatsuo Ishii <<br>
> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>>:<br>
> <br>
>> BTW, x4.5 slowness sounds unusual. What is your application doing?<br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > 40% loss is for typical light weight query case. For heavier query,<br>
>> > for example something like, SELECT count(*) FROM really_big_table,<br>
>> > performance loss will be pretty subtle.<br>
>> ><br>
>> > But if you want really low performance loss (for example less than<br>
>> > 10%) in any query, probably you'd better to look for another<br>
>> > solutions.<br>
>> ><br>
>> > Best regards,<br>
>> > --<br>
>> > Tatsuo Ishii<br>
>> > SRA OSS, Inc. Japan<br>
>> > English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> > Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> ><br>
>> >> The load average was pretty normal in both cases so I dont think that<br>
>> >> limited resources are the root cause here. 40% performance loss is<br>
>> pretty<br>
>> >> big number, isnt there a way to debug it ?<br>
>> >><br>
>> >> בתאריך יום א׳, 23 בדצמ׳ 2018 ב-13:49 מאת Tatsuo Ishii <<br>
>> >> <a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>>:<br>
>> >><br>
>> >>> Hi,<br>
>> >>><br>
>> >>> Pgpool-II needs to store and forward each network packet from clients<br>
>> >>> and PostgreSQL. So usually about 40% performance loss is expected<br>
>> >>> comparing with direct connecting to PostgreSQL. That says, if your<br>
>> >>> application's SELECT reads a lot of rows for example, it may take more<br>
>> >>> time.<br>
>> >>><br>
>> >>> Another point of consideration is, hardware resources especially CPU,<br>
>> >>> memory and network. In your test case #1, on node A only applications<br>
>> >>> and walreciver use CPU, On the other hand in test case #2, on node A<br>
>> >>> the applications, Pgpool-II and walreciver use CPU. So it maybe<br>
>> >>> possible Pgpool-II cannot get enough CPU. Have you checked resource<br>
>> >>> usage?<br>
>> >>><br>
>> >>> Best regards,<br>
>> >>> --<br>
>> >>> Tatsuo Ishii<br>
>> >>> SRA OSS, Inc. Japan<br>
>> >>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >>><br>
>> >>> > I meant that max_connection is set to 500. I did the same text with<br>
>> >>> > num_init_children=500 but same performance(because my app doesnt use<br>
>> in<br>
>> >>> > this architecture more then 200 connections..).<br>
>> >>> ><br>
>> >>> > בתאריך יום א׳, 23 בדצמ׳ 2018 ב-10:29 מאת Pierre Timmermans <<br>
>> >>> > <a href="mailto:ptim007@yahoo.com" target="_blank">ptim007@yahoo.com</a>>:<br>
>> >>> ><br>
>> >>> >> What do you mean by "My db has 500 max connections" ? If you have<br>
>> 500<br>
>> >>> >> concurrent connections, then you should set num_init_children to<br>
>> 500,<br>
>> >>> >> because now you can have no more than 200 concurrent users (with<br>
>> pgpool<br>
>> >>> a<br>
>> >>> >> connection is released from the pgpool only if it the session<br>
>> >>> disconnects<br>
>> >>> >> from postgres)<br>
>> >>> >><br>
>> >>> >> Pierre<br>
>> >>> >><br>
>> >>> >><br>
>> >>> >> On Sunday, December 23, 2018, 9:26:17 AM GMT+1, Mariel Cherkassky <<br>
>> >>> >> <a href="mailto:mariel.cherkassky@gmail.com" target="_blank">mariel.cherkassky@gmail.com</a>> wrote:<br>
>> >>> >><br>
>> >>> >><br>
>> >>> >> Hi,<br>
>> >>> >> I'm using pgpool and I'm suffering from very poor performance issues<br>
>> >>> when<br>
>> >>> >> using the pool. I have the next architecture :<br>
>> >>> >> 1)Node A, contains the application,standby database and pgpool<br>
>> service.<br>
>> >>> >> 2)Node B contains the primary db and pgpool service that will be<br>
>> used<br>
>> >>> only<br>
>> >>> >> in failover.<br>
>> >>> >><br>
>> >>> >> I did the next 2 tests :<br>
>> >>> >> -In the first test, the application on node A access the DB on node<br>
>> B<br>
>> >>> >> directly, without connecting to the pool. I run one of our<br>
>> application`s<br>
>> >>> >> major procedures and it took 40s.<br>
>> >>> >> -In the second test, the application on node A access the pool on<br>
>> node A<br>
>> >>> >> and it redirects the queries to node B. I run the same procedure<br>
>> and it<br>
>> >>> >> took 3minutes.<br>
>> >>> >><br>
>> >>> >> My db has 500 max connections and the application has only 1 user<br>
>> and 1<br>
>> >>> db<br>
>> >>> >> so I set the num_init_children to be 200 and pool_size to be 1.<br>
>> >>> >><br>
>> >>> >> Any idea how can I tune the pgpool ?<br>
>> >>> >><br>
>> >>> >> Thanks.<br>
>> >>> >> _______________________________________________<br>
>> >>> >> pgpool-general mailing list<br>
>> >>> >> <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> >>> >> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> >>> >><br>
>> >>><br>
>> > _______________________________________________<br>
>> > pgpool-general mailing list<br>
>> > <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> > <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>><br>
</blockquote></div>