溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

從 TPCH 測(cè)試看 SPL 性能優(yōu)化技巧 1

發(fā)布時(shí)間：2020-06-18 06:40:44 來源：網(wǎng)絡(luò) 閱讀：294 作者：raqsoft 欄目：大數(shù)據(jù)

測(cè)試環(huán)境說明

CPU：4顆，主頻2.6G，每個(gè)CPU內(nèi)核數(shù)8個(gè)。

硬盤：800G，15000轉(zhuǎn)SAS硬盤，理論讀寫速度150m/s。

內(nèi)存：64G。

操作系統(tǒng)：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表原始數(shù)據(jù)大小為79.6G（文本格式），數(shù)據(jù)行數(shù)600037902。

Oracle空間文件大小為200G，lineItem表數(shù)據(jù)導(dǎo)入時(shí)間20個(gè)小時(shí)。

SQL運(yùn)行時(shí)間為637秒。

在SQL里增加并行選項(xiàng)select /*+ parallel(lineitem 10) */后，運(yùn)行時(shí)間下降到397秒。

用集文件執(zhí)行

???????? 集文件為集算器支持的數(shù)據(jù)文件格式。特點(diǎn)是對(duì)數(shù)據(jù)進(jìn)行一定的壓縮，以提高查找和計(jì)算的磁盤性能。

???????? 集文件結(jié)構(gòu)簡單，應(yīng)用范圍明確，其生成速度遠(yuǎn)遠(yuǎn)高于oracle的數(shù)據(jù)導(dǎo)入速度。lineitem數(shù)據(jù)導(dǎo)入僅需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())

上例運(yùn)行時(shí)間為412秒，比SQL少了225秒。

?

groups和groupx的選用

???????? 在集算器中分組統(tǒng)計(jì)函數(shù)有兩個(gè)，一個(gè)是groups，另一個(gè)是groupx。

???????? 在上例中已經(jīng)介紹了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)

本例中該腳本的運(yùn)行時(shí)間為418秒，與groups相當(dāng)。

?

???????? groups與groupx的區(qū)別在于，groups全內(nèi)存運(yùn)行，支持并行運(yùn)行，但當(dāng)內(nèi)存不足時(shí)不能利用外存，僅僅是拋出異常。groupx在內(nèi)存不足時(shí)會(huì)利用外存完成計(jì)算，但不支持并行。

???????? 選用groups還是groupx需要預(yù)判統(tǒng)計(jì)計(jì)算過程中，內(nèi)存占用的大小。決定統(tǒng)計(jì)計(jì)算中內(nèi)存占用大小的決定因素是，分組表達(dá)式可能產(chǎn)生的分組的個(gè)數(shù)。

???????? 本例中L_RETURNFLAG為二值，L_LINESTATUS為枚舉值，可以判斷分組數(shù)非常小。因此這里采用groups是合適的(groups通過并行可以大幅提高執(zhí)行效率，后面會(huì)介紹)。

???????? ?

關(guān)于游標(biāo)使用

???????? 游標(biāo)原意是為了減少內(nèi)存消耗，保證大數(shù)據(jù)處理能力。但有時(shí)也能用于提高性能，原因在于減少內(nèi)存使用后能減少磁盤換頁機(jī)會(huì)，同時(shí)小內(nèi)存塊更容易分配出來、分配速度更快。

???????? 本例中因數(shù)據(jù)量大，必須使用游標(biāo)。我們?cè)诠P記本上用1G的數(shù)據(jù)量進(jìn)行過測(cè)試。當(dāng)采用非游標(biāo)運(yùn)行的時(shí)候，內(nèi)存占用達(dá)到了2380.2M，運(yùn)行時(shí)間為100秒。而采用游標(biāo)處理后內(nèi)存占用降為183.49M，運(yùn)行時(shí)間降為38秒。

?

用并行計(jì)算提高運(yùn)算速度

我們看一下并行計(jì)算對(duì)運(yùn)算效率的提升：


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線程，運(yùn)行時(shí)間為84秒，運(yùn)算效率提升了近5倍。

???????? 并行計(jì)算可以充分利用CPU、硬盤等計(jì)算機(jī)資源，提升運(yùn)算效率效果明顯。

?

???????? 設(shè)置不同的并行運(yùn)算數(shù)可以取得不同的運(yùn)算效率。在實(shí)際運(yùn)行中，還要受硬盤轉(zhuǎn)速、CPU核數(shù)等多種條件的影響。即使相同條件下，多次測(cè)試的結(jié)果也會(huì)有一定的波動(dòng)。具體的性能指標(biāo)只有多次實(shí)測(cè)才能得出。

?

用組表提高計(jì)算速度

???????? 集算器還提供了列存格式，即組表。我們?cè)儆媒M表來嘗試一下，先生成組表。用文本文件生成組表的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，其大小幾乎只有集文件的一半。

?

組表運(yùn)行腳本：


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())

運(yùn)行時(shí)間變?yōu)?0秒，相對(duì)于集文件效率提高了1/3。

?

組表提高運(yùn)行速度的原因是：

1、采用列存方式，數(shù)據(jù)集中，需要加載的數(shù)據(jù)量更少。

2、列存使得壓縮比更高，磁盤數(shù)據(jù)進(jìn)一步減少。

???????? ?

?

組表排序后對(duì)性能的影響

???????? 組表的一個(gè)好處是，可以讓組表存儲(chǔ)時(shí)，針對(duì)一些常用數(shù)據(jù)有序，以提高性能。本腳本有個(gè)針對(duì)L_SHITDATE的條件，如果將數(shù)據(jù)按此字段排序后會(huì)提高過濾性能。下面程序是讓組表針對(duì)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))

?

排序后，再次運(yùn)行腳本，運(yùn)行時(shí)間為44秒（8線程），明顯優(yōu)于未排序情況。


向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手