[pgpool-general: 5682] Re: How to extend load balancing in pgpool II
ishii at sraoss.co.jp
Thu Aug 3 14:52:02 JST 2017
Probably that is not easier than implementing
"table_name_redirect_preference_list" you proposed because you need to
parse/analyze SQL to check whether tables you do not want to redirect
SRA OSS, Inc. Japan
> Thanks for the info and work-around. Couple of questions more :
> Is there a way to programmatically achieve what is mentioned below ? i.e.
> Can I inject / intercept the sql queries of "interest" so that I can append
> an arbitrary SQL comment to actual SQL queries to make sure that the
> request will go the primary only ? Reason - I'm aware of "heavy write
> tables" but I cannot possibly instruct the app developers to attach a SQL
> comment to such tables.
> " A workaround for the problem is using an SQL comment.
> If "allow_sql_comments" is off,
> /*a comment*/select * from table1;
> will be always redirected to primary (inside /* and */ is an arbitrary
> string). Also you can use:
> pgpool set allow_sql_comments to off;
> to set the configuration variable temporarily. "
> Regards, Dinesh
> On Wed, Aug 2, 2017 at 5:24 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> > Hello,
>> > I would like to know whether it is possible to configure load balancing
>> > module in pgpool II so that it will be possible to redirect requests on
>> > basis of tables (in the sql query) as well. For e.g., configurations like
>> > database_redirect_preference_list and app_name_redirect_preference_list
>> > gives us the granularity to redirect on the basis of database / clients.
>> > I'm looking for something like
>> > table_name_redirect_preference_list =
>> > 'table1:primary;table2:1,table3:standby'.
>> > I couldn't find any such configurations
>> That is not supported in Pgpool-II.
>> > - Is it possible to write some
>> > extensions (custom code to do the stuff mentioned above ) and attach it
>> > pgpool II
>> > or will it be required to branch and add that piece of code to
>> > pgpool source and do a build / install ?
>> Yes. BTW I think you should decide what to do if table1 and table2 are
>> used in a SELECT.
>> > Reason : - I have set up a postgres cluster with SR (Streaming
>> > Replication). Due to inherent design, things are asynchronous and there
>> > will be a read miss if a recently inserted row in master was read from
>> > slave. I went through the configuration sr_check_period and
>> > delay_threshold. What will happen to requests (insert row; read same
>> > which was done under 1 second (lowest possible value for
>> > I do not want to opt for sync replication due to performance reasons and
>> > also due to the fact that majority of the queries are "reads".
>> > I'm aware of tables with heavy writes - Hence such a configuration /
>> > (if available in runtime - even better) will help me to circumvent the
>> > problem. Of course, I'm pretty much new to pgpool II - Please correct me
>> > I've got it completely wrong.
>> A workaround for the problem is using an SQL comment.
>> If "allow_sql_comments" is off,
>> /*a comment*/select * from table1;
>> will be always redirected to primary (inside /* and */ is an arbitrary
>> string). Also you can use:
>> pgpool set allow_sql_comments to off;
>> to set the configuration variable temporarily.
>> Best regards,
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
More information about the pgpool-general