Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2015-02-03
Medium Priority
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;
                     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.

Question by:Sophia Paterakis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Accepted Solution

Daniel Wilson earned 2000 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.

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.
LVL 62

Expert Comment

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.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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