廣州熱線

原標(biāo)題：強(qiáng)烈推薦|消除大流量導(dǎo)致MySQL瓶頸的18件事

• 這篇文章翻譯了3篇blog：

https://www.percona.com/blog/2020/04/03/18-things-you-can-do-to-remove-mysql-bottlenecks-caused-by-high-traffic-part-one/

https://www.percona.com/blog/2020/04/06/18-things-you-can-do-to-remove-mysql-bottlenecks-caused-by-high-traffic-part-two/

https://www.percona.com/blog/2020/04/07/18-things-you-can-do-to-remove-mysql-bottlenecks-caused-by-high-traffic-part-three/

• 毫無征兆的，但是系統(tǒng)的負(fù)載增加了100%，300%，500%，并且您的數(shù)據(jù)庫必須承載這些請求。這是當(dāng)今許多在線系統(tǒng)必須處理的情況。本文專注于處理意外的大流量事件。
• 你可以事先主動做很多事情，在“為應(yīng)對黑色星期五的大流量，您數(shù)據(jù)庫應(yīng)該準(zhǔn)備什么”一文中介紹了這些內(nèi)容。
• 首先，讓我們看看流量高峰時會對數(shù)據(jù)庫產(chǎn)生什么影響---您的應(yīng)用工程團(tuán)隊可能會遇到哪些問題？

• 查詢響應(yīng)事件變長
• 錯誤率變高（連接到數(shù)據(jù)庫并執(zhí)行查詢）
• 數(shù)據(jù)庫已宕機(jī)（不可用）
• 由于復(fù)制延時或者批處理任務(wù)無法完成，導(dǎo)致數(shù)據(jù)不準(zhǔn)確（過期）

• 解決流量高峰的當(dāng)前目標(biāo)是盡快消除這些問題，這對大多數(shù)團(tuán)隊來說意味著專注于“容易實現(xiàn)的目標(biāo)”----可以在幾小時或幾天內(nèi)部署的解決方案，并且不需要進(jìn)行大規(guī)模的應(yīng)用程序或者架構(gòu)的更改。
• 好消息是，對大多數(shù)應(yīng)用程序，您可以通過一些簡單的操作獲得數(shù)據(jù)庫性能的幾倍提升：

1.對您的云數(shù)據(jù)庫的規(guī)格進(jìn)行擴(kuò)容

復(fù)雜性：低

潛在影響：高

• 如果您的數(shù)據(jù)庫運(yùn)行在云環(huán)境（或者一些虛擬化環(huán)境中），使用更高的實例規(guī)格通常是最簡單的方法（也就是俗稱的“鈔能力調(diào)優(yōu)”）。這是解決方案中最貴的方法之一，但這是您在繼續(xù)進(jìn)行其他性能優(yōu)化操作之前時可以采取的短期維護(hù)操作。
• 注意：數(shù)據(jù)庫不會線性擴(kuò)展，所以不要產(chǎn)生錯誤的安全感---如果您的云數(shù)據(jù)庫供應(yīng)商有10倍大的可用實例，不要期望它能承載10倍的流量。根據(jù)負(fù)載可能會少很多。

復(fù)雜性：中等潛在影響：高

• 如果您的負(fù)載是讀多寫少，那么部署更多的從節(jié)點可能是提高性能的好方法。不知道您的負(fù)載是什么類型？重溫“您的負(fù)載是讀多寫少還是讀少寫多”可以幫助您找到答案。
• 部署從節(jié)點是不夠的；您需要確保您的應(yīng)用程序能夠?qū)⒘髁柯酚傻剿鼈?。一些?yīng)用程序在應(yīng)用層實現(xiàn)此功能很容易。對其他應(yīng)用來說，部署ProxySQL并使用它的讀寫分離的功能可能是更好的選擇。
• 在很多場景下，您甚至可以使得整個應(yīng)用程序只使用從節(jié)點：如報表類應(yīng)用程序或者使用MySQL全文檢索類的應(yīng)用程序。
• 請注意，MySQL復(fù)制是異步的，這意味著從節(jié)點會有數(shù)據(jù)延時的情況（有時延時很高），因此，查詢要路由到最新數(shù)據(jù)的從節(jié)點，并確保監(jiān)控復(fù)制延時和復(fù)制是否中斷。

復(fù)雜性：中等潛在影響：高

• ProxySQL是管理MySQL流量的一個很有用的工具，特別是在流量高峰期。ProxySQL有連接池功能，這樣應(yīng)用程序不會耗盡連接，也不會因為有太多的并發(fā)連接導(dǎo)致MySQL超載。
• 在流量高峰時，ProxySQL另一個更有幫助的功能是ProxySQL查詢緩存，它允許您將查詢結(jié)果緩存一段時間。
• 在一些場景下您不需要最新的數(shù)據(jù)結(jié)果時，將這些流量路由到從節(jié)點，并緩存相同的查詢，可以帶來一些性能提升。

復(fù)雜性：中等潛在影響：中等

• 管理和開發(fā)團(tuán)隊常常討厭這樣的想法，但是這是一個很好的方法。并不是所有的應(yīng)用程序功能都會有相同的作用或者調(diào)用頻率相同，很少用到的程序功能通常負(fù)載是占據(jù)最高的，因為沒有花費(fèi)很多時間來優(yōu)化它們。在您經(jīng)歷流量高峰或找時間優(yōu)化它們時，禁用它們（或者短時間禁用）通常是一個很好的做法。

復(fù)雜性：低潛在影響：高

• 數(shù)據(jù)庫硬件層面的瓶頸可能有一個或者多個——CPU，內(nèi)存，磁盤或者網(wǎng)絡(luò)。如果您使用PMM監(jiān)控，您可以在MySQLInstanceSummaryDashboard的NodeSummary章節(jié)看到這一內(nèi)容。
• 如果某個資源已經(jīng)飽和，通常可以通過增加該資源獲得更好的性能，不過要考慮的一件事是優(yōu)化減少該資源的占用。例如，CPU使用率過高通?？梢酝ㄟ^優(yōu)化查詢來解決，而不是通過獲得更快的CPU來解決。

復(fù)雜性：低潛在影響：中

• 關(guān)于MySQL需要了解的一個重要的事情是，它只能使用一個CPU核心來完成運(yùn)行單個查詢的大部分工作，這意味著更多的CPU核心并不會讓您的慢查詢或者批量作業(yè)任務(wù)執(zhí)行的更快。如果說這是您遇到的問題，您需要獲得更快的CPU核心，或者您可能需要獲得更多的CPU核數(shù)。
• 但是如何確認(rèn)您現(xiàn)在的負(fù)載是什么類型的呢？
• 在PMM（或者您喜歡的監(jiān)控中）的NodeSummaryDashboard中查看CPU使用量，CPU飽和度和最大核心使用數(shù)量。
• 如果CPU使用量很高（不包括IOwait）,標(biāo)準(zhǔn)化的負(fù)載平均值為2或者更高，您的系統(tǒng)如果有更多可用CPU核數(shù)性能會更好。
• 但是，如果最大CPU內(nèi)核利用率接近100%，并且CPU使用率不高，那么您應(yīng)該需要更快的CPU核心。
• 例如，假設(shè)您使用了AWS，于通用的M5實例類型相比，CloudC5實例提供了更好的CPU性能。
• 當(dāng)涉及到CPU時，特別是在云環(huán)境和虛擬化環(huán)境中，需要注意的另一件是“CPUStealing”——它是指MySQL實例的CPU資源比表明的CPU頻率和CPU核數(shù)要少的多。

復(fù)雜性：低潛在影響：高

• 如果數(shù)據(jù)不能很好的加載到內(nèi)存，MySQL的性能可能會嚴(yán)重受到限制。如果您的數(shù)據(jù)已經(jīng)加載到內(nèi)存中，那么即使添加更多的內(nèi)存也不會對性能有任何提升。
• 即使運(yùn)行在非?？斓拇鎯ι?，例如InterOptane或者NVMe的存儲，訪問內(nèi)存中的數(shù)據(jù)仍然要快一個數(shù)量級。
• 如何知道您有足夠的內(nèi)存？查看內(nèi)存利用率和I/O。
• I/O實際上是我要首先查看的。例如本例，您沒有讀IO，那么所有的數(shù)據(jù)都在緩存中——MySQL的數(shù)據(jù)緩存或者操作系統(tǒng)的文件緩存。然而，即使所有的數(shù)據(jù)都被緩存，寫操作也無法避免，因為數(shù)據(jù)庫的修改總需要落盤。
• 通常情況下，您不會希望完全消除讀IO——大多數(shù)情況下，它需要太多的內(nèi)存，而且這也不是必要的。但是您需要確保讀IO不會對性能產(chǎn)生實質(zhì)性的影響。您可以通過確保磁盤負(fù)載是否可控來做到這一點，或者，如果您安裝了PMM，您可以在QueryAnalytics的Dashboard查看磁盤讀對特定的查詢性能影響有多大。
• 注意：雖然您可以通過簡單地添加內(nèi)存來獲得一些性能提升，因為操作系統(tǒng)會將其用做緩存，但是為了獲得大部分的新可用內(nèi)存，您應(yīng)該配置MySQL的一些參數(shù)。Innodb_buffer_pool_size是需要考慮的最重要的參數(shù)。內(nèi)存的80%經(jīng)常被用做經(jīng)驗法則，但除此以外還有更多。
• 在配置MySQL以利用所有內(nèi)存時，您應(yīng)該注意一件事是確保您不會過度使用內(nèi)存，MySQL也不會耗盡虛擬內(nèi)存（因為它可能會崩潰或者內(nèi)存不足OOM）。
• 您還需要確保沒有顯著的swap交換（1MB/秒或者更多），但是一些swap空間的使用是可以接受的。更多細(xì)節(jié)查看“為swap辯護(hù)：常見的誤解”。

8.遷移到更快的存儲

復(fù)雜性：中潛在影響：高

• 當(dāng)數(shù)據(jù)量很小時，將其緩存在內(nèi)存中是擴(kuò)展讀取的最好的方法。如果您的數(shù)據(jù)庫很大，這時更快的存儲可能是更好的選擇。另外，即使您有足夠大的內(nèi)存，也需要處理寫操作。這篇古老仍然有效的文章詳細(xì)地討論了這個主題。
• 對于CPU，您需要知道何時需要更多或者更快的核，而存儲的情況則更加復(fù)雜。您需要了解吞吐量（IOPS）與延遲之間的差異，以及讀寫性能之間的區(qū)別。
• 查看IO性能的一種方法是查看存儲的IOPS或者IO的帶寬。
• 它可以幫助您查看您是否接近或者遇到存儲的限制。您可能不知道存儲的具體性能。在這種情況下，最好看一下磁盤IO負(fù)載，它大致顯示了當(dāng)時有多少IO操作在運(yùn)行。
• 如果這個數(shù)字是數(shù)十甚至數(shù)百，您的磁盤很可能已經(jīng)超載了。與CPU不同，存儲的問題在于我們無法知道什么是“天然并發(fā)級別”，何時可以并行處理請求，何時需要排隊。
• 查看讀和寫的請求延時，看看它們與流量峰值之前是否有什么不同。另外，讀寫延時可能會各自受到影響，應(yīng)該分開查看。
• 更快的存儲能在多大程度上影響查詢的性能？從讀取的角度來看，您可以如第7章節(jié)所示使用PMM的QueryAnalytics。但是對于寫入而言，它更復(fù)雜。
• 寫InnoDBRedoLog，或者更具體的說，通過fsync將日志持久化到磁盤是一個非常常見的瓶頸。通過查看MySQLInnodbDetails的Dashboard中InnodbDiskIO章節(jié)中的被阻塞的fsync數(shù)量，來判斷系統(tǒng)是否發(fā)生了這種情況。

• 如果始終接近1，則可能出現(xiàn)磁盤刷新瓶頸。為了改善這種情況，您需要更低的寫（fsync）延時的存儲。您可以調(diào)整MySQL的配置降低持久化保證（雙1），或者調(diào)整工作負(fù)載將查詢分組到更少的事務(wù)中。
• 有哪些更快的存儲可以選用？IntelOptaneSSD或者NVMe存儲往往提供最佳性能和最快和最可預(yù)測的延時。但是，如果您使用這些解決方案，尤其是云環(huán)境中，請確保使用某種形式的復(fù)制來實現(xiàn)數(shù)據(jù)冗余。
• 如果您需要使用網(wǎng)絡(luò)存儲，請使用已經(jīng)對吞吐量優(yōu)化的存儲類型，例如AWSEBSio1類型卷。傳統(tǒng)的“通用”gp2卷可能更劃算，但是他們的性能峰值更低。

復(fù)雜性：低潛在影響：高

• 當(dāng)在流量高峰期檢查網(wǎng)絡(luò)是否是瓶頸的時候，您需要查看帶寬，延時和Errors。
• 網(wǎng)絡(luò)往往比其他資源更加復(fù)雜，因為所有這些都必須分別針對不同的客戶端進(jìn)行測量。例如，運(yùn)行在“本機(jī)”上的客戶端通常不會出現(xiàn)問題，但是，在世界其他地方運(yùn)行的客戶端與數(shù)據(jù)庫通信將會有問題。
• 網(wǎng)絡(luò)帶寬，至少在本地節(jié)點上，很少是問題。
• 很少有應(yīng)用程序檢索大量結(jié)果集能將網(wǎng)絡(luò)打滿。網(wǎng)絡(luò)備份和其他大量數(shù)據(jù)傳輸可能會將網(wǎng)絡(luò)打滿，導(dǎo)致其他用戶的事務(wù)處理變慢。
• 客戶端和數(shù)據(jù)庫服務(wù)器之間的延遲可以通過“ping”或者“mtr”工具粗略的衡量。如果您有一個萬兆網(wǎng)絡(luò)，那么在同一數(shù)據(jù)中心的延時期望值是0.2ms。在云廠商的同一可用區(qū)域中，該值通常會高一些。不同的高可用區(qū)域具有更高的延時，較遠(yuǎn)區(qū)域的延時可能達(dá)100ms，與本地網(wǎng)絡(luò)相比，它們的差異可能非常大。
• 在這個場景下，我們看到客戶端和服務(wù)器之間的通信僅通過一個路由器（可能還有幾個交換機(jī)），平均延時為1.5ms，沒有丟包。
• 您應(yīng)該盡可能的將應(yīng)用程序和數(shù)據(jù)庫部署在一個可用區(qū)域（如果可能的話），但是對于延遲敏感的應(yīng)用程序，必須要將應(yīng)用程序和數(shù)據(jù)庫部署在同一區(qū)域。
• 當(dāng)有errors時，TCP重傳是您最大的敵人，因為它會顯著地增加延時。
• 如果您在流量高峰期間看到重傳的速度在增加，則在網(wǎng)絡(luò)層可能存在需要解決的問題。

復(fù)雜性：低潛在影響：高

• 定位和優(yōu)化慢查詢是您可以做的最有價值的活動之一，因為它帶來了長期的收益。與提升硬件不同，它不需要額外的投資（除了時間）。
• 如果您正在運(yùn)行PMM，那么您應(yīng)該查看QueryAnalyticsDashboard，該工具默認(rèn)情況下根據(jù)產(chǎn)生的負(fù)載對查詢進(jìn)行排序。

• 按照這個順序檢查和優(yōu)化查詢是使系統(tǒng)運(yùn)行得更快的絕妙方法。在某些情況下，類似commit，您不能優(yōu)化SQL，但是您可以通過提升硬件或者更改MySQL配置來加速查詢。
• 查看查詢的執(zhí)行詳細(xì)信息：
• 查看執(zhí)行計劃，看看該查詢是否可以優(yōu)化及如何去優(yōu)化：
• MySQLSQL優(yōu)化是一個非常復(fù)雜的主題，不可能在一篇博客中完全覆蓋。

復(fù)雜性：低潛在影響：高

• 完整的優(yōu)化SQL可能需要改寫SQL，這需要開發(fā)和測試時間，而這很難做到。這也就是為什么作為第一步，您可能希望只是添加缺失的索引。這并不需要更改應(yīng)用程序，而且相當(dāng)安全（極少數(shù)例外），并且不會更改查詢的結(jié)果。

復(fù)雜性：中潛在影響：中

• 隨著時間的推移，數(shù)據(jù)庫schema通常會累積重復(fù)、冗余或者未使用的索引。有些是由于錯誤或者誤解而添加的，有些是在過去是有用的，但是隨著應(yīng)用程序的更改而不在有用。
• 您可以在這篇博客中了解更多關(guān)于冗余和重復(fù)索引的信息。PerconaToolkit中的pt-duplicate-key-checker也是查找它們的好工具。
• 未使用的索引比較復(fù)雜，也有一定的風(fēng)險——僅僅因為上周沒有查詢需要該索引，并不意味著這個月或者這個季度的查詢不會使用該索引。
• 這篇名為《MySQL索引的基本管理》的博客提供了如何找到這些索引的方法。如果您正在使用MySQL8，您可以考慮在刪除它之前先將其置為不可見索引一段時間。

復(fù)雜性：中潛在影響：高

• 配置不當(dāng)?shù)腗ySQL服務(wù)器可能會導(dǎo)致嚴(yán)重的問題，特別是在流量高峰的高負(fù)載情況下，但是正確的基礎(chǔ)配置并不難。雖然MySQL服務(wù)有400個參數(shù)可以調(diào)優(yōu)，但您只需要更改其中的10-20個，就可以為您的工作負(fù)載獲得95%的可用性能。
• 這篇博文涵蓋了最重要的基礎(chǔ)知識。

復(fù)雜性：中潛在影響：中

• 在所有條件相同的情況下，數(shù)據(jù)越多，數(shù)據(jù)庫的運(yùn)行速度就越慢，刪除（或者歸檔）不需要的在線數(shù)據(jù)是提升性能的好方法。
• 在許多場景下，您會發(fā)現(xiàn)應(yīng)用程序在數(shù)據(jù)庫保存的各種日志幾乎追溯到許多年前，除了幾周或者幾個月的數(shù)據(jù)之外它們幾乎沒有什么用處。
• PerconaToolkit中的pt-archiver是一個很好的歸檔舊數(shù)據(jù)的工具。
• 注意：盡管在清理完成之后，您的數(shù)據(jù)庫變得更加精簡、更快，但是該過程本身會占用額外的資源，而且在數(shù)據(jù)庫已經(jīng)超載時不應(yīng)該這樣做。

復(fù)雜性：中潛在影響：中

• 在一切都很平靜時，您可以不用維護(hù)數(shù)據(jù)庫的統(tǒng)計信息。但是這樣一來，數(shù)據(jù)庫統(tǒng)計信息可能會過時，您的表可能因為碎片，并不處在最佳狀態(tài)。
• 在您的表上執(zhí)行OPTIMIZETABLE，重建這些表提高它們的效率并更新統(tǒng)計信息。
• 要對所有的表進(jìn)行優(yōu)化，可以運(yùn)行mysqlcheck-optimization-A。
• 請記住，優(yōu)化可能比清理舊數(shù)據(jù)對系統(tǒng)的影響更大，因此不希望您在負(fù)載高峰期間進(jìn)行優(yōu)化。一個好的方法是將副本（從節(jié)點）的應(yīng)用流量移除，滾動對從節(jié)點執(zhí)行優(yōu)化，然后再提升一個從節(jié)點為主節(jié)點。

復(fù)雜性：中潛在影響：中

• 備份、收集統(tǒng)計信息、報表生成和大數(shù)據(jù)負(fù)載等后臺作業(yè)通常沒有得到很好的優(yōu)化——它們可以在業(yè)務(wù)低峰期運(yùn)行，MySQL服務(wù)器可以處理這些額外的負(fù)載。在流量高峰期，它們可能會導(dǎo)致數(shù)據(jù)庫超載和宕機(jī)。
• 在流量高峰期間后臺任務(wù)帶來的另一個問題是重疊或者雪崩效應(yīng)。如果您的后臺任務(wù)通常運(yùn)行15分鐘，您將其中兩個任務(wù)安排在凌晨2點和3點，通常一次只運(yùn)行其中一個任務(wù)。但是，由于額外的負(fù)載，現(xiàn)在可能任務(wù)需要2個小時才能完成，這可能導(dǎo)致在同時運(yùn)行多個后臺任務(wù)，從而導(dǎo)致額外的負(fù)載并帶來數(shù)據(jù)損壞。
• 檢查您的后臺任務(wù)，并依次核對以下問題：

• 我需要這個后臺任務(wù)嗎？可以將這個任務(wù)推后嗎？
• 可以在從節(jié)點上運(yùn)行這個任務(wù)嗎？在不同的從節(jié)點運(yùn)行不同的任務(wù)可能是一個很好的解決方案！
• 您是否調(diào)度了任務(wù)以確保它們不會重疊？
• 有優(yōu)化的后臺任務(wù)嗎？優(yōu)化這些查詢，或者如果您使用mysqldump備份，則應(yīng)改用Percona的Xtrabackup，這樣效率更高。
• 您會限制這些任務(wù)使用的資源嗎？例如，限制批處理任務(wù)的并發(fā)數(shù)（并行連接的數(shù)量）?；蛘?，如果使用Percona的Xtrabackup會影響您服務(wù)器的性能，那么可以使用ThrottleBackups。

復(fù)雜性：高潛在影響：高

• 有些應(yīng)用程序通過硬件擴(kuò)展可以很好地進(jìn)行擴(kuò)展，而另一些則不能。通常區(qū)別在于，應(yīng)用程序依賴于“熱點”時，需要頻繁更新的數(shù)據(jù)就會成為瓶頸。例如，如果在數(shù)據(jù)庫創(chuàng)建一個計數(shù)器，每個事務(wù)都需要對其進(jìn)行更新，那么它無法很好地進(jìn)行擴(kuò)展。
• 熱點種類很多，其中一些很難查找和診斷。最常見的一種類似于上述內(nèi)容，并顯示為很多行鎖等待（和高死鎖率）。
• 通過PMM的MySQLInnoDBDetailsdashboard，可以查看整體上等待行級鎖的時間：

• 或者查看回滾率：
• 請注意，如果您在流量高峰期看到的應(yīng)用程序超出正常范圍，不同的應(yīng)用程序有不同的正常值。
• 您還可以查看哪些特定的查詢有長的行鎖等待：
• 減少熱點可能與添加更好的索引一樣容易，也可能更加困難，需要重新設(shè)計應(yīng)用程序。無論如何，我在這里引入這部分內(nèi)容，因為如果您設(shè)計了一個具有非常糟糕的有數(shù)據(jù)熱點的應(yīng)用程序，上面涉及的簡單的優(yōu)化技術(shù)可能對您不起作用。

復(fù)雜性：中潛在影響：中

• 在配置MySQL服務(wù)器時，在應(yīng)用服務(wù)器端使用正確的設(shè)置是非常重要的。它需要確保您在使用長連接，而不是為每個小事務(wù)使用短連接，特別是您在使用TLS/SSL連接數(shù)據(jù)庫的時候。如果您使用連接池，請確保其配置正確，特別是在您不使用ProxySQL或者線程池的情況下。具體的優(yōu)化建議需要您根據(jù)編程語言、ORM框架或者連接池有所不同而一一谷歌！

• 這有許多建議，實際上，在流量高峰期，您可以做很多事情來控制一切。好消息是您不需要遵循所有這些建議即可獲得性能提升，并最終以出色的應(yīng)用程序性能贏得客戶青睞（或者讓開發(fā)團(tuán)隊不再為數(shù)據(jù)庫問題煩心），將這些建議看作一個菜單——查看最適合您的環(huán)境的建議以及可能帶來最大收益的建議，然后使用這些建議指導(dǎo)下一步操作！

由葉老師主講的知數(shù)堂「MySQL優(yōu)化課」課程早已升級到MySQL8.0版本了，現(xiàn)在上車剛剛好，掃碼開啟MySQL8.0的修行之旅吧。