Query Load Balancing in Pgpool-II

-

Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are:
  • Connection pooling
  • Query load balancing
  • Automated failover
  • Watchdog (High availability of Pgpool-II)
  • Replication
  • In memory query cache

In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters.

Why use query load balancing?

Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers.

If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is,

  • improve the system's throughput
  • reduce the load on each PostgreSQL server
  • optimize resource utilization of PostgreSQL servers

Pgpool-II's load balancing works best in a scenario where there are a lot of users executing many read-only queries at the same time. 

How does query load balancing work in Pgpool-II?

Pgpool-II can be used as the load balancer for PostgreSQL clusters.

Pgpool-II located between the clients and PostgreSQL servers is able to understand PostgreSQL's backend and frontend protocol and relay client queries to PostgreSQL servers. Since Pgpool-II has imported PostgreSQL parser, it can parse client's queries. Pgpool-II routes queries as follows:

  1. Pgpool-II receives a client query and generates a raw parse tree
  2. Pgpool-II parses the parse tree to determine if it is a READ query or a WRITE query
  3. In case of WRITE query, Pgpool-II sends it to primary PostgreSQL server
  4. In case of READ query, Pgpool-II sends it to the load balancing node selected from all available PostgreSQL servers

To enable load balancing in Pgpool-II, turn on load_balance_mode:

load_balance_mode = on

Failure handling

As mentioned above, Pgpool-II sends WRITE queries to primary server and distributes READ queries across all available PostgreSQL servers. However, when the primary or standby server fails, how does Pgpool-II ensure the connectivity with all available PostgreSQL servers. Fortunately, Pgpool-II can detect database failures, detach the failed database and trigger failover.

Load balancing mode

Pgpool-II has two load balancing modes:

Pgpool-II randomly selects a load balancing node from all available PostgreSQL servers according to backend_weight and sends READ queries to that load balancing node.
The difference between Session level load balancing and Statement level load balancing is the timing to select the load balancing node. In the next sections, I will describe these two modes in detail. 

Session level load balancing

By default, load balancing mode is "session level" which means the load balancing node is determined when a client connects to Pgpool-II. Once the load balancing node is selected, it will not change until the client closes the session.

 

Let's verify session level load balancing. As shown in the result of show pool_nodes, node 1 is the load balancing node for this session.

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt| load_balance_node|  
---------+----------+-------+--------+-----------+--------+-----------+------------------+
 0       | /tmp     | 11002 | up     | 0.333333  | primary| 0         | false            |
 1       | /tmp     | 11003 | up     | 0.333333  | standby| 0         | true             |
 2       | /tmp     | 11004 | up     | 0.333333  | standby| 0         | false            |

Execute SELECT several times.

test=# select 1;
 ?column?
----------
        1
test=# select 1;
 ?column?
----------
        1
test=# select 1;
 ?column?
----------
        1

As shown in the result of show pool_nodes, all the SELECT queries are routed to node 1 (the load balancing node for session).

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt| load_balance_node|
---------+----------+-------+--------+-----------+--------+-----------+------------------+
 0       | /tmp     | 11002 | up     | 0.333333  | primary| 0         | false            |
 1       | /tmp     | 11003 | up     | 0.333333  | standby| 3         | true             |
 2       | /tmp     | 11004 | up     | 0.333333  | standby| 0         | false            |

Statement level load balancing

On the other hand, in statement Level load balancing mode the load balance node is determined at the time each query starts. This mode is useful in case that application has its own connection pooling feature (e.g. Tomcat) which keeps the connections open to Pgpool-II and the load balance node does not change once the connection is created. Another use case is a batch application which issues tremendous number of queries but there's only 1 session. 

 


To enable statement Level load balancing mode:

statement_level_load_balance = on

Let's verify statement level load balancing. As show in the result, the load balacing node is selected for each query.

test=# select 1;
 ?column?
----------
        1

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt| load_balance_node|
---------+----------+-------+--------+-----------+--------+-----------+------------------+
 0       | /tmp     | 11002 | up     | 0.333333  | primary| 0         | false            |
 1       | /tmp     | 11003 | up     | 0.333333  | standby| 0         | false            |
 2       | /tmp     | 11004 | up     | 0.333333  | standby| 1         | true             |

test=# select 1;
 ?column?
----------
        1

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt| load_balance_node|
---------+----------+-------+--------+-----------+--------+-----------+------------------+
 0       | /tmp     | 11002 | up     | 0.333333  | primary| 0         | false            |
 1       | /tmp     | 11003 | up     | 0.333333  | standby| 1         | true             |
 2       | /tmp     | 11004 | up     | 0.333333  | standby| 1         | false            |

test=# select 1;
 ?column?
----------
        1

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role  | select_cnt| load_balance_node|
---------+----------+-------+--------+-----------+--------+-----------+------------------+
 0       | /tmp     | 11002 | up     | 0.333333  | primary| 0         | false            |
 1       | /tmp     | 11003 | up     | 0.333333  | standby| 1         | false            |
 2       | /tmp     | 11004 | up     | 0.333333  | standby| 2         | true             |

Load balancing weight

As I mentioned in the previous section, to perform load balancing, Pgpool-II selects a load balancing node and routes READ queries to that node. The load balancing node is randomly selected according the weight specified in backend_weight.

For example, there are three PostgreSQL nodes in streaming replication, and node 0 is the primary. If the weight of all PostgreSQL nodes are equal, queries are distributed equally.

backend_weight0 = 1
backend_weight1 = 1
backend_weight2 = 1

If the primary is dedicated to executing WRITE queries, you can specify the weight of node 0 to 0:

backend_weight0 = 0
backend_weight1 = 1
backend_weight2 = 1

Load balancing behavior of functions

Although Pgpool-II can parse SQL, it can not determine if a function modifies the database. In Pgpool-II 4.1 and earlier, users need to specify the function names in write_function_list or read_only_function_list respectively.

Since Pgpool-II 4.2, if the write_function_list and the read_only_function_list are both empty, Pgpool-II will access system catalog and check the volatile property. If a function included in SELECT/WITH has volatile property, Pgpool-II regards it a writing function and sends the query to primary.

 

Advanced settings

I have described the basic load balancing in Pgpool-II. In the next sections, I will describe some advanced load balancing settings.

Load balancing by database/application name

Pgpool-II's load balancing allows READ queries for a particular database or application connection to be sent to a particular PostgreSQL server. The relevant configuration parameters are database_redirect_preference_list and app_name_redirect_preference_list. These parameters allow you to specify the dedicated load balancing destination for particular database and application connections so that they do not disturb each other.

database_redirect_preference_list = "application-name:node id(weight)"
app_name_redirect_preference_list = "database-name:node id(weight)"

For example, if you specify the parameter:

database_redirect_preference_list = 'mydb1:1,mydb2:2'

The above settings represents the following query routing rules:

  • Route all READ queries on "mydb1" database to backend node 1 (specified in backend_hostname1).
  • Route all READ queries on "mydb2" database to backend node 2 (specified in backend_hostname2).

Next, let's verify the settings.

As shown in the result of "show pool_nodes" command, the load_balance_node is node 1 when the database name is mydb1 and the load_balance_node is node 2 when the database name is mydb2.

$ psql -h localhost -p 11000 -U pengbo -d mydb1 -c "show pool_nodes"
 node_id | hostname | port  |status| lb_weight|  role  | select_cnt | load_balance_node|
---------+----------+-------+------+----------+--------+------------+------------------+
 0       | /tmp     | 11002 | up   | 0.333333 | primary| 0          | false            |
 1       | /tmp     | 11003 | up   | 0.333333 | standby| 0          | true             |
 2       | /tmp     | 11004 | up   | 0.333333 | standby| 0          | false            |

$ psql -h localhost -p 11000 -U pengbo -d mydb2 -c "show pool_nodes"
 node_id | hostname | port  | status| lb_weight|  role  | select_cnt | load_balance_node|
---------+----------+-------+-------+----------+--------+------------+------------------+
 0       | /tmp     | 11002 | up    | 0.333333 | primary| 0          | false            |
 1       | /tmp     | 11003 | up    | 0.333333 | standby| 0          | false            |
 2       | /tmp     | 11004 | up    | 0.333333 | standby| 0          | true             |

 
For example, if you specify the application name as below: 

app_name_redirect_preference_list = 'myapp1:1,myapp2:2' 

Next, let's verify the settings.  Specify the application name and connect to Pgpool-II. As shown in the result of show pool_nodes command, the load_balance_node is node 1 when the application name is myapp1 and the load_balance_node is node 2 when the application name is myapp2.

$ PGAPPNAME=myapp1 psql -h localhost -p 11000 -U pengbo -d test -c "show pool_nodes"
 node_id | hostname | port  | status| lb_weight|  role  | select_cnt | load_balance_node |
---------+----------+-------+-------+----------+--------+------------+-------------------+
 0       | /tmp     | 11002 | up    | 0.333333 | primary| 0          | false             |
 1       | /tmp     | 11003 | up    | 0.333333 | standby| 0          | true              |
 2       | /tmp     | 11004 | up    | 0.333333 | standby| 0          | false             |

$ PGAPPNAME=myapp2 psql -h localhost -p 11000 -U pengbo -d test -c "show pool_nodes"
 node_id | hostname | port  | status| lb_weight|  role  | select_cnt | load_balance_node |
---------+----------+-------+-------+----------+--------+------------+-------------------+
 0       | /tmp     | 11002 | up    | 0.333333 | primary| 0          | false             |
 1       | /tmp     | 11003 | up    | 0.333333 | standby| 0          | false             |
 2       | /tmp     | 11004 | up    | 0.333333 | standby| 0          | true              |

Routing particular queries to primary

In general, READ queries are load balanced across multiple PostgreSQL servers. If you want to disable load balancing for particular queries, there two ways to route queries to primary server.

  • Inserting arbitrary comments. For example, /*NO LOAD BALANCE*/ SELECT * FROM t1

Load balancing behavior in a transaction

When performing READ query load balancing across multiple PostgreSQL servers, ideally each server should return the same results. 

However, In a PostgreSQL cluster using streaming replication, after a write query is executed, it is possible to read old data from standby servers due to the time lag. disable_load_balance_on_write provides a way to control the load balancing behavior after a write query is executed. This parameter is especially useful in streaming replication mode.

disable_load_balance_on_write = 'off'

READ queries are load balanced even if a WRITE query is executed. 
 

disable_load_balance_on_write = 'transaction' (default) 

Once a WRITE query is executed in an explicit transaction, the subsequent READ queries are not load balanced until the transaction ends.

disable_load_balance_on_write = 'trans_transaction'

Once a WRITE query is executed in an explicit transaction, the subsequent READ queries are not load balanced until the transaction ends. Also, the READ queries are not load balanced in subsequent explicit transactions.

transactions.disable_load_balance_on_write = 'always'

Once a WRITE query is executed in an explicit transaction, load balancing is not performed until the session ends, regardless of whether it is in an explicit transaction or not.
 
There is a trade-off between data inconsistency and performance. For example, if disable_load_balance_on_write = 'always', after a WRITE query is executed, Pgpool-II routes READ queries to primary only to ensure the latest data can be retrieved. However, this setting might cut the system's throughput because only the primary is used.

Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: # ...
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authen...
Read more