Load Balancing/Query Routing using PGPOOL-II ( Part-II)

Oct 1, 2022

Mins to Read

All

In our first part of the Pgpool-II series, the Installation and Configuration of Pgpool-II were discussed. This part will focus on one prominent feature of Pgpool-II, which is Load Balancing/Query Routing.

What is Load Balancing?

load balancing is the process of distributing a set of tasks over a set of resources (computing units), with the aim of making their overall processing more efficient

Wikipedia

Yes, we require load balancing to distribute the traffic on each PostgreSQL server, hence to avoid either over utilization or under utilization. This helps to reduce the load on each server by distributing the workload evenly.

Why do we need Pgpool-II for load balancing?

PostgreSQL won’t have any inbuilt mechanism for load balancing the queries across the servers in its replication topology. Either this should be handled by the application or tools like pgpool-II.

Pgpool-II can help in achieving the distribution of READ queries across the PostgreSQL servers. So, no major changes are required in the application layer.

The following diagram represents the architecture of primary-replica servers with pgpool-II as an intermediate layer between the applications and the databases.

How is it actually work?

Pgpool-II is an intermediate layer between the application and the databases. It receives queries from the application and sends them back to the backend database servers. Before it sends to the database, it parses the queries to identify whether it is READ or WRITE. Based on it, it sends to the database accordingly.

Types of Load Balancing modes

pgpool-II provides two options for load balancing. Based on the use case, it is mandatory to pick either one of them before configuration.

Session Level Load Balancing(Default)
Statement Level Load Balancing

Session Level Load Balancing:

This is the default mode
This mode determines the backend database server to send read queries when the client connects to the pgpool.

For Example, if we have two database servers, one of them is selected randomly each time a new session is created.

Statement Level Load Balancing:

To enable this load balancing method, the variable statement_level_load_balance needs to be on.
This mode determines the backend database server to send read queries whenever each query starts.
some kinds of applications use only one session and execute too many queries in a single session. For these applications, the default mode won’t provide proper load balancing because the majority of the queries hit the same server.

Now, let’s start the demonstration for load balancing. For this demonstration, the following database architecture will be used

‍

The architecture diagram for this demonstration is

The installation, setup, and configuration of Pgpool-II are already discussed in part 1 of this series. Referring to the last part for the pgpool-II installation and configuration is advised. Here, the focus will be on load balancing and its related variables in Pgpool-II.

Session Level Load Balancing:

Here, pgpool is running on port 9999. So, accessing the database through pgpool-II as follows

 
root@pgpool:~# export PGPASSWORD='****' ;psql -h127.0.0.1 -p9999 -Umonitor
psql (14.5 (Ubuntu 14.5-1.pgdg18.04+1))
Type "help" for help.

 
monitor=# show pool_nodes;
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node
 --------+---------------+------+--------+-----------+-----------+---------+---------+------------+-----------------
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 2          | true              
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             
 2       | 192.168.33.14 | 5432 | unused | unknown   | 0.333333  | standby | unknown | 0          | false             
(3 rows)

This session is connected to the node_id 0. (Refer to the value TRUE in load_balance_mode column to check the actual target of the current session)

Now executing a few select queries to check the target server

 
monitor=# select 1;
 ?column?
----------
        1
(1 row)

monitor=# select 1;
 ?column?
----------
        1
(1 row)

monitor=# select 1;
 ?column?
----------
        1
(1 row)

monitor=# show pool_nodes;
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node 
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+---------------------
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 5          | true              
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             
 2       | 192.168.33.14 | 5432 | unused | unknown   | 0.333333  | standby | unknown | 0          | false             
(3 rows)

All the queries executed in this session are pointed to the same server node_id 0. Select_cnt values have increased from 2 to 5 confirming it.

Statement Level Load Balancing:

Now change the mode to statement-based load balancing in pgpool -II by adding the following variable in pgpool config file /etc/pgpool-II/pgpool.conf and followed by pgpool reload (No restart required).

statement_level_load_balance = on

 
monitor=# show pool_nodes;
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node 
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+---------------------
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 12         | true              
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 5          | false             
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             
(3 rows)

monitor=# select 1;
 ?column?
----------
        1
(1 row)

monitor=# show pool_nodes;
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node
 --------+---------------+------+--------+-----------+-----------+---------+---------+------------+--------------------
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 12         | false             
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 6          | true              
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             
(3 rows)

Refer to the value TRUE in load_balance_mode column to check the actual target of the last select execution.

In statement mode, the first select query hits the node_id 0, and the second query hits the node_id 1.

Both the load balancing method won’t send queries to the node_id 2 as it has been marked as down by pgpool-II. So, it helps to avoid query failures by hitting the failed replicas or replicas having replication lag.

More Granular Load Balancing:

Other than this, more advanced query routing is possible in pgpool-II such as Load Balancing based on Application Names, Load Balancing based on Database Names, Routing certain queries to primary, Avoid reading queries from replicas having lag, and Node Weightage.

Load Balancing Based on Application Names:

Sometimes, it may require connecting the application to a specific database server without load balancing. For example, Report Queries to the Reporting Replica. This can be achieved by using the variable app_name_redirect_preference_list

Here, configuring the dashboard app to hit only the replica and the payment app to hit the primary as follows

app_name_redirect_preference_list=’payment:0,dashboard:1′

This requires pgpool reload post added it in the config file.

 
root@pgpool:~# PGAPPNAME='payment' psql -h127.0.0.1 -p9999 -Umonitor -c "show pool_nodes"
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node
 --------+---------------+------+--------+-----------+-----------+---------+---------+------------+--------------------
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 0          | true              
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             
(3 rows)

root@pgpool:~# PGAPPNAME='dashboard' psql -h127.0.0.1 -p9999 -Umonitor -c "show pool_nodes"
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+--
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 0          | false             |
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | true              |
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             |
(3 rows)

Now it is verified that the payment app hits to node_id 0 and the dashboard app hits to node_id 1.

Load Balancing Based on Database Names:

Load Balancing by Database Names is possible by using the following variable

database_redirect_preference_list

Its behavior is the same as app_name_redirect_preference_list. Instead of routing queries based on the application name, it routes based on database names. It will be useful for cases such as a server having a live DB and a reporting DB. It can be configured to route live DB queries to the primary server and the reporting DB queries to the replica server.

database_redirect_preference_list=’live:0,report:1′

This requires pgpool reload post added it in the config file.

 
root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor live -c "show pool_nodes"
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 0          | true              |
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             |
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             |
(3 rows)

root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor report -c "show pool_nodes"
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 0          | false             |
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | true              |
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             |
(3 rows)

As per the configuration, the queries are hitting the databases live and report on node_id 0 and node_id 1 respectively.

Query routing only to the primary server(Query Pattern Based Routing):

Certain queries can be routed only to the primary server by adding comments in the query like /*NO LOAD BALANCE*/ .

Example:

 
root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor -c "select /*NO LOAD BALANCE*/ count(*) from pg_stat_activity"
 count
-------
     8
(1 row)

root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor -c "select /*NO LOAD BALANCE*/ count(*) from pg_stat_activity"
 count
-------
     9
(1 row)

root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor -c "select /*NO LOAD BALANCE*/ count(*) from pg_stat_activity"
 count
-------
    10
(1 row)

root@pgpool:~# psql -h127.0.0.1 -p9999 -Umonitor live -c "show pool_nodes"
 node_id |   hostname    | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node |
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+
 0       | 192.168.33.12 | 5432 | up     | unknown   | 0.333333  | primary | unknown | 3          | true              |
 1       | 192.168.33.13 | 5432 | up     | unknown   | 0.333333  | standby | unknown | 0          | false             |
 2       | 192.168.33.14 | 5432 | down   | unknown   | 0.333333  | standby | unknown | 0          | false             |
(3 rows)

All the queries were hit only to the primary server.

One more option for query pattern-based routing can be achieved by using the variable

primary_routing_query_pattern_list

primary_routing_query_pattern_list = ‘.*pg_stat_activity.*’

To route all the queries on table pg_stat_activity to the primary server.

Replication Lag Handling:

To avoid reading stale data from the replicas having replication lag, it is necessary to set the variable delay_threshold. This value represents the maximum value of replication lag in WAL bytes on the replica server against the primary server.

If the delay exceeds the configured value, queries won’t be sent to the particular replica until the lag reduces.

Node Weightage:

Pgpool sends the queries to the backend server randomly. Sometimes, it may be required to completely avoid sending the read traffic to the primary server. This can be done by using weight.

For the primary server setting the value backend_weight0=0 will avoid further read traffic to the primary server.

An important question may arise about how pgpool handles a transaction. By default, once a write query is executed in an explicit transaction, the subsequent read queries are not load-balanced until the transaction ends. It is defined by the variable disable_load_balance_on_write. The default value for this variable is transaction. This value suits most cases and it is better left untouched. This variable supports the following values as well OFF, trans_transaction, and Always.

‍

That’s it on the load balancing part in pgpool-II. Will explore a lot more features of pgpool-II in upcoming blogs at Mydbops.

No items found.