[pgpool-hackers: 3648] Re: add a feature: dml object level load balance

[Muhammad Usama]

Hi Sunbiao,

Can you kindly resend the 0001-dml-load-balance-patch-v2.patch patch? I am
not able to download the patch file.

Thanks
Best regards
Muhammad Usama


On Fri, Apr 24, 2020 at 9:55 PM Muhammad Usama <m.usama at gmail.com> wrote:

> Hi Sunbiao,
>
> Thanks for the patch and it looks line an interesting feature. I have just
> skimmed
> through the patch and I have a few of small comments.
>
> 1 - Wouldn't it be better to add a new mode existing
> disable_load_balance_on_write parameter
> instead of adding a new configuration parameter i.e
> dml_object_level_load_balance ?
> You can make disable_load_balance_on_write to accept new value like
> disable_load_balance_on_write = 'object' to enable this feature.
>
> 2- The patch contains some invalid changes in pgpool.conf.sample-stream
> file
> i.e it changes the default values of black_function_list and
> disable_load_balance_on_write
> configuration parameters which I am sure were not intended
>
> 3- The default value for new configuration parameter
> dml_object_level_load_balance_token_list
> better be an empty
>
> 4- Instead of attaching a separate test script you could include a proper
> test case for the feature.
>
> Thanks
> Best Regards
>
> Muhammad Usama
> Highgo Software (Canada/China/Pakistan)
> URL : http://www.highgo.ca
> ADDR: 10318 WHALLEY BLVD, Surrey, BC
>
>
>
> On Fri, Apr 24, 2020 at 3:43 PM sunbiao at highgo.com <sunbiao at highgo.com>
> wrote:
>
>> Hi Hackers,
>> If sql like below:
>>
>> begin ;
>> update tb_1 SET id = 1;
>> select * from tb_1 ;
>> select * from tb_2 ;
>> select * from tb_3 ;
>> select * from tb_4 ;
>> select * from tb_5 ;
>> commit ;
>>
>> 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.
>>
>> i think that “update tb_1 SET id = 1” and “select * from tb_1” should be
>> sent to primary node.
>> actually, tb_2 tb_3 tb_4 tb_5 can be sent to standby node. if do this,
>> will reduce primary load.
>>
>> so i made a patch to implement my idea.
>> when transaction start, i will initialize a list to save table name of
>> write queries.
>> read queries will check the list, if find the table name in list, read
>> queries will be sent to primary.
>> when transaction end, i destroy the list.
>>
>> i add two parameter:
>>
>> dml_object_level_load_balance = on
>> dml_object_level_load_balance_token_list=
>> 'tb_t1:tb_t2,insert_tb_f_func():tb_f,tb_v:tb_v_view'
>>
>> use dml_object_level_load_balance_token_list to set relationships between
>> objects, such as trigger, function, view.
>> If set dml_object_level_load_balance = on, disable_load_balance_on_write
>> should be off.
>>
>> Is it possible to add this feature?
>> ------------------------------
>> sunbiao at highgo.com
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200612/0498f12e/attachment.html>