Oracle數(shù)據(jù)庫性能模型.doc

最近一直在思考一個問題：如何為一個數(shù)據(jù)庫建立性能模型？作為一名DBA來說，我們面臨的一個巨大挑戰(zhàn)是：如何保證數(shù)據(jù)庫的性能可以滿足快速變化的應(yīng)用的需求，如何在數(shù)據(jù)量和訪問量持續(xù)增長的情況下，保證應(yīng)用的響應(yīng)時間和數(shù)據(jù)庫的負(fù)載處在合理的水平下。我們可能會經(jīng)常面對以下的問題：某個SQL每秒要執(zhí)行100次，響應(yīng)時間是多少？某個應(yīng)用發(fā)布后，對數(shù)據(jù)庫的影響如何？所以，評估應(yīng)用對數(shù)據(jù)庫所產(chǎn)生的影響，優(yōu)化應(yīng)用并預(yù)測風(fēng)險，保證數(shù)據(jù)庫的可用性和穩(wěn)定性，這是應(yīng)用DBA真正有價值的地方。響應(yīng)時間為中心：如果要選擇一個評價系統(tǒng)優(yōu)劣的性能指標(biāo)，毫無疑問應(yīng)該是響應(yīng)時間。響應(yīng)時間是客戶體驗的第一要素，所有的優(yōu)化都應(yīng)該為降低響應(yīng)時間而努力。對于數(shù)據(jù)庫系統(tǒng)也是如此，我們優(yōu)化系統(tǒng)，優(yōu)化SQL，最終目標(biāo)都是為了降低響應(yīng)時間，單位時間內(nèi)可以處理更多的請求。數(shù)據(jù)庫時間模型：響應(yīng)時間一般分為服務(wù)時間(Service time)和等待時間(Wait time)，服務(wù)時間指進(jìn)程占用CPU的時間，包括前臺進(jìn)程(Server process)和后臺進(jìn)程(Backgroud process)，我們一般只關(guān)注前臺進(jìn)程占用的CPU time。等待時間包括很多類型，一般最常見的是IO等待和并發(fā)等待，IO等待包括sequential read，scattered read和log file sync等等，而并發(fā)等待主要是latch和enqueue。SQL execute elapsed time指用戶進(jìn)程執(zhí)行SQL的響應(yīng)時間，包含CPU time和wait time。以下是Oracle數(shù)據(jù)庫的時間模型：在Oracle系統(tǒng)中，我們可以利用AWR或Statspack報告，看到數(shù)據(jù)庫的時間信息：Statistic NameTime (s)% of DB Timesql execute elapsed time3,062.1791.52DB CPU2,842.0884.95parse time elapsed25.870.77PL/SQL execution elapsed time11.750.35sequence load elapsed time7.550.23hard parse elapsed time5.060.15connection management call elapsed time3.130.09hard parse (sharing criteria) elapsed time0.040.00repeated bind elapsed time0.010.00PL/SQL compilation elapsed time0.000.00DB time3,345.74background elapsed time204.91background cpu time72.30DB time是整個數(shù)據(jù)庫用戶進(jìn)程消耗的總時間，是從第一項到第十項時間的總和（從sql execute elapsed time到PL/SQL compilation elapsed time），但是我們會發(fā)現(xiàn)這十項時間的總和比DB Time要大一些，這是因為部分時間信息有重疊的部分，比如SQL execute elapsed time就包括了很大一部分DB cpu的時間。而background elapsed time和background cpu time則是Oracle后臺進(jìn)程消耗的時間和cpu time。數(shù)據(jù)庫響應(yīng)時間分析：數(shù)據(jù)庫系統(tǒng)的響應(yīng)時間由四個要素決定：CPU，IO，內(nèi)存和網(wǎng)絡(luò)，其中CPU和IO是最重要的因素。與之相比，內(nèi)存與網(wǎng)絡(luò)則簡單很多，因為通常情況下，對于一個調(diào)優(yōu)的系統(tǒng)來說，內(nèi)存訪問的延遲時間非常小（100 ns以下，1 ms=1000000 ns）相比較CPU和IO幾乎可以忽略。而網(wǎng)絡(luò)延遲則通常是一個常數(shù)，比如在一個數(shù)據(jù)中心的情況下，網(wǎng)絡(luò)的延遲一般在3ms以下，如果存在多數(shù)據(jù)中心的情況，網(wǎng)絡(luò)延遲可能會超過20ms，所以對于一個分布式系統(tǒng)來說，網(wǎng)絡(luò)延遲是必須要考慮的問題。在這里，我們不考慮分布式系統(tǒng)，并且忽略內(nèi)存的訪問延遲，重點分析CPU和IO，我們看以下數(shù)據(jù)庫的AWR片段：我們看到這個系統(tǒng)中DB CPU占整個DB time的87.21%，User I/O占整個DB time的9.12%，commit相關(guān)的IO等待占2.35%（主要是log file sync），CPU和IO占用了整個DB time的96.68%。由于DB CPU所占的比例很高，所以這個數(shù)據(jù)庫系統(tǒng)是CPU intensive類型，這里的DB CPU主要是執(zhí)行SQL的服務(wù)時間。我們再看另外的一個數(shù)據(jù)庫的AWR片段：我們看到，Commit和User I/O占DB time的81.46%，而DB CPU只占13.82%，所以這個數(shù)據(jù)庫系統(tǒng)是IO instensive類型的。Physical readPhysical read是指Oracle在buffer cache中沒有找到相應(yīng)的block，需要從IO子系統(tǒng)讀取相應(yīng)的block的過程，對應(yīng)的IO稱為物理IO，物理讀數(shù)量代表物理IO讀取的block數(shù)量。因為一般IO子系統(tǒng)都是慢速的磁盤，所以物理IO對整體響應(yīng)時間的影響非常大，如果發(fā)生大量的物理IO，整個系統(tǒng)的響應(yīng)時間會變得很差。系統(tǒng)的IO子系統(tǒng)可能是文件系統(tǒng)，裸設(shè)備或者ASM，底層硬件可能是SAN存儲，NAS存儲或者普通SAS磁盤等等。為了提高響應(yīng)時間，通常在物理磁盤與Oracle之間增加cache層，對于Oracle來說，物理IO并不一定是真正訪問磁盤，很可能是訪問文件系統(tǒng)cache，存儲的cache等等。不管IO subsystem是什么，Oracle只關(guān)心物理IO的響應(yīng)時間。通過AWR報告，我們可以看到物理IO的響應(yīng)時間：db file sequential read（單塊讀，隨機(jī)IO）的平均響應(yīng)時間為3ms，db file scattered read（多塊讀，連續(xù)IO）的平均響應(yīng)時間是4ms，logfile file sync的平均響應(yīng)時間是3ms，前兩者的Wait class是User I/O，代表用戶進(jìn)程讀操作的響應(yīng)時間，logfile sync的wait class是Commit，代表lgwr進(jìn)程寫redo的響應(yīng)時間，因為用戶commit必須完成log file sync的操作，所以它也會直接影響用戶進(jìn)程寫操作的響應(yīng)時間。關(guān)于物理IO的響應(yīng)時間，我們有一個經(jīng)驗值。對于Sequential read和Scattered read，我們認(rèn)為小于10ms屬于正常狀態(tài)，而大于10ms則認(rèn)為IO subsystem的響應(yīng)延遲過大。所以我們在衡量存儲系統(tǒng)的性能時，只有響應(yīng)時間在10ms以下的IO我們認(rèn)為是有效的。這里有一個有趣的現(xiàn)象，就是sequential read和scattered read的響應(yīng)時間幾乎相差無幾，也就是說隨機(jī)IO讀取8K數(shù)據(jù)和連續(xù)IO讀取128K數(shù)據(jù)，響應(yīng)時間差別很小，這是由磁盤的機(jī)械特性造成的，延遲時間=尋道時間+對于log file sync的響應(yīng)時間，因為用戶commit必須完成log file sync，所以整個系統(tǒng)的寫操作的響應(yīng)時間都取決于它的響應(yīng)時間，而且從整個數(shù)據(jù)庫系統(tǒng)的角度去看，log file sync幾乎是串行的，所以這個響應(yīng)時間對寫操作影響非常大，我們的經(jīng)驗值是必須保證在5ms以下，如果超過5ms整個系統(tǒng)的寫操作都會受到嚴(yán)重的影響。Logical readLogical read是Oracle從buffer cache中讀取block的過程，對應(yīng)的IO稱為邏輯IO，邏輯讀數(shù)量代表邏輯IO讀取的block數(shù)量。因為Oracle必須首先將block讀入buffer cache中(direct path read除外)，所以邏輯讀數(shù)量包含了物理讀數(shù)量。對于一個SQL來說，邏輯讀數(shù)量是衡量其性能的標(biāo)準(zhǔn)，而不是物理讀。雖然物理IO的響應(yīng)延遲比邏輯IO大很多，但是物理讀數(shù)量會隨著執(zhí)行次數(shù)而變化（頻繁讀取導(dǎo)致block被緩存在buffer cache中）。對于一個系統(tǒng)也是如此，邏輯讀應(yīng)該是數(shù)據(jù)庫性能評估模型的核心，我們需要建立邏輯讀與響應(yīng)時間的對應(yīng)關(guān)系。每個邏輯讀的響應(yīng)時間是多少，這是一個巨大的挑戰(zhàn)。因為每個邏輯讀背后隱藏了很多動作，可能包括物理讀，等待事件，CPU time等等。我對很多數(shù)據(jù)庫的AWR報告做了分析，期望根據(jù)經(jīng)驗值建立一個簡化的模型。我們假設(shè)一個數(shù)據(jù)庫如果是充分調(diào)優(yōu)的，除CPU time和IO以外的等待時間應(yīng)該盡可能少（應(yīng)小于DB time 10%）。在這個前提下，我們只關(guān)心CPU time和IO的影響，并將系統(tǒng)分為三類：CPU密集型，IO密集型和混合型：1.IO密集型User IO 85%DB CPU 5%每邏輯讀響應(yīng)時間0.1-0.5ms2.CPU密集型DB CPU 85%User IO 10%每邏輯讀響應(yīng)時間小于0.01ms3.混合型User I/O 60%DB CPU 20%每邏輯讀響應(yīng)時間0.05-0.1ms以上數(shù)據(jù)是根據(jù)很多個典型數(shù)據(jù)庫的AWR報告計算出來的經(jīng)驗值，計算公式很簡單：DB time/邏輯讀=每邏輯讀響應(yīng)時間。因為并沒有考慮硬件和OS上的差異，所以這個數(shù)值并不是特別準(zhǔn)確，但我們還是可以發(fā)現(xiàn)一些規(guī)律：隨著IO所占比例從10%增加到85%，響應(yīng)時間也從小于0.01ms到0.5ms。預(yù)測系統(tǒng)瓶頸對于數(shù)據(jù)庫來說，IO子系統(tǒng)對性能影響非常大，必須保證在一定的IO的壓力下，響應(yīng)延遲控制在合理的范圍內(nèi)（前面說的10ms和5ms）。因為每塊磁盤可以承受的IOPS是基本確定的，比如15K的SAS磁盤，在響應(yīng)延遲不超過10ms的前提下，可以提供150個IOPS，如果不考慮cache的影響，整個存儲子系統(tǒng)的IOPS是比較容易計算的。我們可以在系統(tǒng)上線前，進(jìn)行大量充分的測試，建立存儲IOPS與響應(yīng)延遲的模型，這樣我們就可以預(yù)測出性能出現(xiàn)拐點的風(fēng)險，提前做出擴(kuò)容的判斷。在AWR報告中，我們可以得到每秒的物理IO的數(shù)量和響應(yīng)時間，可以方便的實現(xiàn)性能監(jiān)控和趨勢預(yù)警。評估CPU的容量瓶頸相對簡單，Oracle中CPU time的計算是每個CPU耗費時間的總和，如果有16顆(核)CPU，1個小時理論上可以提供360016=57600s CPU time，不超過57600s CPU time我們可以認(rèn)為不會在CPU上排隊，系統(tǒng)不會出現(xiàn)CPU瓶頸。但是需要注意的是，除了用戶進(jìn)程使用CPU以外，操作系統(tǒng)也需要占用CPU資源，用來管理內(nèi)存和進(jìn)程調(diào)度等。我們在OS上看到的CPU使用率中的sys部分就是系統(tǒng)占用的CPU資源，所以應(yīng)該考慮至少保留10-20%的CPU資源給OS使用。并發(fā)訪問對數(shù)據(jù)庫的影響Oracle是一個Disk-based database，設(shè)計的出發(fā)點就是大部分?jǐn)?shù)據(jù)在外部存儲中，而只有小部分?jǐn)?shù)據(jù)被cache在buffer中，它既不同于Memcache這類KV cache，也不同于timesten這類In-memory database。所以，就算是所有的數(shù)據(jù)都可以被cache在buffer中，在高并發(fā)訪問的情況下，也可能會出現(xiàn)大量的latch等待，最常見的情況就是cache buffer chain。當(dāng)大量并發(fā)訪問同一塊數(shù)據(jù)時，就很可能會出現(xiàn)cache buffer chain的latch爭用，也就是我們常說的“熱點”。需要注意的是：Oracle中的latch等待分為spin和sleep兩個部分，spin消耗cpu time，而sleep則是等待時間。所以大量的latch等待不僅僅會產(chǎn)生大量的等待時間，而且會消耗大量的CPU time。Oracle是一個為并發(fā)操作而設(shè)計的數(shù)據(jù)庫，大量的并發(fā)讀寫請求，可能會帶來額外的性能消耗。比如讀取一部分頻繁修改的數(shù)據(jù)，Oracle為了保證一致性讀的需要，會利用undo信息構(gòu)造產(chǎn)生大量CR block，同時會產(chǎn)生大量的邏輯讀，這樣會消耗額外的CPU和響應(yīng)時間。存儲也可能存在熱點的問題，需要前期對存儲系統(tǒng)充分的優(yōu)化，常見的手段是利用RAID技術(shù)，將數(shù)據(jù)分散在不同的磁盤上，防止出現(xiàn)“熱點”盤。Oracle ASM提供了Rebalance的功能，允許DBA將存儲中的的數(shù)據(jù)重新分布，達(dá)到消除熱點的目的。總之，Oracle是一個可以提供大量并發(fā)讀寫訪問的數(shù)據(jù)庫系統(tǒng)，但是在很多地方，Oracle又不得采用一些串行的控制手段，比如latch，enqueue和mutex，我們要做的就是盡量降低這些串行控制對數(shù)據(jù)庫整體性能的影響。數(shù)據(jù)庫優(yōu)化原則基于響應(yīng)時間的Oracle優(yōu)化原則：盡量減少等待時間（Wait time），提高服務(wù)時間（Service time）。這也是基于Oracle等待事件的分析方法的基本原則：盡量消除各種等待事件對系統(tǒng)的影響，從而提高系統(tǒng)性能和響應(yīng)時間。如果數(shù)據(jù)庫系統(tǒng)除了CPU和IO以外的等待時間超過DB time的5%以上的話，可能存在某些性能問題，需要DBA采用等待事件的分析方法，對系統(tǒng)或應(yīng)用進(jìn)行優(yōu)化。EOF后記：為什么要寫這么一個主題，因為最近和一位同事探討機(jī)器自動審核SQL的問題，就想建立一個簡單的模型，用來開發(fā)一個SQL審核工具，開發(fā)人員通過工具和預(yù)先建立好的模型，就可以確定這個SQL是否存在性能風(fēng)險。之前我們在做SQL優(yōu)化的時候，只是關(guān)注這個SQL本身是否優(yōu)化，邏輯讀是多少。但是，很少有人把邏輯讀和響應(yīng)時間之間的關(guān)系建立起來，我試圖想回答這個問題。關(guān)于容量規(guī)劃和風(fēng)險預(yù)測其實是一個很有意義的命題，但是我們很多時候都局限在一些具體的技術(shù)細(xì)節(jié)中，而忽略了對整個系統(tǒng)容量的把握，事實上，這也是非常難的一件事。也許到目前為止，我