歡迎光臨
每天分享高質量文章

MySQL 性能調優技巧

來自:ITeye,作者:Shree Nair ,翻譯:無阻我飛揚

鏈接:www.iteye.com/news/32796

原文:dzone.com/articles/mysql-performance-tuning-tips-for-the-shopping-sea


針對購物旺季網站流量會對資料庫造成的壓力,作者給出了MySQL性能調優的一些技巧,這些技巧極具參考價值,通過這些調優,可以有效避免因為流量過大造成服務器宕機,從而給企業造成經濟損失。


技巧#1:確定MySQL的最大連接數


對於MySQL的最大連接數,一次最好是發送5個請求到Web服務器。對Web服務器的5個請求中的一部分將用於CSS樣式表,圖像和腳本等資源。由於諸如瀏覽器快取等原因,要獲得準確的MySQL到Web服務器的請求比率可能很困難; 要想得到一個確切的數字,就需要分析Web服務器的日誌檔案。例如,可以手動訪問Apache的“access_log”日誌檔案,也可以通過AnalogWebalizer等實用程式訪問日誌檔案。


一旦有了對特定使用情況的準確估計,請將該比率乘以Web服務器的最大連接數。例如,如果Web服務器配置為最多為256個客戶端提供服務,MySQL請求與Web請求的比率為1/8,則最好將最大資料庫連接數設置為32。還要考慮留有安全餘量,把這個數乘以2,得到最終的數量。只有在基礎設施支持的情況下,才能嘗試將資料庫連接數的最大數量與Web服務器的客戶端限制相匹配。在大多數情況下,最好保持接近32。


在Monyog中查看MySQL連接


在MySQL資料庫中,MySQL的最大併發連接數是儲存在全域性變數max_connections中的。Monyog報告變數“max_connections”作為當前連接監控組中的“最大允許”指標。它還將該數字除以打開的連接數,以生成連接使用百分比:


還有一個連接歷史記錄監控,可以幫助計算最佳的最大併發連接數。它包括嘗試,拒絕和成功連接的數量。此外,允許達到的最大指標的百分比顯示為一個進度條,可以讓你快速評估服務器在過去達到的最大併發連接數:


技巧#2:為臨時表分配足夠的記憶體


在某些情況下,服務器在處理陳述句時會創建內部臨時表。臨時表用於內部操作如GROUP BY和distinct,還有一些ORDER BY查詢以及UNION和FROM子句(派生表)中的子查詢。這些都是在記憶體中創建的記憶體表。記憶體中臨時表的最大大小由tmp_table_size和max_heap_table_size中較小的值確定。如果臨時表的大小超過這個閾值,則將其轉換為磁盤上的InnoDB或MyISAM表。此外,如果查詢涉及BLOB或TEXT列,而這些列不能儲存在記憶體表中,臨時表總是直接指向磁盤。


這種轉換的代價很大,所以考慮增加max_heap_table_size和tmp_table_size變數的大小來幫助減少在磁盤上創建臨時表的數量。請記住,這將需要大量記憶體,因為記憶體中臨時表的大小是基於“最壞情況”的。例如,記憶體表總是使用固定長度的列,所以字符列使用VARCHAR(255)。這可以使記憶體中的臨時表比想象的要大得多—事實上,這比查詢表的總大小要大很多倍!當增加max_heap_table_size和tmp_table_sizevariables的大小時,一定要監視服務器的記憶體使用情況,因為記憶體中的臨時表可能會增加達到服務器記憶體容量的風險。


一般來說,32M到64M是建議值,從這兩個變數開始並根據需要進行調優。


在Monyog中的臨時表監測


臨時表的監測是許多預定義的Monyog監測之一。它提供了一些臨時表使用的指標,包括:


  • 允許的最大值:顯示tmp_table_size服務器變數的值,它定義了在記憶體中創建的臨時表的最大大小。與max_heap_table_size一起,這個值定義了可以在記憶體中創建的臨時表的最大大小。如果記憶體臨時表大於此大小,則將其儲存在磁盤上。

  • 記憶體表的最大大小:顯示max_heap_table_size服務器變數的值,該值定義了顯式創建的MEMORY儲存引擎表的最大大小。

  • 創建的臨時表總數:顯示created_tmp_tables服務器變數的值,它定義了在記憶體中創建的臨時表的數量。

  • 在磁盤上創建的臨時表:顯示created_tmp_disk_tables服務器變數的值,該變數定義了在磁盤上創建的臨時表的數量。如果這個值很高,則應該考慮增加tmp_table_size和max_heap_table_size的值,以便增加創建記憶體臨時表的數量,從而減少在磁盤上創建臨時表的數量。

  • 磁盤:總比率:基於created_tmp_disk_tables除以created_tmp_tables的計算值。由於tmp_table_size或max_heap_table_size不足而在磁盤上創建的臨時表的百分比。Monyog將這個數字顯示為一個進度條和百分比,以便快速確定有多少磁盤用於臨時表,而不是記憶體。


趨勢圖可用於創建的總表,磁盤上創建的表和磁盤的總比值。這些讓我們看到了它們隨著時間的演變:


技巧#3:增加執行緒快取大小


連接管理器執行緒處理服務器監聽的網絡接口上的客戶端連接請求。連接管理器執行緒將每個客戶端連接與專用於它的執行緒關聯,該執行緒負責處理該連接的身份驗證和所有請求處理。因此,執行緒和當前連接的客戶端之間是一對一的比例。確保執行緒快取足夠大以容納所有傳入請求是非常重要的。

MySQL提供了許多與連接執行緒相關的服務器變數:


執行緒快取大小由thread_cache_size系統變數決定。預設值為0(無快取),這將導致為每個新連接設置一個執行緒,併在連接終止時需要處理該執行緒。如果希望服務器每秒接收數百個連接請求,那麼應該將thread_cache_size設置的足夠高,以便大多數新連接可以使用快取執行緒。可以在服務器啟動或運行時設置max_connections的值。


還應該監視快取中的執行緒數(Threads_cached)以及創建了多少個執行緒,因為無法從快取中獲取執行緒(Threads_created)。關於後者,如果Threads_created繼續以每分鐘多於幾個執行緒的增加,請考慮增加thread_cache_size的值。


使用MySQL show status命令顯示MySQL的變數和狀態信息。這裡有幾個例子:


Monyog執行緒快取監測


Monyog提供了一個監控執行緒快取的屏幕,名為“執行緒”。與MySQL執行緒相關的服務器變數映射到以下Monyog指標:


  • thread_cache_size:可以快取的執行緒數。

  • Threads_cached:快取中的執行緒數。

  • Threads_created:創建用於處理連接的執行緒。


Monyog執行緒屏幕還包括“執行緒快取命中率”指標。這是一個提示執行緒快取命中率的指標。如果值較低,則應該考慮增加執行緒快取。在狀態欄以百分比形式顯示該值;它的值越接近100%越好。


如果這些指標的值等於或超過指定值,則可以將每一個指標配置為發出警告和/或嚴重警報。


其他相關的服務器變數


除了上述指標以外,還應該監控以下內容:


  1. InnoDB緩衝池大小: InnoDB緩衝池大小在使用InnoDB的MySQL資料庫中起著至關重要的作用。緩衝池同時快取資料和索引。它的值應該盡可能的大,以確保資料庫使用記憶體而不是硬碟驅動器進行讀取操作。

  2. 臨時表大小: MySQL使用max_heap_table_size和tmp_table_size中較小的一個來限制記憶體中臨時表的大小。擁有較大的值可以幫助減少在磁盤上創建臨時表的數量,但也會增加服務器記憶體容量的風險,因為這個指標適用於每個客戶端。一般來說,32M到64M是建議的值,從這兩個變數開始並根據需要進行調優。

  3. InnoDB日誌緩衝區大小: MySQL每次寫入日誌檔案時,它都會利用可用於處理銷售資料的重要系統資源。因此,將InnoDB日誌緩衝區大小設置為較大值才有意義。這樣,服務器在大型事務中寫入磁盤的次數就減少了,從而最大限度地減少了這些耗時的操作。64M是這個變數的一個很好的起點。


結論


雖然即便是最大的公司網站也會因宕機而遭受損失,但這種影響對於處理網上銷售的中小型企業尤其關鍵。根據最近的一份調查報告顯示,一分鐘的宕機導致企業平均損失約5000美元。不要讓你的業務成為那種統計資料(因為宕機造成的損失)的一部分。在假日繁忙之前,主動調優MySQL資料庫服務器(S)並收穫回報吧!


●本文編號261,以後想閱讀這篇文章直接輸入261即可

●輸入m獲取到文章目錄

推薦↓↓↓

 

Web開發

更多推薦18個技術類公眾微信

涵蓋:程式人生、演算法與資料結構、黑客技術與網絡安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

赞(0)

分享創造快樂