原文鏈接:MySQL Memory Allocation -- by Rick James
原文日期: Created 2010; Refreshed Oct, 2012, Jan, 2014
翻譯人員:鐵錨
翻譯日期: 2014年5月28日
MySQL 內(nèi)存分配—— 快速設(shè)置方案
如果僅使用MyISAM存儲(chǔ)引擎,設(shè)置key_buffer_size為可用內(nèi)存的20%,(再加上設(shè)置 innodb_buffer_pool_size = 0 )
如果僅使用InnoDB存儲(chǔ)引擎,設(shè)置innodb_buffer_pool_size為可用內(nèi)存的 70%, (設(shè)置 key_buffer_size = 10M,很小但不是0.)
調(diào)優(yōu)mysql的實(shí)踐經(jīng)驗(yàn):

首先拷貝 my.cnf / my.ini 文件副本.

根據(jù)使用的存儲(chǔ)引擎及可用內(nèi)存,設(shè)置 key_buffer_size 和innodb_buffer_pool_size.

慢查詢(Slow queries)的修正一般是通過(guò)添加索引(indexes),改變表結(jié)構(gòu)(schema),改變 SELECT 語(yǔ)句 來(lái)實(shí)現(xiàn),而不是通過(guò)數(shù)據(jù)庫(kù)調(diào)優(yōu).

不要隨便設(shè)置查詢緩存(Query cache),除非你真正掌握它的優(yōu)缺點(diǎn)以及適用場(chǎng)景.

不要改變其他的參數(shù),除非你遇到了相應(yīng)的問(wèn)題(如最大連接數(shù)問(wèn)題, max connections).

確保修改的是 [mysqld] 這一節(jié)下的內(nèi)容,而不是其他部分.

下面向您展示一些實(shí)際的細(xì)節(jié). (本文不涉及 NDB Cluster)
什么是索引緩存(key_buffer)?
MyISAM引擎的緩存分為兩部分.

索引塊(Index blocks,每個(gè)1 KB,BTree結(jié)構(gòu)、存放于 .MYI 文件) 緩存到 “key buffer” 中.

數(shù)據(jù)塊緩存(Data block caching, 存放于 .MYD 文件中)交給操作系統(tǒng)負(fù)責(zé), 所以確保留下了適量的空閑內(nèi)存(給操作系統(tǒng)).

警告: 某些類型的操作系統(tǒng)總是報(bào)告說(shuō)內(nèi)存使用超過(guò)90%,雖然實(shí)際上還有很多的空閑內(nèi)存.
SHOW GLOBAL STATUS LIKE 'Key%';執(zhí)行后計(jì)算 Key_read_requests / Key_reads 的值, 如果比值較大(比如大于10), 那么 key_buffer 就足夠了.
什么是緩存池(buffer_pool)?
InnoDB將所有緩存都放在 “buffer pool” 中, 緩存池的大小通過(guò)innodb_buffer_pool_size控制. 包含被打開(kāi)表(open tables)中的 16KB一塊的數(shù)據(jù)/索引塊,此外還有一些附加開(kāi)銷.
MySQL 5.5(以及帶插件的 5.1版本)允許您指定 塊大小(block size)為 8 KB或4 KB. MySQL 5.5可以有多個(gè)緩沖池,因?yàn)槊總€(gè)緩存池有一個(gè)互斥鎖, 所以設(shè)置多個(gè)池可以緩解一些互斥鎖瓶頸.
更多InnoDB調(diào)優(yōu)信息
另一種計(jì)算緩存大小的方法
將主緩存(main cache)設(shè)置為最小值; 如果同一臺(tái)機(jī)器上有許多其他應(yīng)用在跑, 并且/或者RAM內(nèi)存小于2GB, 那么可以這樣指定.
SHOW TABLE STATUS; 顯示各個(gè)數(shù)據(jù)庫(kù)中所有表的狀態(tài).

計(jì)算所有MyISAM表的 Index_length 值的總和. 讓 key_buffer_size 小于等于這個(gè)和值.

計(jì)算所有 InnoDB表 Data_length + Index_length 值的總和. 設(shè)置 innodb_buffer_pool_size 為不超過(guò)總和值的110%.

如果有內(nèi)存交換(swapping發(fā)生),需要將兩個(gè)參數(shù)適量地按減小一些.
執(zhí)行下面的SQL語(yǔ)句查看適合的參數(shù)值. (如果有很多表,可能耗時(shí)幾分鐘.)
互斥鎖瓶頸
MySQL 是單核CPU時(shí)代設(shè)計(jì)的,且可以很容易移植到不同的硬件體系架構(gòu)中. 不幸的是,這導(dǎo)致了對(duì)連結(jié)鎖(interlock)操作的凌亂. 在幾個(gè)重要的流程中存在少量(非常少)的“互斥(mutexes)”. 包括:

MyISAM的 key_buffer

查詢緩存(Query Cache)

InnoDB的buffer_pool

隨著多核CPU的盛行,互斥問(wèn)題引起了MySQL的性能問(wèn)題. 一般來(lái)說(shuō),CPU超過(guò) 4~8 核越多,則MySQL變得越慢,而不會(huì)更快. MySQL 5.5 中 InnoDB 的增強(qiáng)版 Percona XtraDB 對(duì)多核CPU的支持要好很多; 實(shí)際的限制大致是32核, CPU核心超過(guò)這個(gè)數(shù)后性能會(huì)達(dá)到瓶頸 ,但不再下降. MySQL 5.6版聲稱最多可以支持48核.
超線程和多核CPU
簡(jiǎn)單的處理方式:

禁用超線程(HyperThreading)

停用超過(guò)8個(gè)核心以上的部分

超線程這里主要是指以前的超線程技術(shù),因此此部分可能不一定正確.

超線程適合拿來(lái)做營(yíng)銷宣傳,但對(duì)(專用應(yīng)用的)性能極不友好. 有兩個(gè)處理單元在共享同一個(gè)物理緩存. 如果這兩個(gè)線程在做同樣的事情,緩存會(huì)相當(dāng)高效. 如果這倆線程在干不同的事,他們會(huì)相互妨礙到另一個(gè)(超)線程的緩存項(xiàng).
總的來(lái)說(shuō)MySQL在多核處理上并不占優(yōu)勢(shì). 所以,如果禁用超線程(HT),剩下的核心將會(huì)運(yùn)行得更快一點(diǎn).
32位操作系統(tǒng)和MySQL
(譯者注: 肯定64位的MySQL在 32位OS上跑不起來(lái)...)
首先,操作系統(tǒng)(以及硬件?) 會(huì)限制進(jìn)程不能使用4GB RAM中的全部,如果有 4G內(nèi)存的話. 如果物理 RAM 超過(guò) 4 GB, 超過(guò)的部分在32位操作系統(tǒng)中不可訪問(wèn),也是不可用的.
其次,操作系統(tǒng)可能會(huì)限制單個(gè)進(jìn)程最大使用多少內(nèi)存.
例如:FreeBSD的maxdsiz,默認(rèn)為512 MB.
示例:
因此,確定了 mysqld有多少可用內(nèi)存, 就可以設(shè)置為 20% ~ 70%,但需要適當(dāng)?shù)臏p少一些.
如果系統(tǒng)報(bào)錯(cuò),例如[ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes), 可能是MySQL申請(qǐng)了超過(guò)操作系統(tǒng)允許的內(nèi)存范圍. 需要減小緩存設(shè)置.
64位OS與32位MySQL
64位操作系統(tǒng)不受4 GB內(nèi)存的限制,但32位MySQL依然受這個(gè)限制.
如果你有 4 GB以上的內(nèi)存,那么可以設(shè)置:

key_buffer_size = 20%(所有RAM的),但不要超過(guò)3 GB.

buffer_pool = 3G

當(dāng)然最好的辦法是將MySQL換成64位版本.
64位OS與64位MySQL

只使用MyISAM引擎: (5.0.52 ~ 5.1.23之前的)key_buffer_size有 4GB的硬性限制. 詳情請(qǐng)參考MySQL 5.1 限制(restrictions) 在更高版本中,設(shè)置 key_buffer_size 為 20%的RAM. 在(my.cnf / my.ini)中加上 innodb_buffer_pool_size = 0.

只使用InnoDB引擎: 設(shè)置 innodb_buffer_pool_size = 70%的RAM. 如果內(nèi)存很大,并使用 5.5(及以上)版本,可以考慮使用 多個(gè)緩存池. 推薦設(shè)置 1 - 16 個(gè) innodb_buffer_pool_instances, 每個(gè)都不小于1 GB. (很抱歉,沒(méi)有最優(yōu)設(shè)置為多少個(gè)的具體參考指標(biāo);但應(yīng)該不能設(shè)置太多).

與此同時(shí),設(shè)置 key_buffer_size = 20M(很小,但不是零)
如果你在數(shù)據(jù)庫(kù)中混合使用多個(gè)引擎,將兩個(gè)值都降低一些.
最大連接數(shù),線程棧
(max_connections,thread_stack)
每個(gè)“線程”都要占用一定的內(nèi)存. 通常為 200 KB左右; 因此 100個(gè)線程大概就是 20 MB. 如果設(shè)置max_connections= 1000,那大概就需要 200 MB,或者更多. 同時(shí)連接數(shù)太大可能會(huì)引起其他某些問(wèn)題,這點(diǎn)需要注意.
在5.6(或 MariaDB5.5)中,可以選擇線程池與 max_connections 交互. 這是一個(gè)高級(jí)話題.
線程棧溢出很少出現(xiàn). 如果確實(shí)發(fā)生了,可以設(shè)置: thread_stack = 256K
點(diǎn)擊查看更多關(guān)于max_connections, wait_timeout,連接池的討論
table_cache(table_open_cache)
(某些版本中名字不一樣).
操作系統(tǒng)對(duì)單個(gè)進(jìn)程能打開(kāi)的文件數(shù)有限制. 打開(kāi)每個(gè)表需要 1-3個(gè)文件. 每個(gè)表分區(qū)(PARTITION)等價(jià)于一個(gè)表. 在分區(qū)表上的多數(shù)操作都會(huì)打開(kāi)所有的分區(qū).
在 *nix中, ulimit 顯示文件限制是多少. 最大值一般是上萬(wàn),但有可能被設(shè)置為 1024. 這就限制了只能打開(kāi)300個(gè)左右的表. 更多關(guān)于ulimit的討論請(qǐng)點(diǎn)擊這里
(這一段是有爭(zhēng)議的.) 另一方面,表緩存(過(guò)去?)的實(shí)現(xiàn)方式很低效 —— 查找通過(guò)線性掃描來(lái)完成. 因此,設(shè)置 table_cache 為幾千確實(shí)會(huì)使得 mysql變慢. (基準(zhǔn)測(cè)試也證明了這一點(diǎn).)
你可以通過(guò) SHOW GLOBAL STATUS;查看系統(tǒng)的性能信息, 并計(jì)算 每秒打開(kāi)數(shù)(opens/second): Opened_files /Uptime , 如果這個(gè)值較大,例如大于 5, 那么應(yīng)該加大 table_cache; 如果很小,比如是 1,通過(guò)減小 table_cache 值,可能會(huì)對(duì)性能有所改善.
查詢緩存(Query Cache)
簡(jiǎn)短的回答: 設(shè)置query_cache_type = OFF及query_cache_size = 0
QC(Query Cache)實(shí)際上是將 SELECT語(yǔ)句與結(jié)果集(resultsets)進(jìn)行散列映射.
詳細(xì)的回答…… 關(guān)于“查詢緩存”有許多種觀點(diǎn); 其中許多是負(fù)面的.

新手警告! QC與key_buffer和buffer_pool完全無(wú)關(guān).

當(dāng)命中時(shí), QC速度快如閃電. 要?jiǎng)?chuàng)建一個(gè)運(yùn)行快1000倍的基準(zhǔn)測(cè)試并不難.

在QC中只有一個(gè)互斥鎖(譯者注: 鎖越少,就是鎖鑰匙越少,高并發(fā)時(shí)就會(huì)激烈競(jìng)爭(zhēng)/等待).

除非將QC設(shè)置為OFF與0,否則每次查詢都會(huì)去對(duì)比一遍.

真相,互斥鎖會(huì)發(fā)生碰撞,即使 query_cache_type = DEMAND (2).

真相,互斥鎖會(huì)發(fā)生碰撞,即便設(shè)置了 SQL_NO_CACHE.

查詢語(yǔ)句只要變了一點(diǎn)點(diǎn)(即使多了個(gè)空格)都可能導(dǎo)致在QC中生成多個(gè)不同的緩存項(xiàng).

“修改”是代價(jià)高昂與頻繁的:

在一個(gè)表中發(fā)生任何 write 事件, QC中對(duì)應(yīng)到這個(gè)表的所有條目都會(huì)被清除.

即便在只讀從服務(wù)器(readonly Slave)上也是這樣.

清除使用的是線性算法來(lái)執(zhí)行,所以QC較大(比如200MB)則會(huì)導(dǎo)致速度明顯地變慢.

要查看QC的執(zhí)行效率如何,執(zhí)行SHOW GLOBAL STATUS LIKE 'Qc%';然后計(jì)算read的命中率: Qcache_hits / Qcache_inserts, 如果大于5,則 QC的效率還不錯(cuò).
如果QC適合你的應(yīng)用,那么我推薦:

query_cache_size = 不超過(guò)50M

query_cache_type = DEMAND

在所有 SELECT 語(yǔ)句中指明 SQL_CACHE 或 SQL_NO_CACHE, 根據(jù)哪些查詢可能會(huì)從QC緩存中命中.

深入了解Query Cache
thread_cache_size
這是一個(gè)很小的調(diào)優(yōu)項(xiàng). 設(shè)置為 0 會(huì)降低線程(連接)創(chuàng)建的速度. 設(shè)置為較小的值(比如 10) 是比較好的. 該選項(xiàng)對(duì)RAM沒(méi)有多少影響.
它是服務(wù)器額外保持的線程數(shù)量,不會(huì)影響實(shí)際線程數(shù); 起限制作用的是 max_connections.
二進(jìn)制日志
如果為 復(fù)制(replication) 或 時(shí)間點(diǎn)恢復(fù)(point-in-time recovery) 啟用二進(jìn)制日志(通過(guò) og_bin開(kāi)啟), 則服務(wù)器將一直記錄二進(jìn)制日志(binary logs). 也就是說(shuō),可能慢慢地占用磁盤(pán). 建議設(shè)置expire_logs_days = 14,只保留14天的日志記錄.
swappiness

RHEL,非常英明地,允許用戶自己控制 OS 如何進(jìn)行預(yù)先內(nèi)存交換分配. 總的來(lái)說(shuō)這是很好的策略,但對(duì)MySQL來(lái)說(shuō)則是一個(gè)災(zāi)難.

(感覺(jué)翻譯的有點(diǎn)不流暢，本段原文為: RHEL, in its infinite wisdom, decided to let you control how aggressively the OS will preemptively swap RAM. This is good in general, but lousy for MySQL)

MySQL期望相當(dāng)穩(wěn)定的內(nèi)存分配 —— 緩存(大部分)是預(yù)先分配的; 線程(大都)是限制數(shù)量的. 任何內(nèi)存交換都可能極大地?fù)p害MySQL的性能.
設(shè)置很高的swappiness值,會(huì)丟失一些內(nèi)存,因?yàn)椴僮飨到y(tǒng)試圖為以后的分配保留大量的自由空間(MySQL一般是不需要的).
設(shè)置swappiness = 0,不交換,在內(nèi)存不足時(shí)操作系統(tǒng)可能會(huì)崩潰,. 我寧愿MySQL一卡一卡的,也不希望他崩了.
對(duì)于MySQL-only(專用)服務(wù)器, 中間數(shù)(比如5 ?)可能是一個(gè)很好的值.
NUMA
OK,是時(shí)候了解一些CPU管理內(nèi)存的架構(gòu)了. 我們先看NUMA(Non-Uniform Memory Access, 非統(tǒng)一內(nèi)存尋址). 每個(gè)CPU(或多路服務(wù)器中的每個(gè)socket(CPU插座)) 都掛載有一部分內(nèi)存. 這使得訪問(wèn)本地(local) RAM 非常快, 而訪問(wèn)掛載在其他 CPU下的RAM要慢上數(shù)十個(gè)周期.
接著看操作系統(tǒng). 在(RHEL ?)很多情形下,有兩個(gè)行為:

OS分配的內(nèi)存固定到 “first(第一個(gè))” CPU名下.

接著分配的其他內(nèi)存也默認(rèn)分配到第一個(gè)CPU名下,直到它滿了.

現(xiàn)在問(wèn)題來(lái)了.

OS與MySQL分配完了第一個(gè) CPU的所有RAM.

MySQL分配了第二個(gè) CPU的部分內(nèi)存.

操作系統(tǒng)OS還需要分配一些其他內(nèi)存.

Ouch —— 一個(gè)CPU需要分配內(nèi)存,但自己名下控制的RAM已經(jīng)耗盡了,所以它將MySQL的部分內(nèi)存置換出去. 渣渣！
可能的解決方案:配置BIOS內(nèi)存分配為 “interleave”(交錯(cuò)). 這將防止過(guò)早交換(premature swapping),代價(jià)是有一半左右的 RAM 訪問(wèn)要跨CPU(off-CPU). 嗯,不論如何訪問(wèn)的代價(jià)都較大, 如果真的要使用所有內(nèi)存的話.
整體性能損失/收益:幾個(gè)百分點(diǎn).
大內(nèi)存分頁(yè)(huge pages)
這里有另一個(gè)硬件性能陷阱.
CPU訪問(wèn)RAM,特別是將64位地址映射到某個(gè)地方, 比如 128 GB 或“真實(shí)”的RAM,會(huì)使用TLB. (TLB =Translation Lookaside Buffer,旁路轉(zhuǎn)換緩沖.) TLB是硬件實(shí)現(xiàn)的內(nèi)存關(guān)聯(lián)查找表; 將64位的虛擬地址轉(zhuǎn)換到實(shí)際的物理地址.
因?yàn)門(mén)LB是一個(gè)小的,虛擬尋址的緩存,有時(shí)會(huì)發(fā)生 “misses”(未命中),那就會(huì)進(jìn)入物理RAM來(lái)查找. 這是兩次查找是很費(fèi)時(shí)的操作,所以應(yīng)該避免.
通常,內(nèi)存被 “分頁(yè)” 為 4 KB一頁(yè),TLB實(shí)際上將高位的(64 - 12)位映射到一個(gè)特定頁(yè)面. 而低12位通過(guò)虛地址轉(zhuǎn)換得到完整的地址.
例如,128 GB的RAM按 4 KB分頁(yè)需要 32M(3200萬(wàn)個(gè)) page-table條目. 這太大了, 遠(yuǎn)遠(yuǎn)超過(guò)TLB的容量. 所以陷入了“Huge page”的騙局.
隨著硬件與操作系統(tǒng)的支持,使部分RAM成為巨型頁(yè)面成為可能 ,比如說(shuō)4 MB(而不是4 KB). 這使得TLB條目劇減,對(duì)這部分RAM來(lái)說(shuō)分頁(yè)單元是4 MB. 因此,巨大的頁(yè)面相當(dāng)于是不分頁(yè)的(non-pagable).
現(xiàn)在內(nèi)存被分為 pagable 和 non pagable 兩部分; 哪些部分 non pagable 是合理的? 在MySQL中, innodb_buffer_pool 就是一個(gè)完美的使用者. 通過(guò)正確地配置這些,InnoDB能跑得更快一點(diǎn):

啟用 Huge pages

通知操作系統(tǒng)分配適當(dāng)?shù)臄?shù)量(和 buffer_pool 個(gè)數(shù)一致)

通知MySQL使用huge pages

innodb memory usage vs swap 該帖包含有很多需要關(guān)注點(diǎn)以及如何設(shè)置的細(xì)節(jié).
整體性能收益:幾個(gè)百分點(diǎn). Yawn.
MEMORY引擎(ENGINE=MEMORY)
這是一個(gè)不常用的存儲(chǔ)引擎,算是MyISAM和InnoDB的替代品. 其數(shù)據(jù)不是持久的,所以其應(yīng)用范圍相當(dāng)有限. 內(nèi)存表的大小受限于 max_heap_table_size ,默認(rèn)值是16 MB. 我提起它,以防你將此值修改得太大;這會(huì)偷偷地占用可用的RAM.
如何設(shè)置變量(VARIABLEs)
在文本文件my.cnf中(Windows上是my.ini),添加一行,例如
innodb_buffer_pool_size = 5G
即: 變量名,等號(hào)“=”,變量的值. 有些值允許縮寫(xiě),如M代表 million(1048576),G代表billion.
要讓服務(wù)器看到這些設(shè)置,必須將其放到配置文件的 “[mysqld]”節(jié)下.
對(duì) my.cnf 或 my.ini的設(shè)置不會(huì)立即生效,需要你重啟服務(wù)器.
大多數(shù)的設(shè)置可以通過(guò) root 賬號(hào)登陸后在線修改 (其他 SUPER權(quán)限賬號(hào)也可以),例如:
SET @@global.key_buffer_size = 77000000;
注意:此處不允許設(shè)置 M 或 G 等單位.
查看全局變量的設(shè)置信息：
注意,這部分設(shè)置MySQL會(huì)向下取整,對(duì)齊到一定的數(shù)字.
你可能需要修改兩個(gè)地方(執(zhí)行SET 并修改my.cnf),以使修改立即生效,并且下次重啟后依然是同樣的值(不管是手動(dòng),還是其他原因重新啟動(dòng))
Web服務(wù)器
像Apache這樣的web服務(wù)器使用多線程來(lái)處理. 如果每個(gè)線程打開(kāi)一個(gè) MySQL連接,可能會(huì)超過(guò)允許的最大連接數(shù). 確保將web服務(wù)器的 MaxClients (或類似參數(shù)) 設(shè)置為一個(gè)合理的值(如50以下).
工具
MySQLTuner
TUNING-PRIMER
上面是幾個(gè)對(duì)內(nèi)存設(shè)置建議的工具. 其中有一個(gè)誤導(dǎo)性條目:
Maximum possible memory usage: 31.3G(266% of installed RAM)
可能使用的內(nèi)存最大值為: 31.3G (可能是物理內(nèi)存的 266%)
不要讓它嚇到你,這些工具使用的公式過(guò)于保守了. 他們假設(shè)所有 max_connections 都在使用并且處于活躍狀態(tài),并正在執(zhí)行一些內(nèi)存密集型的工作.
Total fragmented tables: 23
有碎片的tables: 23 個(gè)
這意味著 OPTIMIZE TABLE 可能會(huì)有作用. 我建議對(duì)表設(shè)置高百分比的 “free space”(見(jiàn)SHOW TABLE STATUS) 或者你知道對(duì)什么表做了大量的刪除/更新操作. 不過(guò),不必費(fèi)心頻繁地對(duì)table進(jìn)行OPTIMIZE 優(yōu)化整理. 一個(gè)月一次可能就夠了.
文章修改記錄
2010創(chuàng)建;2012年10月更新,2014年1月更新;

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com