stop_on_error;
warning |
CLI command sent: stop_on_error |
/*
killing cluster (pid:21812
21816
21817
21818
21819
21820)
done
cluster is not running on this server
init -> true
foreground -> true
[0] CLUSTER master port: 2220 (0)
[0] CLUSTER slave count: 2 (0)
[0] FIRST_NODE ____comment#5____
[0] FIRST_NODE connecting to localhost : 2219 (0)
[0] SECOND_NODE ____comment#6____
[0] SECOND_NODE connecting to localhost : 2219 (0)
[0] SECOND_NODE -> authentication; (0)
[0] FIRST_NODE -> authentication; (0)
[0] SECOND_NODE <- data ( 104 ms ) (0)
[0] SECOND_NODE -> exec show_data_model(); (0)
[0] SECOND_NODE <- data ( 3 ms ) (0)
[0] FIRST_NODE <- data ( 203 ms ) (0)
[0] FIRST_NODE -> exec show_data_model(); (0)
[0] FIRST_NODE <- data ( 2 ms ) (0)
[0] FIRST_NODE col_type t_item_id exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL item_id FROM v_item_tags GROUP BY item_id; (0)
[0] SECOND_NODE col_type t_item_id exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL item_id FROM v_item_tags GROUP BY item_id; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 14 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 4 ms ) (0)
[0] SECOND_NODE #2 len: 14 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK t_item_id 1/9 (0)
[0] FIRST_NODE col_type sys#type#items#art_label exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL art_label FROM v_items GROUP BY art_label; (0)
[0] SECOND_NODE col_type sys#type#items#art_label exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL art_label FROM v_items GROUP BY art_label; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 12 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 3 ms ) (0)
[0] SECOND_NODE #2 len: 12 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK sys#type#items#art_label 2/9 (0)
[0] FIRST_NODE col_type t_dept_id exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL dept FROM v_items GROUP BY dept; (0)
[0] SECOND_NODE col_type t_dept_id exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL dept FROM v_items GROUP BY dept; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 12 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 4 ms ) (0)
[0] SECOND_NODE #2 len: 12 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK t_dept_id 3/9 (0)
[0] FIRST_NODE col_type t_customer_id exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL customer_id FROM v_sales GROUP BY customer_id; (0)
[0] SECOND_NODE col_type t_customer_id exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL customer_id FROM v_sales GROUP BY customer_id; (0)
[0] FIRST_NODE <- data ( 3 ms ) (0)
[0] FIRST_NODE #2 len: 13 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 4 ms ) (0)
[0] SECOND_NODE #2 len: 13 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK t_customer_id 4/9 (0)
[0] FIRST_NODE col_type t_date exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL sales_date FROM v_sales GROUP BY sales_date; (0)
[0] SECOND_NODE col_type t_date exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL sales_date FROM v_sales GROUP BY sales_date; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 17 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 4 ms ) (0)
[0] SECOND_NODE #2 len: 17 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK t_date 5/9 (0)
[0] FIRST_NODE col_type t_customer_info exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL info FROM v_item_customer_infos GROUP BY info; (0)
[0] SECOND_NODE col_type t_customer_info exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL info FROM v_item_customer_infos GROUP BY info; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 14 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 4 ms ) (0)
[0] SECOND_NODE #2 len: 14 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK t_customer_info 6/9 (0)
[0] FIRST_NODE col_type sys#type#customers#customer_name exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL customer_name FROM v_sales GROUP BY customer_name; (0)
[0] SECOND_NODE col_type sys#type#customers#customer_name exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL customer_name FROM v_sales GROUP BY customer_name; (0)
[0] FIRST_NODE <- data ( 3 ms ) (0)
[0] FIRST_NODE #2 len: 12 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 3 ms ) (0)
[0] SECOND_NODE #2 len: 12 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK sys#type#customers#customer_name 7/9 (0)
[0] FIRST_NODE col_type sys#type#sales#line_id exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL line_id FROM v_sales GROUP BY line_id; (0)
[0] SECOND_NODE col_type sys#type#sales#line_id exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL line_id FROM v_sales GROUP BY line_id; (0)
[0] SECOND_NODE <- data ( 3 ms ) (0)
[0] SECOND_NODE #2 len: 17 (0)
[0] SECOND_NODE #3 (0)
[0] FIRST_NODE <- data ( 3 ms ) (0)
[0] FIRST_NODE #2 len: 17 (0)
[0] FIRST_NODE #3 (0)
[0] CLUSTER colType OK sys#type#sales#line_id 8/9 (0)
[0] FIRST_NODE col_type sys#type#item_tags#tag exec sql #1 (0)
[0] FIRST_NODE -> SELECT_ALL tag FROM v_item_tags GROUP BY tag; (0)
[0] SECOND_NODE col_type sys#type#item_tags#tag exec sql #1 (0)
[0] SECOND_NODE -> SELECT_ALL tag FROM v_item_tags GROUP BY tag; (0)
[0] FIRST_NODE <- data ( 4 ms ) (0)
[0] FIRST_NODE #2 len: 12 (0)
[0] FIRST_NODE #3 (0)
[0] SECOND_NODE <- data ( 3 ms ) (0)
[0] SECOND_NODE #2 len: 12 (0)
[0] SECOND_NODE #3 (0)
[0] CLUSTER colType OK sys#type#item_tags#tag 9/9 (0)
*/
/*
cluster is not running on this server
archiving previous log file
purging log archive (7 days)
starting stormbase cluster .. OK: cluster listening on tcp port 2220
*/
/*
PID: 25586, CMD: STORMBASE, TCP_PORT: 2219
PID: 25645, CMD: CLUSTER, TCP_PORT: 2220
PID: 25649, CMD: CLUSTER, TCP_PORT: 2220
PID: 25650, CMD: CLUSTER, TCP_PORT: 2220
*/
Cluster
SQL steps: SB client -> SB cluster -> send SQL to all SBs of the cluster -> merge results -> reply to SB client
cluster_readme.txt
../02_sql/cluster_readme.txt
##############
## SB cluster
##############
1/ create file cluster.conf, sample here under
# cluster port
TCP_PORT:2220
# node 1
ID1:node1 (optional, defaults to N1)
HOST1:stormbase1_ip
TCP_PORT1:stormbase1_port
# node 12
ID2:node2
HOST2:stormbase2_ip
TCP_PORT2:stormbase2_port
# repartition keys
REPART_COL_TYPES:t_art_site_key,t_art_cinv
OR
REPART_COL_TYPES:/(t_art_site_key|t_art_cinv)/
# #process, defaults to 5
NB_WORKERS:a_number
# if an sql take more that x seconds then execution is cancelled and error 173 is sent, defaults to 100 seconds
SQL_TIMEOUT:a_number_in_seconds
# all SB nodes must have the same data model (otherwise init will return an error), defaults to y
SAME_DATA_MODEL:y/n
# if sql match the regex the sql will be sent to node #1 only, defaults to null
REGEX_FIRST_NODE_ONLY:a_javascript_regex
2/ init cluster
./cluster.sh init
Note. each time a cluster node receives a refresh the cluster must be re-init
3/ start cluster
./cluster start <|foreground>
Note. logs are in cluster.log
##############
## SB with PGSQL driver
##############
1/ create file cluster.conf, sample here under
create a classic SB cluster conf file (maybe with one SB node only)
2/ init companion
./cluster.sh init_pg_companion
PG_COMPANION_PORT:5432
PG_COMPANION_HOST:51.255.89.11
PG_COMPANION_USER:postgres
PG_COMPANION_PWD:toto
3/ start cluster
./cluster start <|foreground>
init cluster
SB cluster will connect to all SB nodes, download data model, and store it under 02_sql/INT_CLU
02_sql> ./cluster.sh init;
../02_sql/cluster.conf
TCP_PORT:2220
ID1:FIRST_NODE
HOST1:localhost
TCP_PORT1:2219
ID2:SECOND_NODE
HOST2:localhost
TCP_PORT2:2219
REGEX_FIRST_NODE_ONLY:/(create|insert|delete|refresh|set)\s/
NB_WORKERS:2
SQL_TIMEOUT:3
start cluster
Start SB cluster in companion mode
02_sql> ./cluster.sh start|bounce;
basics
select
count(*) from
v_sales;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden | node_id |
sales | 5 | 7 | n | n | n | FIRST_NODE |
sales | 5 | 7 | n | n | n | SECOND_NODE |
desc
nothing;
param_name | current_value |
TCP_PORT | 2220 |
ID1 | FIRST_NODE |
HOST1 | localhost |
TCP_PORT1 | 2219 |
ID2 | SECOND_NODE |
HOST2 | localhost |
TCP_PORT2 | 2219 |
REPART_COL_TYPES | - |
REGEX_FIRST_NODE_ONLY | /(create!insert!delete!refresh!set)\s/ |
NB_WORKERS | 2 |
SQL_TIMEOUT | 3 |
SAME_DATA_MODEL | y |
nothing;
nothing;
param_name | current_value |
SAME_DATA_MODEL | n |
custom.js
../02_sql/custom.js
module.exports = {};
module.exports.fnSetNodes = function (sql, nodeId2Execute) {
if (sql.match(/(\s|^)v_item_tags(\s|;)/im)) {
nodeId2Execute["FIRST_NODE"] = false;
}
if (sql.match(/(\s|^)v_items(\s|;)/im)) {
nodeId2Execute["SECOND_NODE"] = false;
}
}
desc
view
callback
where
(1,'v_item_tags'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count | node_id |
v_item_tags | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 | FIRST_NODE |
v_item_tags | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 | SECOND_NODE |
select
count(*) from
v_item_tags;
desc
view
callback
where
(1,'v_items'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count | node_id |
v_items | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 | FIRST_NODE |
v_items | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 | SECOND_NODE |
select
count(*) from
v_items;
SB_CLUSTER tag
Use SB_CLUSTER tag to help SB cluster
desc
table
sales;
table_name | column_name | column_type | col_type_name | node_id |
sales | item_id | text | t_item_id | FIRST_NODE |
sales | customer_id | text | t_customer_id | FIRST_NODE |
sales | sales_date | text | t_date | FIRST_NODE |
sales | sales_qty | number | sys#type#sales#sales_qty | FIRST_NODE |
sales | line_id | text | sys#type#sales#line_id | FIRST_NODE |
sales | item_id | text | t_item_id | SECOND_NODE |
sales | customer_id | text | t_customer_id | SECOND_NODE |
sales | sales_date | text | t_date | SECOND_NODE |
sales | sales_qty | number | sys#type#sales#sales_qty | SECOND_NODE |
sales | line_id | text | sys#type#sales#line_id | SECOND_NODE |
REGEX_FIRST_NODE_ONLY
Use SB_CLUSTER tag to help SB cluster
errors management
Timeouts may happen in this mode
continue_on_disconnect;
nothing;
error |
173: connection closed |
stop_on_disconnect;
select
count(*) from
v_sales;
;