目錄
數據倉庫對數據庫的新需求
摘要
詳細報告
平台配置概覽
增至10億條記錄
性能測試詳細情況
多表查詢集
數據倉庫對數據庫的新需求
利用完整的信息及時作出正確的決策，這就是數據倉庫所要完成的任務。隨著數據倉庫的不斷發展，市場對數據倉庫的核心組件——數據庫引擎提出了新的需求。本次測試正是基於對這些需求的深刻認識。
1、海量數據
隨著信息系統的不斷發展，來自客戶、產品、業務運營等的數據量不斷膨脹，數據庫容量逐漸成為事實上影響數據倉庫性能的瓶頸，當傳統的操作型數據庫對海量數據的支持能力表現不佳，亟需一個經過驗證的能夠支持海量數據達到100TB以上的數據倉庫參考模型。
2、實時數據加載
要及時准確的作出決策，就需要更接近實時的數據更新、即席查詢、甚至人工智能。BI已經逐漸成為事務處理的完整部分。從數據庫的角度來講，不僅要求數據能夠實時更新，而且要保證數據的實時加載對查詢響應速度的影響最小。
3、復雜查詢
商業智能從根本上來說已經不再是簡單的查詢，而越來越成為客戶行為模式與客戶特征及客戶行為之間復雜的相互關系。“客戶”數據庫常常包含眾多的客戶屬性，“客戶”數據庫是對家庭關系、復雜的企業組織、財務關系等的模型化。 數據的復雜性與問題的深度表明：商業智能的許多查詢相當復雜。這要求數據庫不僅能夠處理單表的復雜查詢，更要處理海量數據量下的跨表查詢。
4、並行查詢
基於Web，公司正逐步向客戶、供應商、代理商以及其他商業合作伙伴開放對數據的訪問，而不象從前只有總部的25個分析員或者公司高層在使用數據倉庫，而且今天的信息交互模式遠比從前復雜。這要求數據庫必須能夠處理大量的用戶群，以及由此產生的大量並發查詢。
5、快速響應
Internet的廣泛使用對數據倉庫帶來重要影響：大量交互式的在線數據訪問。用戶希望在秒或分鐘內獲得結果，而不是幾小時或整晚。這要求數據庫引擎能夠提供交互式的快速響應，即使是在海量數據、復雜查詢、多用戶與並行查詢的情況下。
6、可伸縮性
今天的數據倉庫應用於一個非常動態的環境。新的應用、主題、用戶、數據迅速增加，可伸縮性在數據倉庫中比以往更加重要。這不僅要求數據庫能夠維護大量的數據及索引，更要求數據倉庫架構能夠隨著新增加的應用、主題、用戶、數據等靈活進行擴展。
關於Sybase-Sun數據倉庫參考架構的基准測試對上述各方面都進行了有效的檢測，測試結果表明，Sybase IQ是唯一經過驗證的完全滿足上述需求的面向數據倉庫等分析型應用的關系型數據庫引擎。這正是IQ誕生近10年來，不斷創新的結果。今天的IQ數據庫，擁有眾多的專利技術，其獨特的列存儲、數據壓縮、專利的索引技術、並行結構等已經為眾多客戶提供了高性能、高擴展性的數據倉庫系統。這些客戶包括：
中國聯通
中國電信
泰康保險
大鵬證券
興業證券
招商銀行
浙江地稅
全國鐵路局（客票系統）
山東煙草
上海公安
興澄鋼鐵
上海社保
美林證券
摩根斯坦利
Internal Revenue Service（IRS）
韓國朝興銀行
土耳其Yapi Kredi銀行
比利時Fortis銀行
挪威Lindoff資產管理機構
愛爾蘭聯合銀行
加拿大蒙特利爾銀行
MasterLink證券公司
韓國人壽保險公司
GE Capital保險服務公司
AT&T
澳大利亞Telstra移動公司
西班牙Telefonica電信公司
西班牙Airtel電信公司
英國RSL電信公司
葡萄牙Telecel電信公司
洛杉機時報
尼爾森媒體機構
North Jersey媒體集團
德國EMI唱片公司
AOL
加拿大統計局
巴西聖保羅財政局
美國尤他州財政局
美國航空
法國鐵路運輸局
美國運輸管理局
……
一萬億行
Sybase-Sun數據倉庫參考架構
摘要
2004年3月，應Sybase及Sun公司的要求，我對Sybase-Sun數據倉庫參考架構的性能進行了檢驗，測試平台建於美國加州Palo Alto，基於該平台，我對基准測試進行了設計及檢測。
這一企業級數據倉庫參考架構，基於兩台Sun Fire服務器以及 Sybase IQ，經過驗證取得了眾多意義重大的成就：
◆事實表的總行數達到1萬億行，這是迄今為止經過獨立驗證的最大的數據庫；足以記錄20多年來全美可以出售的每一筆鋁礦。
◆深度數據存儲及壓縮比例，155TB的原始數據在存儲到該數據倉庫的星型模型中，實際只占用55TB的存儲空間
◆在不斷增加查詢壓力至5倍的情況下，同時維持查詢響應時間及數據加載速度性能的能力（如下圖所示）。
圖1
詳細報告
數據倉庫測試平台包括兩台獨立的Sun服務器節點與一個共享磁盤架構，通過光纖與Sun磁盤陣列連接，數據庫系統為Sybase IQ 12.5.0。
平台配置概覽
圖2
Sybase IQ Server配置
◆一個IQ 寫（Writer）服務器，安裝於A節點服務器。寫服務器綁定了24個CPU中的16個CPU，分配了4GB的主緩沖（Main IQ Cach）和4GB的臨時緩沖（Temp IQ Cach）。
◆一個IQ查詢服務器，安裝於A節點服務器。該查詢服務器綁定了剩下的8個CPU中的7個（剩余一個留給操作系統使用），分配了28GB的主緩沖和48GB的臨時緩沖。
◆一個IQ查詢服務器，安裝於B節點服務器。該查詢服務器綁定了24個CPU中的7個CPU（其余的CPU基本保持空閒），分配了20GB的主緩沖和20GB的臨時緩沖。
增至10億條記錄
建立數據庫 – Sybase IQ數據庫以一個基本星型結構建立，以大量事實表為中心——每個事實表裝載一個月的事實數據（每行25列）。建立四個維表用以對事實數據進行鑽取。裝載於維表的數據可以提供更大范圍的表基數（從5千行到5億行）。下圖描述了維表的數據裝載情況：
維表
行數
列數
行的大小
Customer(客戶)
500,000,000
11
246 Bytes
Product（產品）
1,000,000
8
144 Bytes
Channel(渠道)
5,00
6
106 Bytes
Location(地址)
30,000
7
108 Bytes
初始裝載 – 首先，建立一組事實表共60張。每張表裝載1999年到2003年間的一個月的事實數據。數據由1億條種子記錄生成，通過對種子記錄的一個或多個相關的域值進行多次修改以保證行的唯一性，然後對記錄進行復制。使用Sybase IQ的索引技術，事實表完全被索引化。另外，在主鍵上建立一個high-group索引，在四個對應於維表的外鍵上也建立high-group索引。
第一次驗證 – 接著定義了幾張包含12張事實表的聯合（UNION ALL）視圖，為每年的數據各定義一張。同時，建立了一張包含所有5年的事實數據的視圖。這樣，確定了數據庫的內容，接下來，我們進行了一系列的性能測試（詳見後述）。
繼續增加 – 在第一次驗證之後，更多的事實表被建立，同時與第一次驗證類似，裝載了各年份的事實數據。Sun存儲系統的配置相應的增加，為新建立的事實表提供空間。
跨越1萬億行的界限 – 總共對44張事實表進行了數據加載。然後定義了一個聯合所有104張事實表的全局視圖，涵蓋了全部8年6個月的事實數據。下表描述了加載到事實表一萬億行中的數據的詳細情況：
事實表 行數 列數 原始記錄大小 原始數據大小 ALL_FACTS(所有事實表) 1萬億 25 170 Bytes 154.6 Terabytes
最終驗證 – 一萬億行數據加載的驗證，以及對基於全局視圖的跨表查詢的執行，用以驗證數據庫是否依然運轉正常，響應時間是否顯著下降（詳見後述）。
性能測試詳細情況
執行以下的測試手段以驗證性能：
◆基於6千億條記錄的多表連接
◆在已有6千億條記錄的情況下追加事實數據
◆基於6千億條記錄的並行加載與查詢
◆基於1萬億條記錄的多表連接
多表連接 – 定義了一組跨表連接（見後述），並基於6千億條記錄執行查詢。這些查詢被一條一條的執行以獲取基准響應時間。被測試的響應時間在5秒到500秒之間。
追加事實數據 – 在已有6千億條記錄的情況下，建立新的事實表。向新建的事實表追加新的數據，測量該加載速度以建立一個基准。接著刪除該表，建立兩張新表，同時向兩張新表中追加數據，同樣，測量並行加載的速度也可以建立一個基准。在並行加載的情況下，合計的加載速度為每分鐘3千萬條。
並行加載與查詢 – 在執行兩表並行加載的同時，不斷增加執行的查詢數目，直到有8條查詢並行運行於同一個節點服務器。然後在另一台節點服務器上同時執行8條查詢，共享同一個數據庫。
在兩台節點服務器上並行執行16條查詢對兩表並行加載的影響非常小，如下圖所示，25分鐘內有6700萬條記錄被加載。
圖3
包含10個連接查詢的16條查詢彼此之間對響應時間的影響也非常小（增加了不到10秒），相比於增加了5倍的查詢流量。如下圖所示：
圖4
基於1萬億行的連接查詢 – 在事實表行數增加到1萬億行之後，從查詢集中挑選了一些查詢進行執行。對查詢語句“Where”從句中的時間參數進行調整以覆蓋所有事實表中的時間范圍。
通過對由Sybase IQ查詢優化器選擇的執行計劃進行研究，證實了在這個巨大的一萬億行的“UNION ALL”的視圖中，所有事實表都可以被這些查詢訪問到。盡管數據庫的大小增加了65%，達到創記錄的1萬億行，查詢響應速度與先前測試的並行查詢相比，並沒有明顯的影響。
多表查詢集
用於大部分性能測試的查詢集由下列10個查詢組成，“WHERE”從句中的參數值根據每條查詢而改變。
Query 1
SELECT LOCATION.STORE_NAME,
AVG(ALL_FACTS.TOTAL),
AVG(ALL_FACTS.QUANTITY)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID
AND CHANNEL.CHANNEL_ID in ( 90, 61, 20)
AND FACT_DATE = CAST('2002-11-01' AS DATE)
AND LOCATION.STORE_NUMBER = 18242
GROUP BY
LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
Query 2
SELECT LOCATION.STORE_NAME,
AVG(ALL_FACTS.TOTAL),
MIN(ALL_FACTS.TOTAL),
MAX(ALL_FACTS.TOTAL)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID
AND CHANNEL.CHANNEL_ID = 94
AND FACT_DATE = CAST('2003-01-01' AS DATE)
AND LOCATION.STORE_NUMBER IN ( 5401, 6319, 18950)
GROUP BY LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
Query 3
SELECT STORE_NAME,
COUNT(*)
FROM ALL_FACTS,
CUSTOMER,
LOCATION
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND STORE_NUMBER = 29743
AND CUSTOMER_SCORE < 5
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2003-01-25' AND '2003-02-03'
GROUP BY STORE_NAME
Query 4
SELECT LOCATION.STORE_NAME,
MAX(ALL_FACTS.TOTAL),
MAX(ALL_FACTS.QUANTITY)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID AND
ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID AND
CHANNEL.CHANNEL_ID = 59 AND
ALL_FACTS.FACT_DATE = CAST('2003-06-01' AS DATE) AND
LOCATION.STORE_NUMBER = 25341
GROUP BY _LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
Query 5
SELECT CUSTOMER_FNAME,
STORE_NAME,
ALL_FACTS.CUSTOMER_ID,
PAYMENT_METHOD,
FACT_DATE,
ORDER_DATE,
SHIP_DATE,
DELIVERY_DATE,
SHIPPING,
DISCOUNT,
TAX,
TOTAL,
QUANTITY
FROM ALL_FACTS,
CUSTOMER,
LOCATION
WHERE CUSTOMER.CUSTOMER_ID = 345123
AND ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND STORE_NUMBER = 29343
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2002-07-01' AND '2002-08-07'
Query 6
SELECT STORE_NAME,
COUNT(*)
FROM ALL_FACTS,
CUSTOMER,
LOCATION,
PRODUCT
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND STORE_NUMBER = 12744
AND CUSTOMER_SCORE < 5
AND PRODUCT_TYPE = '0000000006'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-05-25' AND '2001-06-03'
GROUP BY STORE_NAME
Query 7
SELECT CUSTOMER_FNAME,
ALL_FACTS.CUSTOMER_ID,
PAYMENT_METHOD,
FACT_DATE,
ORDER_DATE,
SHIP_DATE,
DELIVERY_DATE,
SHIPPING,
DISCOUNT,
TAX,
TOTAL,
QUANTITY
FROM ALL_FACTS,
CUSTOMER
WHERE CUSTOMER.CUSTOMER_ID = 2838456
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2002-06-01' AND '2002-07-07'
Query 8
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 85 AND 90
AND PRODUCT_TYPE = '0000000016'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-09-03'
GROUP BY CUSTOMER_SCORE
Query 9
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 85 AND 90
AND PRODUCT_TYPE = '0000000015'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-08-31'
GROUP BY CUSTOMER_SCORE
HAVING SUM(TOTAL) > (
SELECT SUM(TOTAL)
FROM FACT_2000,
PRODUCT
WHERE FACT_2000.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND PRODUCT_TYPE = '0000000015'
AND FACT_DATE = '2000-08-25'
)
Query 10
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 75 AND 80
AND PRODUCT_TYPE = '0000000041'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-08-31'
AND ALL_FACTS.CUSTOMER_ID IN (
SELECT CUSTOMER_ID
FROM FACT_2002
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND CUSTOMER_SCORE = 80
AND CUSTOMER_ID BETWEEN 13000 AND 14000
AND PRODUCT.PRODUCT_ID = 4323
AND FACT_DATE = '2001-08-20'
)
GROUP BY CUSTOMER_SCORE
