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

[sunbiao at highgo.com]

Hi, Usama

Thanks for your comments. it’s helpfull.
I have removed dml_object_level_load_balance and made disable_load_balance_on_write to accept new value.
I have removed my changes in pgpool.conf.sample-stream.

Now i am writing a test script.
When i finish it, i will make a new patch.

Thanks
Best Regards


sunbiao at highgo.com
 
From: Muhammad Usama
Date: 2020-04-25 00:55
To: sunbiao at highgo.com
CC: pgpool-hackers
Subject: Re: [pgpool-hackers: 3592] add a feature: dml object level load balance
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/20200427/73431936/attachment.html>