数据仓库InfoBright测试报告

<!-- [if gte mso 9]><![endif]--><!-- [if gte mso 9]><![endif]--><!-- [if gte mso 10]><style> <!-- table.MsoNormalTable {font-size:10.0pt; font-family:"Times New Roman","serif"} --> </style><![endif]-->

1 背景介绍

1.1 InfoBright简介

InfoBright）集成的开源数据仓库软件，可作为MySQL。

Infobright索引的模式，而是采用了基于列存储+的模式，在执行SQL、min、avg等统计类操作的效率。而且处理巨大数据量时，性能也不会有明显的下降（详细请见《Infobright所在服务器

MySQL服务器型号

HP DL380G6

HP DL380G6

CPU

INTEL Nehalem E5520 2.26GHZx2

INTEL Nehalem E5520 2.26GHZx2

Memory

HP PC3-8500 4GBx4

HP PC3-8500 4GBx4

Hard Disk

HP SAS 300G 10Kx6

HP SATA 1T 7.2Kx6

RAID

1+0

1+0

MySQL版本

3.4.2-x86_64

-

2 测试用例及对比测试

2.1 测试用例

均为Beidou表名

文本文件大小

IB每秒加载行数

表名

存储空间（myisam存储空间（brighthouse压缩比

stat_group_mainsite_20101107

54543696

2.8G

279M

90%

stat_group_mainsite_20101108

53516823

2.8G

277M

90%

stat_group_mainsite_20101109

48335997

2.5G

244M

90%

detail20101107

93755092

8.6G

1.0G

88%

detail20101108

90386326

8.3G

0.9G

89%

detail20101109

80573627

7.4G

0.8G

89%

2.4 数据导入效率对比测试

数据行数	MySQL导入时间	IB提升
stat_ad_201011	5848926	477s	257s	46%
stat_user_2010	9320454	224s	71s	68%
stat_mainsite_2010	11780404	643s	229s	64%
stat_group_mainsite_20101109	48335997	1943s	2591s	-33%

引擎的导入时间，其余均是使用myisam查询行数

MySQL时间

IB时间

IB提升率

Query_sql_1

61.78亿

275s

176s

36%

Query_sql_3

7954万

50s

<1s

>98%

Query_sql_5

26.39存储空间对比测试

数据行数

bigint存储空间

Varchar导入时间对比测试

数据行数

Bigint类型

Varchar查询对比测试

SQL	类型	Char类型
Query_sql_2	2.76可以获得约10:1型数据的导入速度要高于char类型，但是存储空间占用比char类型要多； c)的统计操作，使用infobright左右的性能提升； d)的统计操作，infobright使用联合索引时的性能，但是可以采用折中方式调整，如重写SQL、或者在表中增加一个合并列，如测试用例中将group两列的值合并存入group_mainsite操作。 e)可以获得平均40%、max倍以上的性能提升。 3 InfoBright已知问题和不足 a)InfoBright引擎，但Brighthouse引擎不支持索引（也无需索引）; c)引擎不支持DML的方式导入数据（商业版支持DMLBrighthouseBrighthouse类型; f)及以上版本; g)，意味着数据不能再往下同步; 4 附录一：所涉及部分表的结构定义 a)Detail20101109表 CREATE TABLE `stat_group_mainsite_20101109` ( `groupid` int(10) NOT NULL, `mainsiteid` bigint(20) NOT NULL, `srchs`bigint(20) NOT NULL, `clks`int(11) NOT NULL, `cost`int(11) NOT NULL, `userid`int(10) NOT NULL, `planid`int(10) NOT NULL ) 5 附录二：导入SQL a)stat_user_2010表导入SQL insert into stat_mainsite_2010 select mainsiteid,sum(srchs),sum(clks),sum(cost),count(distinctuserid), count(distinct planid), count(distinct groupid), count(distinctadid),20101110 from detail20101110 group by mainsiteid c)stat_ad_201011表导入SQL select groupid,mainsiteid, sum(srchs),sum(clks),sum(cost), userid, planid into outfile ‘stat_group_mainsite_20101110.sql ‘ fromdetail20101109 group by groupid,mainsiteid; load data infile 'stat_group_mainsite_20101110.sql'into table stat_group_mainsite_2010109 CHARACTER SET utf8 FIELDS TERMINATED BY'/t' 6 附录三：查询SQL a)Query_sql_1 select a.userid,t.username,t.ucorpid,t.f_userid,t.f_username,t.agentid,t.agent_username,t.provid,t.cityid,t.realname,a.srchs,d.e_clks,d.e_clks/a.srchs*100as ctr2 ,d.bid,d.cash,d.bid/d.e_clks as acp,d.bid/a.srchs*1000 ascpm,e.lastclicktime,'',h.balance,c.c_plans,c.c_groups,c.c_ads,a.e_mainsites,a.e_sites,c.c_mainsites,c.c_sites,f.e_plans,f.e_groups,f.e_ads from (select userid,sum(srchs) assrchs,count(distinct mainsiteid) as e_mainsites,count(distinct siteid) ase_sites,count(distinct adid) as e_ads from beidoustat.detail20101110 where userid>30 group by userid) a left join (select userid,count(distinct planid)as c_plans,count(distinct groupid) as c_groups ,count(distinct adid) asc_ads,count(distinct mainsiteid) as c_mainsites,count(distinct siteid) as c_sitesfrom beidoustat.detail20101110 where cost>0 group by userid) c ona.userid=c.userid left join (select userid,count(clktime) ase_clks,sum(price) as bid,sum(price*rrate) as cash frombeidoufinan.cost_20101110 whereuserid>30 group by userid) d on a.userid=d.userid left join (selectt1.userid,t1.username,t1.ucorpid,t4.userid as f_userid,t4.username asf_username,t1.fatuid as agentid,t3.username asagent_username,t2.provid,t2.cityid,t2.realname from (selecta.userid,a.username,a.ucorpid,if(c.fatuid is null,b.fatuid,c.fatuid) as fatuid ,b.fatuid as sonuid fromSF_User.useracct a left join SF_User.usermaps b on a.userid=b.sonuid left joinSF_User.usermaps c on b.fatuid=c.sonuid ) t1 left join SF_User.userinfo t2 ont1.fatuid=t2.userid left join SF_User.useracct t3 on t1.fatuid=t3.userid leftjoin SF_User.useracct t4 on t1.sonuid=t4.userid) t on a.userid=t.userid left join (select userid,count(clktime) asclks,sum(price) as bid,sum(price*rrate) as cash,max(clktime) as lastclicktimefrom beidoufinan.cost_20101110 group by userid) e on a.userid=e.userid left join (select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads from beidou.cproplan a,beidou.cprogroupb,beidou.cprounitstate0 c where a.planid = b.planid and b.groupid = c.gid anda.planstate = 0 and b.groupstate = 0 and c.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate1 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate2 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate3 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate4 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate5 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate6 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid union select uid,count(distinct c.pid) ase_plans,count(distinct c.gid) as e_groups,count(distinct c.id) as e_ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate7 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by uid) f on a.userid=f.uid left join beidou.useraccount g ona.userid=g.userid left join beidou.userbalance h on a.userid=h.userid; b)Query_sql_2 " p="" class="code" span="" lang="EN-US" left="" join="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" span="" nbsp="" span="" from="" beidou="" cproplan="" a="" beidou="" cprogroupb="" beidou="" cprounitstate0="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" anda="" planstate="0" and="" b="" groupstate="0" and="" c="" state="0" group="" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" frombeidou="" cproplan="" a="" beidou="" cprogroup="" b="" beidou="" cprounitstate1="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" andc="" state="0" group="" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" as="" e_plans="" count="" distinctc="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" from="" beidou="" cproplana="" beidou="" cprogroup="" b="" beidou="" cprounitstate2="" c="" where="" a="" planid="b.planid" andb="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" and="" c="" state="0group" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" frombeidou="" cproplan="" a="" beidou="" cprogroup="" b="" beidou="" cprounitstate3="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" andc="" state="0" group="" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" frombeidou="" cproplan="" a="" beidou="" cprogroup="" b="" beidou="" cprounitstate4="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" andc="" state="0" group="" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" frombeidou="" cproplan="" a="" beidou="" cprogroup="" b="" beidou="" cprounitstate5="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" andc="" state="0" group="" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" from="" beidou="" cproplana="" beidou="" cprogroup="" b="" beidou="" cprounitstate6="" c="" where="" a="" planid="b.planid" andb="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" and="" c="" state="0group" by="" uid="" span="" p="" p="" class="code" span="" lang="EN-US" union="" all="" span="" p="" p="" class="code" span="" lang="EN-US" select="" uid="" count="" distinct="" c="" pid="" ase_plans="" count="" distinct="" c="" gid="" as="" e_groups="" count="" distinct="" c="" id="" as="" e_ads="" frombeidou="" cproplan="" a="" beidou="" cprogroup="" b="" beidou="" cprounitstate7="" c="" where="" a="" planid="b.planid" and="" b="" groupid="c.gid" and="" a="" planstate="0" and="" b="" groupstate="0" andc="" state="0" group="" by="" uid="" f="" on="" a="" userid="f.uid" span="" p="" p="" class="code" span="" lang="EN-US" where="" b="" userid="" is="" null="" span="" p="" p="" class="code" span="" lang="EN-US" nbsp="" span="" p="" div="" p="" class="MsoNormal" span="" lang="EN-US" nbsp="" span="" p="" p="" style="TEXT-INDENT: -21pt; MARGIN-LEFT: 21pt" class="MsoBodyText" span="" lang="EN-US" span="" c="" span="" style="FONT: 7pt 'Times New Roman'" nbsp="" nbsp="" nbsp="" nbsp="" nbsp="" nbsp="" nbsp="" span="" span="" span="" span="" lang="EN-US" query_sql_3="" span="" font="" face="">： select sum(srchs) srchs frombeidoustat.detail20101110 where 1=1 and mainsiteid in(408727336037844657,1251650138411375580,1251405759224588906); d)Query_sql_4： use beidou; selectc.userid,e.username,c.planid,c.planname,a.groupid,b.groupname,a.srchs,a.clks,round(a.ctr2,3),round(a.cost,2),round(a.acp,3),round(a.cpm,3),round(g.price/100,2),h.c_ads,a.e_mainsites,a.e_sites,h.c_mainsites,h.c_sites,if(g.sitesum=0,'allsites',g.sitesum),if(g.regsum=38,'nationwide',g.regsum),i.ads,g.reglist from (select groupid,sum(srchs) assrchs,sum(clks) as clks,sum(cost)/100 as cost,sum(cost)/sum(srchs)*10 ascpm,sum(cost)/100/sum(clks) as acp,sum(clks)/sum(srchs)*100 as ctr2,count(distinctmainsiteid) as e_mainsites,count(distinct siteid) as e_sites ,count(distinctadid) as e_ads from beidoustat.detail20101110 group by userid,planid,groupid )a left join beidou.cprogroup b ona.groupid=b.groupid left join beidou.cproplan c on b.planid=c.planid left join beidou.useraccount e onc.userid=e.userid left join beidou.cprogroupinfo g onb.groupid=g.groupid left join (select groupid,count(distinctmainsiteid) as c_mainsites,count(distinct siteid) as c_sites ,count(distinctadid) as c_ads from beidoustat.detail20101110 where clks>0 group by groupid) h on a.groupid=h.groupid left join (select gid,count(distinct c.id) as ads frombeidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate0 c where a.planid =b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate = 0 andc.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate1 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate2 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate3 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate4 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate5 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate6 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid union all select gid,count(distinct c.id) as adsfrom beidou.cproplan a,beidou.cprogroup b,beidou.cprounitstate7 c wherea.planid = b.planid and b.groupid = c.gid and a.planstate = 0 and b.groupstate= 0 and c.state = 0 group by gid) i on i.gid=b.groupid;