从 TPCH 测试学习性能优化技巧

新版本 用 TPCH 练习性能优化

一、     目标

TPCH是由TPC(Transaction Processing Performance Council)事务处理性能委员会公布的一套针对数据库决策支持能力的测试基准,通过模拟数据库中与业务相关的复杂查询考察数据库的综合处理能力,获取数据库操作的响应时间。

TPCH基准模型中定义了一个数据库模型,容量可以在1GB~10000GB8个级别中进行选择。数据库模型包括CUSTOMERLINEITEMNATIONORDERSPARTPARTSUPPREGIONSUPPLIER 8张数据表,以及22SQL查询语句,涉及内容广泛丰富,可以较完整地测试数据库的运算性能。

TPCHSQL中不乏一些多层嵌套的复杂查询,执行性能较差。对于这些查询,如果能采用更合理的存储方案,设计低复杂度算法并配合并行等手段,将获得更优的性能。但遗憾的是,由于理论体系的限制,很多想法无法用SQL实现,而SQL程序员也因此不关注这些性能优化方法,经常只能忍受数据库的低速运算。

本系列文章将通过这8张表及22SQL讨论数据运算时的性能优化技巧,仔细分析每一条语句,发现其运算和数据特征,设计更合理的算法加以实现。由于SQL难以实现这些算法和存储结构,我们将使用集算器组表来存储数据,并用SPL实现这些算法,同时与Oracle上的SQL对比性能。希望读者能够通过这些内容学会各种情况下适用的高性能计算技术。

二、     环境

CPU2Intel3014,主频1.7G,每个CPU内核数6个。

硬盘(SSD)1T  561MB/s()   523MB/s()   接口:SATA 6.0Gb/s

内存:64G

操作系统:Linux CentOS 7

 

三、     数据准备

为了回避硬盘读取数据能力的差异,我们将Oracle数据表的数据和SPL组表文件均存储在同一块SSD固态硬盘上。

1.        TPCH原始数据生成

利用TPCH官方工具生成8张表的原始数据,数据量总规模100G,各文件大小见后表。

2.        数据导入Oracle

数据导入Oracle比较简单,在Oracle数据库中创建一个数据库名为tpch,运行TPCH工具生成的dss.ddldss.ri文件来创建8张数据表以及表的主键、外键关系。然后运行ctl文件将原始数据分别导入各表中。例如导入lineitem表数据的ctl文件如下:

LOAD DATA INFILE '/home/tpch_2_17_0/tbls/lineitem.tbl'

INTO TABLE LINEITEM

(L_ORDERKEY terminated by '|',

L_PARTKEY terminated by '|',

L_SUPPKEY terminated by '|',

L_LINENUMBER terminated by '|',

L_QUANTITY terminated by '|',

L_EXTENDEDPRICE terminated by '|',

L_DISCOUNT terminated by '|',

L_TAX terminated by '|',

L_RETURNFLAG terminated by '|',

L_LINESTATUS terminated by '|',

L_SHIPDATE date "yyyy-mm-dd"  terminated by '|',

L_COMMITDATE date "yyyy-mm-dd"  terminated by '|',

L_RECEIPTDATE date "yyyy-mm-dd"  terminated by '|',

L_SHIPINSTRUCT terminated by '|',

L_SHIPMODE terminated by '|',

L_COMMENT terminated by '|')

将此脚本存成lineitem.ctl文件,然后在linux命令行执行

>sqlldr 数据库用户名/用户密码@tpch control="/home/sjr/oracle/ctl/lineitem.ctl"

即可,最后可在日志里查看导入数据执行时间。其它数据表的数据导入与此类似,不再赘述。

 

3.        生成SPL组表

SPL组表也由原始数据文件tbl来生成,每张表生成一个组表文件,用表的主键作为组表的维。对于数据量很小的 region 和 nation 表,使用集文件来存取数据可以获得更好的性能;对于数据量较大的表需要使用sortx函数对主键排序,关于sortx函数使用的jvm配置请看《JVM 参数调整对 sortx 的影响

运行SPL脚本生成所需要的组表,下文介绍生成各组表的SPL脚本。

3.1   region/nation 集文件

region表为例:


A

1

=file(path+"region.tbl").cursor(;   , "|").new(_1:R_REGIONKEY, _2:R_NAME, _3:R_COMMENT).sortx(R_REGIONKEY;4000000)

2

=file(destinate+"region.btx").export@b(A1)  

用文本文件创建游标取数并按主键排序后再写入集文件,排序时的第二个参数可根据内存大小来确定,省略不写也能工作。nation表与此类似,仅字段名不同。

3.2   customer/supplier/part/orders 组表

customer表为例:


A

1

=file(path+"customer.tbl").cursor(;   , "|").new(_1:C_CUSTKEY, _2:C_NAME, _3:C_ADDRESS, _4:C_NATIONKEY,   _5:C_PHONE, _6:C_ACCTBAL, _7:C_MKTSEGMENT,   _8:C_COMMENT).sortx(C_CUSTKEY;15000000)

2

=file(destinate+"customer.ctx").create(  #C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY,   C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT)

3

>A2.append(A1)

用文本文件创建游标取数并按主键排序后再写入组表,注意组表创建时要指名主键字段(前面加#),排序时的第二个参数可根据内存大小来确定,省略不写也能工作。

其它各表类似,只要改文件名、排序的主键名及数据结构信息即可,不再赘述。

 

3.3  partsupp/lineitem 组表

partsupplineitem分别是partorders的子表,对于主子表的存储,组表有特殊的要求,以lineitem表为例说明。

lineitem表的主键是L_ORDERKEYL_LINENUMBERL_ORDERKEYorders表中的O_ORDERKEY相对应,L_LINENUMBERorders表中某一个订单的明细项编号,这样的表数据结构称为主子表。


A

1

=file(path+"lineitem.tbl").cursor(;   , "|").new(_1:L_ORDERKEY, _4:L_LINENUMBER, _2:L_PARTKEY,   _3:L_SUPPKEY,  _5:L_QUANTITY,   _6:L_EXTENDEDPRICE,_7:L_DISCOUNT, _8:L_TAX, _9:L_RETURNFLAG,   _10:L_LINESTATUS,_11:L_SHIPDATE,     _12:L_COMMITDATE, _13:L_RECEIPTDATE,_14:L_SHIPINSTRUCT,  _15:L_SHIPMODE,   _16:L_COMMENT).sortx(L_ORDERKEY,L_LINENUMBER;4000000)

2

=file(destinate+"lineitem.ctx").create(  #L_ORDERKEY,#L_LINENUMBER,L_PARTKEY, L_SUPPKEY,  L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,   L_TAX,  L_RETURNFLAG, L_LINESTATUS,   L_SHIPDATE, L_COMMITDATE,    L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT;L_ORDERKEY)

3

>A2.append(A1)

注意这里A2在创建组表时和前面不同,后面多了一个参数,表明该组表将会按L_ORDREKEY分段,不会将L_ORDERKEY字段值相同的记录分到两段去,才能保证正确的并行计算结果,我们会在后面的例子中看到这种存储方案的效果。

类似地,partsupp可以看作part表的子表,则生成组表的代码是这样:


A

1

=file(path+"partsupp.tbl").cursor(;   , "|").new(_1:PS_PARTKEY, _2:PS_SUPPKEY, _3:PS_AVAILQTY,   _4:PS_SUPPLYCOST, _5:PS_COMMENT).sortx(PS_PARTKEY,PS_SUPPKEY;4000000)

2

=file(destinate+"partsupp.ctx").create(  #PS_PARTKEY, #PS_SUPPKEY,   PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT;PS_PARTKEY)

3

>A2.append(A1)

 

四、     数据准备时间及文件大小

数据导入Oracle与产生组表的时间对比见下表,只比较数据量最大的lineitemorderspartsupp三张表。

数据表名

LINEITEM

ORDERS

PARTSUPP

Oracle

34859

5494

3542

SPL组表

6000

1068

647

 

原始文件大小、SPL组表文件大小见下表。

数据表名

LINEITEM

ORDERS

PARTSUPP

tbl原始文件

79.5G

17.8G

12.2G

SPL组表

29.4G

7.2G

4.9G

Oracle表数据都是存在库文件里,没有单独的表文件。但是导入数据后的库文件比原始数据的总和要大,可以推断每个表所占的硬盘空间要比原始数据文件大。

TPCH原始数据导入Oracle库表的时间,要比导入到SPL组表的时间多5倍以上(即使加上了排序时间);而且所占硬盘空间也比组表要大一倍多(组表有压缩,有序后更容易压缩)。所以,为了获得更好的查询性能而换用组表来存储数据,是非常值得的,既省时又省空间,数据准备工作变得更快速。

五、     TPCH 测试详细解析

从 TPCH 测试学习性能优化技巧之 Q1

从 TPCH 测试学习性能优化技巧之 Q2

从 TPCH 测试学习性能优化技巧之 Q3

从 TPCH 测试学习性能优化技巧之 Q4

从 TPCH 测试学习性能优化技巧之 Q5

从 TPCH 测试学习性能优化技巧之 Q6

从 TPCH 测试学习性能优化技巧之 Q7

从 TPCH 测试学习性能优化技巧之 Q8

从 TPCH 测试学习性能优化技巧之 Q9

从 TPCH 测试学习性能优化技巧之 Q10

从 TPCH 测试学习性能优化技巧之 Q11

从 TPCH 测试学习性能优化技巧之 Q12

从 TPCH 测试学习性能优化技巧之 Q13

从 TPCH 测试学习性能优化技巧之 Q14

从 TPCH 测试学习性能优化技巧之 Q15

从 TPCH 测试学习性能优化技巧之 Q16

从 TPCH 测试学习性能优化技巧之 Q17

从 TPCH 测试学习性能优化技巧之 Q18

从 TPCH 测试学习性能优化技巧之 Q19

从 TPCH 测试学习性能优化技巧之 Q20

从 TPCH 测试学习性能优化技巧之 Q21

从 TPCH 测试学习性能优化技巧之 Q22

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾