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

[Lucas Luengas]

Hello.

You can try to enable more log information. In pgpool, you can enable
log_statement and log_per_node_statement. In postgresql, en both nodes, you
can enable log_statement.
With this information, you can see if connections to standby node are from
pgpool health checks or from your application.



On Sat, Aug 20, 2016 at 6:22 PM, Vlad Novikov <xou.slackware at gmail.com>
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. 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.
>
> 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
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160820/8d5ce740/attachment.html>