[pgpool-general: 4946] Re: Load balancing issue
Tatsuo Ishii
ishii at sraoss.co.jp
Wed Aug 24 13:35:45 JST 2016
You can use pcp_proc_info command to check if the PostgreSQL process
are relevant to Pgpool-II or not.
http://www.pgpool.net/docs/latest/pgpool-en.html#pcp_proc_info
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> Interesting thing, though, is that I see postgres processes being forked on
> pgpool2 eve though in the logs I see only the queries listed above.
> ...
>
> 65032 ? Ss 0:00 *postgres*: myuser mydb 192.168.241.101(40278)
> idle
>
> 65033 ? Ss 0:00 *postgres*: myuser mydb 192.168.241.101(40280)
> idle
>
> 65034 ? Ss 0:00 *postgres*: myuser mydb 192.168.241.101(40282)
> idle
>
> 65035 ? Ss 0:00 *postgres*: myuser mydb 192.168.241.101(40288)
> idle
>
> 65036 ? Ss 0:00 *postgres*: myuser mydb 192.168.241.101(40290)
> idle
>
> [vlad at pgpool2 ~]$ ps ax | grep postgres | grep myuser | wc -l
>
> 317
>
> Any ideas why can those processes be forked? It feels like pgpool creates
> slots just in case it needs to failover, but it's not documented anywhere
> and that's why it looks weird.
>
> On Tue, Aug 23, 2016 at 9:30 AM, Vlad Novikov <xou.slackware at gmail.com>
> wrote:
>
>> 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
>>> >> >>>
>>> >> >>>
>>> >> >>
>>> >>
>>>
>>
>>
More information about the pgpool-general
mailing list