<div dir="ltr"><div dir="ltr">Hi Sunbiao,<br></div><div dir="ltr"><br></div><div>Thanks for the updated patch. Overall the patch looks good and works as expected.</div><div><br></div><div>However, I am a little concerned about the performance aspect of</div><div><span style="font-variant-ligatures:no-common-ligatures;color:rgb(0,0,0);font-family:"Meslo LG L DZ for Powerline";font-size:14px">the check_object_relationship_list() </span>function.</div><div>Since it is parsing the item in the <span style="font-variant-ligatures:no-common-ligatures;color:rgb(0,0,0);font-family:"Meslo LG L DZ for Powerline";font-size:14px">dml_load_balance_object_relationship_list</span></div><div><span style="font-variant-ligatures:no-common-ligatures;color:rgb(0,0,0);font-family:"Meslo LG L DZ for Powerline";font-size:14px">l</span>ist every time it is invoked. So I think we need to fissure out the way to</div><div>store the pre-parsed list (which can be constructed at session start) and try</div><div>to save the parsing at each function call.</div><div><br></div><div>Other than that you need to provide the documentation updates for the feature.</div><div><br></div>Finally, one last comment is how about if we change the disable_load_balance_on_write<br>setting name from 'dml_load_balance' to 'adaptive'?<div><br></div><div><br></div><div><a class="gmail_plusreply" id="plusReplyChip-1" href="mailto:ishii@sraoss.co.jp" tabindex="-1">@Tatsuo Ishii</a> <br></div><div>What do you think about this feature and patch? If you do not have any reservations then</div><div>I will commit it after Sunbiao takes care of review comments.</div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><div><br></div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jun 15, 2020 at 7:25 AM <a href="mailto:sunbiao@highgo.com">sunbiao@highgo.com</a> <<a href="mailto:sunbiao@highgo.com">sunbiao@highgo.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>
<div><div>Hi, Usama</div><div>I found a problem in patch v2.</div><div>I can not use “pool show” to show new added parameter.</div><div>So i made patch v3.</div><div><br></div><div>I make disable_load_balance_on_write to accept new value.</div><div>Set disable_load_balance_on_write = 'dml_load_balance' to enable this feature.</div><div>This new patch contains a test script in path ‘src/test/dml-load-balance-test’.</div><div>If pg installed by default in /usr/local/pgsql, just execute test.sh.</div><div>If pg is in other dir, execute ‘test.sh -p /path_to_pg_dir/’.</div><div>It will show “success: dml load balance test pass.” , when test pass.</div><div><br></div><div>this script will test below sql:</div><div>show pool_nodes;</div><div><br></div><div>-- test DML</div><div>begin ;</div><div>insert into tb_dml_insert values (1);</div><div>select * from tb_dml_insert ;</div><div>commit ;</div><div><br></div><div>begin ;</div><div>update tb_dml_update SET a = 2;</div><div>select * from tb_dml_update ;</div><div>commit ;</div><div><br></div><div>begin ;</div><div>delete from tb_dml_delete;</div><div>select * from tb_dml_delete;</div><div>commit ;</div><div><br></div><div>-- test trigger</div><div>begin ;</div><div>insert into tb_t1 values (1);</div><div>select * from tb_t2 ;</div><div>commit ;</div><div><br></div><div>-- test function</div><div>begin ;</div><div>select insert_tb_f_func(6);</div><div>select * from tb_f ;</div><div>commit ;</div><div><br></div><div>-- test view</div><div>begin ;</div><div>insert into tb_v values (8);</div><div>select * from tb_v_view ;</div><div>commit ;</div><div><br></div><div><div>Thanks</div><div>Best Regards</div></div><span></span></div><hr style="width:210px;height:1px" color="#b5c4df" size="1" align="left">
<div><span><div style="margin:10px;font-family:verdana;font-size:10pt"><div><a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a></div></div></span></div>
<blockquote style="margin-top:0px;margin-bottom:0px;margin-left:0.5em;margin-right:inherit"><div> </div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(181,196,223);padding:3pt 0cm 0cm"><div style="padding:8px;font-size:12px;font-family:tahoma;color:rgb(0,0,0);background:rgb(239,239,239)"><div><b>From:</b> <a href="mailto:m.usama@gmail.com" target="_blank">Muhammad Usama</a></div><div><b>Date:</b> 2020-06-12 18:08</div><div><b>To:</b> <a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a></div><div><b>CC:</b> <a href="mailto:pgpool-hackers@pgpool.net" target="_blank">pgpool-hackers</a></div><div><b>Subject:</b> Re: [pgpool-hackers: 3592] add a feature: dml object level load balance</div></div></div><div><div class="gmail-m_-3550795774487411786FoxDiv20200615102122028595"><div dir="ltr">Hi Sunbiao,<div><br></div><div>Can you kindly resend the <span style="color:rgb(0,0,0);white-space:pre-wrap">0001-dml-load-balance-patch-v2.patch </span>patch? I am not able to download the patch file.</div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Apr 24, 2020 at 9:55 PM Muhammad Usama <<a href="mailto:m.usama@gmail.com" target="_blank">m.usama@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Sunbiao,<br><br>Thanks for the patch and it looks line an interesting feature. I have just skimmed<br>through the patch and I have a few of small comments.<br><br>1 - Wouldn't it be better to add a new mode existing disable_load_balance_on_write parameter<br>instead of adding a new configuration parameter i.e dml_object_level_load_balance ?<br>You can make disable_load_balance_on_write to accept new value like<br>disable_load_balance_on_write = 'object' to enable this feature.<br><br>2- The patch contains some invalid changes in pgpool.conf.sample-stream file<br>i.e it changes the default values of black_function_list and disable_load_balance_on_write<br>configuration parameters which I am sure were not intended<br><br>3- The default value for new configuration parameter dml_object_level_load_balance_token_list<br>better be an empty<br><br>4- Instead of attaching a separate test script you could include a proper test case for the feature.<br><br>Thanks<br>Best Regards<br><br><div><font color="#666666">Muhammad Usama<br>Highgo Software (Canada/China/Pakistan) <br>URL : <a href="http://www.highgo.ca" target="_blank">http://www.highgo.ca</a> <br>ADDR: 10318 WHALLEY BLVD, Surrey, BC </font><div><br></div></div></div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Apr 24, 2020 at 3:43 PM <a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a> <<a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>
<div><span></span><div>Hi Hackers,</div><div>If sql like below:</div><div><br></div><div>begin ;</div><div>update tb_1 SET id = 1;</div><div>select * from tb_1 ;</div><div>select * from tb_2 ;</div><div>select * from tb_3 ;</div><div>select * from tb_4 ;</div><div>select * from tb_5 ;</div><div>commit ;</div><div><br></div><div>when set disable_load_balance_on_write = 'transaction'. write queries appear in an explicit transaction, subsequent read queries are not load balanced until the transaction ends. so all sql will be sent to primary node.</div><div><br></div><div>i think that “update tb_1 SET id = 1” and “select * from tb_1” should be sent to primary node.</div><div>actually, tb_2 tb_3 tb_4 tb_5 can be sent to standby node. if do this, will reduce primary load.</div><div><br></div><div>so i made a patch to implement my idea.</div><div>when transaction start, i will initialize a list to save table name of write queries.</div><div>read queries will check the list, if find the table name in list, read queries will be sent to primary.</div><div>when transaction end, i destroy the list.</div><div><br></div><div>i add two parameter:</div><div><br></div><div>dml_object_level_load_balance = on</div><div>dml_object_level_load_balance_token_list= 'tb_t1:tb_t2,insert_tb_f_func():tb_f,tb_v:tb_v_view'</div><div><br></div><div>use dml_object_level_load_balance_token_list to set relationships between objects, such as trigger, function, view.</div><div>If set dml_object_level_load_balance = on, disable_load_balance_on_write should be off.</div><div><br></div><div>Is it possible to add this feature?</div></div><hr style="width:210px;height:1px" color="#b5c4df" size="1" align="left">
<div><span><div style="margin:10px;font-family:verdana;font-size:10pt"><div><a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a></div></div></span></div>
</div>_______________________________________________<br>
pgpool-hackers mailing list<br>
<a href="mailto:pgpool-hackers@pgpool.net" target="_blank">pgpool-hackers@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
</blockquote></div>
</blockquote></div>
</div></div></blockquote>
</div></blockquote></div></div></div>