Re: pgpool with PostgreSQL 7.4

> Tatsuo,
>
> I'm excited that pgpool is working for us now. We plan to start using it in the near future on our production applications (hundreds of thousands of DB transactions per day). I have just a couple follow-up questions:
>
> 1. When we use pgpool, should we still use a database connection pool? Or should we have the application share only one connection (that pgpool then transforms into many simulatenous connections)?

I'm afraid I don't understand what you are saying. Maybe you mean
num_init_chidlren = 1 ?

> 2. We have some SELECT statements that are for reporting, and we would like them only to get load balanced to the slave. Is there any way to force a query to be sent to the slave (e.g. using comments at the beginning of the query)? If not, is this something you would consider adding to pgpool?

Sounds nice and it should not be super hard to implement. I'm looking
for adding this to pgpool.

> 3. Do you plan on adding support for more than one slave in the future? Is this feasible?

Yes, it's on my TODO list.
--
Tatsuo Ishii

> Thank you for your time,
> Kevin
>
> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> wrote:
> What are client_encoding values on these two servers? It seems they do
> not match...
> --
> Tatsuo Ishii
>
> > Sure, here is the debug output when I run the following command..
> >
> > -bash-2.05b$ psql -p 9999 -c 'show pool_status' template1
> > psql: server closed the connection unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
> >
> > Debug output from pgpool:
> >
> > 2005-04-14 10:23:55 DEBUG: pid 5557: I am 5557 accept fd 6
> > 2005-04-14 10:23:55 DEBUG: pid 5557: Protocol Major: 3 Minor: 0 database: template1 user: postgres
> > 2005-04-14 10:23:55 DEBUG: pid 5557: connecting postmaster Unix domain socket: /tmp/.s.PGSQL.5432
> > 2005-04-14 10:23:55 DEBUG: pid 5557: connected to postmaster Unix domain socket: /tmp/.s.PGSQL.5432 fd: 7
> > 2005-04-14 10:23:55 DEBUG: pid 5557: read_message_length: lenghth: 8
> > 2005-04-14 10:23:55 DEBUG: pid 5557: read_message_length: lenghth: 30
> > 2005-04-14 10:23:55 ERROR: pid 5557: read_message_length: length does not match between backends master(30) secondary(27)
> >
> > --
> > Kevin
> >
> > Tatsuo Ishii wrote:
> > Can you show me debug messages by starting pgpool with -d option?
> > --
> > Tatsuo Ishii
> >
> > > Hello,
> > >
> > > I'm having trouble getting pgpool to work in master/slave mode. I'm planning on using something like Slony-I for replication, and use pgpool only to load balance the queries across the master and slave, as well as provide failover if the master goes down.
> > >
> > > I configured the settings so replication_mode = false, load_balance_mode = true, and master_slave_mode = true. I also set secondary_backend_host_name and secondary_backend_port to refer to the slave. Here's what happens when I try to connect to the database to show the status:
> > >
> > > -bash-2.05b$ psql -p 9999 -c 'show pool_status' template1
> > > psql: server closed the connection unexpectedly
> > > This probably means the server terminated abnormally
> > > before or while processing the request.
> > > The strange thing is that if I change the configuration so master_slave_mode = false, it seems that the failover works but load balancing does not. When master/slave is disabled, I'm able to show the status and run queries against the master. If I use "pgpool -s s switch" to perform a manual switchover, I am then able to run queries against the slave. But as soon as I try running pgpool with master_slave_mode = true, it stops working and when I try to connect I get the error above ("server closed the connection unexpectedly").
> > >
> > > Am I doing something wrong? Below are my configuration settings when I receive the error. I'm using the latest stable build of pgpool (2.5.1). The master is PG 7.4.7, the slave is PG 7.4.
> > >
> > > [root(at)vl-pe6650-004 etc]# more pgpool.conf
> > > #
> > > # pgpool configuration file sample
> > > # $Header: /cvsroot/pgpool/pgpool/pgpool.conf.sample,v 1.1.1.1 2005/02/14 14:52:21 t-ishii Exp $
> > > # host name or IP address to listen on: '*' for all, '' for no TCP/IP connections
> > > listen_addresses = '*'
> > > # port number for pgpool
> > > port = 9999
> > > # Unix domain socket path. Debian package default to /var/run/postgresql!
> > > socket_dir = '/tmp'
> > > # host name where PostgreSQL server is running on. '' means localhost using UNIX
> > > # domain socket
> > > backend_host_name = ''
> > > # port number PostgreSQL server is running on.
> > > backend_port = 5432
> > > # Unix domain socket path for the backend. Debian package default to /var/run/postgresql!
> > > backend_socket_dir = '/tmp'
> > > # host name where secondary PostgreSQL server is running on. '' means localhost using UNIX
> > > # domain socket
> > > secondary_backend_host_name = '192.168.0.153'
> > > # port number secondary PostgreSQL server is running on.
> > > # 0 means no secondrary PostgreSQL
> > > secondary_backend_port = 5432
> > > # number of pre-forked child process
> > > num_init_children = 32
> > > # numer of connection pool allowed for a child process.
> > > max_pool = 4
> > > # if idle for this seconds, child exits. 0 means no timeout.
> > > child_life_time = 300
> > > # if idle for this seconds, connection to PostgreSQL closes. 0 means
> > > # no timeout
> > > connection_life_time = 0
> > > # logging directory
> > > logdir = '/tmp'
> > > # replication mode
> > > replication_mode = false
> > > # set this to true if you want to avoid deadlock situation when
> > > # replication enabled.
> > > # there will be noticable performance degration, however.
> > > # a work around is set this to false and insert /*STRICT*/ comment
> > > # at the beginning of the SQL command.
> > > replication_strict = true
> > > # when replication_strict is set to false, there will be a chance for
> > > # deadlocks. set this to non 0 (in milli seconds) to detect this
> > > # situation and resolve the deadlock aborting current session.
> > > replication_timeout = 5000
> > > # load balancing mode. i.e. all SELECT except in a transaction block
> > > # are load balanced. This is ignored if replication_mode is false.
> > > load_balance_mode = true
> > > # load balance weight for master and secondary. actual weight is
> > > # calculated by weight_master:weight_secondary. For example both
> > > #
> > > # weight_master = 10 and weight_secondary = 5
> > > # weight_master = 4 and weight_secondary = 2
> > > #
> > > # are regarded as master has double the weight comparing with secondary.
> > > # master and secondary have same weight in the default.
> > > weight_master = 0.5
> > > weight_secondary = 0.5
> > > # if there's a data mismatch between master and secondary
> > > # start degenration to stop replication mode
> > > replication_stop_on_mismatch = false
> > > # semicolon separated list of quries to be issued at the end of session
> > > reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> > > # if true print time stamp to each log line
> > > print_timestamp = true
> > > # if true, operate in master/slave mode
> > > master_slave_mode = true
> > > # 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 = 0
> > > # health check user
> > > health_check_user = 'nobody'
> > >
> > > Thanks for your help,
> > > Kevin
> > >
> > >
> > >
> > >
> > > ---------------------------------
> > > Do you Yahoo!?
> > > Yahoo! Mail - Helps protect you from nasty viruses.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Small Business - Try our new resources site!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site!