[pgpool-general: 5329] load_balance_mode off : pooling and database_redirect_preference_list

[Dorfman, Ilya]

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 ?



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 ...



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>



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170212/87de16f8/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 3790 bytes
Desc: image001.png
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170212/87de16f8/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 775 bytes
Desc: image002.png
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170212/87de16f8/attachment-0001.png>