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

Vlad Novikov xou.slackware at gmail.com
Mon Aug 22 23:40:39 JST 2016


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. Then, when I start pgpool2, I need to
manually attach it, so pgpool-II would know that the backend is online,
right?

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 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. Also, as far as I
understand, this setting should not matter when load_balance_mode=off.

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
> >>>
> >>>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160822/11bd9c17/attachment.html>


More information about the pgpool-general mailing list