性能調優
- 選擇合适的CPU
- 記憶體的重要性
- 硬碟對資料庫性能的影響
-
- 傳統機械硬碟
- 固态硬碟
- 合理地設定RAID
-
- RAID 類型
-
- RAID 0
- RAID 1
- RAID 5
- RAID 10 和 RAID 01
- RAID 50
- 總結
- RAID Write Back功能
- RAID配置工具(待完善)
- 作業系統的選擇
- 不同的檔案系統對資料庫性能的影響
- 選擇合适的基準測試工具
-
- sysbench(待完善)
- mysql-tpcc(待完善)
将從以下幾個方面集中講解InnoDB存儲引擎的性能問題：
- 選擇合适的CPU
- 記憶體的重要性
- 硬碟對資料庫性能的影響
- 合理地設定RAID
- 作業系統的選擇也很重要
- 不同檔案系統對資料庫的影響
- 選擇合适的基準測試工具
選擇合适的CPU
目前資料庫的應用類型一般可分為兩大類：OLTP（Online Transaction Processing，線上事務處理）和OLAP（Online Analytical Processing，線上分析處理）
OLAP多用在資料倉庫或資料集市中，一般需要執行複雜的SQL語句來進行查詢
OLTP多用在日常的事物處理應用中，如銀行交易、線上商品交易、Blog、網絡遊戲等應用。相對于OLAP，資料庫的容量較小
InnoDB存儲引擎一般都應用于OLTP的資料庫應用，這種應用的特點如下：
- 使用者操作的并發量大
- 事務處理的時間一般比較短
- 查詢的語句較為簡單，一般都走索引
- 複雜的查詢較少
OLTP的資料庫應用本身對CPU的要求并不是很高，因為複雜的查詢可能需要執行比較、排序、連接配接等非常耗CPU的操作，這些操作在OLTP的資料庫應用中較少發生
可以說OLAP是CPU密集型的操作，而OLTP是IO密集型的操作
從InnoDB存儲引擎的設計架構上來看，其主要的背景操作都是在一個單獨的master thread中完成的，是以并不能很好地支援多核的應用
InnoDB1.0版本在各種測試下已經顯示出對多核CPU的處理性能的支援有了極大的提高
InnoDB1.2版本又支援多個purge線程，以及将重新整理操作從master thread中分離出來
是以，若CPU支援多核，InnoDB的版本應該選擇1.1或更高版本
另外，還可以通過修改參數
innodb_read_io_threads
和
innodb_write_io_threads
來增大IO的線程，這樣也能更充分有效地利用CPU的多核性能
在目前的MySQL資料庫版本中，一條SQL查詢語句隻能在一個CPU中工作，并不支援多CPU的處理
OLTP的資料庫應用操作一般都很簡單，是以對OLTP應用的影響并不是很大
但多個CPU或多核CPU對處理大并發量的請求還是會有幫助
記憶體的重要性
記憶體的大小是最能直接反映資料庫的性能
InnoDB存儲引擎 既 緩存資料，又 緩存索引，并且将它們緩存于一個很大的緩沖池中，即InnoDB Buffer Pool。是以，記憶體的大小直接影響了資料庫的性能
如何判斷目前資料庫的記憶體是否已經達到瓶頸了？
可以通過檢視目前伺服器的狀态，比較實體磁盤的讀取和記憶體讀取的比例來判斷緩沖池的命中率，通常InnoDB存儲引擎的緩沖池的命中率不應該小于99%
SHOW GLOBAL STATUS LIKE 'innodb%read%'
Variable_name,Value
"Innodb_buffer_pool_read_ahead_rnd","0"
"Innodb_buffer_pool_read_ahead","0"
"Innodb_buffer_pool_read_ahead_evicted","0"
"Innodb_buffer_pool_read_requests","188769811"
"Innodb_buffer_pool_reads","1182"
"Innodb_data_pending_reads","0"
"Innodb_data_read","19436544"
"Innodb_data_reads","1207"
"Innodb_pages_read","1182"
"Innodb_rows_read","140634457"
"Innodb_ready_for_write_lsn","333002131"
具體含義如下：
以下公式可以計算各種對緩沖池的操作
即使緩沖池的大小已經大于資料庫檔案的大小，這也并不意味着沒有磁盤操作
資料庫的緩沖池 隻是一個 用來存放 熱點的區域，背景的線程 還負責 将髒頁 異步地 寫入到磁盤
此外，每次事務送出時 還需要 将日志 寫入 重做日志檔案
硬碟對資料庫性能的影響
傳統機械硬碟
機械硬碟有兩個重要的名額：一個是尋道時間，另一個是轉速
目前伺服器機械硬碟的尋道時間已經能夠達到3ms，轉速為15 000RPM（rotate per minute）
傳統機械硬碟最大的問題在于讀寫磁頭，讀寫磁頭的設計 使硬碟 可以不再像 錄音帶一樣，隻能進行順序通路，而是可以随機通路
機械硬碟的通路 需要耗費 長時間的磁頭旋轉和定位 來查找，是以 順序通路的速度 要遠高于 随機通路
傳統關系資料庫的很多設計也都是在盡量充分地利用順序通路的特性
通常來說，可以将多塊機械硬碟組成RAID來提高資料庫的性能，也可以将資料檔案分布在不同硬碟上來達到通路負載的均衡
固态硬碟
固态硬碟，更準确地說是基于閃存的固态硬碟，内部由閃存（Flash Memory）組成
企業級應用一般使用固态硬碟，通過并聯多塊閃存來進一步提高資料傳輸的吞吐量
不同于傳統的機械硬碟，閃存是一個完全的電子裝置，沒有傳統機械硬碟的讀寫磁頭
固态硬碟不需要像傳統機械硬碟一樣，需要耗費大量時間的磁頭旋轉和定位來查找資料，是以固态硬碟可以提供一緻的随機通路時間
閃存中的資料是不可以更新的，隻能通過扇區（sector）的覆寫重寫，而在覆寫重寫之前，需要執行非常耗時的擦除（erase）操作
擦除操作 不能在 所含資料的扇區上 完成，而 需要在 删除整個 被稱為 擦除塊的基礎上 完成，這個擦除塊的尺寸 大于 扇區的大小，通常為128KB或者256KB
每個擦除塊 有擦寫次數 的 限制，對于資料庫應用，需要認真考慮固态硬碟在寫入方面存在的問題
閃存提供的 讀寫速度 是 非對稱的，讀取速度 要遠快于 寫入的速度，是以對于固态硬碟在資料庫中的應用，應該好好利用其讀取的性能，避免過多的寫入操作
由于閃存是一個完全的電子裝置，沒有讀寫磁頭等移動部件，是以固态硬碟有着較低的通路延時
當釋出一個讀寫請求時，固态硬碟的控制器 會把 I/O指令 從邏輯位址 映射成 實際的實體位址，寫操作 還需要 修改相應的映射表資訊。算上這些額外的開銷，固态硬碟的通路延時一般小于0.1ms左右
對于固态硬碟在InnoDB存儲引擎中的優化，可以增加
innodb_io_capacity
變量的值 達到 充分利用 固态硬碟帶來的高IOPS特性
在InnoSQL及InnoDB1.2版本中，可以選擇 關閉 鄰接頁的重新整理，同樣可以為資料庫的性能帶來一定效果的提升
還可以使用InnoSQL開發的L2 Cache解決方案，該解決方案 可以 充分利用 固态硬碟的超高速随機讀取性能，在 記憶體緩沖池 和 傳統存儲層 之間 建立一層 基于閃存固态硬碟 的 二級緩沖池，以此來擴充緩沖池的容量，提高資料庫的性能
與基于磁盤的固态硬碟Cache類似的解決方案還有Facebook Flash Cache和bcache，隻不過它們是基于通用檔案系統的，對InnoDB存儲引擎本身的優化較少
合理地設定RAID
RAID 類型
RAID（Redundant Array of Independent Disks，獨立磁盤備援數組）的基本思想就是把多個相對便宜的硬碟組合起來，成為一個磁盤數組，使性能達到甚至超過一個價格昂貴、容量巨大的硬碟
由于将多個硬碟組合成為一個邏輯扇區，RAID看起來就像一個單獨的硬碟或邏輯存儲單元，是以作業系統隻會把它當作一個硬碟。
RAID的作用是：
- 增強資料內建度
- 增強容錯功能
- 增加處理量或容量
根據不同磁盤的組合方式，常見的RAID組合方式可分為RAID 0、RAID 1、RAID 5、RAID 10和RAID 50等
RAID 0
将 多個磁盤 合并成 一個大的磁盤，不會有備援，并行I/O，速度最快
RAID 0亦稱為 帶區集，它将多個磁盤并列起來，使之成為一個大磁盤
在存放資料時，其将資料按磁盤的個數進行分段，同時将這些資料寫進這些盤中
在所有的級别中，RAID 0的速度是最快的
但是RAID 0沒有備援功能，如果一個磁盤（實體）損壞，則所有的資料都會丢失
理論上，多磁盤的效能就等于（單一磁盤效能）×（磁盤數），但實際上受限于總線I/O瓶頸及其他因素的影響，RAID效能會随邊際遞減
也就是說，假設一個磁盤的效能是50MB/s，兩個磁盤的RAID 0效能約96MB/s，三個磁盤的RAID 0也許是130MB/s而不是150MB/s
RAID 1
兩組以上的 N個磁盤 互相作為鏡像，在一些多線程作業系統中能有很好的讀取速度，但寫入速度略有降低
除非 擁有 相同資料的 主磁盤與鏡像 同時 損壞，否則隻要一個磁盤正常即可維持運作，可靠性最高
RAID 1就是鏡像，其原理為 在主硬碟上存放資料的同時 也在 鏡像硬碟上寫相同的資料
當主硬碟（實體）損壞時，鏡像硬碟則代替主硬碟的工作
因為有鏡像硬碟做資料備份，是以RAID 1的資料安全性在所有的RAID級别上來說是最好的
但是，無論用多少磁盤作為RAID 1，僅算一個磁盤的容量，是所有RAID中磁盤使用率最低的一個級别
RAID 5
RAID 5是一種存儲性能、資料安全和存儲成本兼顧的存儲解決方案
它使用的是Disk Striping（硬碟分區）技術
RAID 5至少需要三個硬碟，RAID 5不對存儲的資料進行備份，而是把 資料 和 相對應的奇偶校驗資訊 存儲到 組成RAID 5的各個磁盤上，并且奇偶校驗資訊和相對應的資料分别存儲于不同的磁盤上
當RAID 5的一個磁盤資料發生損壞後，利用剩下的資料和相應的奇偶校驗資訊去恢複被損壞的資料
RAID 5可以了解為是RAID 0和RAID 1的折中方案
RAID 5可以為系統提供資料安全保障，但保障程度要比鏡像低 而 磁盤空間使用率要比鏡像高
RAID 5具有和RAID 0相近似的資料讀取速度，隻是多了一個奇偶校驗資訊，寫入資料的速度相當慢，若使用Write Back可以讓性能改善不少
同時，由于多個資料對應一個奇偶校驗資訊，RAID 5的磁盤空間使用率要比RAID 1高，存儲成本相對較低
- 問題：如果存儲了奇偶校驗資訊得disk壞了怎麼辦？
RAID 10 和 RAID 01
RAID 10是 先鏡像 再分區 資料，将所有硬碟分為兩組，視為RAID 0的最低組合，然後将這兩組各自視為RAID 1運作
RAID 10有着不錯的讀取速度，而且擁有比RAID 0更高的資料保護性
RAID 10巧妙地利用了RAID 0的速度 及 RAID 1的安全（保護）兩種特性，它的缺點是需要較多的硬碟，因為至少必須擁有四個以上的偶數硬碟才能使用
RAID 01則與RAID 10的程式相反，先 分區 再将 資料 鏡射到 兩組硬碟
RAID 01将所有的硬碟分為兩組，變成RAID 1的最低組合，而将兩組硬碟各自視為RAID 0運作
RAID 01比RAID 10有着更快的讀寫速度，不過也多了一些會讓整個硬碟組停止運轉的幾率，因為隻要同一組的硬碟全部損毀，RAID 01就會停止運作，而RAID 10可以在犧牲RAID 0的優勢下正常運作
RAID 50
RAID 50也被稱為鏡像陣列條帶，由至少六塊硬碟組成，像RAID 0一樣，資料 被分區成 條帶，在同一時間内向多塊磁盤寫入；像RAID 5一樣，也是以資料的校驗位來保證資料的安全，且校驗條帶均勻分布在各個磁盤上，其目的在于提高RAID 5的讀寫性能
總結
對于資料庫應用來說，RAID 10是最好的選擇，它同時兼顧了RAID 1和RAID 0的特性。但是，當一個磁盤失效時，性能可能會受到很大的影響，因為條帶（strip）會成為瓶頸
RAID Write Back功能
RAID Write Back功能 是指 RAID控制器 能夠 将 寫入的資料 放入 自身的緩存中，并把 它們 安排到後面再執行
這樣做的好處是，不用等待實體磁盤實際寫入的完成，是以寫入變得更快了
對于資料庫來說，這顯得十分重要，例如，對重做日志的寫入，在将
sync_binlog
設為1的情況下二進制日志的寫入、髒頁的重新整理等都可以使性能得到明顯的提升
當作業系統或資料庫關機時，Write Back功能可能會破壞資料庫的資料
這是由于已經寫入的資料庫可能還在RAID卡的緩存中，資料可能并沒有完全寫入磁盤，而這時故障發生了
為了解決這個問題，目前大部分的硬體RAID卡都提供了電池備份單元（BBU，Battery Backup Unit），是以可以放心地開啟Write Back的功能
如果沒有啟用Write Back功能，那麼在RAID卡設定中顯示的就是Write Through
Write Through沒有緩沖寫入，是以寫入性能可能不是很好，但它卻是最安全的寫入
即使使用者開啟了Write Back功能，RAID卡也可能隻是在Write Through模式下工作
這是因為安全使用Write Back的前提是RAID卡有電池備份單元
為了確定電池的有效性，RAID卡會定期檢查電池狀态，并在電池電量不足時對其進行充電，在充電的這段時間内會将Write Back功能切換為最為安全的Write Through
可以在沒有電池備份單元的情況下強制啟用Write Back功能，也可以在電池充電時強制使用Write Back功能，隻是寫入是不安全的。應該非常确信這點，否則不應該在沒有電池備份單元的情況下啟用Write Back
RAID配置工具(待完善)
作業系統的選擇
Linux是MySQL資料庫伺服器中最常使用的作業系統
與其他作業系統不同的是Linux有着衆多的發行版本，每個使用者的偏好可能不盡相同
在将Linux作業系統作為資料庫伺服器時需要考慮更多的是作業系統的穩定性，而不是新特性
除了Linux作業系統外，FreeBSD也是另一個常見的優秀作業系統
之前版本的FreeBSD對MySQL資料庫支援得不是很好，需要選擇單獨的線程庫進行手動編譯，但是新版本的FreeBSD對MySQL資料庫的支援已經好了很多，直接下載下傳二進制安裝包即可
Solaris也是非常不錯的作業系統，之前是基于SPARC硬體的作業系統，現在已經移植到了X86平台上
Solaris是高性能、高可靠性的作業系統，同時其提供的ZFS檔案系統非常适合MySQL的資料庫應用。如果需要，使用者可以嘗試它的開源版本Open Solaris
不同的檔案系統對資料庫性能的影響
每個作業系統都預設支援一種檔案系統并推薦使用者使用，雖然不同特性的檔案系統有很多，但是在實際使用過程中從未感覺到檔案系統的性能差異有多大
檔案系統可提供的功能 也許是 DBA需要關注的，例如ZFS檔案系統本身就可以支援快照，是以就不需要LVM這樣的邏輯卷管理工具。此外，可能還需要知道mount的參數，這些參數在每個檔案系統中可能有所不同
選擇合适的基準測試工具
sysbench(待完善)
sysbench是一個子產品化的、跨平台的多線程基準測試工具，主要用于測試各種不同系統參數下的資料庫負載情況。它主要包括以下幾種測試方式：
- CPU性能
- 磁盤IO性能
- 排程程式性能
- 記憶體配置設定及傳輸速度
- POSIX線程性能
- 資料庫OLTP基準測試
sysbench的官網位址是：http://sysbench.sourceforge.net，可以從該位址下載下傳最新版本的sysbench工具，然後進行編譯和安裝
mysql-tpcc(待完善)
TPC（Transaction Processing Performance Council，事務處理性能協會）是一個 用來 評價 大型資料庫系統軟硬體性能的非盈利組織
TPC-C是TPC協會制定的，用來測試典型的複雜OLTP（線上事務處理）系統的性能
TPC-C用3NF（第三範式） 虛拟實作了 一家倉庫銷售供應商公司，擁有一批分布在不同地方的倉庫和地區分公司
當公司業務擴大時，将建立新的倉庫和地區分公司
通常每個倉庫供貨覆寫10家地區分公司，每個地區分公司服務3000名客戶
公司共有100 000種商品，分别儲存在各個倉庫中
該系統包含了庫存管理、銷售、分發産品、付款、訂單查詢等一系列操作，一共包含了9個基本關系，如下：
TPC-C的性能度量機關是tpmC，tpm是transaction per minute的縮寫，C代表TPC的C基準測試
該值越大，代表事務處理的性能越高。
tpcc-mysql是開源的TPC-C測試工具，該測試工具完全遵守TPC-C的标準。其官方網站為：https://code.launchpad.net/percona-dev/perconatools/tpcc-mysql
tpcc-mysql由以下兩個工具組成：
- tpcc_load：根據倉庫數量，生成9張表中的資料
- tpcc_start：根據不同選項進行TPC-C測試