影響MySQL性能的硬件因素

發(fā)布時(shí)間：2021-08-21 11:20 來(lái)源：ITPUB博客 閱讀：0 作者： 欄目： 數據庫 歡迎投稿：712375056

第一部分 磁盤(pán)I/O與內存

影響MySQL InnoDB引擎性能的最主要因素就是磁盤(pán)I/O，目前磁盤(pán)都是機械方式運作的，主要體現在讀寫(xiě)前尋找此道的過(guò)程中。磁盤(pán)自帶的讀寫(xiě)緩存大小，對于磁盤(pán)的讀寫(xiě)速度至關(guān)重要。讀寫(xiě)速度快的磁盤(pán)，通常都帶有較大的讀寫(xiě)緩存。磁盤(pán)的尋道過(guò)程是機械方式。決定了其隨機讀寫(xiě)速度將明顯低于順序讀寫(xiě)。在多進(jìn)程或多線(xiàn)程并發(fā)讀取磁盤(pán)的情況下，每次執行讀寫(xiě)操作，磁盤(pán)可能存在較大的偏移，磁盤(pán)尋址時(shí)間加大，將會(huì )導致磁盤(pán)I/O性能急劇下降。從很多新特性來(lái)看，幾乎都是圍繞著(zhù)如何充分利用內存，如何減少磁盤(pán)I/O來(lái)展開(kāi)的，例如：innodb_io_capactiy參數，可以加大每秒刷新臟頁(yè)的數量。因此在單塊磁盤(pán)遇到了I/O瓶頸時(shí)，可以把磁盤(pán)升級為RAID或SSD固體硬盤(pán)來(lái)提升性能，SSD固態(tài)硬盤(pán)的特點(diǎn)是：不用磁頭讀取數據，尋道時(shí)間幾乎為0，快速的隨機讀寫(xiě)，延遲極小，當然價(jià)格也很昂貴。目前在生產(chǎn)環(huán)境中主要采用RAID10、RAID5，對于數據讀寫(xiě)操作頻繁的表或數據庫，可以適當采用將數據分級存儲在SSD固態(tài)電子硬盤(pán)中的方式，速度會(huì )得到較大提升！

在Buffer_Pool緩沖池中，涉及的參數為innodb_buffer_pool_size，它是InnoDB引擎中最重要的參數之一，對InnoDB的性能有決定性的影響。默認的設置只有8MB，使用默認值時(shí)InnoDB的性能很差，遠遠不能滿(mǎn)足生產(chǎn)的需求。在只有InnoDB存儲引擎的數據庫服務(wù)器上面，可以將其設置為60%~80%的內存。如果你有足夠的內存，可以將數據量全部放入內存，這時(shí)才能達到最佳性能。內存是影響MySQL服務(wù)器性能好壞的最關(guān)鍵指標，而MySQL的InnoDB引擎中的innodb_buffer_pool_size參數可以設置為物理內存的70%~80%。因為把數據放在內存中比存放在磁盤(pán)中要快得多，何樂(lè )而不為呢？但是話(huà)又說(shuō)回來(lái)，也不能隨意分配內存，要根據系統的整體情況來(lái)做個(gè)判斷。Linux服務(wù)器中的內存主要被四類(lèi)事務(wù)所消耗：內核、文件系統高速緩存、應用程序進(jìn)程、特定預留的共享內存。

上面兩個(gè)因素是從硬件角度上看的，也就是說(shuō)因為業(yè)務(wù)的增長(cháng)，致使硬件遇到了瓶頸，而另一種常見(jiàn)的情況是，大量的慢SQL是導致性能低下的首要“元兇”，在這種情況下，優(yōu)化慢SQL是關(guān)鍵，在上線(xiàn)前，應有專(zhuān)門(mén)的DBA來(lái)審核開(kāi)發(fā)寫(xiě)的SQL語(yǔ)句，通過(guò)這樣的審核，可避免線(xiàn)上遇到問(wèn)題。優(yōu)化一條SQL語(yǔ)句在某種情況下，比增添1條內存管用得多。例如：

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

這條語(yǔ)句乍一看沒(méi)什么問(wèn)題，可運行后馬上就記錄在慢日志里了，這是怎么回事？細心的DBA可能會(huì )發(fā)現id是int數值整型，由于加上了引號（''），轉化為字符型，于是造成了不能使用索引。

而如何分辨是硬件性能上遇到了瓶頸，還是SQL自身的問(wèn)題？這個(gè)就要通過(guò)日常的監控來(lái)確定了，比如，每天早上發(fā)一封慢日志郵件來(lái)查看SQL的情況，自然就對業(yè)務(wù)的SQL較為熟悉，再對比最近二到三天內郵件上的慢SQL，這樣很容易找出存在的問(wèn)題。假設某個(gè)SQL在昨天慢日志里沒(méi)有出現，而在今天卻出現了，那么嘗試著(zhù)在備機上運行下，如果很快就得到了執行結果，那么就不是SQL的問(wèn)題，而是業(yè)務(wù)增長(cháng)造成的硬件瓶頸。

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

對于MySQLDBA來(lái)說(shuō)，系統性能的實(shí)時(shí)檢測和評估是其需要長(cháng)期面對的問(wèn)題，包括上線(xiàn)前各方面的性能測試及上線(xiàn)后整體性能評估，以及隨時(shí)掌握系統的運行狀態(tài)是否健康等，對于數據庫服務(wù)器而言這些工作非常重要。

在操作系統層面影響Linux服務(wù)器性能的因素主要就是服務(wù)器CPU、內存、磁盤(pán)I/O、網(wǎng)絡(luò )I/O，以及Linux系統本身的內核。

3.1 CPU性能指標

從整體上來(lái)說(shuō)，CPU性能指標比較多，因為CPU處理的事物也比較多。常見(jiàn)的指標如下：

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

• %us：應用程序（用戶(hù)空間）表示用戶(hù)應用進(jìn)程所花費的CPU百分比，包括Nice時(shí)間。如果用戶(hù)時(shí)間值很高，表明系統正在執行實(shí)際的工作。

• %sy：系統（內核空間）表示內核操作所花費的CPU百分比，包括中斷。系統時(shí)間值持續很高表明網(wǎng)絡(luò )或驅動(dòng)器堆?？赡艽嬖谄款i。通常，系統只會(huì )花費很少時(shí)間在內核時(shí)間上。

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

• %id：空閑時(shí)間 表示CPU空閑的百分比。這個(gè)值越大表明系統CPU的負荷越小。

• %ni：Nice時(shí)間 表示花費在執行renicing（改變進(jìn)程的執行順序和優(yōu)先級）進(jìn)程的CPU百分比。

3.2 內存性能指標

• 空閑內存與其他操作系統相比，在Linux系統中不必過(guò)分在意空閑內存值。因為L(cháng)inux內核會(huì )將大量未使用的內存分配給文件系統來(lái)緩存數據。

• 交換空間使用 這個(gè)值表示已使用的交換空間大小，相當于Windows系統中的虛擬內存。交換空間的使用只能告訴你Linux在管理內存上是多么的有效。要想確定內存是否存在瓶頸，需要用到SwapIn/Out。如果SwapIn/Out長(cháng)時(shí)間保持在每秒鐘超過(guò)200~300頁(yè)，可能表示內存存在瓶頸。

3.3 磁盤(pán)性能指標

• 磁盤(pán)I/O等待 CPU在等待I/O操作時(shí)所花費的時(shí)間。如果這個(gè)值持續很高，很可能表示I/O存在瓶頸。

• 隊列平均長(cháng)度 I/O請求的數量。通常硬盤(pán)隊列值為2~3時(shí)最佳；過(guò)高可能表示硬盤(pán)I/O存在瓶頸。

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

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

• 每秒鐘讀寫(xiě)塊的數量 這個(gè)指標表示每秒鐘讀寫(xiě)塊的數量，在2.6.XX內核中塊的大小為1024字節，早期的內核可以有不同的塊大小，其范圍可從512字節到4KB。

• 每秒鐘讀寫(xiě)字節的數量 表示塊設備讀寫(xiě)的實(shí)際數據的數量，單位為KB。

 

本文內容參考《賀春旸. MySQL管理之道：性能調優(yōu)、高可用與監控（第2版）》。