数据库性能测试报告-1.0.0

目录1.前言：数据库性能测试报告2.测试方法概述2.1.测试环境2.1.1..2.硬件环境。软件环境测试工具，2.2.1...Tpch介绍.Jmeter介绍Nmon介绍.测试方法．测试过程，3.3.1.3.2.测试数据库搭建测试脚本准备3.2.1.3.2.2..3.3.4.DDL脚本平面数据文件查询sql语句测试数据规模测试工具开发3.4.1.插入数据功能888..26..262.4.2.删除数据功能测试步骤，测试结果4.1.数据量级—1GB.4.1.1.4.1.2.4.1.3...装载时间对比串行时间对比，并行时间对比，Infobright资源消耗情况..PostgreSQL资源消耗情况.数据量级—10GB4.2.1.4.2.2.4.2.3.4.2.4..3.装载时间对比，串行时间对比，并行时间对比，Infobright资源消耗情况，PostgreSQL资源消耗情况..数据量级—30GB4.3.1.4.3.2.4.3.3.4.3.4..4.装载时间对比，串行时间对比，并行时间对比！Infobright资源消耗情况..PostgreSQL资源消耗情况数据量级—100GB+++++.+++++.+++272728..2829..29...30.30313435.35..36..38..41..42..42.43..3.46.484.4.1.4.4.2.4.4.3.4.4.4.4.4.5.装载时间对比，串行时间对比，并行时间对比.AA.AAAAAA..Infobright资源消耗情况.PostgreSQL资源消耗情况测试总结550.50+..++...+..51.55.1.前言通过测试Oracle、Infobright、PostgreSQL三种数据库在TPC-H中的性能表现，作为数据仓库选型的决策依据之一。2.测试方法概述2.1.．测试环境2.1.1.机器ip硬件环境os040302RedHatEnterpriseLinuxServerrelease6.3RedHatEnterpriseLinuxServerrelease6.3RedHatEnterprise磁盘大小1.6T1.6T1.6TcpuIntel(R)Xeon(R)CPUE5-2620v2@2.10GHzIntel(R)Xeon(R)CPUE5-2620v2@2.10GHzIntel(R)Xeon(R)内存32G32G32G网络11082.1.2.序号1234567LinuxServerrelease6.3软件环境软件名称JdkTpchJmeternmoninfobrightOraclePostgreSQL2.2.测试工具2.2.1.·是什么TPC-H介绍CPUE5-2620v2@2.10GHz版本1.7.6572.17.02.1214g14.149.3.5用途备注TPCBenchmarkH(TPC-H)是一个决策支持的基准，它由一系列面向商务应用的查询..和并行数据修改组成。模拟表序号12345678表之间的关系表名regionnationsuppliercustomerpartpartsupporderslineitem区域表国家表供应商表顾客表零件表零件供应商表订单表订单明细表含义PART(P_)SF200.000PARTKEYNAMEMFGRBRANDTYPEsIZECONTAINERRETAILPRICECOMMENTSUPPLIER(S_)SF*10.000SUPPKEYNAMEADDRESSNATIONKEYPHONEACCTBALCOMMENT2.2.2.PARTSUPP(PS_)SF*800.000PARTKEYSUPPKEYAVAILQTYSUPPLYCOSTCOMMENTCUSTOMER(C_)SF150.000CUSTKEYNAMEADDRESSNATIONKEYPHONEACCTBALMKTSEGMENTCOMMENTNATION(N_)25NATIONKEYNAMEREGIONKEYCOMMENTJmeter介绍拥有如下功能：单步测试流程测试并发测试2.2.3.Nmon介绍LINEITEM(L)SF*6.000.000ORDERKEYPARTKEYSUPPKEYLINENUMBERQUANTITYEXTENDEDPRICEDISCOUNTTAXRETURNFLAGLINESTATUSSHIPDATECOMMITDATERECEIPTDATESHIPINSTRUCTSHIPMODECOMMENTREGION(R)6REGIONKEYNAMECOMMENT服务器资源监控工具(监控内容包括：cpu、磁盘IO等)测试方法2.3．ORDERS(O)SF*1.500.000ORDERKEYCUSTKEYORDERSTATUSTOTALPRICEORDERDATEORDER-PRIORITYCLERKSHIP.PRIORITYCOMMENT参照标准TPC-H方案，针对1GB、10GB、30GB、100GB不同级别的数据量进行测试3.测试过程3.1．测试数据库搭建3.2.测试脚本准备3.2.1.DDL脚本Infobrightdss-infobrightdd建表语句：oracledss-orade.ddl建表语句：PostgreSQL建表语句：3.2.2.dss-oracle.ri索引语句：dss-pg.ri索引语句：平面数据文件初始化数据：./dbgen-vf-s1更新数据：./dbgen-vf-U2-s13.2.3.编号查询sql语句功能说明sql语句Sql特点Q1Q2价格摘要报告查询最小代价供应者查询selectwonhigh.crI_returnflag,I_linestatus,sum(l_quantity)assum_qty,sum(I_extendedprice)assum_base_price,sum(l_extendedprice*(1I_discount)assum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax))assum_charge,avg(l_quantity)asavg_qtylavg(l_extendedprice)asavg_priceavg(l_discount)asavg_disc,count(*)ascount_orderfromlineitemwhereI_shipdate<=date'1998-12-01'-interval'[DELTA]'day(3)groupbyI_returnflagI_linestatusorderbyI_returnflag,I_linestatus;selects_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_commentfrompart,supplier,partsupp,nation,regionwhere数据条数：1统计查询结果条数：100WHERE条件中，使用子查询(=)Q3运送优先权查询wonhigh.crp_partkey=ps_partkeyands_suppkey:=ps_suppkeyandp_size=[SIZE]andp_typelike'%[TYPE]'ands_nationkey=n_nationkeyancn_regionkey=r_regionkeyandr_name:'[REGION]'andps_supplycost=(selectTPCBenchmarkTMHStandardSpecificationRevision2.17.1Page31min(ps_supplycost)frompartsupp,supplier,nation,regionwherep_partkey=ps_partkeyands_suppkey=ps_suppkeyands_nationkey=n_nationkeyandn_regionkey=r_regionkeyandr_name:'[REGION]）orderbys_acctbaldesc,n_name,s_name,p_partkey;selectI_orderkey,sum(l_extendedprice*(1-l_discount)asrevenue,o_orderdate,o_shippriority.from结果条数：10多表关联统计查询，并统计(SUM)Q4Q5订单优先权检查查询当地供应者数量查询wonhigh.crcustomer,orders,lineitemwherec_mktsegment='[SEGMENT]'andc_custkey:o_custkeyandI_orderkey=o_orderkeyando_orderdate<date'[DATE]'andI_shipdate>date'[DATE]'groupbyI_orderkey,o_orderdate,o_shippriorityorderbyrevenuedesc，o_orderdate;selecto_orderpriority,count(*)asorder_countfromorderswhereo_orderdate>=date'[DATE]'ando_orderdate<date'[DATE]'+interval'3'monthandexists(select*fromlineitemwhereI_orderkey=o_orderkeyandI_commitdate<I_receiptdate)groupbyo_orderpriorityorderbyo_orderpriority;selectn_name,sum(l_extendedprice*(1-I_discount)asrevenue结果条数：1WHERE条件中，使用子查询(EXISTS并统计(COUNT)1Q6预测收入变化查询fromwonhigh.crcustomer,orders,lineitem,supplier,nation，regionwherec_custkey=o_custkeyandI_orderkey=o_orderkeyandI_suppkey=s_suppkeyandc_nationkey=s_nationkeyands_nationkey:n_nationkeyandn_regionkey=r_regionkeyanor_name='[REGION]'ando_orderdate>=date[DATE]'ando_orderdate<date'[DATE]'+interval'i'yeargroupbyn_nameorderbyrevenuedesc;selectsum(l_extendedprice*l_discount)asrevenuefromlineitemwhereI_shipdate>=date'[DATE]'andI_shipdate<date'[DATE]'+interval'i'yearandI_discountbetween[DISCOUNT]-0.01and[DISCOUNT]+0.01andI_quantity<[QUANTITY]多表关联查询（=），并统计(SUM)1条件(BETWEENAND)查询，并统计(SUM)Q7货运量查询selectwonhigh.crsupp_nation,cust_nation,I_year,sum(volume)asrevenuefrom（selectnl.n_nameassupp_nation,n2.n_nameascust_nation,extract(yearfromI_shipdate)asI_year,I_extendedprice*(1-I_discount)asvolumefromsupplier,lineitem,orders,customer,nationn1，nationn2wheres_suppkey=I_suppkeyando_orderkey:I_orderkeyandc_custkey=o_custkeyands_nationkey=n1.n_nationkeyandc_nationkey:n2.n_nationkeyand((n1.n_name='[NATION1]andn2.n_name=[NATION2]')or(n1.n_name:'[NATION2]'andn2.n_name="[NATION1]')】andI_shipdatebetweendate'1995-01-01'anddate'1996-12-31')asshippinggroupby1带有FROM子查询，从结果集中统计(SUM)Q8国家市场份额查询wonhigh.crsupp_nation,cust_nation,Lyearorderbysupp_nation,cust_nation,I_year;selecto_year,sum(casewhennation='[NATION]thenvolumeelse0end)/sum(volume)asmkt_sharefrom（selectextract(yearfromo_orderdate)aso_yearI_extendedprice*(1-l_discount)asvolume,n2.n_nameasnationfrompart,supplier,lineitem,orders,customer,nationnl,nationn2,regionwherep_partkey=I_partkeyands_suppkey=I_suppkeyandI_orderkey=o_orderkeyando_custkey=c_custkeyandc_nationkey:nl.n_nationkeyandnl.n_regionkey=r_regionkeyandr_name='[REGION]'ands_nationkey=n2.n_nationkeyando_orderdatebetweendate'1995-01-01'anddate'1996-12-1带有FROM多表子查询，从结果集中的查询列上带有逻辑判断(WHENTHENELSE)的统计(SUM)Q9Q10产品类型利润估量查询返回项目报告查询wonhigh.cr31'andp_type='[TYPE]')asall_nationsgroupbyo_yearorderbyo_year;selectnation,o_year,sum(amount)assum_profitfrom（selectn_nameasnation,extract(yearfromo_orderdate)aso_year,I_extendedprice*(1-I_discount)-ps_supplycost*I_quantityasamountfrompart,supplier,lineitem,partsupp,orders,nationwheres_suppkey=l_suppkeyandps_suppkey:I_suppkeyandps_partkey=I_partkeyandp_partkey=I_partkeyando_orderkey:I_orderkeyands_nationkey=n_nationkeyandp_namelike'%[COLOR]%')asprofitgroupby.nation,o_yearorderbynation,o_yeardesc;select1带有FROM多表子查询，查询表中使用函数(EXTRACT)从结果集中统计(SUM)20Q11重要库存标志查询wonhigh.crc_custkey,c_name,sum(l_extendedprice*(1-I_discount))asrevenue,c_acctbal,n_name,c_address,c_phone,c_commentfromcustomer,orders,lineitem,nationwherec_custkey=o_custkeyandI_orderkey=o_orderkeyando_orderdate>=date'[DATE]ando_orderdate<date'[DATE]'+interval'3monthandI_returnflag='R'andc_nationkey:n_nationkeygroupbyc_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_commentorderbyrevenuedesc;selectps_partkey,sum(ps_supplycost*ps_availqty)asvaluefrompartsupp,supplier,nationwhereps_suppkey=s_suppkeyands_nationkey=n_nationkeyandn_name='[NATION]'groupbyps_partkeyhavingsum(ps_supplycost*多表条件查询(>=，<），并统计(SUM)1在GROUPBY中使用比较条件(HAVING>比较值从子查询中查出Q12货运模式和命令优先查询wonhigh.crps_availqty)>(selectsum(ps_supplycost*ps_availqty)*[FRACTION]frompartsupp]supplier,nationwhereps_suppkey=s_suppkeyands_nationkey=n_nationkeyandn_name=[NATION]')orderbyvaluedesc;selectI_shipmode,sum(casewheno_orderpriority'1-URGENT'oro_orderpriority='2-HIGH'then1elseOend)ashigh_line_count,sum(casewheno_orderpriority<>'1-URGENT'ando_orderpriority<>'2-HIGH'then1else0end)aslow_line_countfromorders，lineitemwhereo_orderkey=I_orderkeyandI_shipmodein([SHIPMODE1]','[SHIPMODE2]")andI_commitdate<I_receiptdateandI_shipdate<I_commitdateandI_receiptdate>=date'[DATE]andI_receiptdate<date'[DATE]'+interval'1'yeargroupbyI_shipmodeorderbyI_shipmode;1带有逻辑判断(WHENAND/WHENOR)的查询，并统计(SUM)Q13Q14消费者分配查询促进效果查询selectwonhigh.crc_count,count(*)ascustdistfromselectc_custkey,count(o_orderkey)fromcustomerleftouterjoinordersonc_custkey=o_custkeyando_commentnotlike"%[WORD1]%[WORD2]%'groupbyc_custkey)asc_orders(c_custkey,c_count)groupbyc_countorderbycustdistdesc,c_countdesc;select100.00*sum(casewhenp_typelike'PROMO%thenl_extendedprice*(1-l_discount)else0end)/sum(l_extendedprice*(1-I_discount))aspromo_revenuefromlineitem,partwhere1带有FROM子查询，子查询中使用外联结1使用逻辑判断(caseWHENELSE)的查询Q15Q16头等零件/供应商关系查询wonhigh.crIL_partkey=p_partkeyandI_shipdate>=date'[DATE]'andI_shipdate<date'[DATE]'+interval'1'month;createviewrevenue[STREAM_ID](supplier_nototal_revenue)asselectI_suppkey.sum(l_extendedprice*(1-I_discount))fromlineitemwhereI_shipdate>=date'[DATE]'andI_shipdate<date'[DATE]'+interval'3'monthgroupbyI_suppkey;selects_suppkey,s_name,s_address,s_phone,total_revenuefromsupplier,revenue[STREAM_ID]wheres_suppkey=supplier_noandtotal_revenue:(selectmax(total_revenue)fromrevenue[STREAM_ID])orderbys_suppkey;dropviewrevenue[STREAM_ID]selectp_brand,p_type,p_size,count(distinctps_suppkey)assupplier_cnt全部使用视图和表关联查询1在WHERE子句中使用子查Q17Q18小量订单收入查询大订单顾客查询fromwonhigh.crpartsupp，partwherep_partkey=ps_partkeyandp_brand<>'[BRAND]'andp_typenotlike'[TYPE]%'andp_sizein([SIZE1],[SIZE2],[SIZE3],[SIZE4][SIZE5],[SIZE6],[SIZE7],[SIZE8]andpS_suppkeynotin(selects_suppkeyfromsupplierwheres_commentlike'%Customer%Complaints%')groupbyp_brand,p_type,p_sizeorderbysupplier_cntdesc,p_brand,p_type,p_size;selectsum(I_extendedprice)/7.0asavg_yearlyfromlineitem,partwherep_partkey=I_partkeyandp_brand='[BRAND]andp_container='[CONTAINER]'andI_quantity<(select0.2*avg(l_quantity)fromlineitemwhereIL_partkey=p_partkey);select询，使用IN/NOTIN判断条件，并统请(COUNT)1在WHERE子句中使用子查询，使用<比较，使用了AVG函数100Q19折扣收入查询wonhigh.crc_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity)fromcustomer,orders,lineitemwhereo_orderkeyin(selectI_orderkeyfromlineitemgroupbyI_orderkeyhavingsum(L_quantity)>[QUANTITY])andc_custkey=o_custkeyando_orderkey:I_orderkeygroupbyc_name,c_custkey,o_orderkey,o_orderdate,o_totalpriceorderbyo_totalpricedesc,o_orderdate,selectsum(l_extendedprice*(1-I_discount))asrevenuefromlineitem,partwhere（在WHERE子句中使用IN条件从子查询结果中比较多条件比较查询wonhigh.crp_partkey=I_partkeyandp_brand='[BRAND1]'andp_containerin（'SMCASE'，'SMBOX'，‘SMPACK'，‘SMPKG'）andI_quantity>=[QUANTITY1]andI_quantity<=[QUANTITY1]+10andp_sizebetween1and5andI_shipmodein（'AIR','AIRREG'）andI_shipinstruct=‘DELIVERINPERSON)or（p_partkey=I_partkeyandp_brand='[BRAND2]'andp_containerin（'MEDBAG'，‘MEDBOX'，"MEDPKG'，"MEDPACK')andI_quantity>=[QUANTITY2]andI_quantity<=[QUANTITY2]+10andp_sizebetween1and10andI_shipmodein（'AIR':'AIRREG'）andI_shipinstruct=‘DELIVERINPERSON)or(p_partkey=IL_partkeyandp_brand='[BRAND3]'andp_containerin（"LGCASE'"LGBOX'，“LGPACK'，"LGPKG')andL_quantity>=[QUANTITY3]andI_quantity<:Q20潜在零件促进查询wonhigh.cr[QUANTITY3]+10andp_sizebetween1and15andI_shipmodein（'AIR'，'AIRREG'）andI_shipinstruct='DELIVERINPERSON）selects_name,froms_addresssupplier,nationwheres_suppkeyin(selectps_suppkeyfrompartsuppwhereps_partkeyin（selectp_partkeyfrompartwherep_namelike'[COLOR]%)andps_availqty>（select0.5*sum(l_quantity)fromlineitemwhereI_partkey1WHERE条件子查询(三层)Q21不能按时交货供应商查询wonhigh.cr=ps_partkeyandI_suppkey=ps_suppkeyandI_shipdate>=date('[DATE]')andI_shipdate<date([DATE]'）+interval1'year))ands_nationkey=n_nationkeyandn_name='[NATION]'orderbys_name;selects_name,count(*)asnumwaitfromsupplier,lineitemI1,orders,nationwheres_suppkey=I1.I_suppkeyando_orderkey=I1.l_orderkeyando_orderstatus='F'andI1.l_receiptdate>I1.l_commitdateandexists(select*fromlineitemI2where12.1_orderkey=l1.1_orderkeyand12.1_suppkey<>I1.l_suppkey)andnotexists(select*fromlineitem13where13.1_orderkey=11.1_orderkeyand13.l_suppkey<>l1.I_suppkeyandI3.1_receiptdate>13.1_commitdate)ands_nationkey=n_nationkeyandn_name='[NATION]'groupbys_nameorderbynumwaitdesc,s_name;100在WHERE条件中使用子查询，使用EXISTS和NOTEXISTS判断Q22全球销售机会查询selectwonhigh.crcntrycode,count(*)asnumcust,sum(c_acctbal)astotacctbalfrom（selectsubstring(c_phonefrom1for2)ascntrycodec_acctbalfromcustomerwheresubstring(c_phonefrom1for2)in([11]",[12]”,[13],[14]",[15]",[16],[17]angc_acctbal>(selectavg(c_acctbal)fromcustomerwherec_acctbal>0.00andsubstring(c_phonefrom1for2)in([11]',[12]',[13]',[14]',[15],[16],[17]])andnotexists(select*fromorderswhereo_custkey=c_custkey))ascustsalegroupbycntrycodeorderbycntrycode1在WHERE条件中使用判断子查询、IN、NOTEXISTS，并统计(SUMCOUNT)查询结果3.3.测试数据规模数据规模1GB表名regionnationsuppliercustomerpartpartsupporderslineitem52510000150000200000800000510GB2510000015000002000000800000030GB52530000045000006000000100GB52510000001500000020000000300GB5253000000450000006000000024000000800000002.4E+08150000015000000450000001.5E+086000000600000001.8E+083.4．测试工具开发3.4.1.插入数据功能1）找到需要删除的数据(平面文件中)2）解析文件内容并找到要删除表的主键值拼接删除的sql语句进行删除3）6E+084）先删除lineitem表的数据、最后删除orders表的数据4.5E+081.8E+091000GB525100000001.5E+082E+088E+081.5E+096E+09备注地区表国家表供应商表顾客表零件零件供应商订单表订单明细5）数据大小不超过总大小的0.1%3.4.2.删除数据功能找到需要删除的数据(平面文件中)1）2）解析文件内容并找到要删除表的主键值3）拼接删除的sql语句进行删除先删除lineitem表的数据、最后删除orders表的数据4）数据大小不超过总大小的0.1%5）3.5.测试步骤1)选择一种数据库，通过客户端登陆2)3)4)5)6)删表。如果测试表存在，则删除表创建测试表选择一个数据级的测试数据导入至对应的测试表建立相应的主键、外键约束(infobright不需要创建约束，因为它是列式存储的)记录装载数据的开始、结束时间开始单步顺序执行22条查询语句7）记录第一条查询开始的时间和最后一条查询结束的时间8）9）记录每条语句的响应时间10）开始并发执行组合1(随机组合的查询语句)的查询11）开始并发执行组合2(随机组合的查询语句)的查询12）记录并发执行的开始时间、结束时间13）记录每条查询语句的响应时间14）提取nmon的数据，整理出服务器资源消耗的情况15）根据整理的时间、资源消耗情况绘制出折线图4.测试结果4.1．数据量级一1GB执行时间记录InfobrightPostgreSQL装载数据单条查询并发查询装载数据单条查询并发查询开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间2014-12-1017:37:032014-12-1017:38:012014-12-1121:31:382014-12-1123:06:032014-12-1123:16:052014-12-1200:04:262014-12-1111:31:392014-12-1111:32:242014-12-1111:32:542014-12-1113:07:542014-12-1113:07:542014-12-1113:24:354035302520151050装载时间对比1GB装载时间region4.1.2.450.00400.00350.00300.00250.00200.00150.00100.0050.000.00nationsupplieroracle(S)串行时间对比partpartsuppcustomer-infobright(S)1GB串行时间oracle(S)infobright(s)orderspostgres(S)postgres(S)说明：此处纵轴的400秒代表sql执行响应时间至少超过了1800秒lineitem4.1.3.120.00100.0080.0060.0040.0020.000.00450.00400.00350.00300.00250.00200.00150.00100.0050.000.00并行时间对比1GB的组合1并行时间Q1(S)T0.00Q7(S)Q2(S)oracleQ3(S)infobright11.13Q4(S)Q5(S)postgresTo.511GB的组合2并行时间Q8(S)oracleQ12(S)Q16(S)infobrightQ17(S)+postgresQ6(S)1z14.zzQ21(S)说明：此处纵轴的500秒代表sql执行响应时间至少超过了1800秒Infobright资源消耗情况4.1.4.·CPU+IO防%S+%n100.080.060.040.020.00.0SystemSummarylocalhost2014/12/10CPU%00000005内存情况(总内存：32GB，单位：MB)lo/secMemoryMBlocalhost2014/12/10memfree+30201002015axsa1058000000534555559988500088%000700000000770000PostgreSQL资源消耗情况4.1.5.CPU+IO消耗情况SystemSummarylocalhost2014/12/11CPU%100.0%sAS+%uSn80.060.040.020.00.0052lo/sec15000100005000057035344224555511%S+%n%AS+%sn100.080.060.040.020.00.0100806040200SystemSummarylocalhost2014/12/11CPU%00lo/sec623322222222222222222220552SystemSummarylocalhost2014/12/11CPU%0lo/sec42333333333333333333内存消耗情况(总内存：32GB，单位：MB)53553333MemoryMBlocalhost2014/12/11memfree+3020100绘图区100000302010100008000600040002000039993333333333339于302010302010图表区MemoryMBlocalhost2014/12/11memfree0040802408235223353844440245822222222222222222222222222MemoryMBlocalhost2014/12/11memfree90ot-t9.381数据量级一10GB4.2.OracleInfobright装载数据单条查询并发查询装载数据238593488233333m3333333执行时间记录开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间2014-12-1017:252014-12-1017:392014-12-1017:482014-12-1018:012014-12-1018:052014-12-1018:282013-12-1123:302013-12-1123:39PostgreSQL00350300250200150100500单条查询并发查询装载数据单条查询并发查询开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间装载时间对比2013-12-1123:582014-12-1202:17:012014-12-1202:36:062014-12-1202:54:442014-12-1118:13:182014-12-1118:21:292014-12-1118:26:122014-12-1120:55:132014-12-1120:55:132014-12-1121:30:0210GB装载时间regionnationsupplieroracle(S)partpartsuppcustomerinfobright(S)orderspostgres(S)lineitem4.2.2.450.00400.00350.00300.00250.001200.00150.00100.0050.000.00串行时间对比10GB串行时间oracle(S)infobright(S)postgres(S)说明：此处纵轴的400秒代表sql执行响应时间至少超过了1800秒13500.003000.002500.002000.001500.001000.00500.000.00n并行时间对比la184Q1(S)TR12579911873510GB的组合1并行时间Q3(S)Q2(S)oracleninfobrightQ4(S)Q5(S)postgres说明：此处纵轴的3000秒代表sql执行响应时间至少超过了1小时130066Q6(S)TR7n-450.00400.00350.00300.00250.00200.00150.00100.0050.000.00Q7(S)10GB的组合2并行时间Q8(S)oracleQ12(S)Q16(S)infobrightQ17(S)postgres说明：此处纵轴的400秒代表sql执行响应时间未知4.2.4.Infobright资源消耗情况·CPU+IO使用情况Q21(S)SystemSummarylocalhost2014/12/11CPU%100%AS+%Sn%S+%n806040200lo/sec000638053333333333333333333333333SystemSummarylocalhost2014/12/12CPU%10080604020olo/secA80006000saa40002000040ua302010805000000000000000000000000010080%AS+%Sn840010080%AS+%sn84000SystemSummarylocalhost2014/12/12CPU%0000lo/sec555500SystemSummarylocalhost2014/12/12CPU%2内存使用情况(总内存：32GB，单位：MB)lo/secMemoryMBlocalhost2014/12/11memfree40千30201000000015105040302010082357218538444405458453333333333333333333333333333区+252015105020千15105o千108642o00MemoryMBlocalhost2014/12/12memfree00000COo3-949MemoryMBlocalhost2014/12/12memfree00550000国务##的公店8890MemoryMBlocalhost2014/12/12memfree%04.081353884444845aaaaaaaaaaaaaaaaaaaaaa4.2.5.·PostgreSQL资源消耗情况CPU+IO资源消耗aa0%AS+%Sn%AS+%sn%S+%sn100.080.060.040.020.00.0100.080.060.040.020.00.0100806040200SystemSummarylocalhost2014/12/11CPU%lo/sec24400838025025210555SystemSummarylocalhost2014/12/11CPU%4a892922lo/sec683684440555009090009090999999909090SystemSummarylocalhost2014/12/11CPU%lo/sec150001000050000201510501500010000500045500522520253000022222222200000000000内存资源消耗(总内存：32GB，单位：MB)30千2010MemoryMBlocalhost2014/12/11memfree013333555444444444444444MemoryMBlocalhost2014/12/114444444408040444memfreeOS.830+2010004000300020001000TE.89.bt.810000080800MemoryMBlocalhost2014/12/1188TO8T90:00888880cmemfree82020908S-8T9S.8Tps005499098v.499999MemoryMBlocalhost2014/12/1149299T80:90:6T00099400030002000999992990memfree800000:00202010垂直（值轴02202200000000000008004002292294.3.数据量级一30GBOracleInfobrightPostgreSQL装载数据单条查询并发查询装载数据单条查询并发查询装载数据单条查询并发查询执行时间记录开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间2014-12-1018:242014-12-1018:572014-12-1019:072014-12-1020:232014-12-1020:332014-12-1020:572014-12-1200:082014-12-1200:422014-12-1200:422014-12-1202:372014-12-1202:372014-12-1203:032014-12-1121:30:062014-12-1121:52:382014-12-1122:13:542014-12-1200:46:372014-12-1200:46:372014-12-1201:30:014.3.1.140012001000800600400200o装载时间对比30GB装载时间regionnationsupplieroracle(S)4.3.2.800.00700.00600.00500.00400.00300.00200.00100.000.00串行时间对比partpartsuppcustomerinfobright(S)30GB串行时间oracle(S)infobright(S)orders-postgres(S)postgres(S)lineitem说明：此处纵轴的700秒代表sql执行响应时间至少超过了1800秒4.3.3.2500.002000.001500.001000.00500.000.00并行时间对比30GB的组合1并行时间Q1(S)11n51Q2(S)oracleQ3(S)infobright110577Q4(S)Q5(S)postgres1752说明：此处纵轴的2000秒代表sql执行响应时间未知30GB的组合2并行时间700.00600.00500.00400.00300.00200.00100.000.00Q7(S)Q8(S)oracleQ12(S)Q16(S)Q17(S)infobrightpostgres说明：此处纵轴的600秒代表sql执行响应时间未知Infobright资源消耗情况4.3.4.CPU+IO资源消耗LaaaaaQ6(S)IR2A5Q21(S)Iral%AS+%Sn%sAs+%sn%sAs+%sn%A+%Sn100806040200100806040200100808420100806040200SystemSummaryvipbitest2014/12/12CPU%0lo/sec5000SystemSummaryvipbitest2014/12/12CPU%0lo/sec0SystemSummaryvipbitest2014/12/12CPU%o022232lo/secn23252222SystemSummaryvipbitest2014/12/12CPU%3lo/sec55S10008006004002000500400aa300200ysia100250200saa15010050030001000.内存消耗(总内存：64GB，单位：MB)MemoryMBvipbitest2014/12/12memfree千f8006004002000252015105010864200.080004248888888088888.88888880080g00MemoryMBvipbitest2014/12/12memfree绘图区0.0o08800022002to03050to00MemoryMBvipbitest2014/12/12memfree系列memfree"点02:1500000值：274.6ISZS:00SS059toto03803433085582889998080008000aaaoaaaaaaaaa642MemoryMBvipbitest2014/12/12memfree8008088888804.3.5.PostgreSQL资源消耗情况CPU+IO资源消耗93449585588888088SystemSummarylocalhost2014/12/11CPU%100%A+%Sn%sAs+%n8060402000000022lo/sec100008000600040002000C111111SystemSummarylocalhost2014/12/11CPU%100.080.060.040.020.00.0lo/sec15000ana222222222222222222222222100005000%AS+%Sn100.080.060.040.020.00.0SystemSummarylocalhost2014/12/11CPU%lo/sec333333333333333333333333内存资源消耗(总内存：32GB，单位：MB)MemoryMBlocalhost2014/12/11memfree千2520151050+108642000000z22222402252215000100005000C35394440054552222222222MemoryMBlocalhost2014/12/11memfreeo:209006488482MemoryMBlocalhost2014/12/11memfree0046802458235223853944440545833333333333333333333333333333数据量级—100GB4.4.执行时间记录OracleInfobrightPostgreSQL装载数据单条查询并发查询装载数据单条查询并发查询装载数据开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间结束时间开始时间2014-12-1021:492014-12-1023:522014-12-1100:092014-12-1102:362014-12-1102:372014-12-1103:002014-12-1203:102014-12-1204:502014-12-1204:562014-12-1209:10:112014-12-1209:412014-12-1210:12:032014-12-1201:30:114.4.1.40003500300025002000150010005000单条查询并发查询结束时间开始时间结束时间开始时间结束时间装载时间对比2014-12-1202:44:392014-12-1203:56:252014-12-1208:58:442014-12-1208:58:442014-12-1210:00:02100GB装载时间垂直(值轴主要网格线regionnationsupplieroracle(S)partpartsuppcustomerinfobright(S)orderspostgres(S)lineitem4.4.2.1600.001400.001200.001000.00800.00600.00400.00200.000.00串行时间对比100GB串行时间moracle(S)infobright(S)postgres(S)说明：此处纵轴的1400秒代表sgl执行响应时间至少超过了2000秒4.4.3.2500.002000.001500.001000.00500.000.00并行时间对比100GB的组合1并行时间Q1(S)148710Q2(S)oracleQ3(S)infobright147150Q4(S)Q5(S)postgresIRAnA说明：此处纵轴的2000秒代表sgl执行响应时间未知Q6(S)1370251600.001400.001200.001000.00800.00600.00400.00200.000.00Q7(S)100GB的组合2并行时间Q12(S)Q8(S)oracleinfobrightQ16(S)Q17(S)postgres说明：此处纵轴的1500秒代表sql执行响应时间未知4.4.4.Infobright资源消耗情况·CPU+IO资源消耗Q21(S)SystemSummarylocalhost2014/12/12CPU%100%AS+%Sn%A+%Sn806040200lo/sec4092450555g000000000800000000008000SystemSummarylocalhost2014/12/12CPU%10080604020004ov4lo/sec430002oysia1000150001000050000axsia%AS+%Sn%AS+%sn%S+%sn%+%n1008060402001008060402001008084200SystemSummarylocalhost2014/12/12CPU%00000lo/sec02520858544S40555555555555555555555555555SystemSummarylocalhost2014/12/12CPU%0000666000030036lo/sec3%04866555%0000SystemSummarylocalhost2014/12/12CPU%%00015lo/sec444855SystemSummarylocalhost2014/12/12CPU%10080604020o00808809285882288528088lo/sec4080848870555588801500010000500001000080006000400020000100008000600040002000015000aysa1000050000%AS+%Sn%AS+%sn100806040200100806040200SystemSummarylocalhost2014/12/12CPU%lo/sec44055522085344000522500SystemSummarylocalhost2014/12/12CPU%co00000059200025000o3olo/sec850内存资源消耗(总内存：32GB，单位：MB)oo05500MemoryMBlocalhost2014/12/12memfreeH15105图表区80%030380er3t9t-8tSz63ES01500010000500015000a100005000C15001000500600400200MemoryMBlocalhost2014/12/12memfree08058222688243802458054580MemoryMBlocalhost2014/12/12memfree00058014580145888458044881458图表区988896999999883869993336MemoryMBlocalhost2014/12/12memfree6004002000给圈区800088080090068885808448854582483888988888888888888888000MemoryMBlocalhost2014/12/12memfree25002000150010005000N000680101463840808458OPARPPD66aga6a6aQaa60水平轴MemoryMBlocalhost2014/12/12memfree于408058850883589885888888888888888888888888888888000MemoryMBlocalhost2014/12/12memfree300020001000000006630226802301440668898888988888888888888988889889MemoryM