Tuesday, May 03, 2011

Pgpool-II Load Balancing

I've been trying, so far unsuccessfully, to get load balancing working in a pgpool-II replication environment with two nodes. This is supposed to spread SELECT queries across multiple nodes, according to the nodes' values of backend_weight. Presumably it does so in a round-robin manner, and if the two nodes had the same weight, query 1 would go to one node, query 2 would go to the other node, query 3 would go back to the first node, and so on...However, my queries are all being sent to one node.

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:

MWrynn said...

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.

MWrynn said...
This comment has been removed by the author.
MWrynn said...

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.

Tatsuo Ishii said...

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.

MWrynn said...

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.