資料庫物理設計經驗談(一)

概述
我們無論使用哪種資料庫，無論怎樣設計資料庫，我想都會遵從一個原則：資料安全性和效能高效這兩個主要方面，但是關於這兩個方面的話題太多，在這裡就不一 一陳述，我只是從資料庫物理分佈設計方面和大家一起簡單的探討一下。因為資料庫良好的物理分佈設計也是對資料安全性和效能高效影響比較大, 就象我們在建大樓之前一定要先打好地基一樣。
現實中我們在應用各種不同資料庫的時候，往往會忽略資料庫的物理佈局，只有在資料庫效能遇到問題的時候才去考慮，但這是得不償失的，這樣一來不僅會導致與 設計相關的問題出現，而且會影響效能的調整效果，所以我們在建立資料庫之前先進行規劃資料庫的物理佈局也是很必要的，這也符合人們常說的”磨刀不誤砍柴 工”的道理。下面我就以Oracle為例從優化作業系統、磁碟佈局優化和配置、資料庫初始化引數的選擇、設定和管理記憶體、設定和管理CPU、設定和管理表 空間、設定和管理回滾段、設定和管理聯機重做日誌、設定和管理歸檔重做日誌、設定和管理控制檔案等幾個方面作以陳述。

一. 優化作業系統
為了獲得最佳的伺服器效能, 對作業系統的優化也是很必要的, 因為作業系統效能問題通常會涉及到程式管理、記憶體管理、排程等，所以使用者需要確保有足夠的I/O頻寬、CPU的處理能力、交換空間來儘可能的降低系統時 間。如果應用程式在緩衝區出現過多的”忙”等待, 那麼系統呼叫的程式將會增加, 雖然可以通過優化SQL語句等方法來降低呼叫的數目，但是這也是治病不治根的。使用者可以啟動Oracle的初始化引數timed_statistics來 增加系統呼叫的數目, 反之如果關閉此引數,那麼系統呼叫的數目也會減少。作業系統的快取和Oracle自己的快取管理是不相沖突的, 雖然它能消耗一定的資源, 但是它對效能還是有一定好處的, 因為一般所有資料庫的I/O需要通過系統檔案快取來訪問檔案儲存器。
Oracle的操作可能會用到許多的程式(有的系統叫執行緒), 所以使用者應該確保所有Oracle的程式、後臺程式、使用者程式具有相同的優先順序, 否則就會產生惡化的現象, 導致高優先順序的程式等待低優先順序的程式處理完畢釋放出CPU資源後再處理, 更不能將Oracle的後臺程式繫結到CPU中, 這樣一來也會導致被繫結的程式被CPU資源餓死。
比較好的是有些作業系統提供有作業系統資源管理器(Operating System Resource Manager), 通過它可以對系統資源訪問劃分優先順序來降低峰值負載模式的影響，來實現多種管理策略和方法，控制使用者資源的訪問，限制使用者資源的可消耗量。

二．磁碟佈局優化和配置
在大多數產品資料庫應用中，資料庫檔案一般都放在磁碟上，因此磁碟的良好使用和佈局也是很重要的。磁碟佈局的目標是：磁碟效能是不能阻礙實現資料庫效能， 資料庫磁碟必須專用於資料庫檔案，否則非資料庫將會影響到該資料庫,且這種影響是不可預測的; 系統硬體和映象必須滿足恢復和效能的要求，資料檔案大小和I/O不能超過磁碟的大小和I/O，資料庫一定是可以恢復的，必須使後臺程式之間的競爭最小化。 在規劃硬碟配置時也要注意：首先所用的磁碟容量，有時用多個容量小的磁碟比用一個大的磁碟效果更好，因為可以進行更高階的並行I/O操作; 其次磁碟的速度，如反應時間和尋道時間都將影響I/O的效能, 可以考慮使用合適的檔案系統作為資料檔案; 再者使用合適的RAID。
RAID(Redundant Arrays of Inexpensive Disks)廉價冗餘陣列可以改善資料的可靠性，而I/O的效能又取決於RAID配置的方式：RAID1可以提供比較好的可靠性和較快的讀取速度，但寫的 代價比較大，所以不適合頻繁寫的應用；RAID0+1在原RAID1的基礎上讀取的速度更快，所以這也是大家常會選擇的方式；RAID5可以提供比較好的 可靠性，有順序的讀操作比較適合這種方式，但效能會受到影響，對於寫操作頻繁的應用也不適合這種。對於該選擇那種方式不能一概而論，要根據具體的情況而 定。
有些應用軟體先天性受到磁碟的I/O限制, 所以在設計的時候應儘量使Oracle的效能不受I/O的限制, 所以在設計一個I/O系統時要考慮以下的資料庫需要: 儲存磁碟的最小位元組; 可用性, 如24X7, 9X5; 效能如I/O的輸出和響應時間。決定Oracle檔案的I/O統計資訊可以來查詢下列: 物理讀數量(V$filestat.phyrds)、物理寫數量(V$filestat.phywrites)、平均時間, I/O=物理讀+物理寫。而I/O的平均數量=(物理讀+物理寫)/共用秒數), 估計這個資料對於新系統是有用的, 可以查詢出新應用程式的I/O需求與系統的I/O能力是否匹配以便及時調整。

三．建立資料庫初始化引數的選擇
管理資料庫的第一階段就是初始化資料庫的建立，儘管可以在資料庫建立好以後再來調整效能，但是有些引數是不能修改的或很難修改，比如： Db_block_size、Db_name、Db_domain、Compatible、Nls_language、 Nls_characterset、Nls_nchar_characterset。
Db_block_size引數決定Oracle資料庫塊的大小，一般可以選擇的範圍是2K、4K、8K、16K、32K，使用下一個較大值資料庫塊大小 的效果一般可以集中查詢中效能提高50%。但是按常規來說對於一般伺服器不提倡把這個值設的很大，小型機除外，因為這樣一來資料庫塊中將會有更多的行，在 資料庫維護期間發生塊級競爭的可能性比較大，避免這種競爭的辦法是在表級和索引級增大Freelists、maxtrans和initrans 的設定值，通常Freelists設定為大於4會帶來更多的好處。
Db_name該引數指定一個資料庫識別符號，一般在Create Database中指定的名稱，改引數是可選的（在Oracle9i實時應用叢集時是必選的，多個例項有相同的引數值），但是建議在Create Database之前設定它，如果不指定則要出現在Startup或Alter Database mount命令中。
Db_domain該引數指定全域性資料庫名的擴充套件部分，在Oracle9i實時應用叢集時是必選的，多個例項有相同的引數值。
Compatible該引數指定Oracle伺服器維護版本的相容性，保證與早期的版本向下相容的時候允許使用者使用新的版本，在Oracle9i實時應用叢集時是必選的，多個例項有相同的引數值。
Nls_language和Nls_characterset及Nls_nchar_characterset三個引數是資料庫的字符集引數，在資料庫建立完成後一般也不能改變或很難改變，所以在建立資料庫的時候要先設定好。

四．設定和管理記憶體
Oracle使用共享記憶體來管理其記憶體和檔案結構，Oracle常使用的記憶體結構如下：
系統全域性區（System Global Area，SGA），SGA隨著不同的環境而不同，沒有一種普通的最佳方案，我們在設定它直前要先考慮以下的幾個方面：實體記憶體多大；作業系統是那種及佔 多大的記憶體，資料庫系統是檔案系統還是裸裝置；資料庫執行的模式。SGA包括：Fixed size、Variable size、Database Buffers、Redo Buffers。SGA佔有實體記憶體的比例沒有嚴格的規定，只能遵從一般的規則：SGA佔據實體記憶體的40%--60%左右。如果通過直觀的公式化來表達 則為：OS使用記憶體+SGA+併發程式數*（Sort_area_size+Hash_area_size+2M）<0.7RAM,這個公式也只是 參考，不必拘於此，實際情況可以自由發揮。初始化引數檔案中的一些引數對SGA的大小有決定性的影響。引數Db_block_Buffers（SGA中存 儲區快取記憶體的緩衝區數目），引數Shared_pool_size（分配給共享SQL區的位元組數），是SGA大小的主要影響者。Database Buffers 引數是SGA大小和資料庫效能的最重要的決定因素。該值較高，可以提高系統的命中率，減少I/O。每個緩衝區的大小等於引數Db_block_size的 大小。Oracle資料庫塊以位元組表示大小。Oracle SGA區共享池部分由庫快取記憶體、字典快取記憶體及其他一些使用者和伺服器會話資訊組成，共享池是最大的消耗成分。調整SGA區各個結構的大小，可以極大地提 高系統的效能。
資料塊緩衝快取區（Data block buffers cache），Data buffers在8i中是Db_block_buffers*Db_block_size，9i中用Db_cache_size來代替這個引數。在記憶體的 配置中把別的引數設定完成後，應該把能給的都給Data buffers。Oracle 在執行期間向資料庫快取記憶體讀寫資料，快取記憶體命中表示資訊已在記憶體中，快取記憶體失敗意味著Oracle必需進行磁碟I/O。保持快取記憶體失敗率最小的關 鍵是確保快取記憶體的大小。Oracle8i中初始化引數Db_block_buffers控制資料庫緩衝區快取記憶體的大小。可通過查詢V$sysstat 命中率，以確定是否應當增加Db_block_buffers的值。
　　SELECT name,value FROM V$sysstat
　　WHERE name in (’dbblock gets’,’consistent gets’,’physical reads’);
通過查詢結果命中率=1-physical reads/(dbblock gets+consistent gets) 如果命中率<0.6～0.7，則應增大Db_block_buffers。
字典快取區（Dictionary CACHE），資料字典快取區的大小由資料庫內部管理，大小由引數Shared_pool_size來設定。資料字典快取記憶體包括了有關資料庫的結構、用 戶、實體資訊等。資料字典的命中率對系統有很大的影響。命中率的計算中，getmisses 表示失敗次數，gets表示成功次數。查詢V$ROWCACHE表：
　　SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100
　　FROM V$rowcache;
如果該值>90%，說明命中率合適。否則，應增大共享池的大小。
重做日誌緩衝區（Read log buffer）,下面將有陳述，在此就不做說明。
SQL共享池（Shared pool size），該共享池包括包括執行計劃及針對資料庫執行SQL語句的語法分析用的，在第二次執行相同的SQL語句時可用SQL中的語法分析來加快執行速 度。如果它太小，語句會連續不斷地再裝入到庫快取區，從而影響效能。可以通過Alter system命令來修改此引數，9I以後的版本可以動態地修改其大小。
大池（Large pool size），是一個可選的記憶體區。如果選擇可對資料庫象備份/恢復這些大的操作提高效能。如果不選擇此引數，則系統會使用共享池。
JAVA池（Java pool size），由其名字而言可知，是為滿足JAVA命令語法分析的需求。在UNIX系統中如果區組的大小為4MB，則預設大小應該為24M，如果區組大小為 16MB，則預設大小為32M。如果資料庫沒有使用JAVA，則保持在10M—20M足夠。
多緩衝區池（Multiple buffer pools），可以使用多緩衝區池把大資料集與應用的剩餘部分分開，以減少它們爭奪快取區內相同資源的可能性，建立時需要在初始化引數中設定其大小。
程式全域性區（Program global area，PGA）是Oracle的一個私有的記憶體區，9i以後的版本中，如果Workarea_size_policy=auto，則所有的會話共用一 塊記憶體，該記憶體在引數Pga_aggregate_target設定，它的一個好的初始設定是：對於一個OLTP系統 Pga_aggregate_target=（totalL_mem*80%）*20%；對於一個DSS系統Pga_aggregate_target= （total_mem*80%）*50%。這裡的total_mem是實體記憶體。在調整Pga_aggregate_target引數時，下面的幾個動態 檢視會有幫助的：V$sysstat和V$sesstat；V$sql_workarea_active；V$pgastat；V$ sql_workarea； V$process。

五．設定和管理CPU
在設定和安裝資料庫的過程中，基本不用對CPU做什麼配置的，系統會自動預設的，但是在管理過程中我們可以利用作業系統監控工具來監控CPU的狀況。例如 在UNIX系統中，可以執行sar–u的工具來檢查整個系統使用CPU的水平。其統計資訊包括：使用者時間、系統時間、空閒時間、I/O等待時間。在正常工 作負載的情況下，如果空閒時間和I/O等待時間接近於0或少於5%，那就表示CPU的使用存在問題。
對於Windows系統可以通過效能監視器（Performance monitor）來檢查CPU的使用狀況可以提供以下資訊：處理器時間、使用者時間、特權時間、中斷時間、DPC時間。
如果CPU的使用存在問題，則可以通過以下的方式來解決：優化系統和資料庫；增加硬體的能力；對CPU資源分配進行劃分優先順序，Oracle資料庫資源管理器（Database Resource Manager）負責在使用者和應用程式之間分配和管理CPU資源。

引用自：http://shanchao7932297.blog.163.com/blog/static/1363624200693093641943/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15688952/viewspace-661720/，如需轉載，請註明出處，否則將追究法律責任。