diff --git a/doc.ja/src/sgml/loadbalance.sgml b/doc.ja/src/sgml/loadbalance.sgml index 209cf4d..a0a179e 100644 --- a/doc.ja/src/sgml/loadbalance.sgml +++ b/doc.ja/src/sgml/loadbalance.sgml @@ -1364,6 +1364,43 @@ writeクエリがプライマリサーバに送られた時、この変更はス + + statement_level_load_balance (boolean) + + + statement_level_load_balance 設定パラメータ + + + + + +onに設定すると、参照クエリごとに負荷分散先を決めます。 +offに設定すると、セッションが始まるときに決められた負荷分散先がセッションが終了するまで変更されません。 +例えば、コネクションプールを利用し、バックエンドサーバに接続したままのようなアプリケーションの場合、 +セッションが長い間保持される可能性があるので、セッションが終了するまで負荷分散先のノードが変わりません。 +このようなアプリケーションでは、statement_level_load_balanceを有効にすると、 +セッションごとではなく、クエリごとに負荷分散先を決めることが可能です。デフォルトはoffです。 + + + +このパラメータはPgpool-IIの設定を再読み込みすることで変更可能です。 + + + diff --git a/doc/src/sgml/loadbalance.sgml b/doc/src/sgml/loadbalance.sgml index 2002e8e..c03cc5c 100644 --- a/doc/src/sgml/loadbalance.sgml +++ b/doc/src/sgml/loadbalance.sgml @@ -960,6 +960,29 @@ app_name_redirect_preference_list = 'psql:primary,myapp1:1(0.3),myapp2:standby' + + statement_level_load_balance (boolean) + + statement_level_load_balance configuration parameter + + + + + When set to on, the load balancing node is decided for each read query. + When set to off, load balancing node is decided at the session start time + and will not be changed until the session ends. + For example, in applications that use connection pooling remain connections + open to the backend server, because the session may be held for a long time, + the load balancing node does not change until the session ends. + In such applications, When statement_level_load_balance is enabled, + it is possible to decide load balancing node per query, not per session. + The default is off. + + + This parameter can be changed by reloading the Pgpool-II configurations. + + + diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c index 806776a..ad7bbfe 100644 --- a/src/config/pool_config_variables.c +++ b/src/config/pool_config_variables.c @@ -557,6 +557,16 @@ static struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, + { + {"statement_level_load_balance", CFGCXT_INIT, LOAD_BALANCE_CONFIG, + "Enables statement level load balancing", + CONFIG_VAR_TYPE_BOOL, false, 0 + }, + &g_pool_config.statement_level_load_balance, + false, + NULL, NULL, NULL + }, + /* End-of-list marker */ EMPTY_CONFIG_BOOL diff --git a/src/context/pool_query_context.c b/src/context/pool_query_context.c index b82da45..98d961d 100644 --- a/src/context/pool_query_context.c +++ b/src/context/pool_query_context.c @@ -145,6 +145,7 @@ pool_start_query(POOL_QUERY_CONTEXT * query_context, char *query, int len, Node query_context->rewritten_query = NULL; query_context->parse_tree = node; query_context->virtual_master_node_id = my_master_node_id; + query_context->load_balance_node_id = my_master_node_id; query_context->is_cache_safe = false; query_context->num_original_params = -1; if (pool_config->memory_cache_enabled) @@ -226,7 +227,7 @@ pool_setall_node_to_be_sent(POOL_QUERY_CONTEXT * query_context) * In streaming replication mode, if the node is not primary node * nor load balance node, there's no point to send query. */ - if (SL_MODE && + if (SL_MODE && !pool_config->statement_level_load_balance && i != PRIMARY_NODE_ID && i != sc->load_balance_node_id) { continue; @@ -342,7 +343,7 @@ pool_virtual_master_db_node_id(void) (errmsg("pool_virtual_master_db_node_id: virtual_master_node_id:%d load_balance_node_id:%d PRIMARY_NODE_ID:%d", node_id, sc->load_balance_node_id, PRIMARY_NODE_ID))); - if (node_id != sc->load_balance_node_id && node_id != PRIMARY_NODE_ID) + if (node_id != sc->query_context->load_balance_node_id && node_id != PRIMARY_NODE_ID) { /* * Only return the primary node id if we are not processing @@ -602,8 +603,12 @@ pool_where_to_send(POOL_QUERY_CONTEXT * query_context, char *query, Node *node) else { + if (pool_config->statement_level_load_balance) + session_context->load_balance_node_id = select_load_balancing_node(); + + session_context->query_context->load_balance_node_id = session_context->load_balance_node_id; pool_set_node_to_be_sent(query_context, - session_context->load_balance_node_id); + session_context->query_context->load_balance_node_id); } } else diff --git a/src/context/pool_session_context.c b/src/context/pool_session_context.c index 7634acf..a273a1e 100644 --- a/src/context/pool_session_context.c +++ b/src/context/pool_session_context.c @@ -1153,7 +1153,7 @@ pool_pending_message_create(char kind, int len, char *contents) msg->portal[0] = '\0'; msg->is_rows_returned = false; msg->not_forward_to_frontend = false; - msg->node_ids[0] = msg->node_ids[1] = -1; + memset(msg->node_ids, false, sizeof(msg->node_ids)); MemoryContextSwitchTo(old_context); @@ -1168,20 +1168,11 @@ void pool_pending_message_dest_set(POOL_PENDING_MESSAGE * message, POOL_QUERY_CONTEXT * query_context) { int i; - int j = 0; for (i = 0; i < MAX_NUM_BACKENDS; i++) { if (query_context->where_to_send[i]) - { - if (j > 1) - { - ereport(ERROR, - (errmsg("pool_pending_messages_dest_set: node ids exceeds 2"))); - return; - } - message->node_ids[j++] = i; - } + message->node_ids[i] = true; } message->query_context = query_context; @@ -1210,11 +1201,11 @@ pool_pending_message_query_context_dest_set(POOL_PENDING_MESSAGE * message, POOL /* Rewrite where_to_send map */ memset(query_context->where_to_send, 0, sizeof(query_context->where_to_send)); - for (i = 0; i < 2; i++) + for (i = 0; i < MAX_NUM_BACKENDS; i++) { - if (message->node_ids[i] != -1) + if (message->node_ids[i]) { - query_context->where_to_send[message->node_ids[i]] = 1; + query_context->where_to_send[i] = 1; } } } @@ -1633,12 +1624,18 @@ int pool_pending_message_get_target_backend_id(POOL_PENDING_MESSAGE * msg) { int backend_id = -1; + int i; - if (msg->node_ids[0] != -1) - backend_id = msg->node_ids[0]; - else if (msg->node_ids[1] != -1) - backend_id = msg->node_ids[1]; - else + for (i = 0; i < MAX_NUM_BACKENDS; i++) + { + if (msg->node_ids[i]) + { + backend_id = i; + break; + } + } + + if (backend_id == -1) ereport(ERROR, (errmsg("pool_pending_message_get_target_backend_id: no target backend id found"))); @@ -1654,6 +1651,7 @@ pool_pending_message_get_message_num_by_backend_id(int backend_id) ListCell *cell; ListCell *next; int cnt = 0; + int i; if (!session_context) { @@ -1666,8 +1664,14 @@ pool_pending_message_get_message_num_by_backend_id(int backend_id) { POOL_PENDING_MESSAGE *msg = (POOL_PENDING_MESSAGE *) lfirst(cell); - if (msg->node_ids[0] == backend_id || msg->node_ids[1] == backend_id) - cnt++; + for (i = 0; i < MAX_NUM_BACKENDS; i++) + { + if (msg->node_ids[i] && i == backend_id) + { + cnt++; + break; + } + } next = lnext(cell); } diff --git a/src/include/context/pool_query_context.h b/src/include/context/pool_query_context.h index 55e8b87..bca7cd1 100644 --- a/src/include/context/pool_query_context.h +++ b/src/include/context/pool_query_context.h @@ -62,6 +62,7 @@ typedef struct Node *rewritten_parse_tree; /* rewritten raw parser output if any */ bool where_to_send[MAX_NUM_BACKENDS]; /* DB node map to send * query */ + int load_balance_node_id; /* load balance node id per statement */ int virtual_master_node_id; /* the 1st DB node to send query */ POOL_QUERY_STATE query_state[MAX_NUM_BACKENDS]; /* for extended query * protocol */ @@ -97,7 +98,6 @@ extern void pool_query_context_destroy(POOL_QUERY_CONTEXT * query_context); extern POOL_QUERY_CONTEXT * pool_query_context_shallow_copy(POOL_QUERY_CONTEXT * query_context); extern void pool_start_query(POOL_QUERY_CONTEXT * query_context, char *query, int len, Node *node); extern void pool_set_node_to_be_sent(POOL_QUERY_CONTEXT * query_context, int node_id); -extern void pool_unset_node_to_be_sent(POOL_QUERY_CONTEXT * query_context, int node_id); extern bool pool_is_node_to_be_sent(POOL_QUERY_CONTEXT * query_context, int node_id); extern void pool_set_node_to_be_sent(POOL_QUERY_CONTEXT * query_context, int node_id); extern void pool_unset_node_to_be_sent(POOL_QUERY_CONTEXT * query_context, int node_id); diff --git a/src/include/context/pool_session_context.h b/src/include/context/pool_session_context.h index 5f495d1..172c7e9 100644 --- a/src/include/context/pool_session_context.h +++ b/src/include/context/pool_session_context.h @@ -140,14 +140,10 @@ typedef struct bool not_forward_to_frontend; /* Do not forward response from * backend to frontend. This is * used by parse_before_bind() */ - int node_ids[2]; /* backend node ids this message was sent to. - * -1 means no message was sent. */ + bool node_ids[MAX_NUM_BACKENDS]; /* backend node map which this message was sent to */ POOL_QUERY_CONTEXT *query_context; /* query context */ } POOL_PENDING_MESSAGE; -/* Return true if node_id is one of node_ids */ -#define IS_SENT_NODE_ID(msg, node_id) (msg->node_ids[0] == node_id || msg->node_ids[1] == node_id) - /* * Per session context: */ diff --git a/src/include/pool_config.h b/src/include/pool_config.h index 9de9c28..32ca641 100644 --- a/src/include/pool_config.h +++ b/src/include/pool_config.h @@ -442,6 +442,8 @@ typedef struct * will not be load balanced * until the session ends. */ + bool statement_level_load_balance; /* if on, select load balancing node per statement */ + /* * add for watchdog */ diff --git a/src/protocol/child.c b/src/protocol/child.c index 75a2545..21270cc 100644 --- a/src/protocol/child.c +++ b/src/protocol/child.c @@ -1709,7 +1709,7 @@ select_load_balancing_node(void) for (i = 0; i < NUM_BACKENDS; i++) { - if (VALID_BACKEND(i)) + if (VALID_BACKEND_RAW(i)) { if (i == no_load_balance_node_id) continue; @@ -1735,7 +1735,7 @@ select_load_balancing_node(void) if ((suggested_node_id == -1 && i == PRIMARY_NODE_ID) || i == no_load_balance_node_id) continue; - if (VALID_BACKEND(i) && BACKEND_INFO(i).backend_weight > 0.0) + if (VALID_BACKEND_RAW(i) && BACKEND_INFO(i).backend_weight > 0.0) { if (r >= total_weight) selected_slot = i; diff --git a/src/protocol/pool_proto_modules.c b/src/protocol/pool_proto_modules.c index 0f09645..c7fa492 100644 --- a/src/protocol/pool_proto_modules.c +++ b/src/protocol/pool_proto_modules.c @@ -3379,6 +3379,7 @@ static POOL_STATUS parse_before_bind(POOL_CONNECTION * frontend, memset(new_qc->where_to_send, 0, sizeof(new_qc->where_to_send)); new_qc->where_to_send[PRIMARY_NODE_ID] = 1; new_qc->virtual_master_node_id = PRIMARY_NODE_ID; + new_qc->load_balance_node_id = PRIMARY_NODE_ID; /* * Before sending the parse message to the primary, we need to diff --git a/src/sample/pgpool.conf.sample b/src/sample/pgpool.conf.sample index 827cb2c..e578d76 100644 --- a/src/sample/pgpool.conf.sample +++ b/src/sample/pgpool.conf.sample @@ -348,6 +348,9 @@ disable_load_balance_on_write = 'transaction' # 'always': if a write query is issued, read queries will # not be load balanced until the session ends. +statement_level_load_balance = off + # Enables statement level load balancing + #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ diff --git a/src/sample/pgpool.conf.sample-logical b/src/sample/pgpool.conf.sample-logical index dd4fce8..afcda85 100644 --- a/src/sample/pgpool.conf.sample-logical +++ b/src/sample/pgpool.conf.sample-logical @@ -335,6 +335,9 @@ allow_sql_comments = off # If off, SQL comments effectively prevent the judgment # (pre 3.4 behavior). +statement_level_load_balance = off + # Enables statement level load balancing + #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ diff --git a/src/sample/pgpool.conf.sample-master-slave b/src/sample/pgpool.conf.sample-master-slave index caee02f..5607e93 100644 --- a/src/sample/pgpool.conf.sample-master-slave +++ b/src/sample/pgpool.conf.sample-master-slave @@ -348,6 +348,9 @@ disable_load_balance_on_write = 'transaction' # 'always': if a write query is issued, read queries will # not be load balanced until the session ends. +statement_level_load_balance = off + # Enables statement level load balancing + #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ diff --git a/src/sample/pgpool.conf.sample-replication b/src/sample/pgpool.conf.sample-replication index 5090b0b..d81c547 100644 --- a/src/sample/pgpool.conf.sample-replication +++ b/src/sample/pgpool.conf.sample-replication @@ -347,6 +347,9 @@ disable_load_balance_on_write = 'transaction' # 'always': if a write query is issued, read queries will # not be load balanced until the session ends. +statement_level_load_balance = off + # Enables statement level load balancing + #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ diff --git a/src/sample/pgpool.conf.sample-stream b/src/sample/pgpool.conf.sample-stream index 9e60643..e569aac 100644 --- a/src/sample/pgpool.conf.sample-stream +++ b/src/sample/pgpool.conf.sample-stream @@ -349,6 +349,9 @@ disable_load_balance_on_write = 'transaction' # 'always': if a write query is issued, read queries will # not be load balanced until the session ends. +statement_level_load_balance = off + # Enables statement level load balancing + #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ diff --git a/src/test/extended-query-test/test.sh b/src/test/extended-query-test/test.sh index 414de6b..922d0ce 100755 --- a/src/test/extended-query-test/test.sh +++ b/src/test/extended-query-test/test.sh @@ -10,6 +10,7 @@ PGSOCKET_DIR=/tmp timeout=30 export PGPORT=11000 +export PGHOST=127.0.0.1 export PGDATABASE=test #export PGPOOL_INSTALL_DIR=$HOME/work/pgpool-II/current #export PGPOOLDEBUG=true @@ -104,30 +105,135 @@ verify_pginstallation export_env_vars print_info -rm -f $results/* -test ! -d $results && mkdir $results +okcnt=0 +failcnt=0 +timeoutcnt=0 + +lb_level=(session statement) +for lb in ${lb_level[@]} +do + cd $dir + rm -f $results/* + test ! -d $results && mkdir $results + + diffs=$dir/diffs + rm -f $diffs + + if [ $# -gt 0 ];then + tests=`(cd tests;ls |grep $1)` + else + tests=`(cd tests;ls)` + fi + rm -fr testdata + mkdir testdata + cd testdata + echo -n "*** creating test database with 2 nodes..." + $PGPOOL_SETUP > /dev/null 2>&1 + echo "done." + cp etc/pgpool.conf pgpool.conf.back + + # change load balancing level + if [ $lb == 'statement' ]; then + echo "statement_level_load_balance = on" + echo "statement_level_load_balance = on" >> etc/pgpool.conf + else + echo "statement_level_load_balance = off" + fi + + for i in $tests + do + echo -n "testing $i ... " + + # check if modification to pgpool.conf specified. + d=/tmp/diff$$ + grep '^##' $testdir/$i > $d + if [ -s $d ] + then + sed -e 's/^##//' $d >> etc/pgpool.conf + fi + rm -f $d + + ./startall >/dev/null 2>&1 + + while : + do + psql -c "select 1" test >/dev/null 2>&1 + if [ $? = 0 ] + then + break + fi + sleep 1 + done -diffs=$dir/diffs -rm -f $diffs + timeout $timeout $PGPROTO -f $testdir/$i > $results/$i 2>&1 + if [ $? = 124 ] + then + echo "timeout." + timeoutcnt=`expr $timeoutcnt + 1` + else + sed -e 's/L [0-9]*/L xxx/g' $expected/$i > expected_tmp + sed -e 's/L [0-9]*/L xxx/g' $results/$i > results_tmp + cmp expected_tmp results_tmp >/dev/null 2>&1 + if [ $? != 0 ] + then + echo "failed." + echo "=== $i ===" >> $diffs + diff -N $expected/$i $results/$i >> $diffs + failcnt=`expr $failcnt + 1` + else + extra_fail=0 + # excute extra scripts if exists. + if [ -x $extra_scripts/$i ] + then + $extra_scripts/$i > $results/$i.extra 2>&1 + if [ $? != 0 ] + then + echo "extra test failed." + extra_fail=1 + failcnt=`expr $failcnt + 1` + fi + fi + + if [ $extra_fail = 0 ] + then + echo "ok." + okcnt=`expr $okcnt + 1` + fi + fi + rm expected_tmp results_tmp + fi + grep pool_check_pending_message_and_reply log/pgpool.log + ./shutdownall >/dev/null 2>&1 + cp pgpool.conf.back etc/pgpool.conf + process=`ps x|grep pgpool|grep idle` + if [ ! -z $process ] + then + echo "Some process remains. Aborting tests" + exit 1 + fi + + done +done + +## Test statement_level_load_balance with 3 nodes + +cd $dir +testdir=$dir/tests_n3 if [ $# -gt 0 ];then - tests=`(cd tests;ls |grep $1)` + tests_n3=`(cd tests_n3;ls | grep $1)` else - tests=`(cd tests;ls)` + tests_n3=`(cd tests_n3;ls)` fi rm -fr testdata mkdir testdata cd testdata -echo -n "creating test database..." -$PGPOOL_SETUP > /dev/null 2>&1 +echo -n "creating test database with 3 nodes..." +$PGPOOL_SETUP -n 3 > /dev/null 2>&1 echo "done." cp etc/pgpool.conf pgpool.conf.back -okcnt=0 -failcnt=0 -timeoutcnt=0 - -for i in $tests +for i in $tests_n3 do echo -n "testing $i ... " @@ -202,6 +308,7 @@ do done +###### total=`expr $okcnt + $failcnt + $timeoutcnt` echo "out of $total ok: $okcnt failed: $failcnt timeout: $timeoutcnt." diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh index 1e78c5c..3ee9ae7 100755 --- a/src/test/regression/tests/001.load_balance/test.sh +++ b/src/test/regression/tests/001.load_balance/test.sh @@ -120,6 +120,27 @@ EOF fi echo ok: black query pattern list works. +# check if statement level load balance worked + ./shutdownall + echo "statement_level_load_balance = on" >> etc/pgpool.conf + echo "log_min_messages = debug1" >> etc/pgpool.conf + + ./startall + wait_for_pgpool_startup + + $PSQL test <statement_level_load_balance); + StrNCpy(status[i].desc, "statement level load balancing", POOLCONFIG_MAXDESCLEN); + i++; + /* MASTER/SLAVE MODE */ StrNCpy(status[i].name, "master_slave_mode", POOLCONFIG_MAXNAMELEN);