[Pgpool-general] Antwort: Re: pgpool-II hanging
Tatsuo Ishii
ishii at sraoss.co.jp
Thu Dec 27 09:31:57 UTC 2007
Thanks for the precious info. Any info which can reproduce the problem
or self containted test cases are always welcome.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
> Hi,
>
> Remarks on reproducing the problem with Your test data:
>
> 1. reproducing not possible within psql command line.
> You have to use a sql script:
>
> psql -d test -f a.sql
>
> where a.sql:
>
> -- start of a.sql
> select * from t1;
> BEGIN;
> select * from t2 limit 1;
> COMMIT;
> select * from t1;
> -- end of a.sql
>
> 2. reproducing was not possible on a single machine with several postgres instances
>
> 3. reproducing was possible on two ore more machines over ethernet
>
> 4. using the latest cvs tree (from 26.th Dec) the problem has been fixed
>
> Thanks for Your fast bug fix!
>
> -----Tatsuo Ishii <ishii at sraoss.co.jp> wrote: -----
>
> >An: Rainer.Rohmfeld at emuge.de
> >Von: Tatsuo Ishii <ishii at sraoss.co.jp>
> >Datum: 22.12.2007 00:43
> >Kopie: pgpool-general at pgfoundry.org
> >Thema: Re: [Pgpool-general] pgpool-II hanging
> >
> >Hi,
> >
> >I could not reproduce your problem here. Can you confirm that my test
> >case reproduces the problem on your system?
> >
> >Here is the test case:
> >
> >... connect to pgpool
> >test=# create table t1(i int);
> >CREATE TABLE
> >test=# insert into t1 values(1);
> >INSERT 0 1
> >test=# insert into t1 values(2);
> >INSERT 0 1
> >test=# insert into t2 values(1);
> >ERROR: relation "t2" does not exist
> >test=# create table t2(i int);
> >CREATE TABLE
> >test=# insert into t2 values(1);
> >INSERT 0 1
> >test=# insert into t2 values(2);
> >INSERT 0 1
> >disconnect to pgpool
> >
> >... connect to pgpool again
> >test=# SELECT 'DBD::Pg ping test';
> > ?column?
> >-------------------
> > DBD::Pg ping test
> >(1 row)
> >
> >test=# BEGIN;
> >BEGIN
> >test=# select * from t1;
> > i
> >---
> > 1
> > 2
> >(2 rows)
> >
> >test=# COMMIT;
> >COMMIT
> >test=# select * from t2;
> > i
> >---
> > 1
> > 2
> >(2 rows)
> >
> >pgpool.conf:
> >
> >#
> ># pgpool-II configuration file sample
> ># $Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.15
> >2007/10/31 10:45:52 y-asaba Exp $
> >
> ># Host name or IP address to listen on: '*' for all, '' for no TCP/IP
> ># connections
> >listen_addresses = 'localhost'
> >
> ># Port number for pgpool
> >port = 9999
> >
> ># Port number for pgpool communication manager
> >pcp_port = 9898
> >
> ># Unix domain socket path. (The Debian package defaults to
> ># /var/run/postgresql.)
> >socket_dir = '/tmp'
> >
> ># Unix domain socket path for pgpool communication manager.
> ># (Debian package defaults to /var/run/postgresql)
> >pcp_socket_dir = '/tmp'
> >
> ># Unix domain socket path for the backend. Debian package defaults to
> >/var/run/postgresql!
> >backend_socket_dir = '/tmp'
> >
> ># pgpool communication manager timeout. 0 means no timeout, but
> >strongly not recommended!
> >pcp_timeout = 3600
> >
> ># number of pre-forked child process
> >num_init_children = 32
> >
> ># Number of connection pools allowed for a child process
> >max_pool = 4
> >
> ># If idle for this many seconds, child exits. 0 means no timeout.
> >child_life_time = 300
> >
> ># If idle for this many seconds, connection to PostgreSQL closes.
> ># 0 means no timeout.
> >connection_life_time = 0
> >
> ># If child_max_connections connections were received, child exits.
> ># 0 means no exit.
> >child_max_connections = 0
> >
> ># If client_idle_limit is n (n > 0), the client is forced to be
> ># disconnected whenever after n seconds idle (even inside an explicit
> ># transactions!)
> ># 0 means no disconnect.
> >client_idle_limit = 0
> >
> ># Maximum time in seconds to complete client authentication.
> ># 0 means no timeout.
> >authentication_timeout = 60
> >
> ># Logging directory
> >logdir = '/tmp'
> >
> ># Replication mode
> >replication_mode = true
> >
> ># Set this to nonzero (in milliseconds) to detect this situation and
> ># resolve the deadlock by aborting current session.
> >replication_timeout = 5000
> >
> ># Load balancing mode, i.e., all SELECTs except in a transaction
> >block
> ># are load balanced. This is ignored if replication_mode is false.
> >load_balance_mode = true
> >
> ># if there's a data mismatch between master and secondary
> ># start degeneration to stop replication mode
> >replication_stop_on_mismatch = false
> >
> ># If true, replicate SELECT statement when load balancing is
> >disabled.
> ># If false, it is only sent to the master node.
> >replicate_select = false
> >
> ># Semicolon separated list of queries to be issued at the end of a
> >session
> >reset_query_list = 'RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> >
> ># If true print timestamp on each log line.
> >print_timestamp = true
> >
> ># If true, operate in master/slave mode.
> >master_slave_mode = false
> >
> ># If true, cache connection pool.
> >connection_cache = true
> >
> ># Health check timeout. 0 means no timeout.
> >health_check_timeout = 20
> >
> ># Health check period. 0 means no health check.
> >health_check_period = 5
> >
> ># Health check user
> >health_check_user = 'nobody'
> >
> ># Execute command by failover.
> ># special values: %d = node id
> ># %h = host name
> ># %p = port number
> ># %D = database cluster path
> ># %% = '%' character
> >#
> >#failover_command = 'ls'
> >
> ># Execute command by failback.
> ># special values: %d = node id
> ># %h = host name
> ># %p = port number
> ># %D = database cluster path
> ># %% = '%' character
> >#
> >#failback_command = 'ls'
> >
> ># If true, automatically lock table with INSERT statements to keep
> >SERIAL
> ># data consistency. An /*INSERT LOCK*/ comment has the same effect.
> > A
> ># /NO INSERT LOCK*/ comment disables the effect.
> >insert_lock = false
> >
> ># If true, ignore leading white spaces of each query while pgpool
> >judges
> ># whether the query is a SELECT so that it can be load balanced.
> > This
> ># is useful for certain APIs such as DBI/DBD which is known to adding
> >an
> ># extra leading white space.
> >ignore_leading_white_space = true
> >
> ># If true, print all statements to the log. Like the log_statement
> >option
> ># to PostgreSQL, this allows for observing queries without engaging
> >in full
> ># debugging.
> >log_statement = false
> >
> ># If true, incoming connections will be printed to the log.
> >log_connections = false
> >
> ># If true, hostname will be shown in ps status. Also shown in
> ># connection log if log_connections = true.
> ># Be warned that this feature will add overhead to look up hostname.
> >log_hostname = false
> >
> ># if non 0, run in parallel query mode
> >parallel_mode = false
> >
> ># if non 0, use query cache
> >enable_query_cache = false
> >
> >#set pgpool2 hostname
> >pgpool2_hostname = ''
> >
> ># system DB info
> >system_db_hostname = 'localhost'
> >system_db_port = 5432
> >system_db_dbname = 'pgpool'
> >system_db_schema = 'pgpool_catalog'
> >system_db_user = 'pgpool'
> >system_db_password = ''
> >
> ># backend_hostname, backend_port, backend_weight
> ># here are examples
> >
> ># - HBA -
> >
> ># If true, use pool_hba.conf for client authentication. In pgpool-II
> ># 1.1, the default value is false. The default value will be true in
> ># 1.2.
> >enable_pool_hba = false
> >
> ># - online recovery -
> ># online recovery user
> >recovery_user = 't-ishii'
> >
> ># online recovery password
> >recovery_password = 't-ishii'
> >
> ># execute a command in first stage.
> >recovery_1st_stage_command = 'copy_base_backup'
> >
> ># execute a command in second stage.
> >recovery_2nd_stage_command = 'pgpool_recovery_pitr'
> >replication_strict = true
> >failover_command = ''
> >failback_command = ''
> >backend_hostname0 = ''
> >backend_port0 = 5432
> >backend_weight0 = 1
> >backend_data_directory0 = '/usr/local/pgsql/data'
> >backend_hostname1 = ''
> >backend_port1 = 5433
> >backend_weight1 = 1
> >backend_data_directory1 = '/usr/local/pgsql/data.repli'
> >
> >> Hello,
> >>
> >> we are testing pgpool-II for data replication in our
> >PostgreSQL-Cluster.
> >> pgpool is hanging during the combinations of select and
> >update/insert
> >> statements
> >> within a transaction. We have reduced all to the following script:
> >>
> >> ---- begin sql ---
> >> SELECT 'DBD::Pg ping test';
> >> BEGIN;
> >> select * from sometable;
> >> COMMIT
> >> select * from anothertable;
> >> --- end sql ----
> >>
> >> pgpool is hanging after the commit-Statement.
> >>
> >>
> >> #---pgpool.conf --------------
> >> listen_addresses = '*'
> >> port = 5432
> >> pcp_port = 9898
> >> socket_dir = '/var/run/postgresql'
> >> ...
> >> replication_mode = true
> >> replication_timeout = 5000
> >> load_balance_mode = true
> >> replicate_select = false
> >> replication_stop_on_mismatch = true
> >> reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION
> >DEFAULT'
> >> print_timestamp = true
> >> connection_cache = true
> >> health_check_timeout = 20
> >> health_check_period = 60
> >> health_check_user = 'nobody'
> >> insert_lock = true
> >> ignore_leading_white_space = true
> >> ...
> >> parallel_mode = false
> >> enable_query_cache = false
> >> backend_hostname0 = 'dbfs2'
> >> backend_port0 = 5435
> >> backend_weight0 = 1
> >> backend_data_directory0 = '/pgpool/dbfs2'
> >> backend_hostname1 = 'dbfs3'
> >> backend_port1 = 5435
> >> backend_weight1 = 1
> >> backend_data_directory1 = '/pgpool/dbfs3'
> >> enable_pool_hba = false
> >> #--- end pgpool.conf
> >>
> >> After setting
> >> replicate_select = true
> >>
> >> all is working fine.
> >>
> >> ----
> >> Best Regards / Mit freundlichen Grüßen
> >>
> >> Dr. R. Rohmfeld
> >> IT und Software Development
> >>
> >> e-mail: Rainer.Rohmfeld at emuge.de
> >>
> >
> >
>
More information about the Pgpool-general
mailing list