Solved

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

Posted on 2015-02-03
3
185 Views
Last Modified: 2015-05-14
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
Comment
Question by:Sophia Paterakis
3 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40586342
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
 
LVL 1

Author Comment

by:Sophia Paterakis
ID: 40586397
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
 
LVL 61

Expert Comment

by:gheist
ID: 40597133
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now