PostgreSQL + pgpool replication with miss balancing

Question

I have a PostgreSQL replication M-S with pgpool as a load balancer on master server only. The replication is going OK and there is no delay on the process. The problem is that the master server is receiving more request than the slave even when I have configured a balance different from 50% for each server.

This is the pgpool show_pool_nodes with backend weigth M(1)-S(2)

node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master-ip   | 9999 | up     | 0.333333  | primary | 56348331   | false             | 0
 1       | slave-ip    | 9999 | up     | 0.666667  | standby | 3691734    | true              | 0

as you can appreciate the master server is receiving +10x request than slave

This is the pgpool show_pool_nodes with backend weigth M(1)-S(5)

 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master-ip   | 9999 | up     | 0.166667  | primary | 10542201   | false             | 0
 1       | slave-ip    | 9999 | up     | 0.833333  | standby | 849494     | true              | 0

The behave is quite similar when I assign M(1)-S(1)

Now I wonder if I miss understood the pgpool functioning:

1. Pgpool only balances read queries(as write queries are sent to master always)

2. Backend Weight parameter is assigned to calculate distribution only in balancing mode. As greater the value is more likely to be chosen for pgpool, so if a server has a greater lb_weight it would be selected more times than others with lower values.

If I'm right why is happening this? Is there a way that I can actually assign a proper balancing configuration of select_cnt queries? My intention is to overcharge the slave with read queries and let to master only a "few" read queries as it is taking all the writing.

Answer 1

The optimal configuration is to set the master node for write operations and the slave node for read operations. For a more efficient setup, use a three-node cluster where one node serves as the master (write-only) and the other two as slaves (read-only).

To configure Pgpool to send only read-only queries to standby nodes, adjust the following settings in /etc/pgpool2/pgpool.conf under the #LOAD BALANCING MODE section:

load_balance_mode = on
                                   # Enable load balancing mode
                                   # (requires restart to take effect)
ignore_leading_white_space = on
                                   # Ignore leading white spaces in each query
white_function_list = ''
                                   # List of functions that do not write to the database
                                   # (comma-separated, regular expressions allowed)
black_function_list = 'currval,lastval,nextval,setval'
                                   # List of functions that write to the database
                                   # (comma-separated, regular expressions allowed)
black_query_pattern_list = ''
                                   # List of query patterns that should be sent to the primary node
                                   # (semicolon-separated, regular expressions allowed)
                                   # Valid only in streaming replication mode
database_redirect_preference_list = '.*:standby'
                                   # Pairs of databases and node IDs
                                   # e.g., 'postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
                                   # Valid only in streaming replication mode
app_name_redirect_preference_list = ''
                                   # Pairs of application names and node IDs
                                   # e.g., 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
                                   # Valid only in streaming replication mode
allow_sql_comments = off
                                   # If on, ignores SQL comments for load balancing or query cache judgments
                                   # If off, SQL comments affect these judgments (pre-3.4 behavior)
disable_load_balance_on_write = 'transaction'
                                   # Load balance behavior when a write query is issued within a transaction
                                   # 'transaction' (default): Subsequent read queries are not load balanced until the transaction ends
                                   # 'trans_transaction': Subsequent read queries in an explicit transaction are not load balanced until the session ends
                                   # 'always': Subsequent read queries are not load balanced until the session ends
statement_level_load_balance = on
                                   # Enables load balancing at the statement level

Answer 2

You can try to adjust below one configs in pgpool.conf file: 1. wal lag delay size delay_threshold = 10000000

it is used to let pgpool know if the slave postgresql wal is too delay to use. Change large more query can be pass to slave. Change small more query will go to master.

Besides, the pgbench testing parameter is also key. Use -C parameter, it will let connection per query, otherwise connection per session.

pgpoll load balance decision making depends of a matrix of parameter combination. not only a single parameter

Here is reference. https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html#GUC-LOAD-BALANCE-MODE

Answer 3

You are right on pgpool load balancing. There could be some reasons why this doesn't seem to work. For start, notice that you have the same port number for both backends. Try configuring your backend connection settings like shown in the sample pgpool.conf: https://github.com/pgpool/pgpool2/blob/master/src/sample/pgpool.conf.sample (lines 66-87), (where you also set the weights to your needs) and assign different port numbers to each backend.

Also check (assuming your running mode is master/slave):

• load_balance_mode = on
• master_slave_mode = on

-- changes require restart

There is a relevant FAQ entry " It seems my pgpool-II does not do load balancing. Why?" here: https://www.pgpool.net/mediawiki/index.php/FAQ (if pgpool version 4.1 also consider statement_level_load_balance). So far, i have assumed that the general conditions for load balancing (https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html) are met.