"新版本：[链接] 一、目标 TPCH是由TPC(Transaction Processing Performance Council)事务处 .."

sjr 北京
乾学院 32 号会员
1 回帖 • 4,562 浏览 • 5 年前

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

计算＆AI

集算器(484)

一、目标

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

TPCH基准模型中定义了一个数据库模型，容量可以在1GB~10000GB的8个级别中进行选择。数据库模型包括CUSTOMER、LINEITEM、NATION、ORDERS、PART、PARTSUPP、REGION和SUPPLIER 共8张数据表，以及22条SQL查询语句，涉及内容广泛丰富，可以较完整地测试数据库的运算性能。

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

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

二、环境

CPU：2颗Intel3014，主频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.ddl和dss.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 组表

partsupp和lineitem分别是part和orders的子表，对于主子表的存储，组表有特殊的要求，以lineitem表为例说明。

lineitem表的主键是L_ORDERKEY和L_LINENUMBER，L_ORDERKEY与orders表中的O_ORDERKEY相对应，L_LINENUMBER是orders表中某一个订单的明细项编号，这样的表数据结构称为主子表。

	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)