[pgpool-general: 5330] Re: load_balance_mode off : pooling and database_redirect_preference_list

[Tatsuo Ishii]

> Hi
> PgPool gurus  I need some help - probably lacking basic understanding.
> 
> I came to a project which was relying on pgpool for all three main features - replication, pooling and load_balancing.
> Project was running on 1(master)+3(replica) cluster suffering from frequent inconsistency issues ( select issued right after insert/update returning not expected results ) as well as extremely
> unbalanced  heavy load on master at times when system becomes busy due to multiple small queries ...
> The former , I guess due to wrong / lack of transaction management in application , the later - due to multiple  queries issues by pgpool itself against master to validate ability to redirect "selects" to replicas.
> ( this , in turn , might be influenced/explained by somewhat big data dictionary I have - hundreds of schemsa with 20-40 tables in each ? ) .
> 
> Anyway , by simply setting load_balance_mode to "off" I was able to resolve both above issues and release 2  servers ( per environment ! ). Now running on 1 + 1 using replica for failover only.
> Needless to say that transaction response time was closely monitored and remains same or lower after the change ...
> 
> However , this "failover only" part , makes me unhappy as entire server still  stays unutilized. Furthermore , I am trying to leverage internal knowledge that one ( biggest ) database on the cluster is essentially "read only"
> ( only controlled bulk loads few times an hour ) . Basically , want to have an ability to route queries for certain database to dedicated replica ... I also want these connections to use large work_mem ...
> Unfortunately was not able to find a way to implement via pgpool. Answers for questions below could really help :
> 
> 
> Two  questions :
> 
> 1.       Tests I performed shows that pgpool is  issuing queries on master with or without database_redirect_preference_list set ( in load_balance mode , of course ? )
> 
> Is it expected behavior ?  Configurable , maybe ?

Yes, it's an expected behavior.

> The second question is somewhat related :
> 
> 2.       With load_balance_mode=off I would expect no connections to be opened to replica. Am I right ?
> 
> One can tell that it is necessary for faster failover ...

No. See [pgpool-general: 5295].

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Appreciate your help
> 
> 
> Versions involved :
> Current prod versions
> Postgres : PostgreSQL 9.3.3 ( scheduled upgrade for 9.5 already . 9.6 ? - maybe )
> pgpool : pgpool-II version 3.4.7 (tataraboshi)
> 
> Tests above  performed on PostgreSQL 9.5.5 and pgpool-II version 3.5.4 (ekieboshi)
> 
> 
> 
> 
> 
> [download]
> 
> Ilya Dorfman
> StormRunner DBA
> Office +972-35398451 | Mobile +972-52-6815384
> 
> [cid:image002.png at 01D12D20.35EFB3C0]<https://www.hp.com/go/srl>
> 
> 
>