4.3单点的vertica与infobright数据查询测试

trace_htlorder表6千万行数据

#常用查询 WHERE BETWEEN AND LIKE ORDER BY

sql01 = "SELECTTextData,StartTime,EndTime,DatabaseName,ObjectName,HostName,ApplicationName,LoginName,Duration,Reads,Writes,CPU,RowCounts,Error,HashCodeFROM trace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime< '2014-05-15 02:00:00 ' AND TextData LIKE '%GROUP BY%' ORDER BY StartTime"

total: 457.178461075

total: 2516.28422379

#常用查询 WHERE BETWEEN AND LIKE GROUP BY

sql02 = "SELECTTextData,StartTime,EndTime,DatabaseName ,ObjectName,HostName,ApplicationName,LoginName,Duration,Reads,Writes,CPU,RowCounts,Error,HashCodeFROM trace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime< '2014-05-15 02:00:00 ' AND TextData LIKE '%GROUP BY%' GROUP BY TextData,StartTime,EndTime,DatabaseName,ObjectName,HostName,ApplicationName,LoginName,Duration,Reads,Writes,CPU,RowCounts,Error,HashCode"

total: 278.15408802

total: 3426.65513086

#全字段,无条件 一千万行

sql03 = "SELECT TextData,TransactionID,HostName ,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes,CPU ,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROMtrace_htlorder limit 100000"

total: 60.5239160061

total: 26.4451019764

#个别字段,无条件 一千万行

sql04= "SELECT HostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorder limit100000"

total: 3.23667478561

total: 3.97934985161

#全字段,WHERE BETWEEN AND LIKE GROUP BY

sql05 = "SELECT TextData,TransactionID,HostName ,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes,CPU ,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROMtrace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime <'2014-05-15 02:00:00 ' AND TextData LIKE '%GRUOP BY%' GROUP BY TextData,TransactionID ,HostName

,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes,CPU ,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCounts,XactSequence,hashcode,filter"

total: 58.3177318573

total: 1042.12857699

#个别字段,WHERE BETWEEN AND LIKE GROUP BY

sql06 = "SELECT HostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorder WHEREStartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-15 02:00:00' AND TextData LIKE '%GRUOP BY%' GROUPBY HostName ,ApplicationName,ServerName,DatabaseName,DBUserName"

total: 53.3943109512

total: 987.122587919

#全字段,WHERE BETWEEN AND LIKE ORDER BY 分别按不同字段排序DESC/ASC

sql07 = "SELECT TextData,TransactionID,HostName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes ,CPU,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROMtrace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime <'2014-05-15 02:00:00 ' AND TextData LIKE '%GRUOP BY%' ORDER BY Duration DESC"

total: 39.8180880547

total: 984.204402924

sql08 = "SELECT TextData,TransactionID,HostName ,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes,CPU ,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROMtrace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime <'2014-05-15 02:00:00 ' AND TextData LIKE '%GRUOP BY%' ORDER BY Duration ASC"

total: 40.1778831482

total: 984.4428339

sql09 = "SELECT TextData,TransactionID,HostName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes ,CPU,Success,ServerName,EventClass,Error,ObjectName, DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROMtrace_htlorder WHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime <'2014-05-15 02:00:00 ' AND TextData LIKE '%GRUOP BY%' ORDER BY SPID DESC"

total: 39.5487890244

total: 984.184374094

sql10 = "SELECT TextData,TransactionID,HostName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,Reads,Writes ,CPU,Success,ServerName,EventClass,Error,ObjectName,DatabaseName,DBUserName,RowCoun

ts,XactSequence,hashcode,filter FROM trace_htlorderWHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-1502:00:00 ' AND TextData LIKE '%GRUOP BY%' ORDER BY SPID ASC"

total: 39.7879989147

total: 984.206639051

#个别字段,WHERE BETWEEN AND LIKE ORDER BY 分别按不同字段排序DESC/ASC

sql11 = "SELECTHostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorderWHERE StartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-1502:00:00 ' AND TextData LIKE '%GRUOP BY%' ORDER BY Duration DESC;"

total: 48.5239658356

total: 990.392697096

sql12 = "SELECT HostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorder WHEREStartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-15 02:00:00' AND TextData LIKE '%GRUOP BY%' ORDERBY Duration ASC"

total: 48.5928411484

total: 990.359231949

sql13 = "SELECT HostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorder WHEREStartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-15 02:00:00' AND TextData LIKE '%GRUOP BY%' ORDERBY SPID DESC"

total: 48.5275650024

total: 990.474725962

sql14 = "SELECT HostName,ApplicationName,ServerName,DatabaseName,DBUserName FROM trace_htlorder WHEREStartTime >= '2014-05-15 00:00:00 ' AND StartTime < '2014-05-15 02:00:00' AND TextData LIKE '%GRUOP BY%' ORDERBY SPID ASC"

total: 48.8557629585

total: 990.433614969

total为无间断连续运行十次的总时间，上边为vertica的值，下边为infobright的值

14条语句各运行十遍的总时间：

vertica：1264.63847303

infobright：15901.3139489

结论

对于包含like、groupby、order by等条件的数据 ，vertica查询速度明显快于infobright。最快可达20倍以上。对于没有查询条件的语句两者相当，查询列为全部字段时vertica劣于infobright。查询列为部分字段时vertica略优于infobright。

注意事项

vertica中 longvarchar 不支持 LIKE操作，只有char，varchar，binary，varbinary支持，所以需先将原long varchar改为varchar

alter table trace_htlorder alter column TextDataset data type varchar(65000);

infobright SQL 中不能包含关键字 如 database，host，writes，reads等，关键字需用反引号括起来，若是在linux shell里直接以命令方式执行还需对反引号转义，否则会被解析为命令。

4.4测试包含三个节点的verticacluster（k-safety设置为1）导入数据

查看cluster状态

dmintools -t view_cluster

DB | Host | State

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

testVertica | ALL | UP

查看cluster中的节点

admintools -t list_allnodes;

Node | Host | State | Version | DB

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

v_testvertica_node0001 | 192.X.X.A | UP | vertica-7.0.1.000 | testVertica

v_testvertica_node0003 | 192.X.X.B | UP | vertica-7.0.1.000 | testVertica

v_testvertica_node0004 | 192.X.X.C | UP | vertica-7.0.1.000 | testVertica

测试用的数据库表

databases：testVertica

schema：public

table：sbtest、trace_htlorder

测试只在v_testvertica_node0001节点导入数据后的数据分布情况

数据：sbtest.txt -17G-2亿行

copy public.sbtest from '/data/tmp/sbtest.txt' onv_testvertica_node0001 DELIMITER'*' ENCLOSED BY '"' DIRECT;

Time: First fetch (1 row): 233226.446 ms. Allrows formatted: 233226.528 ms

原来单节点时时间的一般左右，数据被压缩后各节点上大约有2.5G总共2.5*3=7.5G刚好是原来3.8G的两倍左右，证明vertica在cluster内存储了两个副本以保证值为1的k-safety

数据：trace_order_less.txt -20G-3千万行

copy public.trace_htlorder from '/data/tmp/trace_order_less.txt'on v_testvertica_node0001 DELIMITER'*' ENCLOSED BY '"' DIRECT;

Time: First fetch (1 row): 1927267.476 ms. Allrows formatted: 1927267.623 ms

原来单节点时时间的一般左右，数据被压缩后各节点上大约有1G总共1*3=3G刚好是原来1.4G的两倍左右，证明vertica在cluster内存储了两个副本以保证值为1的k-safety

测试在v_testvertica_node0001、 v_testvertica_node0003、 v_testvertica_node0004多节点并行导入

数据：sbtest.txt -17G-2亿行，分别存在于A、B、C三个节点中

copy public.sbtest from '/data/tmp/sbtest.txt' onv_testvertica_node0001,'/tmp/sbtest.txt' on v_testvertica_node0003,'/tmp/sbtest.txt'on v_testvertica_node0004 DELIMITER '*' ENCLOSED BY '"' DIRECT;

Time: First fetch (1 row): 364039.015 ms. Allrows formatted: 364039.062 ms

6亿行数据被导入cluster用时6.3分钟

数据：trace_order_less.txt -20G-3千万行，别存在于A、B、C三个节点中

copy public.trace_htlorder from'/data/tmp/trace_order_less.txt' onv_testvertica_node0001,'/tmp/trace_order_less.txt' onv_testvertica_node0003,'/tmp/trace_order_less.txt'