如何為 MySQL 選擇 CPU？

MySQL 服務(wù)器性能受制于整個(gè)系統(tǒng)最薄弱的環(huán)節(jié)，承載它的操作系統(tǒng)和硬件往往是限制因素。磁盤(pán)大小、可用內(nèi)存和 CPU 資源、網(wǎng)絡(luò)，以及所有連接它們的組件，都會(huì)限制系統(tǒng)的最終容量。

因此，需要小心地選擇硬件，并對(duì)硬件和操作系統(tǒng)進(jìn)行合適的配置。

本文選自MySQL圣經(jīng)級(jí)著作《高性能MySQL（第3版）》一書(shū)。硬件的更新?lián)Q代非常迅速，我們的目標(biāo)是幫助提升對(duì)這些概念的理解，這樣對(duì)于即使沒(méi)有直接覆蓋到的知識(shí)也可以舉一反三。

許多不同的硬件都可以影響 MySQL 的性能，但我們認(rèn)為最常見(jiàn)的兩個(gè)瓶頸是 CPU 和 I/ O 資源。當(dāng)數(shù)據(jù)可以放在內(nèi)存中或者可以從磁盤(pán)中以足夠快的速度讀取時(shí)，CPU 可能出現(xiàn)瓶頸。把大量的數(shù)據(jù)集完全放到大容量的內(nèi)存中，以現(xiàn)在的硬件條件完全是可行的。注 1

另一方面，I/O 瓶頸，一般發(fā)生在工作所需的數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過(guò)有效內(nèi)存容量的時(shí)候。如果應(yīng)用程序是分布在網(wǎng)絡(luò)上的，或者如果有大量的查詢和低延遲的要求，瓶頸可能轉(zhuǎn)移到網(wǎng)絡(luò)上，而不再是磁盤(pán) I/O 。注 2

雖然一些技巧可以幫助找到系統(tǒng)的限制因素，但即使你認(rèn)為已經(jīng)找到了瓶頸，也應(yīng)該透過(guò)表象去看更深層次的問(wèn)題。某一方面的缺陷常常會(huì)將壓力施加在另一個(gè)子系統(tǒng)， 導(dǎo)致這個(gè)子系統(tǒng)出問(wèn)題。

例如，若沒(méi)有足夠的內(nèi)存，MySQL 可能必須刷出緩存來(lái)騰出 空間給需要的數(shù)據(jù)——然后，過(guò)了一小會(huì)，再讀回剛剛刷新的數(shù)據(jù)（讀取和寫(xiě)入操作都 可能發(fā)生這個(gè)問(wèn)題）。本來(lái)是內(nèi)存不足，卻導(dǎo)致出現(xiàn)了 I/O 容量不足。當(dāng)找到一個(gè)限制系 統(tǒng)性能的因素時(shí)，應(yīng)該問(wèn)問(wèn)自己，“是這個(gè)部分本身的問(wèn)題，還是系統(tǒng)中其他不合理的 壓力轉(zhuǎn)移到這里所導(dǎo)致的？”

還有另外一個(gè)例子 ：內(nèi)存總線的瓶頸也可能表現(xiàn)為 CPU 問(wèn)題。事實(shí)上，我們說(shuō)一個(gè)應(yīng)用程序有“CPU 瓶頸”或者是“CPU 密集型”，真正的意思應(yīng)該是計(jì)算的瓶頸。接下來(lái)將深入探討這個(gè)問(wèn)題。

注 1 ：普通 PC Server 也能配到 192GB 內(nèi)存。

注 2 ：網(wǎng)絡(luò)吞吐也是一種 I/O。

在升級(jí)當(dāng)前硬件或購(gòu)買新的硬件時(shí)，應(yīng)該考慮下工作負(fù)載是不是 CPU 密集型。?

可以通過(guò)檢查 CPU 利用率來(lái)判斷是否是 CPU 密集型的工作負(fù)載，但是僅看 CPU 整體的 負(fù)載是不合理的，還需要看看 CPU 使用率和大多數(shù)重要的查詢的 I/O 之間的平衡，并注意 CPU 負(fù)載是否分配均勻。

當(dāng)遇到 CPU 密集型的工作時(shí)，MySQL 通?？梢詮母斓?CPU 中獲益（相對(duì)更多的 CPU）。

但這不是絕對(duì)的，因?yàn)檫€依賴于負(fù)載情況和 CPU 數(shù)量。更古老的 MySQL 版本在多 CPU 上有擴(kuò)展性問(wèn)題，即使新版本也不能對(duì)單個(gè)查詢并發(fā)利用多個(gè) CPU。因此，CPU 速度限制了每個(gè) CPU 密集型查詢的響應(yīng)時(shí)間。

當(dāng)我們討論 CPU 的時(shí)候，為保證本文易于閱讀，對(duì)某些術(shù)語(yǔ)將不會(huì)做嚴(yán)格的定義?，F(xiàn)在一般的服務(wù)器通常都有多個(gè)插槽（Socket），每個(gè)插槽上都可以插一個(gè)有多個(gè)核心的 CPU（有獨(dú)立的執(zhí)行單元），并且每個(gè)核心可能有多個(gè)“硬件線程”。這些復(fù)雜的架構(gòu)需 要有點(diǎn)耐心去了解，并且我們不會(huì)總是明確地區(qū)分它們。不過(guò)，在一般情況下，當(dāng)談到 CPU 速度的時(shí)候，談?wù)摰钠鋵?shí)是執(zhí)行單元的速度，當(dāng)提到的 CPU 數(shù)量時(shí)，指的通常是在操作系統(tǒng)上看到的數(shù)量，盡管這可能是獨(dú)立的執(zhí)行單元數(shù)量的多倍。?注 3

注 3 ：超線程技術(shù)。

這幾年 CPU 在各個(gè)方面都有了很大的提升。例如，今天的 Intel CPU 速度遠(yuǎn)遠(yuǎn)超過(guò)前幾代， 這得益于像直接內(nèi)存連接（directly attached memory）技術(shù)以及 PCIe 卡之類的設(shè)備互聯(lián) 上的改善等。這些改進(jìn)對(duì)于存儲(chǔ)設(shè)備尤其有效，例如 Fusion-io 和 Virident 的 PCIe 閃存 驅(qū)動(dòng)器。?

超線程的效果相比以前也要好得多，現(xiàn)在操作系統(tǒng)也更了解如何更好地使用超線程。而以前版本的操作系統(tǒng)無(wú)法識(shí)別兩個(gè)虛擬處理器實(shí)際上是在同一芯片上，認(rèn)為它們是獨(dú)立 的，于是會(huì)把任務(wù)安排在兩個(gè)實(shí)際上是相同物理執(zhí)行單元上的虛擬處理器。實(shí)際上單個(gè)執(zhí)行單元并不是真的可以在同一時(shí)間運(yùn)行兩個(gè)進(jìn)程，所以這樣做會(huì)發(fā)生沖突和爭(zhēng)奪資源。而同時(shí)其他 CPU 卻可能在閑置，從而浪費(fèi)資源。操作系統(tǒng)需要能感知超線程，因?yàn)樗?須知道什么時(shí)候執(zhí)行單元實(shí)際上是閑置的，然后切換相應(yīng)的任務(wù)去執(zhí)行。這個(gè)問(wèn)題之前 常見(jiàn)的原因是在等待內(nèi)存總線，可能花費(fèi)需要高達(dá)一百個(gè) CPU 周期，這已經(jīng)類似于一個(gè) 輕量級(jí)的 I/O 等待。新的操作系統(tǒng)在這方面有了很大的改善。超線程現(xiàn)在已經(jīng)工作得很好。過(guò)去，我們時(shí)常提醒人們禁用它，但現(xiàn)在已經(jīng)不需要這樣做了。

這就是說(shuō)，現(xiàn)在可以得到大量的快速的 CPU。所以多和快哪個(gè)更重要？一般來(lái)說(shuō)兩個(gè)都想要。

從廣義上來(lái)說(shuō)，調(diào)優(yōu)服務(wù)器可能有如下 兩個(gè)目標(biāo) ：?

要做到這一點(diǎn)，需要高速 CPU，因?yàn)槊總€(gè)查詢只能使用一個(gè) CPU。?

如果能同時(shí)運(yùn)行很多查詢語(yǔ)句，則可以從多個(gè) CPU 處理查詢中受益。然而，在實(shí)踐中， 還要取決于具體情況。因?yàn)?MySQL 還不能在多個(gè) CPU 中完美地?cái)U(kuò)展，能用多少 個(gè) CPU 還是有極限的。在舊版本的 MySQL 中（MySQL 5.1 以后的版本已經(jīng)有一些提升），這個(gè)限制非常嚴(yán)重。在新的版本中，則可以放心地?cái)U(kuò)展到 16 或 24 個(gè) CPU， 或者更多，取決于使用的是哪個(gè)版本（Percona 往往在這方面略占優(yōu)勢(shì)）。

如果有多路 CPU，并且沒(méi)有并發(fā)執(zhí)行查詢語(yǔ)句，MySQL 依然可以利用額外的 CPU 為后 臺(tái)任務(wù)（例如清理 InnoDB 緩沖、網(wǎng)絡(luò)操作，等等）服務(wù)。然而，這些任務(wù)通常比執(zhí)行 查詢語(yǔ)句更加輕量化。?

MySQL 復(fù)制也能在高速 CPU 下工作得非常好，而多 CPU 對(duì)復(fù)制 的幫助卻不大。如果工作負(fù)載是 CPU 密集型，主庫(kù)上的并發(fā)任務(wù)傳遞到備庫(kù)以后會(huì)被簡(jiǎn) 化為串行任務(wù)，這樣即使備庫(kù)硬件比主庫(kù)好，也可能無(wú)法保持跟主庫(kù)之間的同步。也就是說(shuō)，備庫(kù)的瓶頸通常是 I/O 子系統(tǒng)，而不是 CPU。?

如果有一個(gè) CPU 密集型的工作負(fù)載，考慮是需要更快的 CPU 還是更多 CPU 的另外一個(gè)因素是查詢語(yǔ)句實(shí)際在做什么。在硬件層面，一個(gè)查詢可以在執(zhí)行或等待。處于等待狀 態(tài)常見(jiàn)的原因是在運(yùn)行隊(duì)列中等待（進(jìn)程已經(jīng)是可運(yùn)行狀態(tài)，但所有的 CPU 都忙）、等待閂鎖（Latch）或鎖（Lock）、等待磁盤(pán)或網(wǎng)絡(luò)。那么你期望查詢是等待什么呢？如果 等待閂鎖或鎖，通常需要更快的 CPU ；如果在運(yùn)行隊(duì)列中等待，那么更多或者更快的 CPU 都可能有幫助。（也可能有例外，例如，查詢等待 InnoDB 日志緩沖區(qū)的 Mutex，直 到 I/O 完成前都不會(huì)釋放——這可能表明需要更多的 I/O 容量）。?

這就是說(shuō)，MySQL 在某些工作負(fù)載下可以有效地利用很多 CPU。例如，假設(shè)有很 多連接查詢的是不同表（假設(shè)這些查詢不會(huì)造成表鎖的競(jìng)爭(zhēng)，實(shí)際上對(duì) MyISAM 和 MEMORY 表可能會(huì)有問(wèn)題），并且服務(wù)器的總吞吐量比任何單個(gè)查詢的響應(yīng)時(shí)間都更重要。吞吐量在這種情況下可以非常高，因?yàn)榫€程可以同時(shí)運(yùn)行而互不爭(zhēng)用。?

再次說(shuō)明，在理論上這可能更好地工作 ：不管查詢是讀取不同的表還是相同的表， InnoDB 都會(huì)有一些全局共享的數(shù)據(jù)結(jié)構(gòu)，而 MyISAM 在每個(gè)緩沖區(qū)都有全局鎖。而且不僅僅是存儲(chǔ)引擎，服務(wù)器層也有全局鎖。以前 InnoDB 承擔(dān)了所有的罵名，但最近 做了一些改進(jìn)后，暴露了服務(wù)器層中的其他瓶頸。例如臭名昭著的 LOCK_open 互斥量 （Mutex），在 MySQL 5.1 和更早版本中可能就是個(gè)大問(wèn)題，另外還有其他一些服務(wù)器級(jí) 別的互斥量（例如查詢緩存）。?

通常可以通過(guò)堆棧跟蹤來(lái)診斷這些類型的競(jìng)爭(zhēng)問(wèn)題，例如 Percona Toolkit 中的 pt-pmp 工 具。如果遇到這樣的問(wèn)題，可能需要改變服務(wù)器的配置，禁用或改變引起問(wèn)題的組件， 進(jìn)行數(shù)據(jù)分片（Sharding），或者通過(guò)某種方式改變做事的方法。這里無(wú)法列舉所有的問(wèn) 題和相應(yīng)的解決方案，但是一旦有一個(gè)確定的診斷，答案通常是顯而易見(jiàn)的。大部分不 幸遇到的問(wèn)題都是邊緣場(chǎng)景，最常見(jiàn)的問(wèn)題隨著時(shí)間的推移都在服務(wù)器上被修復(fù)了。

可能 99% 以上的 MySQL 實(shí)例（不含嵌入式使用）都運(yùn)行在 Intel 或者 AMD 芯片的 x86 架構(gòu)下。

64 位架構(gòu)現(xiàn)在都是默認(rèn)的了，32 位 CPU 已經(jīng)很難買到了。MySQL 在 64 位架構(gòu)上工作良好，盡管有些事暫時(shí)不能利用 64 位架構(gòu)來(lái)做。因此，如果使用的是較老舊版本的 MySQL，在 64 位服務(wù)器上可能要小心。例如，在 MySQL 5.0 發(fā)布的早期時(shí)候，每個(gè) MyISAM 鍵緩沖區(qū)被限制為 4 GB，由一個(gè) 32 位整數(shù)負(fù)責(zé)尋址。（可以創(chuàng)建多個(gè)鍵緩沖 區(qū)來(lái)解決這個(gè)問(wèn)題。）?

確保在 64 位硬件上使用 64 位操作系統(tǒng)！最近這種情況已經(jīng)不太常見(jiàn)了，但以前經(jīng)?？?以遇到，大多數(shù)主機(jī)托管提供商暫時(shí)還是在服務(wù)器上安裝 32 位操作系統(tǒng)，即使是 64 位 CPU。32 位操作系統(tǒng)意味著不能使用大量的內(nèi)存 ：盡管某些 32 位系統(tǒng)可以支持大量的 內(nèi)存，但不能像 64 位系統(tǒng)一樣有效地利用，并且在 32 位系統(tǒng)上，任何一個(gè)單獨(dú)的進(jìn)程 都不能尋址 4 GB 以上的內(nèi)存。

多 CPU 在聯(lián)機(jī)事務(wù)處理（OLTP）系統(tǒng)的場(chǎng)景中非常有用。這些系統(tǒng)通常執(zhí)行許多小的 操作，并且是從多個(gè)連接發(fā)起請(qǐng)求，因此可以在多個(gè) CPU 上運(yùn)行。在這樣的環(huán)境中，并 發(fā)可能成為瓶頸。大多數(shù) Web 應(yīng)用程序都屬于這一類。?

OLTP 服務(wù)器一般使用 InnoDB，盡管它在多 CPU 的環(huán)境中還存在一些未解決的并發(fā)問(wèn)題。然而，不只是 InnoDB 可能成為瓶頸 ：任何共享資源都是潛在的競(jìng)爭(zhēng)點(diǎn)。InnoDB 之所以 獲得大量關(guān)注是因?yàn)樗歉卟l(fā)環(huán)境下最常見(jiàn)的存儲(chǔ)引擎，但 MyISAM 在大壓力時(shí)的表 現(xiàn)也不好，即使不修改任何數(shù)據(jù)只是讀取數(shù)據(jù)也是如此。許多并發(fā)瓶頸，如 InnoDB 的 行級(jí)鎖和 MyISAM 的表鎖，沒(méi)有辦法優(yōu)化——除了盡可能快地處理任務(wù)之外，沒(méi)有別的 辦法解決，這樣，鎖就可以盡快分配給等待的任務(wù)。如果一個(gè)鎖是造成它們（其他任務(wù)） 都在等待的原因，那么不管有多少 CPU 都一樣。因此，即使是一些高并發(fā)工作負(fù)載，也 可以從更快的 CPU 中受益。

實(shí)際上有兩種類型的數(shù)據(jù)庫(kù)并發(fā)問(wèn)題，需要不同的方法來(lái)解決，如下所示。?

應(yīng)用程序可以看到資源的競(jìng)爭(zhēng)，如表或行鎖爭(zhēng)用。這些問(wèn)題通常需要好的策略來(lái)解決， 如改變應(yīng)用程序、使用不同的存儲(chǔ)引擎、改變服務(wù)器的配置，或使用不同的鎖定提 示或事務(wù)隔離級(jí)別。?

比如信號(hào)量、訪問(wèn) InnoDB 緩沖池頁(yè)面的資源爭(zhēng)用，等等?？梢試L試通過(guò)改變服務(wù) 器的設(shè)置、改變操作系統(tǒng)，或使用不同的硬件解決這些問(wèn)題，但通常只能緩解而無(wú) 法徹底消滅。在某些情況下，使用不同的存儲(chǔ)引擎或給存儲(chǔ)引擎打補(bǔ)丁，可以幫助 緩解這些問(wèn)題。?

MySQL 的“擴(kuò)展模式”是指它可以有效利用的 CPU 數(shù)量，以及在壓力不斷增長(zhǎng)的情 況下如何擴(kuò)展，這同時(shí)取決于工作負(fù)載和系統(tǒng)架構(gòu)。通過(guò)“系統(tǒng)架構(gòu)”的手段是指通 過(guò)調(diào)整操作系統(tǒng)和硬件，而不是通過(guò)優(yōu)化使用 MySQL 的應(yīng)用程序。CPU 架構(gòu)（RISC、 CISC、流水線深度等）、CPU 型號(hào)和操作系統(tǒng)都影響 MySQL 的擴(kuò)展模式。這也是為什 么說(shuō)基準(zhǔn)測(cè)試是非常重要的 ：一些系統(tǒng)可以在不斷增加的并發(fā)下依然運(yùn)行得很好，而另 一些的表現(xiàn)則糟糕得多。

有些系統(tǒng)在更多的處理器下甚至可能降低整體性能。這是相當(dāng)普遍的情況，我們了解到 許多人試圖升級(jí)到有多個(gè) CPU 的系統(tǒng)，最后只能被迫恢復(fù)到舊系統(tǒng)（或綁定 MySQL 進(jìn) 程到其中某些核心），因?yàn)檫@種升級(jí)反而降低了性能。在 MySQL 5.0 時(shí)代，Google 的補(bǔ) 丁和 Percona Server 出現(xiàn)之前，能有效利用的 CPU 核數(shù)是 4 核，但是現(xiàn)在甚至可以看到 操作系統(tǒng)報(bào)告多達(dá) 80 個(gè)“CPU”的服務(wù)器。如果規(guī)劃一個(gè)大的升級(jí)，必須要同時(shí)考慮硬件、 服務(wù)器版本和工作負(fù)載。?

某些 MySQL 擴(kuò)展性瓶頸在服務(wù)器層，而其他一些在存儲(chǔ)引擎層。存儲(chǔ)引擎是怎么設(shè)計(jì) 的至關(guān)重要，有時(shí)更換到一個(gè)不同的引擎就可以從多處理器上獲得更多效果。?

我們看到在世紀(jì)之交圍繞處理器速度的戰(zhàn)爭(zhēng)在一定程度上已經(jīng)平息，CPU 廠商更多地專 注于多核 CPU 和多線程的變化。CPU 設(shè)計(jì)的未來(lái)很可能是數(shù)百個(gè)處理器核心，四核心 和六核心的 CPU 在今天是很常見(jiàn)的。不同廠商的內(nèi)部架構(gòu)差異很大，不可能概括出線程、 CPU 和內(nèi)核之間的相互作用。內(nèi)存和總線如何設(shè)計(jì)也是非常重要的。歸根結(jié)底，多個(gè)內(nèi) 核和多個(gè)物理 CPU 哪個(gè)更好，這是由硬件體系結(jié)構(gòu)決定的。?

現(xiàn)代 CPU 的另外兩個(gè)復(fù)雜之處也值得提一下。首先是頻率調(diào)整。這是一種電源管理技術(shù)， 可以根據(jù) CPU 上的壓力而動(dòng)態(tài)地改變 CPU 的時(shí)鐘速度。問(wèn)題是，它有時(shí)不能很好地處 理間歇性突發(fā)的短查詢的情況，因?yàn)椴僮飨到y(tǒng)可能需要一段時(shí)間來(lái)決定 CPU 的時(shí)鐘是否 應(yīng)該變化。結(jié)果，查詢可能會(huì)有一段時(shí)間速度較慢，并且響應(yīng)時(shí)間增加了。頻率調(diào)整可 能使間歇性的工作負(fù)載性能低下，但可能更重要的是，它會(huì)導(dǎo)致性能波動(dòng)。

第二個(gè)復(fù)雜之處是 boost 技術(shù)，這個(gè)技術(shù)改變了我們對(duì) CPU 模式的看法。我們?cè)?jīng)以為 四核 2GHz CPU 有四個(gè)同樣強(qiáng)大的核心，不管其中有些是閑置或非閑置。因此，一個(gè)完 美的可擴(kuò)展系統(tǒng)，當(dāng)它使用所有四個(gè)內(nèi)核的時(shí)候，可以預(yù)計(jì)得到四倍的提升。但是現(xiàn)在 已經(jīng)不是這樣了，因?yàn)楫?dāng)系統(tǒng)只使用一個(gè)核心時(shí)，處理器會(huì)運(yùn)行在更高的時(shí)鐘速度上， 例如 3GHz。這給很多的規(guī)劃容量和可擴(kuò)展性建模的工具出了一個(gè)難題，因?yàn)橄到y(tǒng)性能 表現(xiàn)不再是線性的變化了。這也意味著，“空閑 CPU”并不代表相同規(guī)模的資源浪費(fèi)， 如果有一臺(tái)服務(wù)器上只運(yùn)行了備庫(kù)的復(fù)制，而復(fù)制執(zhí)行是單線程的，所以有三個(gè) CPU 是 空閑的，因此認(rèn)為可以利用這些 CPU 資源執(zhí)行其他任務(wù)而不影響復(fù)制，可能就想錯(cuò)了。

看到這還沒(méi)來(lái)得及跑的同學(xué)，給個(gè)三連好嗎？
微信5000好友是什么體驗(yàn)？
機(jī)器學(xué)習(xí)從 入 門 到 精 通 路 線 圖
機(jī)器學(xué)習(xí)最好的入門課程是什么？
阿里云Python訓(xùn)練營(yíng)總結(jié)：聊聊堅(jiān)持的意義