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

[Fernando Luizão]

It is far from the 40/60 proportion I would like.

So far, it seems that when a query is dispatched to a server, the following
are as well. Sometimes I have a load of more than 90% in the standby, when
the writing starts, the proportions are inverted and practically all the
queries goes to the primary.

Another thing I noticed is that sometimes the standby appears down even
though everything it is ok.
If I restart the pgpool, it still shows as down. So I need to stop pgpool,
delete pgpool_status, and start again, only then it shows up. Maybe I have
to set up the healthchecks properly?

Thanks you for your help.

On Thu, Mar 14, 2019 at 6:08 PM Pierre Timmermans <ptim007 at yahoo.com> wrote:

> 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.2
> pgpool - 4.0.2
>
> Here's what I changed in pgpool.conf:
>
> listen_addresses = '*'
> port = 5432
>
> backend_hostname0 = '192.168.51.186'
> backend_port0 = 5433
> backend_weight0 = 0.4
> backend_data_directory0 = '/var/lib/postgresql/11/main'
> backend_flag0 = 'ALWAYS_MASTER'
>
> backend_hostname1 = '192.168.51.185'
> backend_port1 = 5433
> backend_weight1 = 0.6
> backend_data_directory1 = '/var/lib/postgresql/11/main'
>
> load_balance_mode = on
>
> master_slave_mode = on
> master_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/20190315/06e73f13/attachment.html>