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

[Muhammad Usama]

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/20200424/d9b20a47/attachment.html>