二八科技-二八科技新聞資訊

原標題：強烈推薦|消除大流量導致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/

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

• 查詢響應事件變長
• 錯誤率變高（連接到數據庫并執行查詢）
• 數據庫已宕機（不可用）
• 由于復制延時或者批處理任務無法完成，導致數據不準確（過期）

• 解決流量高峰的當前目標是盡快消除這些問題，這對大多數團隊來說意味著專注于“容易實現的目標”----可以在幾小時或幾天內部署的解決方案，并且不需要進行大規模的應用程序或者架構的更改。
• 好消息是，對大多數應用程序，您可以通過一些簡單的操作獲得數據庫性能的幾倍提升：

1.對您的云數據庫的規格進行擴容

復雜性：低

潛在影響：高

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

復雜性：中等潛在影響：高

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

復雜性：中等潛在影響：高

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

復雜性：中等潛在影響：中等

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

復雜性：低潛在影響：高

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

復雜性：低潛在影響：中

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

復雜性：低潛在影響：高

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

8.遷移到更快的存儲

復雜性：中潛在影響：高

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

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

復雜性：低潛在影響：高

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

復雜性：低潛在影響：高

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

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

復雜性：低潛在影響：高

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

復雜性：中潛在影響：中

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

復雜性：中潛在影響：高

• 配置不當的MySQL服務器可能會導致嚴重的問題，特別是在流量高峰的高負載情況下，但是正確的基礎配置并不難。雖然MySQL服務有400個參數可以調優，但您只需要更改其中的10-20個，就可以為您的工作負載獲得95%的可用性能。
• 這篇博文涵蓋了最重要的基礎知識。

復雜性：中潛在影響：中

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

復雜性：中潛在影響：中

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

復雜性：中潛在影響：中

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

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

復雜性：高潛在影響：高

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

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

復雜性：中潛在影響：中

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

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

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