什么影響了MySQL性能

影響性能的一些常見因素

1. 服務(wù)器硬件
2. 服務(wù)器系統(tǒng)
3. 數(shù)據(jù)庫存儲引擎
   • MyISAM：可以很好的利用內(nèi)存，但不支持事務(wù)，表級鎖
   • InnoDB：事務(wù)級存儲引擎，完美支持行級鎖以及事務(wù)ACID特性
4. 數(shù)據(jù)庫參數(shù)配置
5. 數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計和SQL語句執(zhí)行效率
6. 數(shù)據(jù)庫的版本

CPU資源和可用內(nèi)存大小

在服務(wù)器硬件中，最容易影響數(shù)據(jù)庫性能的是CPU資源和可用內(nèi)存大小以及I/O。

選擇CPU時需要考慮的點(diǎn)：

• 我們的應(yīng)用是否是CPU密集型？如果是CPU密集型，則需要計算能力強(qiáng)的CPU，即頻率高的
• 我們的應(yīng)用并發(fā)量如何？如果并發(fā)量大的話，就需要選擇更多核心的CPU來提高吞吐量。若選擇多核心的CPU的話，最好使用MySQL5.6以上的版本，因?yàn)榈桶姹緦Χ嗪诵牡腃PU支持得不是很好
• 注：MySQL目前不支持多CPU對同一SQL進(jìn)行并發(fā)處理。
• 注：不要在64位的CPU上使用32位的操作系統(tǒng)

內(nèi)存：

• 容量越多越好，但對性能影響有限，因?yàn)椴⒉荒軣o限的增加性能
• 主頻則選擇服務(wù)器主板可支持的最高頻率最好

傳統(tǒng)的機(jī)械磁盤

使用傳統(tǒng)機(jī)器磁盤。機(jī)械硬盤讀取數(shù)據(jù)的過程：

1. 移動磁頭到磁盤表面上的正確位置
2. 等待磁盤旋轉(zhuǎn)，使得所需的數(shù)據(jù)在磁頭之下
3. 等待磁盤旋轉(zhuǎn)過去，所有所需的數(shù)據(jù)都被磁頭讀出

注：第一步+第二部=磁盤的訪問時間。第三步消耗的時間=磁盤的傳輸速度

所以選擇機(jī)械硬盤主要參考以下幾個點(diǎn)：

1. 存儲容量
2. 傳輸速度
3. 訪問時間
4. 主軸轉(zhuǎn)速
5. 物理尺寸

使用RAID增強(qiáng)傳統(tǒng)機(jī)器磁盤的性能

RAID：

• RAID是磁盤冗余隊列的簡稱（Redundant Arrays of Independent Disks）
• 簡單來說RAID的作用就是可以把多個容量較小的磁盤，組成一組容量更大的磁盤，并提供數(shù)據(jù)冗余來保證數(shù)據(jù)完整性的技術(shù)

常用的RAID模式 - RAID 0：

RAID 0是最早出現(xiàn)的RAID模式,也稱之為數(shù)據(jù)條帶。是組建磁盤陣列中最簡單的一種形式,只需要2塊以上的硬盤即可,成本低,可以提高整個磁盤的性能和吞吐量。 RAID 0沒有提供冗余或錯誤修復(fù)能力,但是實(shí)現(xiàn)成本是最低的

RAID 0 模式結(jié)構(gòu)圖：

常用的RAID模式 - RAID 1：

RAID 1又稱磁盤鏡像,原理是把一個磁盤的數(shù)據(jù)鏡像到另一個磁盤上,也就是說數(shù)據(jù)在寫入一塊磁盤的同時會在另一塊閑置的磁盤上生成鏡像文件,在不影響性能情況下最大限度的保證系統(tǒng)的可靠性和可修復(fù)性。

RAID 1 模式結(jié)構(gòu)圖：

常用的RAID模式 - RAID 5：

RAID 5又稱之為分布式奇偶校驗(yàn)磁盤陣列通過分布式奇偶校驗(yàn)塊把數(shù)據(jù)分散到多個磁盤.上這樣如果任何一個盤數(shù)據(jù)失效,都可以從奇偶校驗(yàn)塊中重建。但是如果兩塊磁盤失效,則整個卷的數(shù)據(jù)都無法恢復(fù)。

RAID 5 模式結(jié)構(gòu)圖：

常用的RAID模式 - RAID 10，適合數(shù)據(jù)庫的模式：

RAID 10又稱分片的鏡像它是對磁盤先做RAID 1之后對兩組RAID 1的磁盤再做RAID 0 ,所以對讀寫都有良好的性能，相對于RAID 5重建起來更簡單,速度也更快。

RAID 10 模式結(jié)構(gòu)圖：

RAID級別的選擇，可以參考下表：

使用固態(tài)存儲SSD和PCIe卡

相比機(jī)械磁盤固態(tài)磁盤有更好的隨機(jī)讀寫性能，相比機(jī)械磁盤固態(tài)磁盤有更好的并發(fā)支持，相比機(jī)械磁盤固態(tài)磁盤更容易損壞

固態(tài)磁盤的特點(diǎn)：

• 使用SATA接口，可以替換傳統(tǒng)磁盤而不需要任何改變
• SATA接口的SSD同樣支持RAID技術(shù)

PCIe卡的特點(diǎn)：

• 無法使用SATA接口，需要獨(dú)特的驅(qū)動和配置
• 價格相當(dāng)于SSD貴，性能比SSD高

固態(tài)磁盤的使用場景：

• 使用于存在大量隨機(jī)I/O的場景
• 解決單線程負(fù)載的I/O瓶頸

使用網(wǎng)絡(luò)存儲NAS和SAN

SAN（Storage Area Network）和NAS（Network-Attached Storage）是兩種外部文件存儲設(shè)備加載到服務(wù)器上的方法

SAN設(shè)備通過光纖鏈接到服務(wù)器，設(shè)備通過塊接口訪問，服務(wù)器可以將其當(dāng)做硬盤使用：

SAN的優(yōu)缺點(diǎn)：

• 適合大量順序讀寫、讀寫I/O、 緩存、 I/O合并
• 隨機(jī)讀寫慢，不如本地RAID磁盤

NAS設(shè)備使用網(wǎng)絡(luò)鏈接，通過基于文件的協(xié)議如NFS或SMB來訪問。

網(wǎng)絡(luò)存儲適合的場景：

• 數(shù)據(jù)庫備份

網(wǎng)絡(luò)對性能的影響：

• 延遲、吞吐量/帶寬、網(wǎng)絡(luò)質(zhì)量（丟包）
• 建議：
  • 采用高性能和高帶寬的網(wǎng)絡(luò)接口設(shè)備和交換機(jī)
  • 對多個網(wǎng)卡進(jìn)行綁定，增強(qiáng)可用性和帶寬
  • 盡可能的進(jìn)行網(wǎng)絡(luò)隔離，不要把數(shù)據(jù)庫暴露到外網(wǎng)上

總結(jié)：服務(wù)器硬件對性能的影響

CPU：

• 64為的CPU一定要工作在64位的系統(tǒng)下
• 對于并發(fā)比較高的場景CPU的數(shù)量比頻率終于
• 對于CPU密集型場景和復(fù)雜SQL則頻率越高越好

內(nèi)存：

• 選擇主板所能支持的最高頻率的內(nèi)存
• 內(nèi)存的大小對性能很重要，所以盡可能的大

I/O子系統(tǒng)：

• PCIe -> SSD -> Raid10 -> 磁盤 -> SAN

操作系統(tǒng)對性能的影響-MySQL適合的操作系統(tǒng)

MySQL在不同操作系統(tǒng)需要注意的事項：

• Windows下對數(shù)據(jù)庫的庫名表名沒有大小寫敏感，但是Unix/Linux上卻是大小寫敏感的，所以盡量統(tǒng)一小寫
• 使用FreeBSD的話，需要使用最新版本，因?yàn)槔习姹緦ySQL的支持不是很好
• Solaris系統(tǒng)上支持MySQL，它以穩(wěn)定性著稱

CentOS系統(tǒng)參數(shù)優(yōu)化

內(nèi)核相關(guān)參數(shù)（/etc/sysctl.conf）：

• 設(shè)置最大監(jiān)聽隊列的長度：net.core.somaxconn=65535
• 每個網(wǎng)絡(luò)接口接收數(shù)據(jù)包的速率比內(nèi)核處理這些包的速率快時，允許送到隊列的數(shù)據(jù)包的最大數(shù)目，即完成連接上限：net.core.netdev_max_backlog=65535
• 指定所能接受SYN同步包的最大客戶端數(shù)量，即半連接上限：net.ipv4.tcp_max_syn_backlog=65535
• 如果套接字由本端要求關(guān)閉，這個參數(shù)決定了它保持在FIN-WAIT-2狀態(tài)的時間：net.ipv4.tcp_fin_timeout=10
• 開啟重用。允許將TIME-WAIT sockets重新用于新的TCP連接，默認(rèn)為0，表示關(guān)閉：net.ipv4.tcp_tw_reuse=1
• 開啟TCP連接中TIME-WAIT sockets的快速回收，默認(rèn)為0，表示關(guān)閉：net.ipv4.tcp_tw_recycle=1
• 默認(rèn)的TCP數(shù)據(jù)發(fā)送窗口大小(字節(jié))：net.core.wmem_default=87380
• 最大的TCP數(shù)據(jù)發(fā)送窗口大小(字節(jié))：net.core.wmem_max=16777216
• 表示接收套接字緩沖區(qū)大小的默認(rèn)值(字節(jié))：net.core.rmem_default=87380
• 表示接收套接字緩沖區(qū)大小的最大值(字節(jié))：net.core.rmem_max=16777216
• TCP發(fā)送keepalive探測消息的間隔時間（秒），用于確認(rèn)TCP連接是否有效：net.ipv4.tcp_keepalive_time=120
• 探測消息未獲得響應(yīng)時，重發(fā)該消息的間隔時間（秒）：net.ipv4.tcp_keepalive_intvl=30
• 在認(rèn)定TCP連接失效之前，最多發(fā)送多少個keepalive探測消息：net.ipv4.tcp_keepalive_probes=3
• Linux內(nèi)核參數(shù)中最重要的參數(shù)之一，用于定義單個共享內(nèi)存段的最大值：kernel.shmmax=429496295
  • 這個參數(shù)應(yīng)該設(shè)置的足夠大，以便能在一個共享內(nèi)存段下容納下整個的Innodb緩沖池的大小
  • 這個值的大小對于64位Linux系統(tǒng)，可取的最大值為物理內(nèi)存的-1byte。建議值為大于物理內(nèi)存的一半，一般取值大于Innodb緩沖池的大小即可，也可以取物理內(nèi)存-1byte
• 這個參數(shù)當(dāng)內(nèi)存不足時會對性能產(chǎn)生比較明顯的影響，這個參數(shù)就是告訴Linux內(nèi)核除非虛擬內(nèi)存完全滿了，否則不要使用交換分區(qū)：vm.swappiness=0

參考：

• https://www.cnblogs.com/DengGao/p/tcp_parameter.html

Linux系統(tǒng)內(nèi)存交換分區(qū)：

• 在Linux系統(tǒng)安裝時都會有一個特殊的磁盤分區(qū)，稱之為系統(tǒng)交換分區(qū)，即swap分區(qū)
• 當(dāng)操作系統(tǒng)因?yàn)闆]有足夠的內(nèi)存時就會將一些虛擬內(nèi)存寫到磁盤的交換分區(qū)中，這樣就會發(fā)生內(nèi)存交換

在MySQL服務(wù)器上是否要使用交換分區(qū)有一些爭議：

• 有些人認(rèn)為在MySQL服務(wù)所在的Linux系統(tǒng)上應(yīng)該完全禁用交換分區(qū)
• 有些人則認(rèn)為禁用交換分區(qū)會帶來以下風(fēng)險：
  • 降低操作系統(tǒng)性能
  • 容易造成內(nèi)存溢出、奔潰，或進(jìn)程都被操作系統(tǒng)kill掉
• 結(jié)論：所以在MySQL服務(wù)器上保留交換分區(qū)還是很有必要的，但是要控制何時使用交換分區(qū)，這時就需要使用到我們上文中所提到的vm.swappiness=0參數(shù)了

增加資源限制（/etc/security/limit.conf），這個文件實(shí)際上是Linux PAM也就是插入式認(rèn)證模塊的配置文件。通過我們會在文件末尾加入以下參數(shù)來控制打開文件數(shù)的限制：

• * soft nofile 65535
• * hard nofile 65535

說明：

• * 表示對所有用戶有效
• soft 指的是當(dāng)前系統(tǒng)生效的設(shè)置，對于同一資源，soft的值不能比hard高
• hard 表明系統(tǒng)中所能設(shè)定的最大值
• nofile 表示所限制的資源是打開文件的最大數(shù)目
• 65535 就是限制的次數(shù)

由于系統(tǒng)默認(rèn)的可打開文件句柄的數(shù)量是比較小的，所以一般我們都會將MySQL服務(wù)所在的操作系統(tǒng)的可打開的文件數(shù)量增加到65535個以保證可以打開足夠多的文件句柄。需要注意的是，這個文件的修改需要重啟系統(tǒng)才生效。

磁盤調(diào)度策略（/sys/block/vda/queue/scheduler）：

• 在Linux上磁盤隊列調(diào)度的算法決定了塊設(shè)備的請求發(fā)送到底層設(shè)備的順序，默認(rèn)使用cfq這種完全公平隊列
• 這種cfq一般用于桌面級的系統(tǒng)，但是用在跑MySQL服務(wù)的系統(tǒng)就不太合適了，因?yàn)樵贛ySQL的工作負(fù)載下，cfq會在隊列中插入一些不必要的請求導(dǎo)致響應(yīng)時間比較差

除了默認(rèn)的cfq策略外還可以選擇以下幾種策略：

1. noop（電梯式調(diào)度策略）：

   NOOP實(shí)現(xiàn)了一個FIFO隊列，它像電梯的工作方式一樣對I/O請求進(jìn)行組織，當(dāng)有一個新的請求到來時，它將請求合并到最近的請求之后，以此來保證請求同一個介質(zhì)。NOOP傾向于餓死讀而利于寫，因此NOOP對于閃存設(shè)備，RAM以及嵌入式是最好的選擇。

2. deadline（介質(zhì)時間調(diào)度策略）：

   Deadline確保了在一個截至?xí)r間內(nèi)服務(wù)請求，這個截至?xí)r間是可調(diào)整的，而默認(rèn)讀期限短于寫期限。這樣就防止了寫操作因?yàn)椴荒鼙蛔x取而餓死的現(xiàn)象。Deadline對數(shù)據(jù)庫類應(yīng)用是最好的選擇。

3. anticipatory（預(yù)料I/O調(diào)度策略）：

   本質(zhì)上與Deadline一樣，但在最后一次讀操作后，要等待6ms，才能繼續(xù)進(jìn)行對其他I/O請求進(jìn)行調(diào)度。它會在每個6ms中插入新的I/O操作，而會將一些小寫入流合并成一個大寫入流，用寫入延時換取最大的寫入吞吐量。AS適合于寫入較多的環(huán)境，比如文件服務(wù)器，AS對數(shù)據(jù)庫環(huán)境表現(xiàn)很差。

修改磁盤調(diào)度策略的命令，例如我將策略改為deadline：

echo deadline > /sys/block/vda/queue/scheduler

文件系統(tǒng)對性能的影響

服務(wù)器所使用的文件系統(tǒng)對服務(wù)器的I/O性能是有一定影響的，而文件系統(tǒng)的選擇十分依賴于操作系統(tǒng)，例如Windows下就只有FAT和NTFS可供選擇：

Linux下則有EXT3、EXT4、XFS，這三種文件系統(tǒng)都是具有日志功能的，這一點(diǎn)對于數(shù)據(jù)的安全性十分重要。其中XFS性能要比EXT3和EXT4高：

如果使用EXT3和EXT4的話，有幾個掛載參數(shù)可以了解一下。EXT3/4文件系統(tǒng)的掛載參數(shù)可以在/etc/fstab 文件中配置：

• 首先是data參數(shù)，有三個可選的值，這三個值代表了不同的日志策略：data=writeback | ordered | journal ，其中 writeback 是Innodb最好的選擇
  • writeback 意味著只有原數(shù)據(jù)寫入日志，原數(shù)據(jù)寫入和數(shù)據(jù)寫入并不是同步的，這是最快的一種配置，因?yàn)镮nnodb有自己的事務(wù)日志，所以選擇Innodb是最好的選擇
  • ordered選項只會記錄原數(shù)據(jù)，但提供了一些一致性的保證，在寫原數(shù)據(jù)之前的會先寫數(shù)據(jù)，使他們保持一致，這個選項比writeback 慢一些，但是如果出現(xiàn)崩潰呢更加安全
  • journal提供了原子日志的一種行為，在數(shù)據(jù)寫入到最終位置之前，將記錄到日志中，這個選項對Innodb來說是沒有必要的，在這三個選項中journal是最慢的一個

然后我們再來看看另外兩個重要的參數(shù)，在介紹這兩個參數(shù)之前，我們需要了解默認(rèn)情況下Linux操作系統(tǒng)會把文件訪問的時間atime做一個記錄，文件系統(tǒng)在文件被訪問、創(chuàng)建、修改等的時候記錄下了文件的一些時間戳。比如：文件創(chuàng)建時間、最近一次修改時間和最近一次訪問時間；這在絕大部分的場合都是沒有必要的。因?yàn)橄到y(tǒng)運(yùn)行的時候要訪問大量文件，如果能減少一些動作（比如減少時間戳的記錄次數(shù)等）將會顯著提高磁盤 IO 的效率、提升文件系統(tǒng)的性能。如果遇到機(jī)器IO負(fù)載高或是CPU WAIT高的情況時，可以嘗試使用noatime和nodiratime禁止記錄最近一次訪問時間戳。

所以 noatime 和 nodiratime，是用于禁止記錄文件的訪問時間和讀取目錄的時間的，禁用了這兩個時間的選項后，可以減少一些，寫的操作。系統(tǒng)在讀取文件和目錄時候，不必寫操作來記錄以上兩個時間。