This table, taken from the pgpool-II docs, shows whether a SELECT query will replicate, be sent to master only, or be load balanced based on three conditions:
| SELECT is inside a transaction block | Y | Y | Y | N | N | N | Y | N |
| replicate_select is true | Y | Y | N | N | Y | Y | N | N |
| load_balance_mode is true | Y | N | N | N | Y | N | Y | Y |
| results(R:replication, M: send only to master, L: load balance) | R | R | M | M | R | R | M | L |
It's absolutely clear that I have replicate_select set to false and load_balance_mode set to true. I seem to be getting a result of "M", which must mean my SELECT is in a transaction block. It doesn't appear to be though, acc. to my pg logs - at least, I don't see any BEGIN before the SELECT. Hmm!
If I fiddle with my two values of backend_weight and restart pgpool, it will switch to the node with the greater weight. Doesn't help me much, but it shows it's doing...something.
Stay tuned...
5 comments:
Found a sugggestion in this presentation - http://www.dalibo.org/_media/pgpool.pdf (page 34) that suggests turning autocommit off if it's not already... Will follow up.
Well, that didn't do the job. What it did was, instead of sending standalone SELECTs to only one node, it sent to one node (for each SELECT):
BEGIN
SELECT
ROLLBACK
In addition, the other node received (again for each SELECT):
BEGIN
ROLLBACK
In other words, with autocommit off, I believe I am seeing the Y/N/Y=M case of the table I posted. Prior to this test (when autocommit was on), I believe I SHOULD have been in the N/N/Y=L case. Apparently this is not true however.
Pgpool-II does not do load balancing using LRU pocily. Rather it sends quries randomly considering "weight". So you need to send enough number of queries(~100 for example) to observe the effect of load balancing. Also note that the node to be sent is determined when client connects to pgpool-II, not each statement. If your client keeps on connecting to pgpool-II, it keeps on sending queries to the same node until it disconnects to pgpool-II.
Thank you Tatsuo. I got it working. I mistakenly believed that load balancing worked within a single connection. Now I can see it is balancing connections.
Post a Comment