Hello, <div> 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.</div><div><br></div>
<div>Let me first describe our environment and on how we wish to use pgpool2. </div><div><br></div><div>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.</div>
<div><br></div><div>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)</div>
<div><br></div><div>Test 1: To test the pgpool connection pooling and loadbalancing.</div><div><br></div><div>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:</div>
<div>starting vacuum...end.</div><div>transaction type: SELECT only</div><div>scaling factor: 100</div><div>query mode: simple</div><div>number of clients: 12</div><div>number of threads: 4</div><div>duration: 30 s</div><div>
number of transactions actually processed: 476867</div><div>tps = 15894.130897 (including connections establishing)</div><div>tps = 15935.429409 (excluding connections establishing)</div><div><br></div><div>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:</div>
<div><div>pgbench -h 127.0.0.1 -p 5432 -U postgres -T 30 -j 4 -S -c 12 pgbench</div><div>starting vacuum...end.</div><div>transaction type: SELECT only</div><div>scaling factor: 100</div><div>query mode: simple</div><div>
number of clients: 12</div><div>number of threads: 4</div><div>duration: 30 s</div><div>number of transactions actually processed: 381812</div><div>tps = 12726.511367 (including connections establishing)</div><div>tps = 12740.921357 (excluding connections establishing)</div>
</div><div><br></div><div>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:</div><div>
<br></div><div><div>Jan 18 10:41:23 localhost pgpool[4686]: DB node id: 0 backend pid: 12227 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 989975;</div><div>Jan 18 10:41:23 localhost pgpool[4684]: DB node id: 1 backend pid: 20430 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 2896056;</div>
<div>Jan 18 10:41:23 localhost pgpool[4679]: DB node id: 0 backend pid: 12222 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 721763;</div><div>Jan 18 10:41:23 localhost pgpool[4617]: DB node id: 1 backend pid: 20426 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 35721;</div>
</div><div><br></div><div>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</div>
<div><br></div><div>Test 2: Connects. </div><div><br></div><div>Ok if pgpool2 is loadbalancing per the connection, I tried with -C option of pgbench. </div><div><br></div><div>Direct connection to master: pgbench -h <master_ip> -p 5432 -U postgres -t 20 -S -C -c 10 pgbench</div>
<div>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.</div><div><br></div><div><div>tps = 41.827791 (including connections establishing)</div>
<div>tps = 3048.315806 (excluding connections establishing)</div></div><div><br></div><div>Now running via pgpool: pgbench -h 127.0.0.1 -p 5432 -U postgres -t 20 -S -C -c 10 pgbench</div><div><br></div><div><div>pgbench -h 127.0.0.1 -p 5432 -U postgres -t 20 -S -C -c 10 pgbench</div>
<div><br></div><div>tps = 139.176424 (including connections establishing)</div><div>tps = 2759.381898 (excluding connections establishing)</div></div><div><br></div><div>(now that is showing some scaling).</div><div><br></div>
<div>Now let us increase the connection, </div><div>pgbench -h <master_ip> -p 5432 -U postgres -t 30 -S -C -c 10 pgbench
</div><div><div><br></div><div>tps = 42.555908 (including connections establishing)</div><div>tps = 3098.405354 (excluding connections establishing)</div></div><div><br></div><div>pgpool: </div><div>pgbench -h 127.0.0.1 -p 5432 -U postgres -t 30 -S -C -c 10 pgbench</div>
<div><div>tps = 162.134643 (including connections establishing)</div><div>tps = 3500.828529 (excluding connections establishing)</div></div><div><br></div><div>(cool still some more improvement)</div><div><br></div><div>Ok lets drive it to the critical state,</div>
<div> pgbench -h <master_ip> -p 5432 -U postgres -t 30 -S -C -c 200 pgbench</div><div>FATAL: sorry, too many clients already</div><div>Thats obvious,</div><div><br></div><div>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.</div>
<div><br></div><div><br></div><div>==============================================</div><div>pgpool.conf</div><div>==============================================</div><div><div>#------------------------------------------------------------------------------</div>
<div># CONNECTIONS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - pgpool Connection Settings -</div><div>listen_addresses = 'localhost'</div>
<div>port = 5432</div><div>socket_dir = '/var/run/postgresql'</div><div><br></div><div># - pgpool Communication Manager Connection Settings -</div><div>pcp_port = 9898</div><div>pcp_socket_dir = '/var/run/postgresql'</div>
<div>pcp_timeout = 10</div><div><br></div><div># - Backend Connection Settings -</div><div>backend_hostname0 = 'master_ip'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div></div><div><br></div><div>
<div>backend_hostname1 = 'slave_ip'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div><br></div><div># - Authentication -</div><div>enable_pool_hba = true</div><div>authentication_timeout = 60</div>
<div><br></div><div># - SSL Connections -</div><div>ssl = false</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># POOLS</div><div>#------------------------------------------------------------------------------</div>
<div># - Pool size -</div><div>num_init_children = 256</div><div>max_pool = 4</div><div><br></div><div># - Life time -</div><div>child_life_time = 120</div><div>child_max_connections = 0</div><div>connection_life_time = 120</div>
<div>client_idle_limit = 120</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOGS</div><div>#------------------------------------------------------------------------------</div>
<div># - Where to log -</div><div>log_destination = 'syslog'</div><div><br></div><div># - What to log -</div><div>print_timestamp = true</div><div>log_connections = false</div><div>log_hostname = false</div><div>log_statement = false</div>
<div>log_per_node_statement = true</div><div>log_standby_delay = 'if_over_threshold'</div><div><br></div></div><div><div># - Syslog specific -</div><div>syslog_facility = 'LOCAL0'</div><div>syslog_ident = 'pgpool'</div>
<div><br></div><div># - Debug -</div><div>debug_level = 0</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># FILE LOCATIONS</div><div>#------------------------------------------------------------------------------</div>
<div>pid_file_name = '/var/run/postgresql/pgpool.pid'</div><div>logdir = '/var/log/postgresql'</div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># CONNECTION POOLING</div><div>#------------------------------------------------------------------------------</div><div>connection_cache = true</div><div>reset_query_list = 'ABORT; DISCARD ALL'</div><div><br>
</div><div>#------------------------------------------------------------------------------</div><div># REPLICATION MODE</div><div>#------------------------------------------------------------------------------</div><div>replication_mode = false</div>
<div>replicate_select = false</div><div>insert_lock = true</div><div>lobj_lock_table = ''</div><div><br></div><div># - Degenerate handling -</div><div>replication_stop_on_mismatch = false</div><div>failover_if_affected_tuples_mismatch = false</div>
<div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOAD BALANCING MODE</div><div>#------------------------------------------------------------------------------</div>
<div>load_balance_mode = true</div><div>ignore_leading_white_space = true</div><div>white_function_list = ''</div><div>black_function_list = 'nextval,setval,lastval,currval'</div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># MASTER/SLAVE MODE</div><div>#------------------------------------------------------------------------------</div><div>master_slave_mode = true</div><div>master_slave_sub_mode = 'stream'</div><div><br></div>
<div># - Streaming -</div><div>sr_check_period = 0</div><div>sr_check_user = 'postgres'</div><div>sr_check_password = ''</div><div>delay_threshold = 10000000</div><div><br></div><div># - Special commands -</div>
<div>#follow_master_command = ''</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div>
<div> # %P = old primary node id</div><div> # %% = '%' character</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># PARALLEL MODE AND QUERY CACHE</div><div>#------------------------------------------------------------------------------</div><div>parallel_mode = false</div><div>enable_query_cache = false</div><div>pgpool2_hostname = ''</div>
<div><br></div></div></div><div><div># - System DB info -</div><div>system_db_hostname = 'localhost'</div><div>system_db_port = 5432</div><div>system_db_dbname = 'pgpool'</div><div>system_db_schema = 'pgpool_catalog'</div>
<div>system_db_user = 'pgpool'</div><div>system_db_password = ''</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># HEALTH CHECK</div><div>
#------------------------------------------------------------------------------</div><div>health_check_period = 0</div><div>health_check_timeout = 20</div><div>health_check_user = 'postgres'</div><div>health_check_password = ''</div>
<div><br></div><div>#------------------------------------------------------------------------------</div><div># FAILOVER AND FAILBACK</div><div>#------------------------------------------------------------------------------</div>
<div>failover_command = '%d%h%p'</div><div>failback_command = '%d%h%p'</div><div>fail_over_on_backend_error = false</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># ONLINE RECOVERY</div><div>#------------------------------------------------------------------------------</div><div>recovery_user = 'postgres'</div><div>recovery_password = ''</div><div>recovery_1st_stage_command = ''</div>
<div>recovery_2nd_stage_command = ''</div><div>recovery_timeout = 90</div><div>client_idle_limit_in_recovery = 0</div><div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># OTHERS</div><div>#------------------------------------------------------------------------------</div><div>relcache_expire = 0</div></div></div><div><br></div><div><br></div><div><br></div><div><br></div>