當前位置:首頁 » 編程語言 » tpchsql
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

tpchsql

發布時間: 2022-09-13 14:27:11

❶ 什麼叫tpch

TPC-H測量在商業分析中決策支持系統(DSS)的性能。DSS是一種計算機應用程序,它分析商業數據展現出來使用戶/管理者可以更容易得進行商業決策,例如供求管理、客戶滿意度分析、市場份額分析等。

TPC-H 模 擬了商業環境中的分析端,大量的數據被細化,可以幫助企業進行可靠的商業決策,其中包含一整套面向商業的特殊查詢和並發數據修改內容。該基準中選擇的查詢 和資料庫中的數據都具有廣泛的全行業關聯性。這種基準測試所描述的決策支持系統可檢查大量的數據,所執行的查詢也具有很高的復雜度。並且,TPC-H會基於資料庫的大小將結果分類。

TPH的性能度量單位有兩個,一個被稱為"TPC-H復合式每小時查詢性能單位"(TPC-H Composite Query-per-Hour Performance Metric - QphH@Size),反映的是系統處理查詢的多方面能力,包括查詢執行時選定的資料庫大小、單個流提交查詢時的查詢處理能力,以及多個並發用戶提交查詢時的查詢吞吐量。另外一個,是價格/性能比計量單位$/QphH

不知道是不是這個

❷ mysql錯誤1064 用load語句是出問題啦,代碼在下面啊

看到錯誤信息的最後那個注釋語句了沒?
找到你代碼中的這個注釋語句,
把 --local-infile=1
該為: -- local-infile=1

mysql 注釋 『--』 需要加一個空格 才能生效!

❸ 資料庫伺服器怎麼設計

我理解你問的是硬體,一般思路: 1.選平台:windows,linux還是unix 2.挑主機:哪個廠商,什麼樣的性能要求(TPCC,TPCH),什麼樣的RAS要求,什麼特殊要求如分區、虛擬化等 3.搭架構:這個和你自身的應用以及選的資料庫有關,比如oracle資料庫,是單機單實例還是RAC或者其他方式 4.配存儲:I/.O常常是資料庫的瓶頸,要配合適的存儲才能發揮伺服器性能 當然理論設計還要看實際預算,暫時想到的,供你參考 藍屏

❹ Greenplum集群部署和架構優化,我總結了5000字的心得

最近對離線數倉體系進行了擴容和架構改造,也算是一波三折,出了很多小插曲,有一些改進點對我們來說也是真空地帶,通過對比和模擬壓測總算是得到了預期的結果,這方面尤其值得一提的是郭運凱同學的敬業,很多前置的工作,優化和應用壓測的工作都是他完成的。 

整體來說,整個事情的背景是因為伺服器硬體過保,剛好借著過保伺服器替換的機會來做集群架構的優化和改造。 


1.集群架構改造的目標

在之前也總結過目前存在的一些潛在問題,也是本次部署架構改進的目標:

1)之前 的GP segment數量設計過度 ,因為資源限制,過多考慮了功能和性能,對於集群的穩定性和資源平衡性考慮有所欠缺,在每個物理機節點上部署了10個Primary,10個Mirror,一旦1個伺服器節點不可用,整個集群幾乎無法支撐業務。


2)GP集群 的存儲資源和性能的平衡不夠 ,GP存儲基於RAID-5,如果出現壞盤,磁碟重構的代價比較高,而且重構期間如果再出現壞盤,就會非常被動,而且對於離線數倉的數據質量要求較高,存儲容量相對不是很大,所以在存儲容量和性能的綜合之上,我們選擇了RAID-10。


3)集 群的異常場景的恢復需要完善, 集群在異常情況下(如伺服器異常宕機,數據節點不可用,伺服器後續過保實現節點滾動替換)的故障恢復場景測試不夠充分,導致在一些遷移和改造中,相對底氣不足,存在一些知識盲區。


4)集群版本過 ,功能和性能上存在改進空間。畢竟這個集群是4年前的版本,底層的PG節點的版本也比較舊了,在功能上和性能上都有一定的期望,至少能夠與時俱進。


5)操作系統版本升 ,之前的操作系統是基於CentOS6,至少需要適配CentOS 7 。


6)集群TPCH 壓測驗收 ,集群在完成部署之後,需要做一次整體的TPCH壓測驗收,如果存在明顯的問題需要不斷調整配置和架構,使得達到預期的性能目標。


此外在應用層面也有一些考慮,總而言之,是希望能夠解決絕大多數的痛點問題,無論是在系統層面,還是應用層面,都能上一個台階。


2.集群規劃設計的選型和思考

明確了目標,就是拆分任務來規劃設計了,在規劃設計方面主要有如下的幾個問題:


1)Greenplum的版本選擇 ,目前有兩個主要的版本類別,一個是開源版(Open Source distribution)和Pivotal官方版,它們的其中一個差異就是官方版需要注冊,簽署協議,在此基礎上還有GPCC等工具可以用,而開源版本可以實現源碼編譯或者rpm安裝,無法配置GPCC。綜合來看,我們選擇了 開源版本的6.16.2 ,這其中也詢問了一些行業朋友,特意選擇了幾個涉及穩定性bug修復的版本。


2)數據集市的技術選型 ,在數據集市的技術選型方面起初我是比較堅持基於PostgreSQL的模式,而業務側是希望對於一些較為復雜的邏輯能夠通過GP去支撐,一來二去之後,加上我咨詢了一些行業朋友的意見,是可以選擇基於GP的方案,於是我們就抱著試一試的方式做了壓測,所以數據倉庫和和數據集市會是兩個不同規模體量的GP集群來支撐。


3)GP的容量規劃 ,因為之前的節點設計有些過度,所以在數量上我們做了縮減,每台伺服器部署12個segment節點,比如一共12台伺服器,其中有10台伺服器是Segment節點,每台上面部署了6個Primary,6個Mirror,另外2台部署了Master和Standby,就是即(6+6)*10+2,整體的配置情況類似下面的模式。

4)部署架構方案選型 ,部署架構想起來比較容易,但是落實起來有很多的考慮細節,起初考慮GP的Master和Standby節點如果混用還是能夠節省一些資源,所以設計的數據倉庫和數據集市的部署架構是這樣考慮的,但是從走入部署階段之後,很快就發現這種交叉部署的模式是不可行的,或者說有一些復雜度。


除此之外,在單個GP集群的部署架構層面,還有4類方案考慮。

  方案1 :Master,Standby和segment混合部署
  方案2 :Master,Standby和segment獨立部署,整個集群的節點數會少一些
  方案3 :Segment獨立部署,Master,Standby虛擬機部署
  方案4 :最小化單節點集群部署(這是數據集市最保底的方案)  

這方面存在較大的發揮空間,而且總體來說這種驗證磨合的成本也相對比較高,實踐給我上了一課, 越是想走捷徑,越是會讓你走一些彎路 ,而且有些時候的優化其實我也不知道改怎麼往下走,感覺已經無路可走,所以上面這4種方案其實我們都做了相關的測試和驗證。


3.集群架構的詳細設計和實踐

1)設計詳細的部署架構圖

在整體規劃之上,我設計了如下的部署架構圖,每個伺服器節點有6個Primary,6個Mirror,伺服器兩兩映射。



2)內核參數優化

按照官方文檔的建議和具體的配置情況,我們對內核參數做了如下的配置:

vm.swappiness=10
vm.zone_reclaim_mode = 0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.dirty_background_ratio = 0 # See System Memory
vm.dirty_ratio = 0
vm.dirty_background_bytes = 1610612736
vm.dirty_bytes = 4294967296
vm.min_free_kbytes = 3943084
vm.overcommit_memory=2
kernel.sem = 500 2048000 200 4096


4.集群部署步驟

1)首先是配置/etc/hosts,需要把所有節點的IP和主機名都整理出來。 

2)配置用戶,很常規的步驟

groupadd  gpadmin

useradd gpadmin -g gpadmin

passwd gpadmin

3)配置sysctl.conf和資源配置

4)使用rpm模式安裝

# yum install -y apr apr-util bzip2 krb5-devel  zip

# rpm -ivh open-source-greenplum-db-6.16.2-rhel7-x86_64.rpm

5)配置兩個host文件,也是為了後面進行統一部署方便,在此建議先開啟gpadmin的sudo許可權,可以通過gpssh處理一些較為復雜的批量操作

6)通過gpssh-exkeys來打通ssh信任關系,這里需要吐槽這個ssh互信,埠還得是22,否則處理起來很麻煩,需要修改/etc/ssh/sshd_config文件

gpssh-exkeys -f hostlist

7)較為復雜的一步是打包master的Greenplum-db-6.16.2軟體,然後分發到各個segment機器中,整個過程涉及文件打包,批量傳輸和配置,可以藉助gpscp和gpssh,比如gpscp傳輸文件,如下的命令會傳輸到/tmp目錄下

gpscp -f /usr/local/greenplum-db/conf/hostlist /tmp/greenplum-db-6.16.2.tar.gz =:/tmp

或者說在每台伺服器上面直接rpm -ivh安裝也可以。

8)Master節點需要單獨配置相關的目錄,而Segment節點的目錄可以提前規劃好,比如我們把Primary和Mirror放在不同的分區。 

mkdir -p /data1/gpdata/gpdatap1

mkdir -p /data1/gpdata/gpdatap2

mkdir -p /data2/gpdata/gpdatam1

mkdir -p /data2/gpdata/gpdatam2

9)整個過程里最關鍵的就是gpinitsystem_config配置了,因為Segment節點的ID配置和命名,埠區間都是根據一定的規則來動態生成的,所以對於目錄的配置需要額外注意。

10)部署GP集群最關鍵的命令是

gpinitsystem -c gpinitsystem_config -s 【standby_hostname】


其中文件gpinitsystem_config的主要內容如下:

MASTER_HOSTNAME=xxxx

declare -a DATA_DIRECTORY=(/data1/gpdata/gpdatap1  /data1/gpdata/gpdatap2 /data1/gpdata/gpdatap3 /data1/gpdata/gpdatap4 /data1/gpdata/gpdatap5 /data1/gpdata/gpdatap6)

TRUSTED_SHELL=ssh

declare -a MIRROR_DATA_DIRECTORY=(/data2/gpdata/gpdatam1  /data2/gpdata/gpdatam2 /data2/gpdata/gpdatam3 /data2/gpdata/gpdatam4 /data2/gpdata/gpdatam5 /data2/gpdata/gpdatam6)

MACHINE_LIST_FILE=/usr/local/greenplum-db/conf/seg_hosts

整個過程大約5分鍾~10分鍾以內會完成,在部署過程中建議要查看後端的日誌查看是否有異常,異常情況下的體驗不是很好,可能會白等。


5.集群部署問題梳理

集群部署中還是有很多細節的問題,太基礎的就不提了,基本上就是配置,目錄許可權等問題,我提另外幾個:

1) 資源配置問題 ,如果/etc/security/limits.conf的資源配置不足會在安裝時有如下的警告:


2) 網路問題 ,集群部署完成後可以正常操作,但是在查詢數據的時候會拋出錯誤,比如SQL是這樣的,看起來很簡單:select count(*) from customer,但是會拋出如下的錯誤:

這個問題的主要原因還是和防火牆配置相關,其實不光需要配置INPUT的許可權,還需要配置OUTPUT的許可權。 

對於數據節點可以開放略大的許可權,如:

入口的配置:

-A INPUT -p all -s xxxxx    -j ACCEPT

出口的配置:

-A OUTPUT -p all -s xxxxx    -j ACCEPT


3)網路配置問題 ,這個問題比較詭異的是,報錯和上面是一樣的,但是在排除了防火牆配置後,select count(*) from customer;這樣的語句是可以執行的,但是執行的等待時間較長,比如表lineitem這表比較大,過億的數據量,,在10個物理節點時,查詢響應時間是10秒,但是4個物理節點,查詢響應時間是在90秒,總體刪感覺說不過去。

為了排查網路問題,使用gpcheckperf等工具也做過測試,4節點和10節點的基礎配置也是相同的。

gpcheckperf -f /usr/local/greenplum-db/conf/seg_hosts -r N -d /tmp

$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1      localhost localhost.localdomain localhost6 localhost6.localdomain6
#127.0.0.1    test-dbs-gp-128-230
xxxxx.128.238 test-dbs-gp-svr-128-238
xxxxx.128.239 test-dbs-gp-svr-128-239

其中127.0.0.1的這個配置在segment和Master,Standby混部的情況是存在問題的,修正後就沒問題了,這個關鍵的問題也是郭運凱同學發現的。


5.集群故障恢復的測試

集群的故障測試是本次架構設計中的重點內容,所以這一塊也是躍躍欲試。

整體上我們包含兩個場景,伺服器宕機修復後的集群恢復和伺服器不可用時的恢復方式。

第一種場景相對比較簡單,就是讓Segment節點重新加入集群,並且在集群層面將Primary和Mirror的角色互換,而第二種場景相對時間較長一些,主要原因是需要重構數據節點,這個代價基本就就是PG層面的數據恢復了,為了整個測試和恢復能夠完整模擬,我們採用了類似的恢復方式,比如宕機修復使用了伺服器重啟來替代,而伺服器不可用則使用了清理數據目錄,類似於一台新配置機器的模式。

1)伺服器宕機修復後集群恢復

select * from gp_segment_configuration where status!='u'

gprecoverseg  -o ./recov

gprecoverseg -r

select * from gp_segment_configuration where status='u'


2)伺服器不可用時集群恢復

重構數據節點的過程中,總體來看網路帶寬還是使用很充分的。

select * from gp_segment_configuration where status='u'

select * from gp_segment_configuration where status='u' and role!=preferred_role;

gprecoverseg -r

select * from gp_segment_configuration where status='u' and role!=preferred_role;


經過測試,重啟節點到數據修復,近50G數據耗時3分鍾左右

6.集群優化問題梳理

1)部署架構優化和迭代

對於優化問題,是本次測試中尤其關注,而且爭議較多的部分。 

首先在做完初步選型後,數倉體系的部署相對是比較順利的,採用的是第一套方案。

數據集市的集群部分因為節點相對較少,所以就選用了第二套方案

實際測試的過程,因為配置問題導致TPCH的結果沒有達到預期。

所以這個階段也產生了一些疑問和懷疑,一種就是折回第一種方案,但是節點數會少很多,要不就是第三種採用虛擬機的模式部署,最保底的方案則是單節點部署,當然這是最牽強的方案。

這個階段確實很難,而在上面提到的修復了配置之後,集群好像突然開悟了一般,性能表現不錯,很快就完成了100G和1T數據量的TPCH測試。

在後續的改造中,我們也嘗試了第三套方案,基於虛擬機的模式,通過測試發現,遠沒有我們預期的那麼理想,在同樣的數據節點下,Master和Standby採用物理機和虛擬機,性能差異非常大,這個是出乎我們預料的。比如同樣的SQL,方案3執行需要2秒,而方案2則需要80秒,這個差異我們對比了很多指標,最後我個人理解差異還是在網卡部分。

所以經過對比後,還是選擇了方案2的混合部署模式。

2)SQL性能優化的分析

此外整個過程的TPCH也為集群的性能表現提供了參考。比如方案2的混合部署模式下,有一條SQL需要18秒,但是相比同類型的集群,可能就只需要2秒鍾左右,這塊顯然是存在問題的。 

在排除了系統配置,硬體配置的差異之後,經典的解決辦法還是查看執行計劃。

性能較差的SQL執行計劃:

# explain analyze select count(*)from customer;

QUERY PLAN   

Aggregate  (cost=0.00..431.00 rows=1 width=8) (actual time=24792.916..24792.916 rows=1 loops=1)

   ->  Gather Motion 36:1  (slice1; segments: 36)  (cost=0.00..431.00 rows=1 width=1) (actual time=3.255..16489.394 rows=150000000 loops=1)

         ->  Seq Scan on customer  (cost=0.00..431.00 rows=1 width=1) (actual time=0.780..1267.878 rows=4172607 loops=1)

Planning time: 4.466 ms

   (slice0)    Executor memory: 680K bytes.

   (slice1)    Executor memory: 218K bytes avg x 36 workers, 218K bytes max (seg0).

Memory used:  2457600kB

Optimizer: Pivotal Optimizer (GPORCA)

Execution time: 24832.611 ms

(9 rows)


Time: 24892.500 ms


性能較好的SQL執行計劃:

# explain analyze select count(*)from customer;                            

QUERY PLAN

Aggregate  (cost=0.00..842.08 rows=1 width=8) (actual time=1519.311..1519.311 rows=1 loops=1)

   ->  Gather Motion 36:1  (slice1; segments: 36)  (cost=0.00..842.08 rows=1 width=8) (actual time=634.787..1519.214 rows=36 loops=1)

         ->  Aggregate  (cost=0.00..842.08 rows=1 width=8) (actual time=1473.296..1473.296 rows=1 loops=1)

               ->  Seq Scan on customer  (cost=0.00..834.33 rows=4166667 width=1) (actual time=0.758..438.319 rows=4172607 loops=1)

Planning time: 5.033 ms

   (slice0)    Executor memory: 176K bytes.

   (slice1)    Executor memory: 234K bytes avg x 36 workers, 234K bytes max (seg0).

Memory used:  2457600kB

Optimizer: Pivotal Optimizer (GPORCA)

Execution time: 1543.611 ms

(10 rows)


Time: 1549.324 ms

很明顯執行計劃是被誤導了,而誤導的因素則是基於統計信息,這個問題的修復很簡單:

analyze customer;

但是深究原因,則是在壓測時,先是使用了100G壓測,壓測完之後保留了原來的表結構,直接導入了1T的數據量,導致執行計劃這塊沒有更新。

3)集群配置優化

此外也做了一些集群配置層面的優化,比如對緩存做了調整。 

gpconfig -c statement_mem -m 2457600 -v 2457600

gpconfig -c gp_vmem_protect_limit -m 32000 -v 32000


7.集群優化數據

最後來感受下集群的性能:

1)10個物理節點,(6+6)*10+2

tpch_1t=# iming on

Timing is on.

tpch_1t=# select count(*)from customer;

   count   

-----------

150000000

(1 row)

Time: 1235.801 ms


tpch_1t=# select count(*)from lineitem;

   count    

------------

5999989709

(1 row)

Time: 10661.756 ms


2)6個物理節點,(6+6)*6

# select count(*)from customer;
   count   
-----------
 150000000
(1 row)
Time: 1346.833 ms


# select count(*)from lineitem;
   count    
------------
 5999989709
(1 row)
Time: 18145.092 ms


3)4個物理節點,(6+6)*4

# select count(*)from customer;
   count   
-----------
 150000000
(1 row)
Time: 1531.621 ms

# select count(*)from lineitem;
   count    
------------
 5999989709
(1 row)
Time: 25072.501 ms

4)TPCH在不通架構模式下的性能比對 ,有19個查詢模型,有個別SQL邏輯過於復雜暫時忽略,也是郭運凱同學整理的列表。

在1T基準下的基準測試表現:

❺ 修改數據表名和列名的實驗結論

摘要 ①使用 SQL Server Management Studio 修改資料庫名稱。 在「對象資源管理器」窗口中右擊需要改名的資料庫,從彈出的快捷菜單中選 擇「重命名」命令。當資料庫名稱處於可編輯狀態時,輸入新名即可。

❻ 在Sql Server trigger中使用if條件句總報錯

看樣子是mssql,我覺得你的as下第一行的if語句不太對啊,orders. orderkey = inserted. orderkey是什麼意思,orders. orderkey應該是一個表的欄位值,不得用select語句來判斷嗎?比如
If exists( select * from orders where orderkey = inserted. orderkey)
.....