想要提升資料庫的處理效能,可以先從系統本身提供的機制或工具開始著手。基本上,大多數廠商都在產品本身,或多或少提供了改善的方法,或許具有協助你找到效能瓶頸的機制。

對於資料庫功能與使用方式的了解,並不只是資料庫管理人員的責任,開發人員也不能置身事外,因此在開始著手系統開發之前,對現有環境配置的相關功能,若有一定程度的認識,在整體規畫時就可以納入這樣的考量,以便達到預期的效果,未來會面臨到的問題會比較少。

等到系統建置起來,才知道要去調整,這樣的影響有時候是非常大的。假設不用改寫程式,純粹從架構來調整,或人員本身的程式開發技巧夠好,就能達到目的,就不會有太大衝擊,但要應用資料庫的一些機制需和程式有所連動,影響會相當可觀,若程式開發人員的素質參差不齊,就會面臨到難以處理的狀況。

「要解決問題,首先要有Know-how」,逸凡科技資深技術顧問鄭樹認為,基本上,開發人員往往不知道SQL語法可以用更好的方式寫,來避免或解決資料庫效能的問題,假如一開始知道這作法的影響,就不至於犯錯,只要有人告訴他們該怎麼進行,而自己親身做過,也比較差異後,也就不會沿用先前錯誤的作法。

如何持續做好資料庫效能管理
效能管理是一種持續性的工作,現在狀況如此,半年後不見得還是一樣。若要以系統化的方式看待資料庫效能管理,IBM全球資訊科技服務事業部經理余佳蓉提出以下4個步驟:

一、找出潛在問題(Identify):對於影響服務水準(SLA)的主要資料庫效能衡量指標(KPI),要清楚建立起來,以便挖掘潛在問題。透過這些技術KPI,可以顯示系統的健康狀態,並且讓你看出問題出在哪裡,對DBA而言,他們可以指出執行時間過久的SQL查詢、緩衝共用區與I/O狀態、記憶體耗用量與資料鎖定的衝突點;而對開發人員來說,他們所關切的是在耗用很多時間的資料存取作業,分析應用程式花在計算作業的時間,以及等待資料庫回應的時間。

這些指標建立後,必須要能有效追蹤。你當然可以利用多人小組來執行手動檢查,以便找到脫離常軌的部分,或是當問題發生時,透過系統發送通知。

二、分析問題(Diagnose):當效能出現問題的通知發出後,我們必須去詳細檢視到底出了什麼狀況系統,但企業資訊系統越來越複雜,須從各種面向進行整體問題分析,例如集合網路、作業系統、中介軟體、應用程式相關資訊。同時,你需要參考、對照過去所記錄的效能資料,找出根本原因。

針對可能的資料庫效能瓶頸,需更進一步地分析SQL執行效能,及SQL存取最佳路徑運算、資料庫系統相關效能參數,以及應用程式撰寫方式分析等等。
若能配合資料庫效能管理工具,提供線上即時的SQL語法分析,並透過工具以情境模擬方式確認問題發生的原因所在,快速掌握效能問題關鍵點,並預測出可能的效能瓶頸點。

此外,她也認為應讓參與問題人員能快速進入問題分析的階段,討論問題核心,不是花大部分時間收集資料,而這也是越來越多企業,希望藉由導入資料庫效能管理工具,持續進行資料庫效能最佳化工作的主要原因。

三、解決問題(Solve):若能於問題發生之前,就針對潛在問題進行有效分析,即能協同資訊部門內部各相關單位人員,擬定問題解決方案,在造成服務中斷事件發生前,有效阻止問題發生,或是於問題發生後快速回應,恢復正常運作。

為了尋找效能問題的根源,我們會廣泛觀察可能的跡象,在解決問題時同樣會需要這樣的資料。很多時候,在IT基礎架構改變幾個小地方,在單一系統上進行全面檢查,成效會比較好。例如針對緩慢的資料庫查詢作業,調校一個很久沒更新的SQL是不錯的,但查詢往往是來自應用程式的執行,因此要注意其中所使用的功能是否必要,或是系統是否有足夠的資源,去存取這些必須被處理的大量資料。

因此,應該從長計議,會比一時權宜的考量來得妥當,用你手上的資料來找出需要修正的部份──造成的影響比手邊問題更大的,並且建立一套隨著每次處理而能日益健全的系統解決方式。

四、預防問題再度發生(Prevent):持續進行資料庫效能監控及優化(Optimization),才能使企業所投入的 IT資產發揮最大的效益,而非不斷編列硬體擴充預算,增加維運人力。

在辨識、解決問題之外,主動監控與收集效能資料,還可以讓你在第一時間就阻止效能不佳狀況出現。若能收集完整、精確的歷史資料,並且做到易於搜尋與分析,你可以了解記憶體使用效能瓶頸的發生位置與時間,以及如何相互關聯至特定的業務事件;為了未來參考之用,你也能將解決問題時所變更的部份寫成文件。

此外,也要透過具體的方式,例如統計圖表,和高層主管溝通已經發生的效能不佳事件與所採取的行動。最後,將辨識、診斷與解決等步驟所收集的記錄,建立出作業範本,以避免類似的問題或更快速解決。這些報告也可以用在資料庫效能指標的稽核。

持續監控與調校資料庫系統的方式
如上所述,管理資料庫效能有很多層面,首先我們先從及早發現資料庫系統運作上的問題,開始著手,這可以透過很多種方式進行,例如資料庫系統本身的機制,或其他外部的管理軟體。

運用資料庫系統本身的機制
Oracle Database 10g開始納入系統自動調校功能(Automatic Tuning),陸續涵蓋儲存、備份、記憶體、應用程式與SQL等範圍,強調自我管理(Self-Managing),到了11g也更為成熟。在這套產品的企業版中,若要進階的系統監控、診斷與規畫等功能,可以選購Oracle Diagnostic Pack,而要更深入的效能調校,則可以選購Oracle Tuning Pack。

而微軟SQL Server 2008 R2有SQL Trace、SQL Profiler,而在管理主控臺介面Management Studio上,提供多種伺服器與資料庫效能相關報表,也整合了活動監視器。到了企業版,你還可以使用資源管理員Resource Governor,利用政策來管理SQL Server工作負載和系統資源耗用量。

對IBM DB2(在Linux、Unix和Windows作業環境下)而言,若要搭配同公司的產品或功能,獲得更完整的資料庫效能管理機制,目前可用InfoSphere Opim系列產品來對應,例如當中的Performance Manager及Query Workload Turner。

而MySQL在效能管理上,若搭配的是企業版授權,可用Enterprise Monitor的代理伺服器架構,來監控資料庫系統與作業系統的變數,了解資源用量,也能掌握資料庫連線狀態,以及分析資料查詢的反應時間。

使用第三方廠商提供的資料庫系統管理與調校工具
相較於資料庫系統廠商自己提供的軟體附加功能或工具軟體,市面上有有一些廠商,也提供管理或調校資料庫系統的工具軟體。例如Embardero的DBArtisan、DB Optimizer,Quest的Toad、SQL Optimizer。

這些產品中,有的可以一套能同時支援多種資料庫系統,有的則是根據不同資料庫系統提供專用的版本,提供的資料庫管理功能,也有基本與進階之分。
用應用程式效能管理的產品來解決問題

若要用專屬套裝軟體來找到問題,我們還可以透過所謂的應用程式效能管理(Application Performance Management,APM)的方式來進行。

這種方法雖然能協助你更快找到出問題的環節,不過這種產品涵蓋的應用範圍實在太廣,不是一般企業所能負荷得起的解決方案。

而且,若問題出在資料庫,APM能做到的部份主要是測量資料庫伺服器本身的反應時間,以及它對個別交易處理的反應時間,所以,若想要了解到底是資料庫的那些部分出問題,還需要透過其他方式。目前可提供相關功能的解決方案有那些?在Quest的Foglight中有一系列特別針對資料庫監控與管理應用的產品,而且依不同資料庫系統來區分。

除此之外,像一些系統管理軟體產品中,也涵蓋到資料庫系統的效能監控管理,例如Oracle Enterprise Manager,可以用來操作與設定Oracle Database的自我診斷機制ADDM(Automatic Database Diagnostic Monitoring),對於其他資料庫系統的監控,如IBM DB2和微軟SQL Server,Enterprise Manager也可以透過System Monitoring Plug-in的外掛程式來做到。

微軟System Center的Operations Manager也可以用來監控資料庫系統,除了針對SQL Server提供管理套件(Management Pack),對於其他資料庫系統的監控,則透過其他廠商開發的管理套件,像是 BridgeWays公司的產品,目前提供了針對Oracle Database、DB2和MySQL環境的套件。

說到微軟System Center Operations Manager與管理套件擴充架構,讓人想到係出同源的NetIQ的AppManager,這套產品目前能以模組(Module)的方式來監控多種資料庫系統,例如微軟SQL Server和Oracle Database。

IBM的Tivoli系列管理軟體當中,也可以找到能涵蓋多種資料庫系統的監控產品。目前我們看到有Tivoli OMEGAMON XE for Databases,可支援DB2、Oracle、微軟SQL Server和Sybase;針對該公司在大型主機上的DB2和IMS資料庫系統,Tivoli OMEGAMON XE系列中,也提供了for DB2 Performance Expert on z/OS和for DB2 Performance Monitor on z/OS,以及for IMS on z/OS等產品因應。

在HP的軟體產品線中,同樣有兩部分是能涵蓋到資料庫效能的監控。首先是HP Operations Smart Plug-in for Databases(DB SPI)系列,可分別針對Oracle Database、微軟SQL Server、IBM DB2 Informix和Sybase等上述5種資料庫系統,提供監控與管理功能,並納入Operations Manager集中控管;另一個部分是SiteScope,它也可以監控資料庫系統,透過Database Solution Template,目前可支援Oracle Database和微軟SQL Server等資料庫,在大型企業環境中,它還可以整合到HP另一套產品System Availability Management(SAM)裡面。

從資料庫顧問服務或安全稽核角度發展而來的產品
除了國際軟體廠商的產品,也有一些臺灣本土軟體廠商推出應用在資料庫效能管理上的解決方案。

例如:鼎新電腦發展出的資料庫守護精靈DBAssistant,這套軟體針對微軟SQL Server的環境,提供了效能資訊、資料庫稽核,以及針對磁碟系統、SQL執行、異常登入等提供警示機制,這套軟體的使用上,通常是搭配在他們的資料庫維護、健診分析、優化等服務中。

相關廠商還有庫柏資訊,本身提供多種資料庫服務與稽核解決方案,他們對資料庫效能與系統穩定性管理應用,開發了專用的產品dbSonar,主要針對的是Informix與Oracle Database,可以層層挖掘資料庫效能問題,以及分析SQL語法耗用資源的方式。在監控資料庫存取行為上,該公司提供了DBAegis,可針對6種資料庫系統通報大量資料撈取,以及記錄SQL回應時間。

Chalet Tech公司也是一家提供資料庫相關服務與產品的廠商,透過該公司的資料庫稽核產品Chalet ADS(Automated Database Security),能將資料庫內所包含的各種物件,視為資產的形式監控,並記錄是否有異常存取(例如短時間內出現大量資料庫存取行為)或違反政策(例如資料查詢作業時間耗費太多時間)的狀況,而ADS支援的資料庫類型同樣是6種。

 

第三方軟體廠商所提供的效能調校功能
若本身的應用環境中存在多種資料庫系統,只靠資料庫系統本身提供的機制逐一去管理,可能會相當麻煩,除非你的IT人員對這些產品樣樣精通。若想簡化管理,你可以透過第三方軟體或服務廠商所開發的工具、設備,來協助你用單一介面管理異質資料庫平臺。

 

Embarcadero DB Optimizer提供效能不佳的SQL改寫建議
Embarcadero的DB Optimizer可直接改寫SQL、套用,以提升效能。

 

針對最耗資源的SQL語法,庫柏dbSonar提供評估工具
為了找出資料庫SQL語法耗用系統資源的模式,庫柏dbSonar有SQL評估工具,能分析每一支SQL語法的執行步驟與成本,協助判斷因SQL或資料表設計所造成的效能瓶頸。

 

用Chalet Tech ADS突顯資料庫發生查詢時間較長的作業時段
以時間為主軸,Chalet Tech ADS透過從網路交換器旁聽的方式記錄資料庫存取,對於發生較長時間查詢的資料庫處理,能以報表方式呈現。

針對超大型資料庫環境提升效能的作法--利用壓縮與資料分割
當你將資料庫系統運作在合理的軟硬體環境下,所使用的SQL語法、資料庫物件也都運用得很好的狀態下,系統的效能還是非常慢,很可能是所儲存與處理的資料量已經大到一個程度了。這時候,你可以考慮採用資料庫本身提供的壓縮(Compression)和資料表分割(Partitioning)功能。

不過要用這些功能,你目前所採用的資料庫系統版本必須是針對更大型環境應用的版本,例如:在Oracle Database和微軟SQL Server的環境下,要使用到企業版才行,標準版以下並不提供這些功能(Oracle Database除了必須是企業版,還要選購相關功能才能運用);IBM DB2是在Enterprise Server版、Advanced Enterprise Server版中,提供壓縮和資料表分割;MySQL若用的是InnoDB、MyISAM儲存引擎,可以使用資料表壓縮,但要用企業版才有資料表分割功能。

除非本身是比較大型的企業,否則一般公司所購買的都是標準版居多,鄭樹看到企業對這些功能的考量是,第一點是並不太了解這些機制,第二點是需要再花一筆錢升級版本,才能去用。除非所諮詢的顧問建議他們去用,這時候才會認真考慮,而導入使用之前,可能會先驗證,看看效果是否如預期,如果答案是肯定的,就會去購買這樣的授權。

壓縮
為什麼資料庫使用壓縮後,可以提升效能?簡單地說,是系統利用處理器的演算將資料庫中重複的部分壓縮、整合,以便讓資料佔用空間變小,如此一來磁碟I/O量也會隨之縮小。集英信誠合夥顧問許致學說:現在伺服器端的處理器、記憶體和磁碟等硬體資源中,速度最慢的是磁碟,當我們從磁碟讀取的資料變少,系統相對效能就提升了,「這是利用處理器去cover磁碟老是跟不上的問題」。原本這非常依賴磁碟的存取速度,現在變成是先在處理器當中去進行瘦身作業,接著再由磁碟進行後續讀寫。而對處理器來說,只要稍微花點力氣去處理,很精準地去套用在特別需要去壓縮的資料庫物件,其實對於系統效能的影響還好。 

對Oracle Database而言,所謂的壓縮是指Advanced Compression,它是選購功能,黃久安表示,在這套產品的第9版時,壓縮的功能還不是很全面,只能在新增資料壓縮;到了目前的11g,功能算是很完備,可以同時針對OLTP和OLAP處理,而且除了新增資料之外,更新與刪除資料時都支援壓縮,此外,Advanced Compression的範圍,也涵蓋到檔案、備份資料、網路流量。

至於微軟SQL Server,是在2008版之後開始提供資料壓縮功能,所針對的資料庫物件是資料表和索引,它分成頁面壓縮(Page Compression)和資料列壓縮(Row Compression)。

實際的資料庫壓縮執行的狀況又是如何?許致學曾經在客戶的SQL Server資料庫環境中上,對一份資料表和相關的索引執行資料庫壓縮,原本佔用的空間是6GB,以Page壓縮模式處理,6分鐘後作業完成,資料只剩990MB。在執行壓縮過程中,他看到資料庫伺服器端的處理器使用率會提升,等到壓縮完成、再經歷長時間觀察,會發現處理器使用率並沒有因為採用壓縮而明顯變高。

資料分割
資料分割是將一張大資料表切割成多個資料量較小的資料表,而分割的依據,主要以常用的查詢條件來判斷,假如一般公司員工常用下單時間去查詢訂單,或像電信業環境在用戶通話帳單確認上,需要以時間來查詢通聯紀錄。

例如常用時間去查詢資料,所以我們考慮用時間這個條件來切割,之後,查詢作業就可以只針對這個部分去處理,而不用掃描整張資料表,才能找到指定資料,速度就會快很多。

資料的分割,也可以用作資料萃取,比如以每個月為單位分割資料表,這些資料若需要匯出、匯入,只要針對一個分割去進行,速度上也會快很多。以健保資料為例,若要做到匯出每個月資料,以前可能要把整張資料表匯出,若透過資料分割,可以做到只匯出一天、一周或一個月的資料,再將這些內容傳到別的地方去處理。

就實際執行功能而言,像在Oracle Database環境執行資料分割時,它支援三種模式:範圍式切割(適合時間或數值等類型的條件)、清單式切割(例如在地區資料表中,北美洲的分割會包含加拿大、美國與墨西哥)、Hash演算法式切割(讓系統來決定切割方式)。

此外,除了單一條件分割(Single-Level Partitioning),Oracle Database還可以用組合式條件來切割資料(Composite Partitioning),例如把時間和分公司等兩個條件合在一起。若對用戶的行為了解不足時,用Hash去切割,也會有一定效果。黃久安說,所以最典型的分割作法,就是將一個查詢條件加上Hash的方式切割,或是只用Hash。

資料分割的用途,不只是為了提升資料庫處理效能,庫柏資訊專業服務部Oracle首席顧問黃子哲認為,這還可以從資訊生命周期(Information Lifecycle Management,ILM)的角度來考量。因為,就法規而言,有些歷史資料是必須保留的,但這些資料在目前用得很少,若把這些資料放在磁帶或其他地方,當稽核來時,可以隨時回復以便存取,在資料庫管理上,我們可以用資料分割,例如,根據月份將資料表的前6個月內容分出來,再將這些歷史資料匯出之後、從資料庫中移除,等需要因應稽核作業時,再將資料取回。這樣的作法的好處是,能在維持現有效能的情況下,又能確保資料生命週期的部份被管理到。

事實上,壓縮和資料分割這兩種功能也可以搭配起來使用。用法上,鼎新電腦在SQL Server上有相關的經驗,該公司科技系統加值暨服務事業部主任系統工程師洪智遠表示,可以把資料,依據歷史資料和現行資料的差異,分割開來,並且對歷史資料執行頁面壓縮,對現行資料則用資料列壓縮。

頁面壓縮因為是整個頁面壓縮,適用在當下比較不會存取到的歷史資料,而資料列壓縮是一筆一筆資料壓縮,比較適合現行資料。如果以相反的方式配置,原本預期想要透過壓縮來提升效能的效果,可能會大打折扣。例如將現行資料以頁面壓縮方式處理,因為經常需要讀寫,因為會經歷整個頁面解壓縮、讀取、寫入、再壓縮至整個頁面的過程,所以反而會增加不少I/O量。

整體而言,會影響資料庫效能的因素很多,我們這裡所探討的解決方式,主要從改善種種資料處理的方式來因應,例如針對應用程式開發與資料庫設計上的問題,以往我們常以治標的方式處理好這些狀況,不一定能解決根本的問題。

逸凡科技資深技術顧問鄭樹感慨地說:「效能調校,只要去更改環境,就會有兩種結果,除了變好的可能性,也有可能變差。」他認為,基本上,若能精準判斷,落在變好的機會是很大的,不過看待問題時,若我們只看到某個部分,卻沒注意到背後隱藏的其他因素而貿然調校,結果有可能會變差。

善用資料庫索引來增進查詢作業效率
以索引為例,它是許多人知道要去用的機制,對資料庫稍微了解的人,可能會馬上想到的是用這個機制來加速尋找,但索引不能盲目使用,仍要依據應用系統用戶環境的產業別、資料特性、使用者習慣來建立。

索引的作用是什麼呢?洪智遠做了一個比喻:資料庫就像一本電話簿,而索引頁就像目錄,我們要找某個人的電話號碼時,若沒有索引的幫忙,就要從第一頁找到最後一頁,這叫做資料表掃描(Table Scan),是最沒有效率的方法,但你如果懂得運用目錄,因為其中會按照姓氏、地區、產業等項目分類,不用逐頁翻閱,就可以很快速地找到所要的資料。

同理,若有一份資料先依照使用者經常要求的搜尋條件去排列順序,例如執行銷貨單查詢時,經常會用到客戶名稱或客戶地區,若將資料表的這兩個欄位建立索引,而之後應用程式在資料庫執行這類查詢時,透過索引這樣的資料庫綱要物件,就會加速資料的尋找,進而會減少磁碟I/O。

以一般應用程式在搭配的資料庫系統上,通常會針對基本的資料來建索引(以單據處理而言,像是單號、客戶代號),但在不同環境下,使用者經常會去查詢的欄位可能有差異,對於需要針對各自使用特性所建立的索引,就需要收集、分析、統計之後,才能決定。

但索引並非用得越多,效能就越好。因為索引建立後,會在磁碟空間上有實際存在的資料──比如把地區別當成索引,資料庫系統就會把地區別的資料,寫一份在磁碟上,之後資料有異動時,系統會需要維護這個索引的內容,所以索引的使用不只是會占額外空間,而且是需要維護的。因此,當索引建立得太多,資料庫的寫入效能就會受到嚴重影響。簡而言之,索引的建立,必須要按照資料使用的特性、該環境的使用習慣去考量。


一般資料表與經過分割的資料表的比較
依圖中所示,不論是未經過或經過分割的資料表,都可以擁有已分割或未分割的索引。

資料分割的3種模式
Oracle Database的資料分割支援3種模式,下圖是各自應用的例子。像這裡的範圍式分割是用年度來切割,清單式分割是用銷售地區來分割,而Hash分割是用Hash群組來分割。

 

相關報導請參考「搶救資料庫效能大作戰」


Advertisement

更多 iThome相關內容