[pgpool-general: 4942] Re: Load balancing issue

[Tatsuo Ishii]

> Hello,
> 
> I attach node only when I start the failover node (PostgreSQL) after
> primary is up and running. E.g. I start PostgreSQL on pgpool1, then I start
> popool-II and it detects the backend.

Probably at this point a fail over happens because PostgreSQL pgpool2
is not running. So far so good.
 
> Then, when I start pgpool2, I need to
> manually attach it, so pgpool-II would know that the backend is online,
> right?

Correct.

> Now nodes status. At this moment I see following nodes status:
> node_id  hostname   port   status   lb_weight  role       select_cnt
> 0        pgpool1    5432      2     0.500000   primary    66133198
> 1        pgpool2    5432      2     0.500000   standby    0

I don't know which node you executed "show pool_nodes" but as long as
the result of the command are same, it is ok.

So you sometimes see write queries are sent to PostgreSQL on pgpool2?

> I have a sneaky feeling that lb_weight has something to do about what is
> going on. However, if you take a look at the pgpool.conf attached to the
> initial message, you'll find the backends configured like this:
> backend_hostname0 = 'pgpool1'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/9.4/data'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_hostname1 = 'pgpool2'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/9.4/data'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> 
> In particular, backend_weight is set to 1 on both nodes.

Don't worry. The load balance weight is always "normalized" so that
the tatal weight is 1.0.

> Also, as far as I
> understand, this setting should not matter when load_balance_mode=off.

Correct.

> Regards,
> Vlad
> 
> 
> On Sat, Aug 20, 2016 at 4:37 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi Lucas,
>> >
>> > I checked the log and found no failover entries. Here's how that
>> happens. I
>> > start two postgres backends (master-slave streaming replication) and
>> > pgpool-II instance. Then I attach both of the nodes and initially all the
>> > clients get connected to master only.
>>
>> Why do you need attach the backends? Pgpool-II automatically attach
>> all backends valid in pgpool.conf.
>>
>> > I see that with ps ax | grep
>> > postgres. After some time new clients start getting connected to the hot
>> > standby node while older clients are still connected to master. Again, I
>> > see that with ps ax | grep postgres. In that case both master and hot
>> > standby have pgpool-II connected. That's what concerns me the most. If
>> > there was a failover event, master would've been detached and there would
>> > be no pgpool-II connections there.
>>
>> Can you connect to pgool using psql then issue "show pool_nodes" when
>> pgpool starts to behaves like this? This should show which is the
>> primary node (role) and which node should be the node the query routes
>> to (load_balance_node).
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > Vlad
>> >
>> > On Sat, Aug 20, 2016 at 1:25 AM, Lucas Luengas <lucasluengas at gmail.com>
>> > wrote:
>> >
>> >> Hello.
>> >>
>> >> Have you checked pgpool log file?  Maybe a failover happened?
>> >>
>> >> On Fri, Aug 19, 2016 at 10:48 PM, Vlad Novikov <xou.slackware at gmail.com
>> >
>> >> wrote:
>> >>
>> >>> Hello,
>> >>>
>> >>> I'm have setup pgpool-II with 2 backends in streaming mode (see
>> >>> configuration file attached). In particular, I have load_balance_mode
>> = off
>> >>> to make sure that in this pool all connections will be established to
>> >>> streaming master only. However, over some time I see pgpool-II
>> establishing
>> >>> connections to hot standby server. As a result client applications
>> start
>> >>> failing as they cannot write to the database they're connected to. So
>> far
>> >>> the only solution for me is to keep the hot standby detached (which is
>> not
>> >>> a good idea in terms of automated failover).
>> >>> Pgpool-II starts behaving like this at about 100 clients connected.
>> >>> PostgreSQL max_connections is set to 900 and with hot standby detached
>> >>> there are no connection issues reported (all clients can connect to the
>> >>> backend with no issues).
>> >>> I use pgpool-II and PostgreSQL provided by PostgreSQL official
>> repository.
>> >>> PostgreSQL 9.4.9
>> >>> pgpool-II 3.5.3
>> >>> OS: CentOS 7.2
>> >>>
>> >>> Is there anything I need to change in the configuration file to make
>> all
>> >>> clients connect to master only when both backends are attached? From
>> what I
>> >>> understand that is expected with load_balance_mode = off.
>> >>>
>> >>> Regards,
>> >>> Vlad
>> >>>
>> >>> _______________________________________________
>> >>> pgpool-general mailing list
>> >>> pgpool-general at pgpool.net
>> >>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>> >>>
>> >>>
>> >>
>>