[Pgpool-general] pgpool-ii health checking

Oleg Laktionov olaktionov at gmail.com
Tue Nov 13 11:40:44 UTC 2007 

Hi,

thank for your response!

This is the configuration file that I use...

listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 32
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
authentication_timeout = 60

# logging
logdir = '/var/log'
print_timestamp = true
log_statement = true
log_connections = true
log_hostname = true
# End of logging

# Work mode
parallel_mode = false
replication_mode = false
load_balance_mode = true
master_slave_mode = true
# End of the work mode

reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
connection_cache = true

# Health checking
health_check_timeout = 20
health_check_period = 10
health_check_user = 'postgres'
# End of health checking

insert_lock = false
ignore_leading_white_space = false
enable_query_cache = false
pgpool2_hostname = 'replica.test.sportbox.ru'
backend_hostname0 = 'replica.test.sportbox.ru'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'replica1.test.sportbox.ru'
backend_port1 = 5432
backend_weight1 = 1
enable_pool_hba = true
backend_hostname2 = 'tmpwc.test.sportbox.ru'
backend_port2 = 5432
backend_weight2 = 1
enable_pool_hba = true

I use OpenVZ test nodes...(2.6.18-028stab035.1-ovz-smp, ubuntu template).

root at replica:~# pgpool -n -d
...Skipped..

2007-11-13 13:39:33 LOG:   pid 24485: starting health checking
2007-11-13 13:39:33 DEBUG: pid 24485: health_check: 0 the DB node status: 2
2007-11-13 13:39:33 DEBUG: pid 24485: health_check: 1 the DB node status: 2
2007-11-13 13:39:33 DEBUG: pid 24485: health_check: 2 the DB node status: 2

...Skipped...

It seems that all is okay... When I stop PostgreSQL server on the node tmpwc
and try to make
a simple query to the pgpool (SELECT ...) I see this information...

2007-11-13 13:42:44 ERROR: pid 24485: connect_inet_domain_socket: connect()
failed: Connection refused
2007-11-13 13:42:44 ERROR: pid 24485: health check failed. 2 th host
tmpwc.test.sportbox.ru at port 5432 is down
2007-11-13 13:42:44 LOG:   pid 24485: set 2 th backend down status
2007-11-13 13:42:44 DEBUG: pid 24485: failover_handler called
2007-11-13 13:42:44 LOG:   pid 24485: starting degeneration. shutdown host
tmpwc.test.sportbox.ru(5432)
2007-11-13 13:42:44 DEBUG: pid 24485: failover_handler: starting to select
new master node
2007-11-13 13:42:44 LOG:   pid 24485: failover_handler: do not restart
pgpool. same master node 0 was selected
2007-11-13 13:42:44 LOG:   pid 24485: failover done. shutdown host
tmpwc.test.sportbox.ru(5432)
2007-11-13 13:42:54 LOG:   pid 24485: starting health checking
2007-11-13 13:42:54 DEBUG: pid 24485: health_check: 0 the DB node status: 2
2007-11-13 13:42:54 DEBUG: pid 24485: health_check: 1 the DB node status: 2
2007-11-13 13:42:54 DEBUG: pid 24485: health_check: 2 the DB node status: 3

Pgpool successfully realized the failure...

Further I start the Postgresql server on the node tmpwc ...

2007-11-13 13:44:04 DEBUG: pid 25879: I am 25879
2007-11-13 13:44:14 LOG:   pid 24485: starting health checking
2007-11-13 13:44:14 DEBUG: pid 24485: health_check: 0 the DB node status: 2
2007-11-13 13:44:14 DEBUG: pid 24485: health_check: 1 the DB node status: 2
2007-11-13 13:44:14 DEBUG: pid 24485: health_check: 2 the DB node status: 3
2007-11-13 13:44:24 LOG:   pid 24485: starting health checking
2007-11-13 13:44:24 DEBUG: pid 24485: health_check: 0 the DB node status: 2
2007-11-13 13:44:24 DEBUG: pid 24485: health_check: 1 the DB node status: 2
2007-11-13 13:44:24 DEBUG: pid 24485: health_check: 2 the DB node status: 3

Nevertheless pgpoll does not see this event... Ordinary connection to the
tmpwc works fine...

legik at replica:~$ /usr/local/postgres/bin/psql -U zope -h
tmpwc.test.sportbox.ru -d sportdb
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

sportdb=#

Further I test with pg_bench...

2007-11-13 13:48:55 LOG:   pid 25848: connection received: host=
tmpwc.test.sportbox.ru port=46253
2007-11-13 13:48:55 DEBUG: pid 25848: Protocol Major: 3 Minor: 0 database:
sportdb user: zope
2007-11-13 13:48:55 DEBUG: pid 25848: new_connection: connecting 0 backend
2007-11-13 13:48:55 DEBUG: pid 25848: new_connection: connecting 1 backend
2007-11-13 13:48:55 DEBUG: pid 25848: new_connection: connecting 2 backend
2007-11-13 13:48:55 DEBUG: pid 25848: new_connection: skipping slot 2
because backend_status = 3
2007-11-13 13:48:55 DEBUG: pid 25848: pool_read_message_length: slot: 0
length: 8
2007-11-13 13:48:55 DEBUG: pid 25848: pool_read_message_length: slot: 1
length: 8
2007-11-13 13:48:55 DEBUG: pid 25848: trying clear text password
authentication
2007-11-13 13:48:55 DEBUG: pid 25848: trying clear text password
authentication
2007-11-13 13:48:55 DEBUG: pid 25848: trying clear text password
authentication
2007-11-13 13:48:55 DEBUG: pid 24485: reap_handler called
2007-11-13 13:48:55 DEBUG: pid 24485: reap_handler: call wait3
2007-11-13 13:48:55 DEBUG: pid 24485: child 25848 exits with status 11 by
signal 11
2007-11-13 13:48:55 DEBUG: pid 26148: I am 26148
2007-11-13 13:48:55 DEBUG: pid 24485: fork a new child pid 26148
2007-11-13 13:48:55 DEBUG: pid 24485: reap_handler: normally exited
2007-11-13 13:49:04 LOG:   pid 24485: starting health checking
2007-11-13 13:49:04 DEBUG: pid 24485: health_check: 0 the DB node status: 2
2007-11-13 13:49:04 DEBUG: pid 24485: health_check: 1 the DB node status: 2
2007-11-13 13:49:04 DEBUG: pid 24485: health_check: 2 the DB node status: 3

Test fail...

Then I stop and start the pgpool and all is okay again...


2007-11-13 13:51:05 DEBUG: pid 26216: key: pcp_port
2007-11-13 13:51:05 DEBUG: pid 26216: value: 9898 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: socket_dir
2007-11-13 13:51:05 DEBUG: pid 26216: value: '/tmp' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: pcp_socket_dir
2007-11-13 13:51:05 DEBUG: pid 26216: value: '/tmp' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_socket_dir
2007-11-13 13:51:05 DEBUG: pid 26216: value: '/tmp' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: pcp_timeout
2007-11-13 13:51:05 DEBUG: pid 26216: value: 10 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: num_init_children
2007-11-13 13:51:05 DEBUG: pid 26216: value: 32 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: max_pool
2007-11-13 13:51:05 DEBUG: pid 26216: value: 4 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: child_life_time
2007-11-13 13:51:05 DEBUG: pid 26216: value: 300 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: connection_life_time
2007-11-13 13:51:05 DEBUG: pid 26216: value: 0 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: child_max_connections
2007-11-13 13:51:05 DEBUG: pid 26216: value: 0 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: authentication_timeout
2007-11-13 13:51:05 DEBUG: pid 26216: value: 60 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: logdir
2007-11-13 13:51:05 DEBUG: pid 26216: value: '/var/log' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: print_timestamp
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: log_statement
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: log_connections
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: log_hostname
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: parallel_mode
2007-11-13 13:51:05 DEBUG: pid 26216: value: false kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: replication_mode
2007-11-13 13:51:05 DEBUG: pid 26216: value: false kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: load_balance_mode
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: master_slave_mode
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: reset_query_list
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'ABORT; RESET ALL; SET SESSION
AUTHORIZATION DEFAULT' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: extract_string_tokens: token: ABORT
2007-11-13 13:51:05 DEBUG: pid 26216: extract_string_tokens: token:  RESET
ALL
2007-11-13 13:51:05 DEBUG: pid 26216: extract_string_tokens: token:  SET
SESSION AUTHORIZATION DEFAULT
2007-11-13 13:51:05 DEBUG: pid 26216: key: connection_cache
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: health_check_timeout
2007-11-13 13:51:05 DEBUG: pid 26216: value: 20 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: health_check_period
2007-11-13 13:51:05 DEBUG: pid 26216: value: 10 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: health_check_user
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'postgres' kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: insert_lock
2007-11-13 13:51:05 DEBUG: pid 26216: value: false kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: ignore_leading_white_space
2007-11-13 13:51:05 DEBUG: pid 26216: value: false kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: enable_query_cache
2007-11-13 13:51:05 DEBUG: pid 26216: value: false kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: pgpool2_hostname
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'replica.test.sportbox.ru'
kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_hostname0
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'replica.test.sportbox.ru'
kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_port0
2007-11-13 13:51:05 DEBUG: pid 26216: value: 5432 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: port slot number 0
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_weight0
2007-11-13 13:51:05 DEBUG: pid 26216: value: 1 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: weight slot number 0
weight: 1.000000
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_hostname1
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'replica1.test.sportbox.ru'
kind: 4
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_port1
2007-11-13 13:51:05 DEBUG: pid 26216: value: 5432 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: port slot number 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_weight1
2007-11-13 13:51:05 DEBUG: pid 26216: value: 1 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: weight slot number 1
weight: 1.000000
2007-11-13 13:51:05 DEBUG: pid 26216: key: enable_pool_hba
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_hostname2
2007-11-13 13:51:05 DEBUG: pid 26216: value: 'tmpwc.test.sportbox.ru' kind:
4
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_port2
2007-11-13 13:51:05 DEBUG: pid 26216: value: 5432 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: port slot number 2
2007-11-13 13:51:05 DEBUG: pid 26216: key: backend_weight2
2007-11-13 13:51:05 DEBUG: pid 26216: value: 1 kind: 2
2007-11-13 13:51:05 DEBUG: pid 26216: pool_config: weight slot number 2
weight: 1.000000
2007-11-13 13:51:05 DEBUG: pid 26216: key: enable_pool_hba
2007-11-13 13:51:05 DEBUG: pid 26216: value: true kind: 1
2007-11-13 13:51:05 DEBUG: pid 26216: num_backends: 3 num_backends: 3
total_weight: 3.000000
2007-11-13 13:51:05 DEBUG: pid 26216: backend 0 weight: 715827882.333333
2007-11-13 13:51:05 DEBUG: pid 26216: backend 1 weight: 715827882.333333
2007-11-13 13:51:05 DEBUG: pid 26216: backend 2 weight: 715827882.333333
2007-11-13 13:51:05 DEBUG: pid 26216: loading "/usr/local/etc/pool_hba.conf"
for client authentication configuration file
2007-11-13 13:51:05 DEBUG: pid 26217: I am 26217
2007-11-13 13:51:05 DEBUG: pid 26218: I am 26218
2007-11-13 13:51:05 DEBUG: pid 26220: I am 26220
2007-11-13 13:51:05 DEBUG: pid 26221: I am 26221
2007-11-13 13:51:05 DEBUG: pid 26222: I am 26222
2007-11-13 13:51:05 DEBUG: pid 26223: I am 26223
2007-11-13 13:51:05 DEBUG: pid 26219: I am 26219
2007-11-13 13:51:05 DEBUG: pid 26224: I am 26224
2007-11-13 13:51:05 DEBUG: pid 26225: I am 26225
2007-11-13 13:51:05 DEBUG: pid 26226: I am 26226
2007-11-13 13:51:05 DEBUG: pid 26227: I am 26227
2007-11-13 13:51:05 DEBUG: pid 26228: I am 26228
2007-11-13 13:51:05 DEBUG: pid 26229: I am 26229
2007-11-13 13:51:05 DEBUG: pid 26230: I am 26230
2007-11-13 13:51:05 DEBUG: pid 26231: I am 26231
2007-11-13 13:51:05 DEBUG: pid 26232: I am 26232
2007-11-13 13:51:05 DEBUG: pid 26233: I am 26233
2007-11-13 13:51:05 DEBUG: pid 26234: I am 26234
2007-11-13 13:51:05 DEBUG: pid 26235: I am 26235
2007-11-13 13:51:05 DEBUG: pid 26236: I am 26236
2007-11-13 13:51:05 DEBUG: pid 26237: I am 26237
2007-11-13 13:51:05 DEBUG: pid 26238: I am 26238
2007-11-13 13:51:05 DEBUG: pid 26239: I am 26239
2007-11-13 13:51:05 DEBUG: pid 26240: I am 26240
2007-11-13 13:51:05 DEBUG: pid 26241: I am 26241
2007-11-13 13:51:05 DEBUG: pid 26242: I am 26242
2007-11-13 13:51:05 DEBUG: pid 26243: I am 26243
2007-11-13 13:51:05 DEBUG: pid 26244: I am 26244
2007-11-13 13:51:05 DEBUG: pid 26245: I am 26245
2007-11-13 13:51:05 DEBUG: pid 26246: I am 26246
2007-11-13 13:51:05 DEBUG: pid 26247: I am 26247
2007-11-13 13:51:05 DEBUG: pid 26248: I am 26248
2007-11-13 13:51:05 LOG:   pid 26216: pgpool successfully started
2007-11-13 13:51:05 DEBUG: pid 26249: I am PCP 26249
2007-11-13 13:51:05 LOG:   pid 26216: starting health checking
2007-11-13 13:51:05 DEBUG: pid 26216: health_check: 0 the DB node status: 1
2007-11-13 13:51:05 DEBUG: pid 26216: health_check: 1 the DB node status: 1
2007-11-13 13:51:05 DEBUG: pid 26216: health_check: 2 the DB node status: 1
2007-11-13 13:51:15 LOG:   pid 26216: starting health checking
2007-11-13 13:51:15 DEBUG: pid 26216: health_check: 0 the DB node status: 1
2007-11-13 13:51:15 DEBUG: pid 26216: health_check: 1 the DB node status: 1
2007-11-13 13:51:15 DEBUG: pid 26216: health_check: 2 the DB node status: 1

And I have some more question. Can I disable failover mode for the pgpool?
For example I wish to use only replica.test.sportbox.ru as the master node.
Is it possible anyhow?
Thank you.


2007/11/13, Yoshiyuki Asaba <y-asaba at sraoss.co.jp>:
>
> Hi,
>
> From: "Oleg Laktionov" <olaktionov at gmail.com>
> Subject: Re: [Pgpool-general] pgpool-ii health checking
> Date: Mon, 12 Nov 2007 10:37:06 +0300
>
> > I use pgpool-ii-1.3.
>
> Hmm... I can't reproduce it.
> Could you show us pgpool's error log?
>
> Regards,
> --
> Yoshiyuki Asaba
> y-asaba at sraoss.co.jp
>
>
> >
> >
> > 2007/11/12, Yoshiyuki Asaba <y-asaba at sraoss.co.jp>:
> > >
> > > Hi,
> > >
> > > From: "Oleg Laktionov" <olaktionov at gmail.com>
> > > Subject: [Pgpool-general] pgpool-ii health checking
> > > Date: Fri, 9 Nov 2007 16:19:43 +0300
> > >
> > > > About pgpool-ii...
> > > > Can anybody explain me the mechanism of performing backend health
> > > checking?
> > > > Pgpool-ii successfully understands that some of the backends is
> down.
> > > > Nevertheless when some of the backends is online again, pgpool does
> not
> > > see
> > > > this event.
> > > > Only restart of the pgpool-ii helps...
> > >
> > > Which version do you use?
> > >
> > > --
> > > Yoshiyuki Asaba
> > > y-asaba at sraoss.co.jp
> > > _______________________________________________
> > > Pgpool-general mailing list
> > > Pgpool-general at pgfoundry.org
> > > http://pgfoundry.org/mailman/listinfo/pgpool-general
> > >
> >
> >
> >
> > --
> > best regards,
> > Oleg Laktionov
>



-- 
best regards,
Oleg Laktionov
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgpool-general/attachments/20071113/5842a405/attachment-0001.html

More information about the Pgpool-general mailing list