Mysql占用內(nèi)存過高的優(yōu)化過程

mysql默認(rèn)配置，右列為當(dāng)前數(shù)據(jù)庫的配置，可見預(yù)期內(nèi)存使用最大值足足達(dá)到了1T，不符合當(dāng)前系統(tǒng)負(fù)載量，說明當(dāng)前配置不合理，需要進(jìn)行調(diào)整



(3).其中

key_buffer_size = 32M         //key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值。由于我的數(shù)據(jù)庫引擎為innodb，大部分表均為innodb，此處取默認(rèn)值一半32M。



query_cache_size = 64M    //查詢緩存大小，當(dāng)打開時(shí)候，執(zhí)行查詢語句會(huì)進(jìn)行緩存，讀寫都會(huì)帶來額外的內(nèi)存消耗，下次再次查詢?nèi)裘性摼彺鏁?huì)立刻返回結(jié)果。默認(rèn)改選項(xiàng)為關(guān)閉，打開則需要調(diào)整參數(shù)項(xiàng)query_cache_type=ON。此處采用默認(rèn)值64M。



tmp_table_size = 64M    //范圍設(shè)置為64-256M最佳，當(dāng)需要做類似group by操作生成的臨時(shí)表大小，提高聯(lián)接查詢速度的效果，調(diào)整該值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%，處于這樣一個(gè)狀態(tài)之下，效果較好，如果網(wǎng)站大部分為靜態(tài)內(nèi)容，可設(shè)置為64M，如果為動(dòng)態(tài)頁面，則設(shè)置為100M以上，不宜過大，導(dǎo)致內(nèi)存不足I/O堵塞。此處我們?cè)O(shè)置為64M。


innodb_buffer_pool_size = 8196M   //這個(gè)參數(shù)主要作用是緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時(shí)的緩沖。專用mysql服務(wù)器設(shè)置的大小： 操作系統(tǒng)內(nèi)存的70%-80%最佳。由于我們的服務(wù)器還部署有其他應(yīng)用，估此處設(shè)置為8G。此外，這個(gè)參數(shù)是非動(dòng)態(tài)的，要修改這個(gè)值，需要重啟mysqld服務(wù)。設(shè)置的過大，會(huì)導(dǎo)致system的swap空間被占用，導(dǎo)致操作系統(tǒng)變慢，從而減低sql查詢的效率。


innodb_additional_mem_pool_size = 16M    //用來存放Innodb的內(nèi)部目錄，這個(gè)值不用分配太大，系統(tǒng)可以自動(dòng)調(diào)。不用設(shè)置太高。通常比較大數(shù)據(jù)設(shè)置16Ｍ夠用了，如果表比較多，可以適當(dāng)?shù)脑龃蟆Ｈ绻@個(gè)值自動(dòng)增加，會(huì)在error log有中顯示的。此處我們?cè)O(shè)置為16M。


innodb_log_buffer_size = 8M  //InnoDB的寫操作，將數(shù)據(jù)寫入到內(nèi)存中的日志緩存中，由于InnoDB在事務(wù)提交前，并不將改變的日志寫入到磁盤中，因此在大事務(wù)中，可以減輕磁盤I/O的壓力。通常情況下，如果不是寫入大量的超大二進(jìn)制數(shù)據(jù)（a lot of huge blobs），4MB-8MB已經(jīng)足夠了。此處我們?cè)O(shè)置為8M。


max_connections = 800    //最大連接數(shù)，根據(jù)同時(shí)在線人數(shù)設(shè)置一個(gè)比較綜合的數(shù)字，最大不超過16384。此處我們根據(jù)系統(tǒng)使用量綜合評(píng)估，設(shè)置為800。

sort_buffer_size = 2M    //是一個(gè)connection級(jí)參數(shù)，在每個(gè)connection第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。并不是越大越好，由于是connection級(jí)的參數(shù)，過大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)內(nèi)存資源。官方文檔推薦范圍為256KB~2MB，這里我們?cè)O(shè)置為2M。


read_buffer_size = 2M   //（數(shù)據(jù)文件存儲(chǔ)順序）是MySQL讀入緩沖區(qū)的大小，將對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)，read_buffer_size變量控制這一緩沖區(qū)的大小，如果對(duì)表的順序掃描非常頻繁，并你認(rèn)為頻繁掃描進(jìn)行的太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能，read_buffer_size變量控制這一提高表的順序掃描的效率 數(shù)據(jù)文件順序。此處我們?cè)O(shè)置得比默認(rèn)值大一點(diǎn)，為2M。


read_rnd_buffer_size = 250K  //是MySQL的隨機(jī)讀緩沖區(qū)大小，當(dāng)按任意順序讀取行時(shí)（列如按照排序順序）將分配一個(gè)隨機(jī)讀取緩沖區(qū)，進(jìn)行排序查詢時(shí)，MySQL會(huì)首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要大量數(shù)據(jù)可適當(dāng)?shù)恼{(diào)整該值，但MySQL會(huì)為每個(gè)客戶連接分配該緩沖區(qū)所以盡量適當(dāng)設(shè)置該值，以免內(nèi)存開銷過大。表的隨機(jī)的順序緩沖 提高讀取的效率。此處設(shè)置為跟默認(rèn)值相似，250KB。


join_buffer_size = 250K   //多表參與join操作時(shí)的分配緩存，適當(dāng)分配，降低內(nèi)存消耗，此處我們?cè)O(shè)置為250KB。


thread_stack = 256K   //每個(gè)連接線程被創(chuàng)建時(shí)，MySQL給它分配的內(nèi)存大小。當(dāng)MySQL創(chuàng)建一個(gè)新的連接線程時(shí)，需要給它分配一定大小的內(nèi)存堆棧空間，以便存放客戶端的請(qǐng)求的Query及自身的各種狀態(tài)和處理信息。Thread Cache 命中率：Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率處于90%才算正常配置，當(dāng)出現(xiàn)“mysql-debug: Thread stack overrun”的錯(cuò)誤提示的時(shí)候需要增加該值。此處我們配置為256K。


binlog_cache_size = 250K  // 為每個(gè)session 分配的內(nèi)存，在事務(wù)過程中用來存儲(chǔ)二進(jìn)制日志的緩存。作用是提高記錄bin-log的效率。沒有什么大事務(wù)，dml也不是很頻繁的情況下可以設(shè)置小一點(diǎn)，如果事務(wù)大而且多，dml操作也頻繁，則可以適當(dāng)?shù)恼{(diào)大一點(diǎn)。前者建議是1048576  --1M；后者建議是： 2097152 -- 4194304  即 2--4M。此處我們根據(jù)系統(tǒng)實(shí)際，配置為250KB。





調(diào)整后各項(xiàng)性能參數(shù)如下圖，且經(jīng)過圖表計(jì)算，實(shí)例使用的內(nèi)存將穩(wěn)定在12G左右，符合當(dāng)前系統(tǒng)負(fù)載情況



之后重啟Mysql實(shí)例，發(fā)現(xiàn)內(nèi)存占用量回落，并且長時(shí)間內(nèi)沒有再次發(fā)生占用過高情況，優(yōu)化成功。



三.總結(jié)：

具體涉及到內(nèi)存分配，緩存的參數(shù)及其具體作用在此不一一贅述，后續(xù)可自行查閱相關(guān)資料，只有多次根據(jù)實(shí)際觀測結(jié)果調(diào)優(yōu)，才能得到符合當(dāng)前業(yè)務(wù)系統(tǒng)運(yùn)行的最佳配置。
---------------------