分享

更多

   

从 TPCH 测试看 SPL 性能优化技巧 1

2019-06-12  raqsoft

测试环境说明

CPU:4颗,主频2.6G,每个CPU内核数8个。

硬盘:800G,15000转SAS硬盘,理论读写速度150m/s。

内存:64G。

操作系统:Linux cent os 6

SQL1

select

         l_returnflag,

         l_linestatus,

         sum(l_quantity) as sum_qty,

         sum(l_extendedprice) as sum_base_price,

         sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

         sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

         avg(l_quantity) as avg_qty,

         avg(l_extendedprice) as avg_price,

         avg(l_discount) as avg_disc,

         count(*) as count_order

from

         lineitem

where

         l_shipdate <= date '1998-12-01' - interval '90' day(3)

group by

         l_returnflag,

         l_linestatus

order by

         l_returnflag,

         l_linestatus;

 

LineItem表原始数据大小为79.6G(文本格式),数据行数600037902。

Oracle空间文件大小为200G,lineItem表数据导入时间20个小时。

SQL运行时间为637秒。

在SQL里增加并行选项select /*+ parallel(lineitem 10) */后,运行时间下降到397秒。

用集文件执行

         集文件为集算器支持的数据文件格式。特点是对数据进行一定的压缩,以提高查找和计算的磁盘性能。

         集文件结构简单,应用范围明确,其生成速度远远高于oracle的数据导入速度。lineitem数据导入仅需48分28秒,最终生成的集文件大小为56.9G。

        

         用集文件改写上面SQL的SPL脚本如下:

A

1

=now()

2

=file(path+"lineitem.btx")

3

=A2.cursor@b(
     L_RETURNFLAG,  L_LINESTATUS,  L_QUANTITY,
     L_EXTENDEDPRICE, L_DISCOUNT, L_TAX,   L_SHIPDATE
    )

4

=A3.select(L_SHIPDATE<=date(“1998-09-02”))

5

=A4.groups(
     L_RETURNFLAG, L_LINESTATUS;
     sum(L_QUANTITY):sum_qty,
     sum(L_EXTENDEDPRICE):sum_base_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT)):sum_disc_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT) * (1 + L_TAX)):sum_charge,
     avg(L_QUANTITY):avg_qty,
     avg(L_EXTENDEDPRICE):avg_price,
     avg(L_DISCOUNT):avg_disc,
     count(1):count_order
     )

6

=interval@s(A1,now())

上例运行时间为412秒,比SQL少了225秒。

 

groups和groupx的选用

         在集算器中分组统计函数有两个,一个是groups,另一个是groupx。

         在上例中已经介绍了groups的脚本。groupx脚本如下:

        

A

1

=now()

2

=file(path+"lineitem.btx")

3

=A2.cursor@b(
     L_RETURNFLAG,  L_LINESTATUS,  L_QUANTITY,
     L_EXTENDEDPRICE, L_DISCOUNT, L_TAX,   L_SHIPDATE
    )

4

=A3.select(L_SHIPDATE<=date(“1998-09-02”))

5

=A4.groupx(
     L_RETURNFLAG, L_LINESTATUS;
     sum(L_QUANTITY):sum_qty,
     sum(L_EXTENDEDPRICE):sum_base_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT)):sum_disc_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT) * (1 + L_TAX)):sum_charge,
     avg(L_QUANTITY):avg_qty,
     avg(L_EXTENDEDPRICE):avg_price,
     avg(L_DISCOUNT):avg_disc,
     count(1):count_order
     ).fetch()

6

=now@s()

7

=interval@s(A1,A8)

本例中该脚本的运行时间为418秒,与groups相当。

 

         groups与groupx的区别在于,groups全内存运行,支持并行运行,但当内存不足时不能利用外存,仅仅是抛出异常。groupx在内存不足时会利用外存完成计算,但不支持并行。

         选用groups还是groupx需要预判统计计算过程中,内存占用的大小。决定统计计算中内存占用大小的决定因素是,分组表达式可能产生的分组的个数。

         本例中L_RETURNFLAG为二值,L_LINESTATUS为枚举值,可以判断分组数非常小。因此这里采用groups是合适的(groups通过并行可以大幅提高执行效率,后面会介绍)。

        

关于游标使用

         游标原意是为了减少内存消耗,保证大数据处理能力。但有时也能用于提高性能,原因在于减少内存使用后能减少磁盘换页机会,同时小内存块更容易分配出来、分配速度更快。

         本例中因数据量大,必须使用游标。我们在笔记本上用1G的数据量进行过测试。当采用非游标运行的时候,内存占用达到了2380.2M,运行时间为100秒。而采用游标处理后内存占用降为183.49M,运行时间降为38秒。

 

用并行计算提高运算速度

我们看一下并行计算对运算效率的提升:

A

1

=now()

2

=file(path+"lineitem.btx")

3

=A2.cursor@mb(
     L_RETURNFLAG,  L_LINESTATUS,  L_QUANTITY,
     L_EXTENDEDPRICE, L_DISCOUNT, L_TAX,   L_SHIPDATE
    )

4

=A3.select(L_SHIPDATE<= date(“1998-09-02”))

5

=A4.groups(
     L_RETURNFLAG, L_LINESTATUS;
     sum(L_QUANTITY):sum_qty,
     sum(L_EXTENDEDPRICE):sum_base_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT)):sum_disc_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT) * (1 + L_TAX)):sum_charge,
     avg(L_QUANTITY):avg_qty,
     avg(L_EXTENDEDPRICE):avg_price,
     avg(L_DISCOUNT):avg_disc,
     count(1):count_order
     )

6

=now@s()

7

=interval@s(A1,A6)

         这里采用的是8线程,运行时间为84秒,运算效率提升了近5倍。

         并行计算可以充分利用CPU、硬盘等计算机资源,提升运算效率效果明显。

 

         设置不同的并行运算数可以取得不同的运算效率。在实际运行中,还要受硬盘转速、CPU核数等多种条件的影响。即使相同条件下,多次测试的结果也会有一定的波动。具体的性能指标只有多次实测才能得出。

 

用组表提高计算速度

         集算器还提供了列存格式,即组表。我们再用组表来尝试一下,先生成组表。用文本文件生成组表的SPL脚本,如下:

A

1

=file(path+"lineitem.tbl").cursor@(;   , "|")

2

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

3

=A1.new(
     _1:L_ORDERKEY, _2:L_PARTKEY,   _3:L_SUPPKEY,
     _4:L_LINENUMBER, _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
    )

4

>A2.append(A3)

 

         组表有更好的压缩效率,最终生成的文件的大小为29.4G,其大小几乎只有集文件的一半。

 

组表运行脚本:

A

1

=now()

2

=file(path+"LINEITEM.ctx").create()

3

=A2.cursor@m(
       L_RETURNFLAG,L_LINESTATUS,L_QUANTITY,
     L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_SHIPDATE;
     L_SHIPDATE<= date(“1998-09-02”)
    )

4

=A3.groups(
     L_RETURNFLAG,L_LINESTATUS;
     sum(L_QUANTITY):sum_qty,
     sum(L_EXTENDEDPRICE):sum_base_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT)):sum_disc_price,
     sum(L_EXTENDEDPRICE * (1 -   L_DISCOUNT) * (1 + L_TAX)):sum_charge,
     avg(L_QUANTITY):avg_qty,
     avg(L_EXTENDEDPRICE):avg_price,
     avg(L_DISCOUNT):avg_disc,
     count(1):count_order
   )

5

=interval@s(A1,now())

运行时间变为60秒,相对于集文件效率提高了1/3。

 

组表提高运行速度的原因是:

1、采用列存方式,数据集中,需要加载的数据量更少。

2、列存使得压缩比更高,磁盘数据进一步减少。

        

 

组表排序后对性能的影响

         组表的一个好处是,可以让组表存储时,针对一些常用数据有序,以提高性能。本脚本有个针对L_SHITDATE的条件,如果将数据按此字段排序后会提高过滤性能。下面程序是让组表针对L_SHIPDATE排序:

A

1

=file(path+"lineitem.tbl").cursor@(;   , "|")

2

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

3

=A1.new(
     _11:L_SHIPDATE, _1:L_ORDERKEY,   _2:L_PARTKEY, _3:L_SUPPKEY,
     _4:L_LINENUMBER, _5:L_QUANTITY,   _6:L_EXTENDEDPRICE,
     _7:L_DISCOUNT, _8:L_TAX,   _9:L_RETURNFLAG, _10:L_LINESTATUS,
     _12:L_COMMITDATE, _13:L_RECEIPTDATE,
     _14:L_SHIPINSTRUCT, _15:L_SHIPMODE,   _16:L_COMMENT
    )

4

>A2.append(A3.sortx(L_SHIPDATE))

 

排序后,再次运行脚本,运行时间为44秒(8线程),明显优于未排序情况。


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    类似文章 更多
    喜欢该文的人也喜欢 更多