一.環境說明: 操作系統:CentOS 6.5 x86_64 資料庫:Mysql 5.6.22 伺服器:阿裡雲VPS,32G Mem,0 swap 二.問題情況: 1.某日發現公司線上系統的Mysql某個實例的從庫長時間記憶體占用達到60%如下圖 2.於是開始按照以下步驟排查: (1).查看mysql ...
一.環境說明:
操作系統:CentOS 6.5 x86_64
資料庫:Mysql 5.6.22
伺服器:阿裡雲VPS,32G Mem,0 swap
二.問題情況:
1.某日發現公司線上系統的Mysql某個實例的從庫長時間記憶體占用達到60%如下圖
2.於是開始按照以下步驟排查:
(1).查看mysql里的線程,觀察是否有長期運行或阻塞的sql:
show full processlist
經查看,沒有發現相關線程,可排除該原因
(2).疑似mysql連接使用完成後沒有真正釋放記憶體,查看mysql記憶體,緩存的相關配置,使用如
show global variables like '%sort_buffer_size%';
查看相關的配置項,結果列表彙總如下
( 註:上圖為mysql使用記憶體計算器,具體地址為http://www.mysqlcalculator.com/ )
其中左列為mysql預設配置,右列為當前資料庫的配置,可見預期記憶體使用最大值足足達到了1T,不符合當前系統負載量,說明當前配置不合理,需要進行調整
(3).其中
key_buffer_size = 32M //key_buffer_size指定索引緩衝區的大小,它決定索引處理的速度,尤其是索引讀的速度。只對MyISAM表起作用。即使你不使用MyISAM表,但是內部的臨時磁碟表是MyISAM表,也要使用該值。由於我的資料庫引擎為innodb,大部分表均為innodb,此處取預設值一半32M。
query_cache_size = 64M //查詢緩存大小,當打開時候,執行查詢語句會進行緩存,讀寫都會帶來額外的記憶體消耗,下次再次查詢若命中該緩存會立刻返回結果。預設改選項為關閉,打開則需要調整參數項query_cache_type=ON。此處採用預設值64M。
tmp_table_size = 64M //範圍設置為64-256M最佳,當需要做類似group by操作生成的臨時表大小,提高聯接查詢速度的效果,調整該值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%,處於這樣一個狀態之下,效果較好,如果網站大部分為靜態內容,可設置為64M,如果為動態頁面,則設置為100M以上,不宜過大,導致記憶體不足I/O堵塞。此處我們設置為64M。
innodb_buffer_pool_size = 8196M //這個參數主要作用是緩存innodb表的索引,數據,插入數據時的緩衝。專用mysql伺服器設置的大小: 操作系統記憶體的70%-80%最佳。由於我們的伺服器還部署有其他應用,估此處設置為8G。此外,這個參數是非動態的,要修改這個值,需要重啟mysqld服務。設置的過大,會導致system的swap空間被占用,導致操作系統變慢,從而減低sql查詢的效率。
innodb_additional_mem_pool_size = 16M //用來存放Innodb的內部目錄,這個值不用分配太大,系統可以自動調。不用設置太高。通常比較大數據設置16M夠用了,如果表比較多,可以適當的增大。如果這個值自動增加,會在error log有中顯示的。此處我們設置為16M。
innodb_log_buffer_size = 8M //InnoDB的寫操作,將數據寫入到記憶體中的日誌緩存中,由於InnoDB在事務提交前,並不將改變的日誌寫入到磁碟中,因此在大事務中,可以減輕磁碟I/O的壓力。通常情況下,如果不是寫入大量的超大二進位數據(a lot of huge blobs),4MB-8MB已經足夠了。此處我們設置為8M。
max_connections = 800 //最大連接數,根據同時線上人數設置一個比較綜合的數字,最大不超過16384。此處我們根據系統使用量綜合評估,設置為800。
sort_buffer_size = 2M //是一個connection級參數,在每個connection第一次需要使用這個buffer的時候,一次性分配設置的記憶體。並不是越大越好,由於是connection級的參數,過大的設置+高併發可能會耗盡系統記憶體資源。官方文檔推薦範圍為256KB~2MB,這裡我們設置為2M。
read_buffer_size = 2M //(數據文件存儲順序)是MySQL讀入緩衝區的大小,將對錶進行順序掃描的請求將分配一個讀入緩衝區,MySQL會為它分配一段記憶體緩衝區,read_buffer_size變數控制這一緩衝區的大小,如果對錶的順序掃描非常頻繁,並你認為頻繁掃描進行的太慢,可以通過增加該變數值以及記憶體緩衝區大小提高其性能,read_buffer_size變數控制這一提高表的順序掃描的效率 數據文件順序。此處我們設置得比預設值大一點,為2M。
read_rnd_buffer_size = 250K //是MySQL的隨機讀緩衝區大小,當按任意順序讀取行時(列如按照排序順序)將分配一個隨機讀取緩衝區,進行排序查詢時,MySQL會首先掃描一遍該緩衝,以避免磁碟搜索,提高查詢速度,如果需要大量數據可適當的調整該值,但MySQL會為每個客戶連接分配該緩衝區所以儘量適當設置該值,以免記憶體開銷過大。表的隨機的順序緩衝 提高讀取的效率。此處設置為跟預設值相似,250KB。
join_buffer_size = 250K //多表參與join操作時的分配緩存,適當分配,降低記憶體消耗,此處我們設置為250KB。
thread_stack = 256K //每個連接線程被創建時,MySQL給它分配的記憶體大小。當MySQL創建一個新的連接線程時,需要給它分配一定大小的記憶體堆棧空間,以便存放客戶端的請求的Query及自身的各種狀態和處理信息。Thread Cache 命中率:Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率處於90%才算正常配置,當出現“mysql-debug: Thread stack overrun”的錯誤提示的時候需要增加該值。此處我們配置為256K。
binlog_cache_size = 250K // 為每個session 分配的記憶體,在事務過程中用來存儲二進位日誌的緩存。作用是提高記錄bin-log的效率。沒有什麼大事務,dml也不是很頻繁的情況下可以設置小一點,如果事務大而且多,dml操作也頻繁,則可以適當的調大一點。前者建議是1048576 --1M;後者建議是: 2097152 -- 4194304 即 2--4M。此處我們根據系統實際,配置為250KB。
調整後各項性能參數如下圖,且經過圖表計算,實例使用的記憶體將穩定在12G左右,符合當前系統負載情況
之後重啟Mysql實例,發現記憶體占用量回落,並且長時間內沒有再次發生占用過高情況,優化成功。
三.總結:
具體涉及到記憶體分配,緩存的參數及其具體作用在此不一一贅述,後續可自行查閱相關資料,只有多次根據實際觀測結果調優,才能得到符合當前業務系統運行的最佳配置。
參考文章:http://blog.csdn.net/zhu19774279/article/details/38645663