[pgpool-general: 5674] Re: How to extend load balancing in pgpool II

[Dinesh Kumar]

Hello,

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
> the
> > 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
> to
> > pgpool II
>
> No.
>
> > 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
> the
> > slave. I went through the configuration sr_check_period and
> > delay_threshold. What will happen to requests (insert row; read same
> row;)
> > which was done under 1 second (lowest possible value for
> sr_check_period).
> > 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 /
> module
> > (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
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170802/987a1045/attachment.html>