第一部分 磁碟I/O與記憶體

影響MySQL InnoDB引擎效能的最主要因素就是磁碟I/O，目前磁碟都是機械方式運作的，主要體現在讀寫前尋找此道的過程中。磁碟自帶的讀寫快取大小，對於磁碟的讀寫速度至關重要。讀寫速度快的磁碟，通常都帶有較大的讀寫快取。磁碟的尋道過程是機械方式。決定了其隨機讀寫速度將明顯低於順序讀寫。在多程式或多執行緒併發讀取磁碟的情況下，每次執行讀寫操作，磁碟可能存在較大的偏移，磁碟定址時間加大，將會導致磁碟I/O效能急劇下降。從很多新特性來看，幾乎都是圍繞著如何充分利用記憶體，如何減少磁碟I/O來展開的，例如：innodb_io_capactiy引數，可以加大每秒重新整理髒頁的數量。因此在單塊磁碟遇到了I/O瓶頸時，可以把磁碟升級為RAID或SSD固體硬碟來提升效能，SSD固態硬碟的特點是：不用磁頭讀取資料，尋道時間幾乎為0，快速的隨機讀寫，延遲極小，當然價格也很昂貴。目前在生產環境中主要採用RAID10、RAID5，對於資料讀寫操作頻繁的表或資料庫，可以適當採用將資料分級儲存在SSD固態電子硬碟中的方式，速度會得到較大提升！

在Buffer_Pool緩衝池中，涉及的引數為innodb_buffer_pool_size，它是InnoDB引擎中最重要的引數之一，對InnoDB的效能有決定性的影響。預設的設定只有8MB，使用預設值時InnoDB的效能很差，遠遠不能滿足生產的需求。在只有InnoDB儲存引擎的資料庫伺服器上面，可以將其設定為60%~80%的記憶體。如果你有足夠的記憶體，可以將資料量全部放入記憶體，這時才能達到最佳效能。記憶體是影響MySQL伺服器效能好壞的最關鍵指標，而MySQL的InnoDB引擎中的innodb_buffer_pool_size引數可以設定為實體記憶體的70%~80%。因為把資料放在記憶體中比存放在磁碟中要快得多，何樂而不為呢？但是話又說回來，也不能隨意分配記憶體，要根據系統的整體情況來做個判斷。Linux伺服器中的記憶體主要被四類事務所消耗：核心、檔案系統快取記憶體、應用程式程式、特定預留的共享記憶體。

上面兩個因素是從硬體角度上看的，也就是說因為業務的增長，致使硬體遇到了瓶頸，而另一種常見的情況是，大量的慢SQL是導致效能低下的首要“元凶”，在這種情況下，優化慢SQL是關鍵，在上線前，應有專門的DBA來稽核開發寫的SQL語句，通過這樣的稽核，可避免線上遇到問題。優化一條SQL語句在某種情況下，比增添1條記憶體管用得多。例如：

1	SELECT * FROM t WHERE  id >= '10'  and  id <= '30' ;

這條語句乍一看沒什麼問題，可執行後馬上就記錄在慢日誌裡了，這是怎麼回事？細心的DBA可能會發現id是int數值整型，由於加上了引號（''），轉化為字元型，於是造成了不能使用索引。

而如何分辨是硬體效能上遇到了瓶頸，還是SQL自身的問題？這個就要通過日常的監控來確定了，比如，每天早上發一封慢日誌郵件來檢視SQL的情況，自然就對業務的SQL較為熟悉，再對比最近二到三天內郵件上的慢SQL，這樣很容易找出存在的問題。假設某個SQL在昨天慢日誌裡沒有出現，而在今天卻出現了，那麼嘗試著在備機上執行下，如果很快就得到了執行結果，那麼就不是SQL的問題，而是業務增長造成的硬體瓶頸。

第二部分 系統效能評估標準 

對於MySQLDBA來說，系統效能的實時檢測和評估是其需要長期面對的問題，包括上線前各方面的效能測試及上線後整體效能評估，以及隨時掌握系統的執行狀態是否健康等，對於資料庫伺服器而言這些工作非常重要。

在作業系統層面影響Linux伺服器效能的因素主要就是伺服器CPU、記憶體、磁碟I/O、網路I/O，以及Linux系統本身的核心。

3.1 CPU效能指標

從整體上來說，CPU效能指標比較多，因為CPU處理的事物也比較多。常見的指標如下：

• CPU使用率：這可能是最直接的指標了，它表示每個處理器的整體使用率。如果在持續一段時間裡CPU的使用率大於80%，這就可能表明CPU出現了瓶頸。

• %us：應用程式（使用者空間）表示使用者應用程式所花費的CPU百分比，包括Nice時間。如果使用者時間值很高，表明系統正在執行實際的工作。

• %sy：系統（核心空間）表示核心操作所花費的CPU百分比，包括中斷。系統時間值持續很高表明網路或驅動器堆疊可能存在瓶頸。通常，系統只會花費很少時間在核心時間上。

• %wa：I/O等待 等待I/O操作所需的CPU時間總和，系統不應該花費過多的時間等待I/O操作，否則你應該檢查一下I/O子系統各方面的效能。

• %id：空閒時間 表示CPU空閒的百分比。這個值越大表明系統CPU的負荷越小。

• %ni：Nice時間 表示花費在執行renicing（改變程式的執行順序和優先順序）程式的CPU百分比。

3.2 記憶體效能指標

• 空閒記憶體與其他作業系統相比，在Linux系統中不必過分在意空閒記憶體值。因為Linux核心會將大量未使用的記憶體分配給檔案系統來快取資料。

• 交換空間使用 這個值表示已使用的交換空間大小，相當於Windows系統中的虛擬記憶體。交換空間的使用只能告訴你Linux在管理記憶體上是多麼的有效。要想確定記憶體是否存在瓶頸，需要用到SwapIn/Out。如果SwapIn/Out長時間保持在每秒鐘超過200~300頁，可能表示記憶體存在瓶頸。

3.3 磁碟效能指標

• 磁碟I/O等待 CPU在等待I/O操作時所花費的時間。如果這個值持續很高，很可能表示I/O存在瓶頸。

• 佇列平均長度 I/O請求的數量。通常硬碟佇列值為2~3時最佳；過高可能表示硬碟I/O存在瓶頸。

• 平均等待時間 I/O請求服務所花費的平均時間。等待時間包括實際I/O操作的時間和在I/O佇列中等待的時間。單位為毫秒（ms）。

• 每秒鐘傳輸的數量 表示每秒鐘執行了多少次I/O操作（包括讀取和寫入）。與每秒鐘傳輸位元組數結合可以幫助確定系統平均傳輸值大小。平均傳輸值通常要與硬碟子系統的條帶大小一致。

• 每秒鐘讀寫塊的數量 這個指標表示每秒鐘讀寫塊的數量，在2.6.XX核心中塊的大小為1024位元組，早期的核心可以有不同的塊大小，其範圍可從512位元組到4KB。

• 每秒鐘讀寫位元組的數量 表示塊裝置讀寫的實際資料的數量，單位為KB。

 

本文內容參考《賀春暘. MySQL管理之道：效能調優、高可用與監控（第2版）》。