資料庫效能調校實戰經驗大募集，你有什麼精彩的調校經驗嗎？

IT邦初學者
8級

轉寄

檢舉

資料庫是企業營運的心臟，一旦效能降低，許多人就會跳腳，甚至危及商務的正常運作。

資料庫的效能調整說來是門大學問，包括資料庫本身的設計、查詢的語法，索引的設置還有硬體的架構等...在你的實務經驗中，曾經遇到哪些效能降低的情況，最後你是用什麼樣的手法解決這些問題？你有什麼好用的調校工具來幫助你嗎？

本題邀請的實戰專家是胡百敬先生，由他選出回答得最有參考價值、最精彩的「實戰王」，iT邦小財神將贈送1000元pchome線上購物金給這位獲選的最佳實戰經驗回答者。

另外，實戰專家也將挑選兩位認真回答的邦友，致贈IT書籍一本。

本題贈送的認真獎書籍分別是《ASP.net 3.5最佳實務講座》、《SQL Server 2005 Reporting Services報表服務實務應用》

(本次活動認真獎書籍由微軟贊助)。

本題最後回答時間訂於7/11（五），超過該日的回答均不列入評選名單。實戰專家將於7/13日選出「實戰王」與認真獎，並公佈結果。

各位邦友們，快來亮出你們的實戰經驗吧~

發佈到：

解決時間：2008-07-13 12:31:09

發問時間：2008-06-30 11:00:01

▼ ADVERTISEMENT ▼

最佳解答(發問者自選)

16人

回答：alexc ( IT邦高手6級 )

時間：2008-07-11 23:29:26

我想大家應該不否認資料庫的效能要從依循正規化準則的資料庫邏輯設計開始打好基礎，然後才思考資料庫的實體設計，例如是否要反正規化，要建立哪些索引，哪些資料要儲存在 RAID，要用怎樣的伺服器，然後就是應用程式的架構是 2-Tire 還是怎樣的架構，要用怎樣的資料存取技術（用 ADO？OLEDB？...）。當然用戶端與伺服器端的硬體組態設定，像：RAM、CPU 也是會影響資料庫的效能。

因為這次的重點在於資料庫效能調校，所以應該是要先確認效能調校的目的為何？是為了加快讀取速度？為了讓寫入效能更好？還是要簡單容易管理的資料庫架構就好了，讀取跟寫入的效能不是重點？

確認效能調校的目的之後，就可以嘗試將資料進行分割（Partition），是要進行水平資料分割還是垂直資料分割，甚至是考慮替資料庫建立多個次要資料檔，設定檔案群組，來讓這些資料庫檔案分散在不同的實體磁碟機中。實務上，為了效能通常會將關鍵資料表與索引放在高效能的 RAID 裡。

講到 RAID 也是一個大議題，是要高可用性？還是高延展性？需不需要用到 hot-swap？hot-standby？hot-spare？決定用哪種 RAID 之後，如果可以的話，應該要儘量將資料庫對磁碟存取的動作平均分散在多部 RAID 中，以免硬碟存取活動較多的那部 RAID 提早掛點。

基本架構設計好，就不怕樹尾作颱風了，這時候才是開始調校資料庫的時候。其實要監控 SQL Server 的效能，從實體的硬碟、邏輯的 I/O 到看不到的網路封包都應該要監控，所以在監控時，一般會分成網路、RAM（包含 Cache）、CPU 與 DISK I/O 這幾個大項目進行監控。

以 Microsoft SQL Server 為例，可用的監控工具有 Windows 內建的 Performance Monitor、SQL Server 的 DBCC、SQL Server Profiler、以及 SQL Server 2005 才新導入的動態管理檢視表。有些公司甚至會自行開發或是花錢購買其他監控工具。這些工具要怎麼用、要監控哪些項目絕非三言兩語就可以講完，所以我只講在進行效能調校的基本概念，從底層的網路開始著手，然後是 CPU 使用率，接著是 RAM 與 Cahce 的使用率，最後才是 Disk I/O 的使用率。

資料庫效能調校不是一件簡單的工作，因為牽扯了太多的因素，因此我才會說應該要分門別類一次一個項目（網路、CPU、RAM、DISK），慢慢地抽絲剝繭找出原因並加以解決。

如果說先天體質不良（網路、CPU、RAM、DISK 都不是我們所能改變的事實），那就靠後天：使用 Microsoft SQL Server 本身所提供的選項來改善效能，例如：SQL Server 用來作為資料倉儲之用，可能會載入大量資料，提供使用者進行複雜的查詢，所以在多 CPU 的伺服器上可用下面的指令來讓執行平行查詢：

exec sp_configure 'cost threshold for parallelism', 2

除了調整 Microsoft SQL Server 所提供的選項設定之外，也可以透過 DTA (Database Engine Tuning Advisor) 這個好用的調整 SQL Server 效能選項的輔助工具，它可以針對資料分割、索引、資料表結構給予建議。一般會使用 GUI 介面的 DTA來進行操作，如果有需要定期執行的話，還可以寫個批次檔，透過命令列的方式來進行。

DTA 的最大特點是針對整個資料庫或僅對資料庫中的某個資料表進行分析。如果說已經確認某個資料表是效能瓶頸所在，就可以執行 DTA 針對該資料表進行分析。怎麼用 DTA Microsoft SQL Server 的線上叢書寫得很清楚了，就不用我在這邊多言了。

至於在程式開發時，開發者應該使用查詢最佳化工具來找出一個能夠以最少的處理時間來存取資料的查詢計劃，以便撰寫出更好的查詢指令、選用較佳的索引，並找出影響效能的潛在因素。

總之，資料庫效能調校絕非一時半刻就可以完成的工作，也不是指作一次就 OK，因為隨著資料成長、系統環境改變...都會影響效能，所以請定期進行效能調校。

參考資料：自己經驗、線上叢書

檢舉

| 推薦排序：高->低低->高發表時間|隱藏全部回應 |

資料庫效能調校實戰經驗大募集，你有什麼精彩的調校經驗嗎？

回答：richardhsieh( IT邦初學者5級 )
時間：2008-07-01 08:52:35

33人

公司本身是用UNIX+ORACLE的環境，ORACLE的Performance一直都是長官關切的重點
所以我的長官有提過一個Tuning的方法(長官之前的公司是國內某ERP大廠)，因為資料庫長時間使用，加上insert delete update的動作，所以資料會較凌亂，這時的解決方法是，"先將資料庫做備份後，全部砍掉後回存"，我的主管要我評估看看是不是可行，可惜，我還是沒勇氣嘗試，萬一要是沒能回來，可是會成為千古罪人，所以還是遵循正常的方法
1.定期做 Schema 的分析
2.定期重起Database，清除Temp的資料
3.Table的搬移及重整
4.Index重建索引
5.ORacle的參數調整
參考了一些書籍後發現，資料庫的Performance跟硬體也有絕大的關係，除了在龇料庫的調整外，Memory的使用，HDD的規劃，Session的設定，網路的使用對資料庫，也有影響，所以不光是在Database中做調整好就OK了，硬體的搭配也需要做調整，
以上小弟拙見，若有不足，也請大家指導及補充，謝謝

[-隱藏]

回應 richardhsieh：

julie8tw 說：

先將資料庫做備份後，全部砍掉後回存

好可怕的ERP大廠,知道DATA有多少嗎?要停多久USER無法使用呢?
還是遵循正常的方法吧!

另提一個你少提的就是SQl的寫法,程式怎寫SQL怎下,INDEX怎建也是很重要的

2008-07-01 11:49:26

antijava 說：

我能體會你的長官的想法

我是沒有管過那麼大型的資料庫
不過要講古的話
我那個年代有個很有名的程式語言，叫 Clipper
(有聽過就聽過，沒聽過就當成聽老人講古)
用 Clipper 開發出來交給客戶的系統
如果客戶反應有問題
第一個動作不是查看 source code
而是先做 REINDEX 的動作

大概有點像現在Windows系電腦出問題
只要重新開機，就可以解決掉一半的問題一樣

2008-07-02 09:37:55

antijava 說：

話又說回來
您的第3點和第4點動作
看起來已經跟重建資料庫一樣了
只是以 table level 而非 database level 的方式

2008-07-02 09:47:30

richardhsieh 說：

小弟學的多是土法煉鋼的方式，各位見笑了

2008-07-08 11:25:01

richardsuma 說：

在 unix 上使用 Oracle, 要注意 share memory 的大小.
因為關係整個資料庫的 performance.

2008-07-08 14:47:22

檢舉

回應

回答：haohao200138( IT邦初學者8級 )
時間：2008-07-02 09:28:52

22人

本身的調校使用目前壓力測試工具,從監控工具監測SQL的使用量;然後使用SQL提供的profiler的工具監測是否為語法產生lock的因素再調整SQL語法;一般發生在硬體上的機率是很低,因為目前企業所購買的伺服器都很高規,依照本身調整的經驗,大概都是PR在撰寫程式時所忽略的並沒有將校能考慮進來.
建議在調校之前可以先利用profiler的工具監測語法是否需要調整,曾經遇到有join一兩百table造成loading過重的因素.

[-隱藏]

回應 haohao200138：

antijava 說：

Join一兩百table？有沒有這麼厲害？

大概是我沒碰過這麼複雜的系統
只要我手下有人敢寫Join一二十個TABLE的SQL指令
我就會叫人把他拖出去斃了 -_-|||

2008-07-02 11:16:28

julie8tw 說：

說的JOIN一二百會動嗎,還是裡面資料只有幾筆

2008-07-02 11:26:28

cfc 說：

好神奇... 一兩百耶
=_=...

2008-07-12 03:35:01

檢舉

回應

回答：lss364551( IT邦初學者10級 )
時間：2008-07-02 13:02:40

14人

Maybe you can try to use database archiving solution to improve your system's performance(there is a software in HP merged from Outerbay and called RIM4DB now). It'll remove the history data from your database and only keep the active data on production system then you can improve your system's performance, and you can also access these 2 database without change your applications. That's my suggestion.

參考資料：http://www.advtekgroup.com.tw/content/view/114/0/

檢舉

回應

回答：andycheng( IT邦初學者10級 )
時間：2008-07-07 10:13:19

22人

我目前用的是SQL Server，以下提供一點建議：
1. 定期可做的事：定期做資料庫index重建(rebuild)和重整(reorg)，並定期更新Statistics。
2. 分析SQL語法：如果系統有較慢的功能，可以用SQL profiler抓出較慢的SQL語法，用工具去分析是否改寫SQL或增加index；另外並非index多就是好的，過去曾經遇過一個table加了二十幾個index，刪掉無用的index反而系統變快了。
3. 監控系統效能：可開啟Windows performance monitor或其他效能監控軟體，監控資料庫和OS的整體效能，並調整系統參數。
4. 其他外在因素：效能差也有可能不是資料庫造成的，可以從網路，硬體等外在因素著手調查。

檢舉

回應

回答：doggy( IT邦初學者9級 )
時間：2008-07-11 11:26:51

15人

我今天分享幾個關於 MS SQL Server 2005資料庫效能調校的經驗：

我想MS SQL Server 2005最常用的工具應該是Management Studio了，除了大部分的維護工作都會透過Management Studio進行操作外，他還提供了許多內建的資料庫報表可供DBA概觀的分析目前資料庫的運作狀況，能以圖形化的介面查看系統狀態真的非常方便，無須特別去記憶許多少用的系統預儲程序進行狀態查詢，如下圖示：

另外，MS SQL Server 2005標準版還有內建兩個很棒的效能調校工具，可以幫助你分析資料庫的效能與提供調校的建議，分別是SQL Server Profiler與Database Engine Tuning Advisor，如下圖是在「程式集」內的兩個項目。

首先，我們會先用SQL Server Profiler將資料庫執行的狀況全部紀錄下來，所儲存下來的檔案稱為「追蹤檔(Trace File)」( *.trc ) 或稱之為「工作負載紀錄檔」，這個檔案十分重要，因為這個檔案可以提供給Database Engine Tuning Advisor進一步分析這些工作負載的詳細資料，並提供一組最佳的索引、索引檢視和資料分割建議。

按下執行後，就會開始錄製應用程式對資料庫的所有動作，所以這時候你就可以開始測試你原本的應用程式，等錄製一段時間後就可以停止SQL Server Profiler並改用Database Engine Tuning Advisor分析 *.trc 檔。

當開啟Database Engine Tuning Advisor之後要先選取剛剛用SQL Server Profiler錄製的「工作負載檔案」，然後再選取要分析的資料庫，最後按下「開始分析」按鈕就可以進行分析了。

執行完成後，他會跟你說進過調校之後的效能估計會改進多少百分比，你可以按下「儲存建議」按鈕將建立索引的T-SQL指令碼存下來，然後再到Management Studio執行這段T-SQL指令即可完成資料庫調校。

參考資料：個人經驗分享

檢舉

回應

回答：davistai( IT邦好手1級 )
時間：2008-07-11 19:30:15

12人

系統調校不是一件容易說得清楚的工作，有蠻多情況下，也是根據既有的資訊(以原廠提供的為主)，再根據經驗做微調--> 一次一個參數 --> 觀察 --> 再微調，有時候為了達到預期的效能，其所花費的時間有時候很長，甚至還會出現比之前亙糟的狀況，然後被user臭罵。
其實正因為資料庫系統在現今企業服務中扮演重要角色，因此基於追求系統穩定的目標下，一般情形是不會任意調動資料庫本身環境設定的。

不過因為資料庫是架在作業系統上的，可是在資料庫上面跑的卻是應用程式(通常是SQL)，因此如果遇到所謂效能下降這種情形，不會在第一時間去想調整資料庫的(因為覺得Oracle的穩定性還不錯)，而是會同時從這三方面(OS、DB、AP)去檢視當時的作業情形。而最常遇到的是AP面的狀況，大概都跟AP的寫法或規劃有很大的關係，因為往往AP一不小心執行到對最大的table做full scan的結果，就是所有的I/O全被它卡住，或是沒建index 導致撈取速度減慢，當然回應就慢啦。通常預到此狀況，就是與AP溝通，取得協調修改程式。一般都可以恢復不錯的效果。

再者，資料庫本身的設定值必須配合作業系統方面可以支援的數值，通常原廠都會有相關資料，就它所提供的公式範例計算，再加上經驗值定出。若AP方面沒有上述問題的話，接下來可能就要從資料庫的log觀察，像alert.log 或 .trc 的檔案來檢查是否有問題的情形發生。
再來就是觀察線上作業情形，通常以v$session_event 跟 v$session_wait為主要的目標，或使用 statspack 工具來做進一步的分析。

最後，作業系統部份雖然比較少是資料庫效能影響的可能，但的確還是會發生，不過那是與作業系統廠商溝通甚久，最後他們在所謂的Internal文件(外人查不到)發現當時的版本的一個參數會影響資料庫運作效能，很神奇吧! 不過，根據以往經驗，如果公司有錢，IT主管往往為了省事及時效，都會建議擴充硬體，如CPU、Memory，不過可惜的是，通常它們能提升的效果真的很有限，甚至狀況好不了幾天，就恢復原狀。

所以，資料庫調校是應該與應用程式與作業系統合併觀察與考量，才能得出較佳的效果的。

參考資料：自己的小小心得

檢舉

回應