• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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