MySQL(InnoDB剖析):性能調優之（CPU的選擇、記憶體的重要性、磁盤對資料庫性能的影響）

原文連結

一、CPU的選擇

資料庫的應用類型可分為兩大類：OLTP（Online Transaction Processing，線上事務處理）和OLAP（Online analytical Processing，線上分析處理）。這是兩種截然不同的資料庫應用：
OLAP多用在資料倉庫或資料集市中，一般需要執行複雜的SQL語句來進行查詢
OLTP多用在日常的事物處理應用中，如銀行交易、線上商品交易、Blog、網絡遊戲等應用。相對于OLAP，資料庫的容量較小
           

InnoDB存儲引擎一般都應用于OLTP的資料庫應用，這種應用的特點如下：

• 使用者操作的并發量大
• 事務處理的時間一般比較短
• 查詢的語句較為簡單,一般都走索引
• 複雜的查詢較少

可以看出，OLTP的資料庫應用本身對CPU的要求并不是很高，因為複雜的查詢可能需要執行比較、排序、連接配接等非常耗CPU的操作，這些操作在OLTP的資料庫應用中較少發生。是以，可以說OLAP是CPU密集型的操作，而OLTP是IO密集型的操作建議在采購裝置時，将更多的注意力放在提高IO的配置上

此外，為了獲得更多記憶體的支援，使用者采購的CPU必須支援64位，否則無法支援64位作業系統的安裝。是以，為新的應用選擇64位的CPU是必要的前提。現在4核的CPU已經非常普遍，如今 Intel和AMD又相繼推出了8核的CPU，将來随着作業系統的更新我們還可能看到128核的CPU，這都需要資料庫更好地對其提供支援。

從 InnoDB存儲引擎的設計架構上來看：

其主要的背景操作都是在一個單獨的master thread中完成的，是以并不能很好地支援多核的應用

當然，開源社群已經通過多種方法來改變這種局面，而InnoDB1.0版本在各種測試下已經顯示出對多核CPU的處理性能的支援有了極大的提高

而 InnoDB1.2版本又支援多個purge線程，以及将重新整理操作從 master thread中分離出來

是以，若使用者的CPU支援多核，InnoDB的版本應該選擇1.1或更高版本。另外，如果CPU是多核的，可以通過修改參數 innodb_read_io_threads和innodb_write_io_threads來增大IO的線程，這樣也能更充分有效地利用CPU的多核性能

在目前的 MySQL資料庫版本中，一條SQL查詢語句隻能在一個CPU中工作，并不支援多CPU的處理。OLTP的資料庫應用操作一般都很簡單，是以對OLTP應用的影響并不是很大。但是，多個CPU或多核CPU對處理大并發量的請求還是會有幫助

二、記憶體的重要性

記憶體的大小是最能直接反映資料庫的性能。通過之前的介紹，已經了解到InnoDB存儲引擎既緩存資料，又緩存索引，并且将它們緩存于一個很大的緩沖池中，即InnoDB Buffer Pool。是以，記憶體的大小直接影響了資料庫的性能。

性能測試

Percona公司的CTO Vadin對此做了一次測試，以此反映記憶體的重要性，結果如下圖所示：

在上述測試中，資料和索引總大小為18GB，然後将緩沖池的大小分别設為2GB、4GB、6GB、8GB、10GB、12GB、14GB、16GB、18GB、20GB、22GB，再進行sysbench的測試

可以發現：

• 随着緩沖池的增大，測試結果TPS(Transaction Per Second)會線性增長
• 當緩沖池增大到20GB和2GB時，資料庫的性能有了極大的提高，因為這時緩沖池的大小已經大于資料檔案本身的大小，所有對資料檔案的操作都可以在記憶體中進行。是以這時的性能應該是最優的，再調大緩沖池并不能再提高資料庫的性能

是以，應該在開發應用前預估“活躍”資料庫的大小是多少，并以此确定資料庫伺服器記憶體的大小。當然，要使用更多的記憶體還必須使用64位的作業系統

如何判斷目前資料庫的記憶體是否已經達到瓶頸了呢？可以通過檢視目前伺服器的狀态，比較實體磁盤的讀取和記憶體讀取的比例來判斷緩沖池的命中率，通常 InnoDB存儲引擎的緩沖池的命中率不應該小于99%，如：

上述參數的具體含義如下所示：

可以通過以下公式計算InnoDB緩存池的命中率：

從上面例子可以看出，緩沖池命中率=167051313/(167051313+129236+0)=99.92%

如果命中率太低，則應考慮擴充記憶體，增加innodb_buffer_pool_size的值。innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * N

其中N為整數，如果innodb_buffer_pool_size的值不是此公式的值，MySQL會自動調整

即使緩沖池的大小已經大于資料庫檔案的大小，這也并不意味着沒有磁盤操作。資料庫的緩沖池隻是一個用來存放熱點的區域，背景的線程還負責将髒頁異步地寫入到磁盤。此外，每次事務送出時還需要将日志寫入重做日志檔案

三、磁盤對資料庫性能的影響

• 傳統機械硬碟

  目前大多數資料庫使用的都是傳統的機械硬碟。機械硬碟的技術目前已非常成熟，在伺服器領域一般使用SAS或SATA接口的硬碟。伺服器機械硬碟開始向小型化轉型，目前大部分使用25寸的SAS機械硬碟。

  機械硬碟有兩個重要的名額：一個是尋道時間，另一個是轉速。目前伺服器機械硬碟的尋道時間已經能夠達到3ms，轉速為15000RM(rotate per minute)。傳統機械硬碟最大的問題在于讀寫磁頭，讀寫磁頭的設計使硬碟可以不再像錄音帶一樣，隻能進行順序通路，而是可以随機通路。但是，機械硬碟的通路需要耗費長時間的磁頭旋轉和定位來查找，是以順序通路的速度要遠高于随機通路。傳統關系資料庫的很多設計也都是在盡量充分地利用順序通路的特性

  通常來說，可以将多塊機械硬碟組成RAID來提高資料庫的性能，也可以将資料檔案分布在不同硬碟上來達到通路負載的均衡。

• 固态硬碟

  固态硬碟，更準确地說是基于閃存的固态硬碟，是近幾年出現的一種新的儲存設備，其内部由閃存( Flash Memory)組成。因為閃存的低延遲性、低功耗，以及防震性，閃存裝置已在移動裝置上得到了廣泛的應用。企業級應用一般使用固态硬碟，通過并聯多塊閃存來進一步提高資料傳輸的吞吐量。傳統的存儲服務提供商EMC公司已經開始提供基于閃存的固态硬碟的TB級别存儲解決方案。資料庫廠商 Oracle公司最近也開始提供綁定固态硬碟的 Exadata伺服器。

  不同于傳統的機械硬碟，閃存是一個完全的電子裝置，沒有傳統機械硬碟的讀寫磁頭。是以，固态硬碟不需要像傳統機械硬碟一樣,需要耗費大量時間的磁頭旋轉和定位來查找資料，是以固态硬碟可以提供一緻的随機通路時間。固态硬碟這種對資料的快速讀寫和定位特性是值得研究的。

  另一方面，閃存中的資料是不可以更新的，隻能通過扇區(sector)的覆寫重寫，而在覆寫重寫之前，需要執行非常耗時的擦除(erase)操作。擦除操作不能在所含資料的扇區上完成，而需要在删除整個被稱為擦除塊的基礎上完成，這個擦除塊的尺寸大于扇區的大小，通常為128KB或者256KB。此外，每個擦除塊有擦寫次數的限制。已經有一些算法來解決這個問題。但是對于資料庫應用，需要認真考慮固态硬碟在寫入方面存在的問題

  因為存在上述寫人方面的問題，閃存提供的讀寫速度是非對稱的。讀取速度要遠快于寫人的速度，是以對于固态硬碟在資料庫中的應用，應該好好利用其讀取的性能，避免過多的寫入操作

  下圖顯示了一個雙通道的固态硬碟架構，通過支援4路的閃存交叉存儲來降低固态硬碟的通路延時，同時增大并發的讀寫操作。通過進一步增加通道的數量，固态硬碟的性能可以線性地提高,例如我們常見的 Intel X25M固态硬碟就是10通道的固态硬碟

  

由于閃存是一個完全的電子裝置，沒有讀寫磁頭等移動部件，是以固态硬碟有着較低的通路延時。當主機釋出一個讀寫請求時，固态硬碟的控制器會把IO指令從邏輯位址映射成實際的實體位址，寫操作還需要修改相應的映射表資訊。算上這些額外的開銷，固态硬碟的通路延時一般小于0.1ms左右。下圖顯示了傳統機械硬碟、 記憶體、固态硬碟的随機通路延時之間的比較

對于固态硬碟在 InnoDB存儲引擎中的優化，可以增加innodb_io_capacity變量的值達到充分利用固态硬碟帶來的高IOPS特性。不過這需要使用者根據自己的應用進行有針對性的調整。在 InnoSQL及 InnoDB1.2版本中，可以選擇關閉鄰接頁的重新整理，同樣可以為資料庫的性能帶來一定效果的提升

此外，還可以使用 InnoSQL開發的L2 Cache解決方案，該解決方案可以充分利用固态硬碟的超高速随機讀取性能，在記憶體緩沖池和傳統存儲層之間建立一層基于閃存固态硬碟的二級緩沖池，以此來擴充緩沖池的容量，提高資料庫的性能。與基于磁盤的固态硬碟 Cache類似的解決方案還有 Facebook Flash Cache和 bcache，隻不過它們是基于通用檔案系統的，對 InnoDB存儲引擎本身的優化較少