[pgpool-general: 1382] PgPool 3.2.2 doesn't appear to be load balancing

web at mr-paradox.net web at mr-paradox.net
Tue Feb 12 08:13:29 JST 2013

pgpool-II version 3.2.2 (namameboshi), CentOS

My configuration is currently like this
master_slave_mode = on             # (change requires restart)
master_slave_sub_mode = 'stream'   # (change requires restart)

sr_check_period = 10
sr_check_user = 'nobody'
sr_check_password = 'n0b0dy'
delay_threshold = 10000000

backend_hostname0 = master
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/db/pg'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = slave
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/db/pg'
backend_flag1 = 'ALLOW_TO_FAILOVER'

All reads and writes are currently going to the master.

load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'

I've read the FAQ:

> It seems my pgpool-II does not do load balancing. Why?
> First of all, pgpool-II' load balancing is "session base", not "statement base". That means, DB node
> selection for load balancing is decided at the beginning of session. So all SQL statements are sent to the
> same DB node until the session ends.

To mitigate this I have a low client_idle_limit, since rails will attempt to retain it's own pool.
I'd love an opinion on this.

> Another point is, whether statement is in an explicit transaction or not. If the statement is in a
> transaction, it will not be load balanced in the replication mode. In pgpool-II 3.0 or later, SELECT will be
> load balanced even in a transaction if operated in the master/slave mode.

I read this as: in version >3 that I should expect to see SELECTs load balanced in master/slave mode.

> Note the method to choose DB node is not LRU or some such. Pgpool-II chooses DB node randomly considering
> the "weight" parameter in pgpool.conf. This means that the chosen DB node is not uniformly distributed among
> DB nodes in short term. You might want to inspect the effect of load balancing after ~100 queries have been
> sent.

I set the weight for the master to 0 in hopes that basically all non-writes go to the slave.

Any help would be great, if i can debug, or log anything I'd be happy to do so. (nothing particularly
useful is in the current logs)


More information about the pgpool-general mailing list