解DBA之惑：資料庫承載能力評估及優化手段

作為DBA，有時會被挑戰類似這樣的問題：

• 如果現有業務規模增加10倍、100倍，資料庫是否能夠支撐？
• 下個月我們搞大促，資料庫這邊沒問題吧？
• 計劃進行去O工作，程式碼邏輯不變，資料庫從Oracle切換到MySQL，MySQL能支撐業務嗎？
• 伺服器採購選型，到底哪款伺服器更適合我們呢？

面對諸如上面的這些質疑，DBA應該如何面對？

身為DBA該如何評估現有資源使用情況？

如果現有資料庫資源確實無法支撐，又該本著什麼原則進行改造呢？

本文是針對上面問題的一些經驗總結，供大家參考。

一、評估工作

面對這樣的問題，首先要進行評估工作，可遵循下面的步驟：

1、建立效能基線

針對系統執行現狀，建立效能基線。將業務指標與效能指標建立起對應關係。這裡所說的效能指標包括CPU、MEM、DISK、NET等。在諸多資源中，肯定存在不均衡的情況，短板的資源最有可能成為業務增長後的瓶頸。在具體操作上，可首先確定一個業務高峰時間段，通過監控平臺或監控工具收集系統各資源的使用情況。然後依據收集的資訊，分析可能的效能短板在哪裡。  

對於DBA來說，對自己掌管系統的效能使用情況要了然於胸。通過對業務的瞭解，將業務指標對映到效能指標上，就可以很容易地推斷出現有系統可承載的最大業務量。此外，對於可能影響承載業務增長的短板，也會有比較清晰的認識。  

一般來說，資料庫類的應用是重資源消耗類的應用。對CPU、MEM、DISK、NET等，均有較大的消耗。但由於不同硬體發展水平不均衡，各資料庫資源消耗特點也不同，因此需要具體問題具體分析。  

下面談談我對硬體發展及與資料庫關係的一點個人觀點:

• CPU

相對於其他硬體而言，CPU技術發展較快。隨著CPU主頻提高及多核CPU技術的發展，CPU提供的計算能力往往不會成為系統的效能瓶頸。但我們需要注意的是，有些資料庫是無法完全利用CPU的能力(例如MySQL就是這樣)。此時，為了充分利用CPU的資源，可以考慮諸如"多例項混跑"的方案，提高CPU利用率。

• MEM

隨著記憶體技術的發揮，記憶體的價格越來越便宜。現在我們在生產環境中，可以見到128、256GB，甚至TB級的記憶體也不罕見。一般來說，資料庫通常會利用記憶體作為緩衝區，大記憶體的配置對資料庫的效能有著比較明顯的提升。此外，資料庫自身技術也在適應著大記憶體的場景，通常採用的策略是劃分子池。將管理的單位進一步細分，例如Oracle中的Sub Pool、MySQL中的多instance buffer pool。

• NET

隨著GigE、10GbE、InfiniBand技術的飛速發展，低延遲、高頻寬的服務品質給資料庫乃至整個IT系統帶來了很多變化。常見的應用領域有：

• 加速分散式資料庫，例如Oracle RAC。

• 加速大資料處理，例如提升Hadoop MapReduce處理。

• 儲存架構的變革，從Scale-Up向Scale-Out演變。

• 容災方案，主備策略…

• DISK

相對於其他硬體技術發展而言，傳統的機械式磁碟是相對而言發展最慢的，其往往也是最容易成為資料庫的效能瓶頸。隨著快閃記憶體技術的橫空出世，為儲存技術帶來的一種變革。下面我們來看看主要效能指標的對比：

從上述指標來看，使用快閃記憶體技術後，儲存能力大大提高，消除了系統最大的瓶頸。這也是為什麼很多DBA都在不同場合，大力推薦使用快閃記憶體，其對於資料庫效能的提升會帶來質的飛躍。但與此同時，我們也應該注意到，傳統關係型資料庫是按照磁碟IO模型設計的，沒有考慮到快閃記憶體技術，現在屬於軟體落後於硬體的階段；相對而言，快閃記憶體技術對於非關係型模型更有優勢。

很多基於傳統設計的優化理論發生了變化，例如: 索引聚簇因子的問題。這一點是需要我們在考慮資料庫優化時，主要注意的。此外，NoSQL的效能優勢因為傳統資料庫結合快閃記憶體技術，而變得不明顯。需要在架構選擇時加以分析。

2、建立業務壓力模型

根據業務特徵，建立業務壓力模型。簡單理解就是將業務模擬抽象出來，便於後面進行壓力放大測試。要做到這一步，需要對業務有著充分的瞭解和評估。

下面通過一個小例子說明一下:

這個表格模擬了某個類電商的業務，其包含的主要模組及模組中的主要操作。針對不同的操作其交易複雜度不同 （交易複雜度可理解為執行SQL語句的個數）。根據不同的讀寫情況，區分是資料讀還是資料寫。在估算了業務總量(交易量)的情況下，很容易推算出資料操作的量。通過這種方式將業務壓力模型轉化為資料壓力模型。此處的難點在於對業務邏輯的抽象能力及對模組業務量的比例評估。

有了上述概覽的表格後，針對每一種業務操作，可細化其操作。最終將其抽象成SQL語句及對應的訪問特徵。其虛擬碼可描述為

可依據上述虛擬碼，編制壓力測試程式碼。通過一些工具呼叫測試程式碼，產生模擬測試的壓力。例如我經常使用的oradbtest/mydbtest(原阿里樓方鑫的一個測試工具)或sysbench等，都是不錯的壓力測試工具。

建議企業根據自身情況，整理出自己的業務壓力模型。這在系統改造、升級、擴容評估、新硬體選型等多種場合都很有用處。它要比廠商提供的類似TPCC測試報告，更有意義。據我瞭解，很多規模較大的公司都有比較成熟的壓力模型。

3、模擬壓力測試

要想考察現有資料庫能否承載增長後的業務壓力，最好的方式就是模擬壓力測試。觀察在近似真實的壓力下，資料庫的表現。重點觀察，資料庫的承載力變化、主要效能瓶頸等。通常可以有兩種方式，一種是從真實環境導流（並可根據需要放大流量，可利用類似TCPCOPY等工具）;一種是根據前面整理的業務壓力模型，通過壓力工具模擬壓力。前者適用於已有專案的擴容評估、系統改造評估等，後者適用於新上專案原型方案評估、效能基準測試等場景。

上述模擬壓力測試結果中，暴露出的效能瓶頸點，就是我們後面需要著重改進、優化的方向。

二、優化層次及步驟

針對上面的評估結果，來確定後面的改進、優化方案。可遵循如下一些步驟：

1、分析瓶頸點

根據上面的評測結果，分析效能瓶頸點。針對不同瓶頸點，可採取不同的一些策略。有時候效能測試時全流程的，對於一個複雜系統來說，要明確定位到效能瓶頸點比較困難。此時，可藉助一些APM工具，量化整個訪問路徑，協助找到瓶頸。也可以類似上面的做法，做好抽象工作，只對資料庫端施加壓力，觀察資料庫行為，判讀資料庫是否為瓶頸。如判斷就是資料庫的承載能力不夠，可按照不同層次進行考慮。

在整個評估資料庫承載能力中，這一步驟是最複雜的、也是最難的一部分。要區分清楚是否是資料庫承載能力不足，還是其他元件的問題。即使明確是資料庫的問題，也要分清楚是整體or區域性的問題；是單一業務功能慢，還是整體都比較慢；是偶爾會慢，還是一直都很慢等等。這些問題的界定有助於後面明確問題層次，採取不同的策略進行解決。

針對資料庫承載能力不足，我將常見出現問題進行了層次劃分，可簡單分為語句級、物件級、資料庫級、資料庫架構級、應用架構級、業務架構級。不同層次採取的方式也有所不同，下面分別描述一下。

2、層次-語句級

如效能核心問題，只是某條SQL語句的問題，可有針對性地進行優化。這種方式是侵入性比較小的一種優化方式，其影響範圍也比較小。下面對比常見的語句級優化方法。說明一下，下面方法已經排除了諸如統計資訊不準確等其他因素，僅從SQL語句本身優化方式考慮。

• 改寫SQL

通過改寫語句，達到調整執行計劃，提高執行效率的目的。這種方式的缺點是需要研發人員修改原始碼，然後再進行部署上線的過程。此外，有些使用O/R Mapping工具產生的SQL，無法直接修改語句，也無法使用此方法。

• 使用Hint

很多種資料庫都提供了提示（Hint）的功能。通過這種方式來指定語句的執行過程。這種方式同樣需要修改原始碼，經歷部署上線的過程。此外，這種修改方式還存在適應性較差的問題。因為其指定了特有的執行過程，隨著資料規模、資料特徵的變化，固化的執行過程可能不是最佳方式了。這種方式實際上是放棄了優化器可能產生的最優路徑。

• 儲存概要、SQL概要、計劃基線

在Oracle中還內建了一些功能，它們可以固化某一條語句的執行方式，從本質上來講，其原理和上面使用Hint差不多。其缺點也類似上面。

• 調整引數

有時也可通過調整某些引數，進而改變語句的執行計劃。但是這種方式要注意適用範圍，不要在全域性使用，避免影響較多的語句。在會話級使用也要控制範圍，避免產生較大影響。

3、層次-物件級

如效能核心問題，在SQL層面無法解決，需要考慮物件層面的調整。這種情況要比較慎重，需要充分評估可能帶來的風險及收益。一個物件的結構修改，可以涉及到數百條、甚至數千條和此相關語句的執行計劃變更。如不做充分測試的情況下，很難保證不出問題。如果是Oracle資料庫，可考慮使用SPA評估一下。其他資料庫的話，可提前手工收集一下相關語句，模擬修改後重放上述語句，評估效能變化。

1）影響因素

在物件級進行調整，除了考慮對其他語句的效能影響外，還需要考慮其他因素。常見的以下這些：

• 資料庫維護成本

常見的例如索引。通過新增索引，往往可以起到加速查詢的目的；但是增加索引，會導致資料DML成本的增加。

• 運維成本

常見的例如全域性分割槽索引。全域性分割槽索引在進行分割槽維護動作後，會導致索引失效，需要自動或手動進行維護索引動作。

• 儲存成本

常見的索引，索引結構是資料庫中真實佔據空間的結構。在以往的一些案例中，甚至出現過索引總大小超過表大小的情況，因此新增時要評估其空間使用。

2）全生命週期管理

這裡還有另外一個很重要的概念——“物件全生命週期管理”，簡單來說就是物件的生老病死。在很多系統中，物件從新建開始，資料不斷增加、膨脹，當資料規模達到一定量級後，各種效能問題就出現了。對一個百萬級的表和億萬級的表，其查詢效能肯定不能同日而語。因此，在物件設計初期，就要考慮相關的歸檔、清理、轉儲、壓縮策略，將儲存空間的評估與生命週期管理一起考慮。

很多效能問題，在做了資料清理後都迎刃而解。但資料清理往往是需要代價的，必須在設計之初就考慮這個問題。在做資料庫評審的時候，除了常規的結構評審、語句評審外，也要考慮這部分因素。

4、層次-資料庫級

到了這個層面，問題往往已經比較嚴重了。一般情況下，資料庫的初始配置都是基於其上面執行系統的負載型別進行專門配置的。如果執行一段時間後，出現效能問題，經評估是屬於全域性性問題的，可以考慮進行資料庫級別的調整。但是這種配置往往代價也比較大，例如需要專門的停機視窗操作等。而且這種操作的風險性也比較大，有可能會帶來很多不確定因素，因此要慎而又慎。

5、層次-資料庫架構級

如效能核心問題，無法在上述層面解決，可能就需要調整資料庫架構。常見的例如採取讀寫分離的訪問方式、分庫分表儲存方式等。這種對應用的侵入性很強了，有些情況下甚至不亞於重構整個系統。

例如，隨著業務的發展，系統的資料量或訪問量超出了預期，通過單一資料庫無法滿足空間或效能要求。此時，可能就需要考慮採用一種分庫分表策略，來滿足這部分的需求。但其改造難度，往往比重新開發一套系統還要大。

比如，我們可能需要一個資料中間層，來遮蔽後面的分庫分表細節。這個中間層可能需要完成語句解析、訪問路由、資料聚合、事務處理等一系列功能。即使使用了中間層產品，對於應用來說，資料庫的功能也會相對“弱化”，應用級程式碼不得不進行很多的調整來適應這種變化。此外，如何把一個線上正在執行的系統，順利平穩地遷移到新的結構下，這無疑又是一個給飛馳的跑車換輪胎的問題等等。

如果專案在執行中，出現了資料庫架構級的調整，很有可能說明在前期專案設計規劃階段出現了失誤，或者對專案的業務預期出現了偏差。因此，這兩點一定在初始階段進行充分的評估，並在設計上保留有充分的“彈性”。

6、層次-應用架構級

有些情況下，單純依靠資料庫是無法解決的，需要綜合考慮整個應用架構。在整個系統架構中，資料庫往往處於系統的最末端，其擴充套件性是最差的。因此，在應用架構設計初期，就應該本著儘量不要對資料庫產生壓力的原則進行設計。或者即使有大的壓力，系統可以採取自動降級等方式保證資料庫的平穩執行。

常見的例如增加快取、通過MQ實現削峰填谷等。通過增加快取，可以大幅度減少對資料庫的訪問壓力，提高整體系統的吞吐能力。引入MQ，則可以將對資料庫的壓力以“穩態”的形式，向資料庫持續施壓，而不至於被某個異常高峰壓死。

7、層次-業務架構級

最後一種情況是從業務角度進行一些調整。這往往是一種妥協，通過做適當的減法保證系統的整體執行。甚至不排除犧牲一部分使用者體驗等方式，來滿足大部分使用者的可用性。這就需要我們的架構師對系統能提供的能力要很清楚，對業務也要有充分的瞭解。對於承載什麼樣的業務，及為了承載業務所需要花費的代價成本有充分的認知，才可以做出一些取捨。

這裡要避免一些誤區，認為技術是“萬能”的。技術可以解決一定的問題，但不能解決所有問題，或者解決所有問題的成本代價是難以接受的。這個時候，從業務角度稍作調整，就可以達到“退一步海闊天空”的結果。

擴充閱讀： 自制小工具大大加速MySQL SQL語句優化(附原始碼)

全面解析Oracle等待事件的分類、發現及優化

循序漸進解讀Oracle AWR效能分析報告

SQL優化：一篇文章說清楚Oracle Hint的正確使用姿勢

作者：韓鋒

來源：宜信技術學院

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