Infobright与ToKuDB存储引擎对比测试

Infobright是开源的MySQL数据仓库解决方案，引入了列存储方案，高强度的数据压缩，优化的统计计算(类似sum/avg/group by之类)。
TokuDB是一个高性能、支持事务处理的MySQL和MariaDB的存储引擎，其主要特点是对高写压力的支持。
我们针对以上两种特殊引擎，和InnoDB作了个简单的对比测试。

 

压缩性能

 InnoDB、Infobright、ToKuDB占用存储空间

同等数据表history（zabbix后台数据表）占用空间情况如下：

 


 
从各引擎存储数据的大小看，Infobright引擎可以让数据获得更大限度的压缩，占用空间最少。

 

 

常用语句执行效率测试

table (rows)	Action	innodb	Infobright	ToKuDB
history(0)	load 427801328 rows	2 hours 5.96	23 min 29.03 sec	6 hours 57 min 1.08 sec
history(427801328)	insert 1million again	49.70 sec	/	55.07 sec
history(427801328)	select count(*) from	13 min 58.01 sec	0.11 sec	3 min 3.27 sec
history_str(27434968)	delete /274349 rows	24.16s	/	2 min 22.32 sec
history_str(27434968)	update/1million rows	21.22 sec	/	28.08 sec
history_str(27434968)	truncate table	4.63 sec	/	1.96 sec

 

 

常用查询性能测试比较

使用单表history_str，数据量27434968条：

 

 

Innodb、tokudb、infobright的表结构，数据量，索引均一样（infobright无索引）

 

执行语句	innodb	infobright	TokuDB
select avg(clock) from history_str	11.54 sec	0.00 sec	8.67 sec
select max(itemid+clock) from history_str	14.36 sec	6.77 sec	12.21 sec
select max(length(clock)) from history_str	12.67 sec	4.44 sec	9.99 sec
select max(right(clock,4)) from history_str	14.07 sec	11.76 sec	11.09 sec
select count(distinct(clock)) from history_str	12.97 sec	2.68 sec	13.86 sec
select count(*) from (select clock from history_str group by clock) as a	14.63 sec	4.69 sec	15.92 sec
select count(*) from (select count(clock) from history_str group by clock) as a	10.51 sec	5.86 sec	7.84 sec
select count(*) from (select count(*) from history_str group by clock,itemid) as a	8 min 44.97 sec	29.00 sec	7 min 54.71 sec
select clock from history_str group by clock,itemid order by itemid desc limit 100;	8 min 12.07 sec	0.00 sec	7 min 24.31 sec
select count(*) from history_str where itemid+clock>1358134535	11.83 sec	15.33 sec	8.53 sec
select count(*) from history_str where clock>ns;	11.41 sec	0.00 sec	8.28 sec
select * from history_str where clock>itemid into outfile '/tmp/h.txt'	32.75 sec	50.06 sec	25.82 sec
select count(*) from history_str where right(clock,3)=100;	15.40 sec	18.18 sec	11.04 sec
select count(*) from history_str where position('m' in value)>0	15.41 sec	2.32 sec	8.72 sec
select count(*) from history_str where greatest(clock,1357868025)=clock	10.69 sec	10.88 sec	8.24 sec
select count(*) from history_str where value like '%true%'	13.89 sec	0.15 sec	8.16 sec
select count(*) from history_str where value in ('true','19ms','9ms');	11.33 sec	0.12 sec	8.33 sec
select count(*) from history_str where value in ('true','19ms','9ms') and clock <1359808970;	1.70 sec	0.11 sec	5.62 sec
select count(*) from history_str as a join history_str as b on a.itemid=b.itemid where a.clock<1357908970;	1 min 34.35 sec	2.93 sec	2 min 17.89 sec
select count(*) from history_str as a join history_str as b on a.itemid=b.itemid where a.clock<1357908970 and b.itemid<266631;	41.61 sec	3.32 sec	1 min 0.66 sec

 

 

小结：

在大部分情况下，infobright具有很好查询性能，远胜innodb、tokudb两种引擎，但是不支持DML，DDL（alter table、truncate table等）语句，语法比较简单，限制也比较多，Infobright还有很多查询注意事项，编写代码的时候需要注意。

Tokudb在走相同索引的情况下，部分情况会优胜innodb，但是数据量非海量的时候，查询优势并不明显。