pgpool2.git / summary / log / commit / refs
commit 1099ba610eef06a9dab6f89486cc52de55ff3ebb
Author: Bo Peng <pengbo@sraoss.co.jp>
Date: Tue Apr 02 01:14:26 2019 +0000
New feature "statement_level_load_balancing".
This feature enables selecting load balancing node per statement.
The current feature for load balancing, the load balancing node is decided
at the session start time and will not be changed until the session ends.
When set to statement_level_load_balancing = on, the load balancing node
is decided for each read query.
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.
doc.ja/src/sgml/loadbalance.sgml | 37 +++
doc/src/sgml/loadbalance.sgml | 23 ++
src/config/pool_config_variables.c | 10 +
src/context/pool_query_context.c | 21 +-
src/context/pool_session_context.c | 52 ++--
src/include/context/pool_query_context.h | 2 +-
src/include/context/pool_session_context.h | 6 +-
src/include/pool_config.h | 2 +
src/protocol/child.c | 4 +-
src/protocol/pool_proto_modules.c | 1 +
src/sample/pgpool.conf.sample | 3 +
src/sample/pgpool.conf.sample-logical | 3 +
src/sample/pgpool.conf.sample-master-slave | 3 +
src/sample/pgpool.conf.sample-replication | 3 +
src/sample/pgpool.conf.sample-stream | 3 +
.../expected/statement_level_load_balance.data | 37 +++
.../statement_level_load_balance.data | 39 +++
src/test/extended-query-test/test.sh | 261 +++++++++++++++------
.../tests_n3/statement_level_load_balance.data | 27 +++
src/test/regression/tests/001.load_balance/test.sh | 23 ++
src/utils/pool_process_reporting.c | 5 +
21 files changed, 454 insertions(+), 111 deletions(-)
diff --git a/doc.ja/src/sgml/loadbalance.sgml b/doc.ja/src/sgml/loadbalance.sgml
index 209cf4d34..cdc0ad612 100644
--- a/doc.ja/src/sgml/loadbalance.sgml
+++ b/doc.ja/src/sgml/loadbalance.sgml
@@ -1364,6 +1364,43 @@ writeクエリがプライマリサーバに送られた時、この変更はス
</listitem>
</varlistentry>
+ <varlistentry id="guc-statement-level-load-balance" xreflabel="statement_level_load_balance">
+ <term><varname>statement_level_load_balance</varname> (<type>boolean</type>)
+ <indexterm>
+<!--
+ <primary><varname>statement_level_load_balance</varname> configuration parameter</primary>
+-->
+ <primary><varname>statement_level_load_balance</varname> 設定パラメータ</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+<!--
+ 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 <varname>statement_level_load_balance</varname> is enabled,
+ it is possible to decide load balancing node per query, not per session.
+ The default is off.
+-->
+onに設定すると、参照クエリごとに負荷分散先を決めます。
+offに設定すると、セッションが始まるときに決められた負荷分散先がセッションが終了するまで変更されません。
+例えば、コネクションプールを利用し、バックエンドサーバに接続したままのようなアプリケーションの場合、
+セッションが長い間保持される可能性があるので、セッションが終了するまで負荷分散先のノードが変わりません。
+このようなアプリケーションでは、<varname>statement_level_load_balance</varname>を有効にすると、
+セッションごとではなく、クエリごとに負荷分散先を決めることが可能です。デフォルトはoffです。
+ </para>
+ <para>
+<!--
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+-->
+このパラメータはPgpool-IIの設定を再読み込みすることで変更可能です。
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
</sect1>
diff --git a/doc/src/sgml/loadbalance.sgml b/doc/src/sgml/loadbalance.sgml
index 2002e8e13..7708ee895 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'
</para>
</listitem>
</varlistentry>
+ <varlistentry id="guc-statement-level-load-balance" xreflabel="statement_level_load_balance">
+ <term><varname>statement_level_load_balance</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>statement_level_load_balance</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ 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 <varname>statement_level_load_balance</varname> is enabled,
+ it is possible to decide load balancing node per query, not per session.
+ The default is off.
+ </para>
+ <para>
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c
index 806776ae0..b7223e44e 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_RELOAD, 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 b82da454d..b237a8ca4 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
@@ -645,9 +650,17 @@ pool_where_to_send(POOL_QUERY_CONTEXT * query_context, char *query, Node *node)
!pool_is_failed_transaction() &&
pool_get_transaction_isolation() != POOL_SERIALIZABLE))
{
+
+
+
/* load balance */
+ 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 7634acf32..9d5afaada 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);
@@ -1167,22 +1167,17 @@ pool_pending_message_create(char kind, int len, char *contents)
void
pool_pending_message_dest_set(POOL_PENDING_MESSAGE * message, POOL_QUERY_CONTEXT * query_context)
{
- int i;
- int j = 0;
+ //int i;
+ /*
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;
}
+ */
+
+ memcpy(message->node_ids, query_context->where_to_send, sizeof(message->node_ids));
message->query_context = query_context;
@@ -1210,11 +1205,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 +1628,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 +1655,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 +1668,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 55e8b873a..bca7cd16e 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 5f495d19b..172c7e9a0 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 9de9c281c..32ca64152 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 84b1462f4..2987aeb4e 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 0f0964515..c7fa492f1 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 827cb2cfe..e578d765f 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 dd4fce8d1..afcda85b9 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 caee02f77..5607e9319 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 5090b0b80..d81c547a6 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 9e6064365..e569aac9c 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/expected/statement_level_load_balance.data b/src/test/extended-query-test/expected/statement_level_load_balance.data
new file mode 100644
index 000000000..8412bee89
--- /dev/null
+++ b/src/test/extended-query-test/expected/statement_level_load_balance.data
@@ -0,0 +1,37 @@
+FE=> Parse(stmt="S0", query="BEGIN")
+FE=> Bind(stmt="S0", portal="")
+FE=> Execute(portal="")
+FE=> Close(stmt="S0")
+FE=> Parse(stmt="S1", query="SELECT 1")
+FE=> Bind(stmt="S1", portal="")
+FE=> Execute(portal="")
+FE=> Close(stmt="S1")
+FE=> Parse(stmt="S2", query="SELECT 2")
+FE=> Bind(stmt="S2", portal="")
+FE=> Execute(portal="")
+FE=> Close(stmt="S2")
+FE=> Parse(stmt="S3", query="COMMIT")
+FE=> Bind(stmt="S3", portal="")
+FE=> Execute(portal="")
+FE=> Close(stmt="S3")
+FE=> Sync
+<= BE ParseComplete
+<= BE BindComplete
+<= BE CommandComplete(BEGIN)
+<= BE CloseComplete
+<= BE ParseComplete
+<= BE BindComplete
+<= BE DataRow
+<= BE CommandComplete(SELECT 1)
+<= BE CloseComplete
+<= BE ParseComplete
+<= BE BindComplete
+<= BE DataRow
+<= BE CommandComplete(SELECT 1)
+<= BE CloseComplete
+<= BE ParseComplete
+<= BE BindComplete
+<= BE CommandComplete(COMMIT)
+<= BE CloseComplete
+<= BE ReadyForQuery(I)
+FE=> Terminate
diff --git a/src/test/extended-query-test/extra_scripts/statement_level_load_balance.data b/src/test/extended-query-test/extra_scripts/statement_level_load_balance.data
new file mode 100644
index 000000000..86d9503ba
--- /dev/null
+++ b/src/test/extended-query-test/extra_scripts/statement_level_load_balance.data
@@ -0,0 +1,39 @@
+#!/usr/bin/env bash
+
+# expected results: Parse: 0 Parse: 1 Parse: 2 Execute:0 Execute:1 Execute:2
+set 0 1 2 0 1 2
+grep "BEGIN" $PGPOOLLOG | grep "LOG" | awk '{print $9}' | while read node
+do
+ if [ $1 -ne $node ]; then
+ echo "expected: $1 result: $node"
+ exit 99
+ fi
+ shift
+done
+
+if [ $? = 99 ]; then
+ exit 1
+fi
+
+# expected results: Parse: 0 Parse: 1 Parse: 2 Execute:0 Execute:1 Execute:2
+set 0 1 2 0 1 2
+grep "COMMIT" $PGPOOLLOG | grep "LOG" | awk '{print $9}' | while read node
+do
+ if [ $1 -ne $node ]; then
+ echo "expected: $1 result: $node"
+ exit 99
+ fi
+ shift
+done
+
+if [ $? = 99 ]; then
+ exit 1
+fi
+
+# select load balance node per query
+n=`grep "selecting load balance node" $PGPOOLLOG | wc -l`
+if [ $n != 3 ]; then
+ exit 1
+fi
+
+exit 0
diff --git a/src/test/extended-query-test/test.sh b/src/test/extended-query-test/test.sh
index 414de6b51..c8723c73b 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
@@ -24,10 +25,10 @@ results=$dir/results
function install_pgpool
{
echo "creating pgpool-II temporary installation ..."
- PGPOOL_PATH=$dir/temp/installed
+ PGPOOL_PATH=$dir/temp/installed
test -d $log || mkdir $log
-
+
make install -C $dir/../../ -e prefix=${PGPOOL_PATH} >& regression.log 2>&1
if [ $? != 0 ];then
@@ -36,7 +37,7 @@ function install_pgpool
fi
echo "moving pgpool_setup to temporary installation path ..."
- cp $dir/../pgpool_setup ${PGPOOL_PATH}/pgpool_setup
+ cp $dir/../pgpool_setup ${PGPOOL_PATH}/pgpool_setup
export PGPOOL_SETUP=$PGPOOL_PATH/pgpool_setup
}
@@ -56,8 +57,8 @@ function export_env_vars
# check if pgpool is in the path
PGPOOL_PATH=/usr/local
export PGPOOL_SETUP=$HOME/bin/pgpool_setup
- fi
-
+ fi
+
if [[ -z "$PGBENCH_PATH" ]]; then
if [ -x $PGBIN/pgbench ]; then
PGBENCH_PATH=$PGBIN/pgbench
@@ -68,16 +69,16 @@ function export_env_vars
if [ ! -x $PGBENCH_PATH ]; then
echo "$0] cannot locate pgbench"; exit 1
- fi
-
+ fi
+
echo "using pgpool-II at "$PGPOOL_PATH
export PGPOOL_INSTALL_DIR=$PGPOOL_PATH
PGPOOLLIB=${PGPOOL_INSTALL_DIR}/lib
if [ -z "$LD_LIBRARY_PATH" ];then
- export LD_LIBRARY_PATH=$PGPOOLLIB
+ export LD_LIBRARY_PATH=$PGPOOLLIB
else
- export LD_LIBRARY_PATH=${PGPOOLLIB}:${LD_LIBRARY_PATH}
+ export LD_LIBRARY_PATH=${PGPOOLLIB}:${LD_LIBRARY_PATH}
fi
export TESTLIBS=$dir/libs.sh
@@ -104,104 +105,210 @@ verify_pginstallation
export_env_vars
print_info
-rm -f $results/*
-test ! -d $results && mkdir $results
+okcnt=0
+failcnt=0
+timeoutcnt=0
+
+# Selecting load balancing node per session or per statement
+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
-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
+ 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 ... "
+ 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
+ # 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
+ ./startall >/dev/null 2>&1
- while :
- do
+ while :
+ do
psql -c "select 1" test >/dev/null 2>&1
if [ $? = 0 ]
then
- break
+ break
fi
sleep 1
- done
-
- 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 ]
+ done
+
+ timeout $timeout $PGPROTO -f $testdir/$i > $results/$i 2>&1
+ if [ $? = 124 ]
then
- echo "failed."
- echo "=== $i ===" >> $diffs
- diff -N $expected/$i $results/$i >> $diffs
- failcnt=`expr $failcnt + 1`
+ echo "timeout."
+ timeoutcnt=`expr $timeoutcnt + 1`
else
- extra_fail=0
- # excute extra scripts if exists.
- if [ -x $extra_scripts/$i ]
- then
- $extra_scripts/$i > $results/$i.extra 2>&1
-
+ 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 "extra test failed."
- extra_fail=1
- failcnt=`expr $failcnt + 1`
- fi
- fi
+ 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
+ if [ $extra_fail = 0 ]
+ then
+ echo "ok."
+ okcnt=`expr $okcnt + 1`
+ fi
+ fi
+ rm expected_tmp results_tmp
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"
+ 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
+ fi
done
+######
total=`expr $okcnt + $failcnt + $timeoutcnt`
echo "out of $total ok: $okcnt failed: $failcnt timeout: $timeoutcnt."
diff --git a/src/test/extended-query-test/tests_n3/statement_level_load_balance.data b/src/test/extended-query-test/tests_n3/statement_level_load_balance.data
new file mode 100644
index 000000000..cc2ca5e11
--- /dev/null
+++ b/src/test/extended-query-test/tests_n3/statement_level_load_balance.data
@@ -0,0 +1,27 @@
+# Test statement_level_load_balance = on
+
+##statement_level_load_balance = on
+##log_min_messages = debug1
+
+'P' "S0" "BEGIN" 0
+'B' "" "S0" 0 0 0
+'E' "" 0
+'C' 'S' "S0"
+
+'P' "S1" "SELECT 1" 0
+'B' "" "S1" 0 0 0
+'E' "" 0
+'C' 'S' "S1"
+
+'P' "S2" "SELECT 2" 0
+'B' "" "S2" 0 0 0
+'E' "" 0
+'C' 'S' "S2"
+
+'P' "S3" "COMMIT" 0
+'B' "" "S3" 0 0 0
+'E' "" 0
+'C' 'S' "S3"
+'S'
+'Y'
+'X'
diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh
index 1e78c5ccb..52e138dc3 100755
--- a/src/test/regression/tests/001.load_balance/test.sh
+++ b/src/test/regression/tests/001.load_balance/test.sh
@@ -120,6 +120,29 @@ EOF
fi
echo ok: black query pattern list works.
+ # check if statement level load balance worked
+ ./shutdownall
+ echo "white_function_list = ''" >> etc/pgpool.conf
+ echo "black_function_list = ''" >> etc/pgpool.conf
+ echo "statement_level_load_balance = on" >> etc/pgpool.conf
+ echo "log_min_messages = debug1" >> etc/pgpool.conf
+
+ ./startall
+ sleep 10
+
+ $PSQL test <<EOF
+SELECT 3333;
+SELECT 4444;
+EOF
+
+ n=`grep "selecting load balance node" log/pgpool.log | wc -l`
+ if [ $n != 3 ]; then
+ # expected result not found
+ echo "fail: statement level load balance doesn't work."
+ ./shutdownall
+ exit 1
+ fi
+ echo ok: statement level load balance works.
# in replication mode if load_balance_mode = off, SELECT query inside
# an explicit transaction should be sent to master only.
diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c
index 85ce5f7cf..6ccb478f5 100644
--- a/src/utils/pool_process_reporting.c
+++ b/src/utils/pool_process_reporting.c
@@ -507,6 +507,11 @@ get_config(int *nrows)
StrNCpy(status[i].desc, "Load balance behavior when write query is received", POOLCONFIG_MAXDESCLEN);
i++;
+ StrNCpy(status[i].name, "statement_level_load_balance", POOLCONFIG_MAXNAMELEN);
+ snprintf(status[i].value, POOLCONFIG_MAXVALLEN, "%d", pool_config->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);
[parent: 96f8a8f1f1f6]