• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

pgpool-II backend_weight... what if all read-only servers are down?

I've found the documentation on the pgpool-II  backend_weight parameter quite thin, and I wondered if anyone can confirm my understanding.

Let's say I have 1 x Master, and 3 x Read-Only servers... so I set their weightings:

backend_weight0=0  # Primary read-write
backend_weight1=1  # Read-only
backend_weight2=1  # Read-only
backend_weight3=1  # Read-only

Open in new window


I'm presuming that this will send 33% of connections to each read-only server, and only Write transactions to the read-write server (0).  In the case of a single failure, then each of the two remaining will get 50% each, and in the case of TWO failed nodes then the final one will get 100% of the read queries.

QUESTION: What happens when all of the read-only servers (1-3) have failed... will it start using the Read-Write sever (0) to keep us online?

I've been reading the code for select_load_balancing_node(), and I think the answer is yes... because NUM_BACKENDS will be 0, so it will default to the MASTER_NODE_ID... but I'm not a C coder, so I can't guarantee I'm not mistaken. :-P

{
       int selected_slot;
       double total_weight,r;
       int i;

       /* choose a backend in random manner with weight */
       selected_slot = MASTER_NODE_ID;
       total_weight = 0.0;

       for (i=0;i<NUM_BACKENDS;i++)
       {
              if (VALID_BACKEND(i))
              {
                     total_weight += BACKEND_INFO(i).backend_weight;
              }
       }
       r = (((double)random())/RAND_MAX) * total_weight;
       total_weight = 0.0;
       for (i=0;i<NUM_BACKENDS;i++)
       {
              if (VALID_BACKEND(i) && BACKEND_INFO(i).backend_weight > 0.0)
              {
                     if(r >= total_weight)
                            selected_slot = i;
                     else
                            break;
                     total_weight += BACKEND_INFO(i).backend_weight;
              }
       }

       pool_debug("select_load_balancing_node: selected backend id is %d", selected_slot);
       return selected_slot;
}

Open in new window


I realize that this isn't an ideal configuration, as I can't guarantee that "node 0" is the Read-Write server... so extra bonus Karma if you can tell me how to tweak these weightings to automatically follow the current state of which is Read-Write and which is Read-only.

Thanks.
0
Sophia Paterakis
Asked:
Sophia Paterakis
1 Solution
 
Daniel WilsonCommented:
If, by line 19, NUM_BACKENDS indeed equals 0, you're right.  MASTER_NODE_ID will be returned.

Where MASTER_NODE_ID and NUM_BACKENDS are assigned, I'm not sure as it's not in the snippet you posted. Often in C, something in all caps like those are, is a constant rather than a variable.  That's not a language feature, merely a convention.  It doesn't seem to make a lot of sense here, so the pg_pool developers may not be following that convention.
0
 
Sophia PaterakisAuthor Commented:
Hi Daniel,

Thanks, that's the way I read the code... and I've no idea what conventions the pgpool folks use for variables, etc.  Ideally instead of checking the code I'd prefer it if someone has direct experience with pgpool and different failure scenarios.  In the absence of that I'm going to have to build a test system to confirm my understanding before I go any further with the design.
0
 
gheistCommented:
pgpool will keep heap of client connections and use server connections only to process transactions. i.e. it is still offloading single postgresql server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now