[pgpool-general: 6460] Re: Load balancing and unused status

[Pierre Timmermans]

I am not familiar with load-balancing feature of pgpool. But you gave a higher weight to the standby, so I suppose the stand-by should get more or less 60% of the load ? 
In order to test I suggest that you start a psql session, execute one read-query, then disconnect it. If you do that 100 times, you should have a 60/40 proportions to the stand-by I suppose ? Write statement will always be sent to the primary database. It is no use to execute multiple queries in each psql session because from the doc you know that all queries in one particular session will always go to the same database.
A problem with load-balancing is that if the stand-by database is stopped then the sessions that were using the standby must be re-created by pgpool (it will do it automatically), but it means that stopping the standby has an impact on the applications (which might not be a big problem but it is good to know).
Pierre 

    On Thursday, March 14, 2019, 8:12:50 PM GMT+1, Fernando Luizão <fernandoluizao at gmail.com> wrote:  
 
 Thanks, now the standby is up.
But I'm not sure if I understand the balancing rules.
What I would like is for the consultations to be divided more evenly.
I tried to test the load balancing by opening several psql's on my local machine and doing queries on each of them, but it looks like the queries are always being routed to the standby now ...

The docs says:
To which node the load balancing mechanism sends read queries is decided at the session start time and will not be changed until the session ends.

Does this mean that every query will be directed to the same node, until there is a write operation, on connection basis?

Thanks,Fernando
On Thu, Mar 14, 2019 at 3:36 PM Pierre Timmermans <ptim007 at yahoo.com> wrote:

For some reason the status is unused, it should be up or down.
I think you should stop pgpool, remove the file /tmp/pgpool_status, then restart pgpool. The file /tmp/pgpool_status is used by pgpool to record the status of the node. When the file is not there it will create it at startup
Regards, 
Pierre 

    On Thursday, March 14, 2019, 7:32:04 PM GMT+1, Fernando Luizão <fernandoluizao at gmail.com> wrote:  
 
 Hi,
I'm new to pgpool, and I'm trying to use the load balancer. I have a primary and a hot standby, using native streaming replication.
Versions: postgresql - 11.2pgpool - 4.0.2
Here's what I changed in pgpool.conf:
listen_addresses = '*'port = 5432
backend_hostname0 = '192.168.51.186'backend_port0 = 5433backend_weight0 = 0.4backend_data_directory0 = '/var/lib/postgresql/11/main'backend_flag0 = 'ALWAYS_MASTER'
backend_hostname1 = '192.168.51.185'backend_port1 = 5433backend_weight1 = 0.6backend_data_directory1 = '/var/lib/postgresql/11/main'
load_balance_mode = on
master_slave_mode = onmaster_slave_sub_mode = 'stream'
However, when I inspect the nodes on pgpool, I get the following:
postgres=# show pool_nodes; node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change  ---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0       | 192.168.51.186 | 5433 | up     | 0.400000  | primary | 6113       | true              | 0                 | 2019-03-14 11:39:15 1       | 192.168.51.185 | 5433 | unused | 0.600000  | standby | 0          | false             | 0                 | 2019-03-14 11:39:15
All queries are being redirected to primary. I can query the standby directly, so everything seems fine with my replication setup.
Why it says the standby is unused? I'm missing something in the configuration?
Thanks,
Fernando_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
  
  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20190314/6dca0a18/attachment.html>