SQL Server效能調優——報表資料庫與業務資料庫分離

標籤：

前段時間把公司的主要資料庫切了，分成業務庫和報表庫，業務庫向報表庫進行即時的Replication。這個項目的上線提升了系統的效能和可維護性，現在把設計時的考量和所做的工作重新回顧一下，作為備忘。

項目起源

在日常的開發過程中，功能總是先於效能被考慮。只有當使用者抱怨系統效能時，我們才開始頭痛醫頭，腳痛醫腳地來解決這些效能問題。

公司的CRM和ERP系統叫作Olite，完全是我們組開發的。從無到有，功能不斷擴充，原先只有CRM模組，後來加入了ERP模組，Accounting功能和Report功能。近來出現的情況是當某些使用者跑一個大Report時，進行中業務操作的使用者感覺系統響應非常慢。通過對系統的效能監控發現，在這些時刻，資料庫中產生了大量的鎖，同時伺服器上出現了CPU和記憶體資源消耗的尖峰。

系統結構

效能問題源於系統的整體結構和發展過程。Olite系統的Application是基於.NET平台的Web Form程式，資料庫為SQL Server 2005。其主體結構如所示：

其Application端包括兩個網站：OliteBase和OliteReport，但串連的都是同一個資料庫。

Olite的Application端其實很薄，而把大量的商務邏輯封裝在預存程序中，放在資料庫端來運行。

這種結構在起初效能很好，而且提供給使用者的Report是即時的業務資料。但隨著提供的業務模組，特別是Report的增多（Report對應的預存程序串連的表多，計算量大，輸出的結果集大），資料庫就成為了瓶頸。

首先，我們做了預存程序的最佳化，通過建立Trace捕獲效能差的預存程序，並對其進行最佳化。我們這麼做了一段時間，但獲得的收效並不大。我們在最佳化以往預存程序的同時，隨著系統新功能的上線，又有新的預存程序進入需要最佳化的列表中。

其次，修改資料庫設計，其中包括修改表結構和最佳化索引。在系統局部重構表結構與關係對於效能的提升還是比較明顯的，但這樣的修改會造成Application端的大量修改，工作量大，風險大，所以不能大規模實施。對於索引最佳化又存在矛盾，業務模組（OliteBase）要求資料庫中的索引不要太多，以支援高效的插入、修改和刪除，而報表模組（OliteReport）則希望在資料庫中有更多的索引，以支援高效讀。

最後，我們還試圖提供晚一天的Report服務，來分流主要資料庫的壓力。每天通過把前一天的備份資料庫恢複在另一台伺服器上，並在此伺服器上提供OliteReport2網站，給使用者提供Report服務。但使用者並不喜歡使用OliteReport2，原因分析下來有3個方面：其一，有時使用者確實需要即時的Report。其二，OliteReport能儲存使用者的Report條件，而OliteReport2由於每天都會被重新整理，無法保留這些條件。其三，使用者更習慣開啟原來的Report連結。

項目需求

上述的各種最佳化方案都沒有根本性的解決系統的效能問題。在這種的背景下我們有了把報表資料庫與業務資料庫分離的想法。

此項目的需求：

1. 提高使用者對整個系統效能的感受，Report模組不要影響到業務模組的運行。

2. 使用者可以和原先一樣使用Report模組，即不增加新的Report網站。

3. 使用者可以和原先一樣儲存填寫的Report條件，以供重複使用。

4. 儘可能提供最小延時的Report。

需求1是這個項目的主要目標，需求2、3、4是儘可能保證項目所帶來的改變對使用者是透明的。

方案選擇

對於原來的系統結構，其Application端已經是兩個獨立的網站OliteBase和OliteReport。所以只要把OliteBase和OliteReport的資料庫進行分離，在分離後的兩資料庫間進行資料的同步就行了。這裡的關鍵在於如何進行資料庫間的同步。

微軟提供了很多種資料同步的選擇：1.叢集；2.Log Shipping；3.Replication；4.Mirror；5.Integration Service。

微軟提供的這些方案中大部分都是用於做資料庫的高可用性的，而我們的項目是以高效能為目標的。為了滿足我們自己的需求，應選擇那種方案，並做哪些修改呢？

1.叢集

這是第一個被我們否決的方案。配置SQL Server資料庫叢集，對硬體有較多限制，而且配置相對其他方案複雜。我們的項目總共的伺服器資源就兩台，除原先主要資料庫伺服器外，另一台是虛擬機器。

2.Log Shipping

Log Shipping把主要資料庫的記錄傳送到從資料庫，並在從資料庫上進行回放來保證主、從資料庫間資料的一致，從資料庫為唯讀。Log Shipping而且還有配置簡單的特點，開始時是我們的一個候選方案，但在進一步的實驗過程中發現了兩個問題。第一、Log Shipping可設定的時間間隔最小單位為分鐘。第二、當從資料庫進行日誌回放時，串連此資料庫的串連需要被斷開。其中第二個問題是難以容忍的，這個方案也被淘汰了。

3.Replication

Replication的原理和Log Shipping有些相似，但其提供了更多的靈活性。Replication可以只多主要資料庫的一些表、函數或預存程序進行，甚至可以對某些合格記錄進行。除此之外，其複製出來的資料庫可寫，而且複製的最小時間間隔可配置為concurrent（測試下來的時間延遲為秒層級），而且其配置也較為簡單。經過一些實驗，我們最後選擇了它。後面會對其原理和配置進一步討論。

4.Mirror

Mirror是SQL Server 2005提供的強大的高可用性方案。其鏡像資料庫不能直接讀取，這和我們的需求情境不符合，所以被否了。

5.Integration Service

Integration Service具有最大的靈活性，其可以為資料倉儲進行資料幫浦，轉換和裝載。但使用Integration Service需要有大量的開發與測試工作，所以我們也沒選用。

Replication方案細分

Replication方案又可以分為Snapshot Replication, Transactional Replication, Peer-2-Peer Replication, Merge Replication。

Snapshot Replication：一般用於對於資料庫的一次性的完全複製。

Transactional Replication：用於主要資料庫向從資料庫的單向複製。

Peer-2-Peer Replication：能進行二個或多個資料庫之間的互相複製，即從資料庫也能向主要資料庫複製，這個功能很強大，但可能會引起衝突，需要特別關注保證各庫的資料完整性。

Merge Replication：可以把多個資料庫中的資料進行合并後，複製到目標資料庫。

對於我們的需求，我們選用了最單純的Transactional Replication。

Transactional Replication原理 

在Transactional Replication中有3個角色：Publisher（發行者）, Distributor（分發者）, Subscriber（訂閱者）。其邏輯圖如下：

在進行增量的Transactional Replication之前，Subscriber需要進行初始化，使其包含和Publisher一樣的表結構和初始資料。

Transactional Replication啟動之後，Distributor上的Log Reader Agent會將讀取Publisher的Log資訊，並分揀出被標識為replication的INSERT, UPDATE, DELETE語句。此後複製這些Transaction到Distributor，並寫入distribution資料庫。最後Distribution Agent把Distributor上的Transaction運送到Subscriber進行重放。

注意：在圖中Distribution Agent運行在Distributor上，這是在push（推）模式下的情況。可以配置為pull（拉）模式，Distribution Agent將運行在Subscriber上。

更多關於Transactional Replication的原理可參考：

http://msdn.microsoft.com/en-us/library/ms151706(SQL.90).aspx

項目中的配置與考量

在前文的系統結構小節，給出了原先的系統結構。我們希望通過這次的項目得到如下所示的系統結構：

OliteReport能串連到一個由主要資料庫複製出的單獨資料庫上，這樣這兩個庫之間的鎖就被隔離了。同時主要資料庫與從資料庫安排在兩台伺服器上（項目中我們把複製出的資料庫放在了一台虛擬機器上），那麼CPU資源與記憶體資源的消耗也被隔離了。需要注意的是圖中OliteReport除了主要的讀操作外，還有少量的寫操作（這是因為使用者可以儲存Report條件）。我們把這些寫指回主要資料庫，從資料庫在下一時刻的複製中得到這些資料。

在Transactional Replication中有三個邏輯角色，而項目中只有兩台伺服器。我們如何來安排這三個邏輯角色呢？

• 候選的方案有兩種：1.主要資料庫上配置Publisher和Distributor，從資料庫上配置Subscriber；2.主要資料庫上只配置Publisher，從資料庫上配置Distributor和Subscriber。矛盾的焦點是Distributor放哪裡？需要指出的是Distributor對於Replication非常重要，這個角色承擔著從主要資料庫抓取Transaction的工作，在Push模式下，它還需要負責把Transaction推送到個Subscriber。這些工作都會消耗所在伺服器的CPU和記憶體資源。我們的項目希望儘可能保證業務模組的效能，所以我們選用了方案2，把Distributor配置在從資料庫上。

我們是選用Push模式還是Pull模式呢？

• Push和Pull其實是針對Distributor傳送Transaction到Subscriber的方式而言的（這點我是很後面才認識到的，開始一直認為Push或Pull會影響Distributor抓取Publisher上的資訊，其實不然）。對於Distributor和Subscriber在一台伺服器上，這兩種模式的效果基本一樣。我們選擇了Pull模式，即Distribution Agent運行在Subscriber端從Distributor拉Transaction資料。這是為了將來擴充考慮，如果以後再加一台伺服器來作為Subscriber時，Distributor不會增加太多的效能壓力。

另一個需要考慮的問題是複製些什嗎？

• Transactional Replication可以選擇複製哪些表、預存程序或函數等內容。最簡單的是把整個資料庫中的所以元素都進行複製，但這會造成Replication服務所要監視的對象很多，同時網路上傳輸的資訊量也很大。項目中我們最後決定只複製所有的表，這樣做是出去效能的考慮。這樣做會對將來的release產生影響，需要注意，下文會進行討論。

還有一個需要考慮的是如何進行從資料庫的初始化？

• 在Transactional Replication開始之前，首先要對從資料庫進行初始化，使其獲得與主要資料庫一致的表結構和初始資料。在配置Transactional Replication中會有一個選項來進行初始化（由Snapshot Agent完成）。但在我們的實驗中初始化耗費了幾個小時，所以我們沒有使用Transactional Replication預設的初始化方式，而是通過Database Backup還原來完成初始化，要這樣做就需要改變更配置置的一些選項，下文還會涉及。

Transactional Replication有些什麼前提條件？

• 資料庫的Compatibility level（相容性等級）需要達到SQL Server 2005(90)（我們使用的是SQL Server 2005，當相容性層級為80時，配置過程中會出現異常）。
• 資料庫的Recovery model（復原模式）需要是Full（完整）。
• 所有需要Replicate的表必須具有主鍵。（這應該是理所當然的，但在這次配置中竟然發現一些非常“可恥”的東西）
• 預存程序或其他指令碼中，不能對進行Replicate的表進行truncate，需把相應預存程序中的語句改為delete。這是因為Replication是基於對Log的抓取與解析，但truncate不產生Log。
• 如果Replicate的元素還包括預存程序或函數，還會有其他一些前提條件，我們不在這裡展開，可以查看msdn。

如何來配置Transactional Replication？

• 微軟提供了非常易用的圖形化介面可以進行Replication的配置。但圖形化配置的靈活性是有限的，有些配置選項在圖形化介面下無法完成。我的建議是先用圖形化配置Replication，並產生相應的script。此後根據需求修改script，並用script進行配置。在我們的項目中也是這麼做的。
• 預設的情況下，Distributor伺服器的D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata會存放replicate資料。由於我們要支援Pull模式，需要共用這一檔案夾，並給此檔案夾設定一個具有Full Control許可權的域賬戶。並把此域賬戶設定為Subscriber伺服器上SQL Server Agent服務的運行賬戶，此服務同時需要被設為Automatic啟動方式。在sp_adddistpublisher的@working_directory參數設為此共用目錄的網路路徑。
• 配置Publisher時，sp_addpublication的參數@sync_method = N‘concurrent‘, @repl_freq = N‘continuous‘保證了Replicate能儘可能即時；@allow_initialize_from_backup = N’true’表示通過備份還原來進行從資料庫的初始化。
• 配置Subscriber時，sp_addsubscription的參數@sync_type = N‘replication support only‘表示從資料庫的初始化完全由外部來完成；@subscription_type = N‘pull‘表示使用拉模式。

後期維護

如何監視Replication的效能與異常？

• 微軟提供了Replication Monitor。這個工具還是比較好用的，可以查看到Publication和Subscription的狀態，還能查看到當前有多少Transaction等待傳送。
• Transactional Replication設定好後，Distributor上將自動產生相關的多個Alerts，如：Replication Warning: Subscription expiration (Threshold: expiration)，Replication Warning: Transactional replication latency (Threshold: latency)等。可以將這些Alerts與Database Mail進行綁定。當出現警告時，自動發出郵件。（此功能雖然在項目中配置了，但從未正常發出警告郵件，一直不知道為什麼，如果有人知道的話可以聯絡我）。

如何進行以後的Release？

• 原先資料庫的Release一般會分為三部分：1.表結構的變化（包括加/刪表，加/刪列）；2.配置資料的裝載（如添加新功能的配置資料）；3.刷函數與預存程序指令碼。
• 對於本項目中的Replication資料庫，在Release過程中需注意以下幾點：1.若新加的表需要進行Replication，除了在主要資料庫建立表之外，還需配置此表進行Replicate，並進行初始化。2.若要刪除某處於Replication的表，需先取消此表的Replication，再在主、從庫中drop此表。3.若需要加/刪Replication表的列（此列不能為主鍵列）時，可以直接在主要資料庫上執行指令碼，變化會自動Replicate到從資料庫。4.配置資料的裝載也只需要在主要資料庫完成。5.函數與預存程序需要在主、從庫上都進行重新整理。

總結與設想

此項目已經上線，基本達到了需求所提出的目標，但這隻是開始，最佳化後的結構給將來系統的擴充提供了一個基礎。

• 通過實驗發現，在主/從資料庫上可以建立不同的索引而不互相干擾（這和Replication的配置相關）。這就可以根據主、從資料庫不同的使用模式，建立更最佳化的索引。我曾在國外某Blog上看到，利用SQL Server 2005的動態視圖，自動根據資料庫的使用模式來建立索引，就像自適應索引機一樣。這也是我將在OliteReport資料庫上做的事。
• 將來如果有了多個Subscriber資料庫，還能做OliteReport的資料庫Load Balance。當有Report請求時，系統首先查看各個Subscriber的CPU和Memory的Load，選擇Load較輕的Subscriber接受Report請求。
• 我們還能利用Replicate出的資料庫進行BI（商業智慧）分析與挖掘，而不會影響到主要資料庫的運行。

SQL Server效能調優——報表資料庫與業務資料庫分離