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

[Vlad Novikov]

Ok, looks like the connections I see on pgpool2 are just healthchecks and
standard db SELECT calls:

SELECT 1;

SELECT class.relname, blocking_activity.client_addr,
blocking_activity.query FROM  pg_catalog.pg_locks blocked JOIN
pg_catalog.pg_stat_activity a ON a.pid=blocked.pid JOIN pg_catalog.pg_locks
locks ON locks.locktype = blocked.locktype AND locks.DATABASE IS NOT
DISTINCT FROM blocked.DATABASE AND locks.relation IS NOT DISTINCT FROM
blocked.relation AND locks.page IS NOT DISTINCT FROM blocked.page AND
locks.tuple IS NOT DISTINCT FROM blocked.tuple AND locks.virtualxid IS NOT
DISTINCT FROM blocked.virtualxid AND locks.transactionid IS NOT DISTINCT
FROM blocked.transactionid AND locks.classid IS NOT DISTINCT FROM
blocked.classid AND locks.objid IS NOT DISTINCT FROM blocked.objid AND
locks.objsubid IS NOT DISTINCT FROM blocked.objsubid AND locks.pid !=
blocked.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON
blocking_activity.pid=locks.pid JOIN pg_catalog.pg_class class ON
class.oid=locks.relation WHERE NOT blocked.GRANTED;

I think we can consider this solved. Thank you, guys, for helping with this.

Regards,

Vlad

On Mon, Aug 22, 2016 at 3:41 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

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