Time: First fetch (1 row): 6336117.459 ms. Allrows formatted: 6336117.554 ms

9千万行数据被导入cluster用时105分钟

vertica cluster故障恢复测试

先检查数据

select count(*) from sbtest;

select count(*) from trace_htlorder;

查看cluster状态

dmintools -t view_cluster

DB | Host | State

-------------+------+-------

testVertica | ALL | UP

关闭B上的vertica，并查看状态

admintools -t stop_node -s 192.X.X.B

admintools -t view_cluster

DB | Host | State

-------------+---------------+-------

testVertica | 192.X.X.A | UP

testVertica| 192.X.X.B | DOWN

testVertica | 192.X.X.C | UP

重新检查数据发现数据并未丢失，还是原来的数目

select count(*) from sbtest;

select count(*) from trace_htlorder;

重启B上的vertica，并查看状态

admintools -t restart_node -d testVertica -s 192.X.X.B

admintools -t view_cluster

DB | Host | State

-------------+------+-------

testVertica | ALL | UP

整个集群恢复原状

逐次关闭B、C上的vertica，并查看状态

admintools -t stop_node -s 192.X.X.B

admintools -t stop_node -s 192.X.X.C

admintools -t view_cluster

DB | Host | State

-------------+------+-------

testVertica | ALL | DOWN

发现我们虽然只关闭了两个节点，还有一个节点没有手工关闭，但vertica已经非常智能的关闭了整个cluster，因为这时一个node已经不能保证高可用了，该node一旦异常down掉数据便会损失，所以vertica干脆关掉整个cluster。

重启整个cluster

admintools –t start_db –d testVertica

参考：

https://my.vertica.com/docs/7.0.x/HTML/index.htm

https://www.infobright.com/

http://ftp.gnu.org/gnu/time/

附件：

vertica的python连接器：vertica-python-0.2.0的安装

require：Python2.7、zlib-devel、openssl-devel、pytz、python-dateutil、pip、psycopg2

python-dateutil

require:six

psycopg2

require: postgresql-devel

yum install zlib-devel

yum install openssl-devel

yum install postgresql-devel

cd Python2.7->./configure --with-zlib ->make -> sudo make install

cd pytz ->sudo python2.7 setup.py install

cd six ->sudo python2.7 setup.py install

cd python-dateutil ->sudo python2.7 setup.pyinstall

cd pip ->sudo python2.7 setup.py install

cd psycopg ->sudo python2.7 setup.py install

cdvertica-python ->sudo python2.7 setup.py install#encoding:utf-8

使用SQL命令运行DBD(DatabaseDesigner)

selectDESIGNER_CREATE_DESIGN('testVerticaDesigner');

select DESIGNER_SET_DESIGN_KSAFETY('testVerticaDesigner',1);

SELECTDESIGNER_SET_OPTIMIZATION_OBJECTIVE('testVerticaDesigner','QUERY');

SELECTDESIGNER_SET_DESIGN_TYPE('testVerticaDesigner','INCREMENTAL');

SELECTDESIGNER_ADD_DESIGN_QUERIES('testVerticaDesigner','/home/op1/testVertia/querys.sql','true');

SELECTDESIGNER_ADD_DESIGN_TABLES('testVerticaDesigner','public.trace_htlorder','true');

SELECTDESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY('testVerticaDesigner','/home/op1/testVertia/vertica_design_files/vertica_design_DDL','/home/op1/testVertia/vertica_design_files/vertica_design_deployment_scripts');

SELECTDESIGNER_WAIT_FOR_DESIGN('testVerticaDesigner');

SELECTDESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS('testVerticaDesigner');

SELECTDESIGNER_OUTPUT_DEPLOYMENT_SCRIPT('testVerticaDesigner')

SELECTDESIGNER_CANCEL_POPULATE_DESIGN('testVerticaDesigner')

SELECTDESIGNER_DROP_DESIGN('testVerticaDesigner')