[pgpool-general: 180] Getting a better understanding of loadbalancing

[Aditya Shanbhag]

Hello,
 First of all, thanks for creating such a awesome product which is much
needed. Also I am very new to pgpool (just started to work with it about a
day or so), so please do excuse my ignorance.

Let me first describe our environment and on how we wish to use pgpool2.

We have a master-slave (streaming replication which comes with postgresql)
Postgresql 9.1. We want run pgpool2 (without the admin) on each webserver.
The application on webserver will connect locally to the pgpool2 and will
be used for connection pooling and loadbalancing (reads) only.

Now I was successfully in setting up pgpool2 (3.1.1) on ubuntu 10.04.3 LTS
and did some tests The pgpool.conf is at the end of this mail. Somehow I
havent been able to get a real grasp on the way pgpool is loadbalancing. So
I can extract the best from the setup. These are some of the tests I ran
(using pgbench)

Test 1: To test the pgpool connection pooling and loadbalancing.

Run against master db server directly:  pgbench -h <masterserver-IP>  -p
5432 -U postgres -T 30 -j 4 -S -c 12 pgbench. For this I get the results:
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 476867
tps = 15894.130897 (including connections establishing)
tps = 15935.429409 (excluding connections establishing)

Now I run it through pgpool: pgbench -h 127.0.0.1 -p 5432 -U postgres -T 30
-j 4 -S -c 12 pgbench. For this I get the results:
pgbench -h 127.0.0.1  -p 5432 -U postgres -T 30 -j 4 -S -c 12 pgbench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 381812
tps = 12726.511367 (including connections establishing)
tps = 12740.921357 (excluding connections establishing)

So definitely the TPS for select only has reduced a lot. I can see that if
I enable "log_per_node_statement = true", I see that loadbalancing is
working as demonstrated here:

Jan 18 10:41:23 localhost pgpool[4686]: DB node id: 0 backend pid: 12227
statement: SELECT abalance FROM pgbench_accounts WHERE aid = 989975;
Jan 18 10:41:23 localhost pgpool[4684]: DB node id: 1 backend pid: 20430
statement: SELECT abalance FROM pgbench_accounts WHERE aid = 2896056;
Jan 18 10:41:23 localhost pgpool[4679]: DB node id: 0 backend pid: 12222
statement: SELECT abalance FROM pgbench_accounts WHERE aid = 721763;
Jan 18 10:41:23 localhost pgpool[4617]: DB node id: 1 backend pid: 20426
statement: SELECT abalance FROM pgbench_accounts WHERE aid = 35721;

Question I do understand the fact that the loadbalancing is done per
connect rather than transaction by pgpool... so that is why may be the
pgpool shows lower TPS. So no pgpool in a sense is adding overheads here.
Am i mistaken or is there something more to this

Test 2: Connects.

Ok if pgpool2 is loadbalancing per the connection, I tried with -C option
of pgbench.

Direct connection to master: pgbench -h <master_ip>  -p 5432 -U postgres -t
20 -S -C -c 10 pgbench
The max connections on master and slave  is set to be 200 each, so the
above parameter are setup to do accomodate for that part.

tps = 41.827791 (including connections establishing)
tps = 3048.315806 (excluding connections establishing)

Now running via pgpool: pgbench -h  127.0.0.1  -p 5432 -U postgres -t 20 -S
-C -c 10 pgbench

pgbench -h 127.0.0.1 -p 5432 -U postgres -t 20 -S -C -c 10 pgbench

tps = 139.176424 (including connections establishing)
tps = 2759.381898 (excluding connections establishing)

(now that is showing some scaling).

Now let us increase the connection,
pgbench -h <master_ip>  -p 5432 -U postgres -t 30 -S -C -c 10 pgbench

tps = 42.555908 (including connections establishing)
tps = 3098.405354 (excluding connections establishing)

pgpool:
pgbench -h 127.0.0.1  -p 5432 -U postgres -t 30 -S -C -c 10 pgbench
tps = 162.134643 (including connections establishing)
tps = 3500.828529 (excluding connections establishing)

(cool still some more improvement)

Ok lets drive it to the critical state,
 pgbench -h <master_ip>  -p 5432 -U postgres -t 30 -S -C -c 200 pgbench
FATAL:  sorry, too many clients already
Thats obvious,

But with pgpool it gives the same error. I was under the impression that
since for read there are two servers, the number of connections available
would be doubled. The observation I made is that moment connections are
sent via pgpool, the same number of connections are created on the master
as well as the slave instantaneously. So that when the max connections were
used  it actual opened up all of them in both the servers and hence met the
same limit. Is this expected behavior or I have messed up on the
configuration.


==============================================
pgpool.conf
==============================================
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------

# - pgpool Connection Settings -
listen_addresses = 'localhost'
port = 5432
socket_dir = '/var/run/postgresql'

# - pgpool Communication Manager Connection Settings -
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
pcp_timeout = 10

# - Backend Connection Settings -
backend_hostname0 = 'master_ip'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = 'slave_ip'
backend_port1 = 5432
backend_weight1 = 1

# - Authentication -
enable_pool_hba = true
authentication_timeout = 60

# - SSL Connections -
ssl = false

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Pool size -
num_init_children = 256
max_pool = 4

# - Life time -
child_life_time = 120
child_max_connections = 0
connection_life_time = 120
client_idle_limit = 120

#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
# - Where to log -
log_destination = 'syslog'

# - What to log -
print_timestamp = true
log_connections = false
log_hostname = false
log_statement = false
log_per_node_statement = true
log_standby_delay = 'if_over_threshold'

# - Syslog specific -
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'

# - Debug -
debug_level = 0

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/var/log/postgresql'

#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
connection_cache =  true
reset_query_list = 'ABORT; DISCARD ALL'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = false
replicate_select = false
insert_lock = true
lobj_lock_table = ''

# - Degenerate handling -
replication_stop_on_mismatch = false
failover_if_affected_tuples_mismatch = false

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = true
ignore_leading_white_space = true
white_function_list = ''
black_function_list = 'nextval,setval,lastval,currval'

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = true
master_slave_sub_mode = 'stream'

# - Streaming -
sr_check_period = 0
sr_check_user = 'postgres'
sr_check_password = ''
delay_threshold = 10000000

# - Special commands -
#follow_master_command = ''
                                   #   %m = new master node id
                                   #   %H = hostname of the new master node
                                   #   %M = old master node id
                                   #   %P = old primary node id
                                   #   %% = '%' character


#------------------------------------------------------------------------------
# PARALLEL MODE AND QUERY CACHE
#------------------------------------------------------------------------------
parallel_mode = false
enable_query_cache = false
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 = ''

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------
health_check_period = 0
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = ''

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '%d%h%p'
failback_command = '%d%h%p'
fail_over_on_backend_error = false


#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0

#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120118/68de7845/attachment-0001.html>