什么影響了MySQL性能

影響性能的一些常見因素

1. 服務器硬件
2. 服務器系統
3. 數據庫存儲引擎
   • MyISAM：可以很好的利用內存，但不支持事務，表級鎖
   • InnoDB：事務級存儲引擎，完美支持行級鎖以及事務ACID特性
4. 數據庫參數配置
5. 數據庫表結構設計和SQL語句執行效率
6. 數據庫的版本

CPU資源和可用內存大小

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

選擇CPU時需要考慮的點：

• 我們的應用是否是CPU密集型？如果是CPU密集型，則需要計算能力強的CPU，即頻率高的
• 我們的應用并發量如何？如果并發量大的話，就需要選擇更多核心的CPU來提高吞吐量。若選擇多核心的CPU的話，最好使用MySQL5.6以上的版本，因為低版本對多核心的CPU支持得不是很好
• 注：MySQL目前不支持多CPU對同一SQL進行并發處理。
• 注：不要在64位的CPU上使用32位的操作系統

內存：

• 容量越多越好，但對性能影響有限，因為并不能無限的增加性能
• 主頻則選擇服務器主板可支持的最高頻率最好

傳統的機械磁盤

使用傳統機器磁盤。機械硬盤讀取數據的過程：

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

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

所以選擇機械硬盤主要參考以下幾個點：

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

使用RAID增強傳統機器磁盤的性能

RAID：

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

常用的RAID模式 - RAID 0：

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

RAID 0 模式結構圖：

常用的RAID模式 - RAID 1：

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

RAID 1 模式結構圖：

常用的RAID模式 - RAID 5：

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

RAID 5 模式結構圖：

常用的RAID模式 - RAID 10，適合數據庫的模式：

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

RAID 10 模式結構圖：

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

使用固態存儲SSD和PCIe卡

相比機械磁盤固態磁盤有更好的隨機讀寫性能，相比機械磁盤固態磁盤有更好的并發支持，相比機械磁盤固態磁盤更容易損壞

固態磁盤的特點：

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

PCIe卡的特點：

• 無法使用SATA接口，需要獨特的驅動和配置
• 價格相當于SSD貴，性能比SSD高

固態磁盤的使用場景：

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

使用網絡存儲NAS和SAN

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

SAN設備通過光纖鏈接到服務器，設備通過塊接口訪問，服務器可以將其當做硬盤使用：

SAN的優缺點：

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

NAS設備使用網絡鏈接，通過基于文件的協議如NFS或SMB來訪問。

網絡存儲適合的場景：

• 數據庫備份

網絡對性能的影響：

• 延遲、吞吐量/帶寬、網絡質量（丟包）
• 建議：
  • 采用高性能和高帶寬的網絡接口設備和交換機
  • 對多個網卡進行綁定，增強可用性和帶寬
  • 盡可能的進行網絡隔離，不要把數據庫暴露到外網上

總結：服務器硬件對性能的影響

CPU：

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

內存：

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

I/O子系統：

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

操作系統對性能的影響-MySQL適合的操作系統

MySQL在不同操作系統需要注意的事項：

• Windows下對數據庫的庫名表名沒有大小寫敏感，但是Unix/Linux上卻是大小寫敏感的，所以盡量統一小寫
• 使用FreeBSD的話，需要使用最新版本，因為老版本對MySQL的支持不是很好
• Solaris系統上支持MySQL，它以穩定性著稱

CentOS系統參數優化

內核相關參數（/etc/sysctl.conf）：

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

參考：

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

Linux系統內存交換分區：

• 在Linux系統安裝時都會有一個特殊的磁盤分區，稱之為系統交換分區，即swap分區
• 當操作系統因為沒有足夠的內存時就會將一些虛擬內存寫到磁盤的交換分區中，這樣就會發生內存交換

在MySQL服務器上是否要使用交換分區有一些爭議：

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

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

• * soft nofile 65535
• * hard nofile 65535

說明：

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

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

磁盤調度策略（/sys/block/vda/queue/scheduler）：

• 在Linux上磁盤隊列調度的算法決定了塊設備的請求發送到底層設備的順序，默認使用cfq這種完全公平隊列
• 這種cfq一般用于桌面級的系統，但是用在跑MySQL服務的系統就不太合適了，因為在MySQL的工作負載下，cfq會在隊列中插入一些不必要的請求導致響應時間比較差

除了默認的cfq策略外還可以選擇以下幾種策略：

1. noop（電梯式調度策略）：

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

2. deadline（介質時間調度策略）：

   Deadline確保了在一個截至時間內服務請求，這個截至時間是可調整的，而默認讀期限短于寫期限。這樣就防止了寫操作因為不能被讀取而餓死的現象。Deadline對數據庫類應用是最好的選擇。

3. anticipatory（預料I/O調度策略）：

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

修改磁盤調度策略的命令，例如我將策略改為deadline：

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

文件系統對性能的影響

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

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

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

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

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

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