MySQL 內存分配—— 快速設置方案
如果僅使用MyISAM存儲引擎,設置 key_buffer_size 為可用內存的20%,(再加上設置 innodb_buffer_pool_size = 0 )
如果僅使用InnoDB存儲引擎,設置 innodb_buffer_pool_size 為可用內存的 70%, (設置 key_buffer_size = 10M,很小但不是0.)
調優mysql的實踐經驗:
首先拷貝 my.cnf / my.ini 文件副本.根據使用的存儲引擎及可用內存,設置 key_buffer_size 和innodb_buffer_pool_size.慢查詢(Slow queries)的修正一般是通過添加索引(indexes),改變表結構(schema),改變 SELECT 語句 來實現,而不是通過數據庫調優.不要隨便設置查詢緩存(Query cache),除非你真正掌握它的優缺點以及適用場景.不要改變其他的參數,除非你遇到了相應的問題(如最大連接數問題, max connections).確保修改的是 [mysqld] 這一節下的內容,而不是其他部分. 下面向您展示一些實際的細節. (本文不涉及 NDB Cluster)
什麼是索引緩存(key_buffer)?
MyISAM引擎的緩存分為兩部分.
索引塊(Index blocks,每個1 KB,BTree結構、存放於 .MYI 文件) 緩存到 “key buffer” 中. 數據塊緩存(Data block caching, 存放於 .MYD 文件中)交給操作系統負責, 所以確保留下了適量的空閒內存(給操作系統). 警告: 某些類型的操作系統總是報告說內存使用超過90%,雖然實際上還有很多的空閒內存.
SHOW GLOBAL STATUS LIKE 'Key%'; 執行後計算 Key_read_requests / Key_reads 的值, 如果比值較大(比如大於10), 那麼 key_buffer 就足夠了.
什麼是緩存池(buffer_pool)?
InnoDB將所有緩存都放在 “buffer pool” 中, 緩存池的大小通過 innodb_buffer_pool_size 控制. 包含被打開表(open tables)中的 16KB一塊的數據/索引塊,此外還有一些附加開銷.
MySQL 5.5(以及帶插件的 5.1版本)允許您指定 塊大小(block size)為 8 KB或4 KB. MySQL 5.5可以有多個緩沖池,因為每個緩存池有一個互斥鎖, 所以設置多個池可以緩解一些互斥鎖瓶頸.
更多InnoDB調優信息
另一種計算緩存大小的方法
將主緩存(main cache)設置為最小值; 如果同一台機器上有許多其他應用在跑, 並且/或者RAM內存小於2GB, 那麼可以這樣指定.
SHOW TABLE STATUS; 顯示各個數據庫中所有表的狀態.
計算所有MyISAM表的 Index_length 值的總和. 讓 key_buffer_size 小於等於這個和值. 計算所有 InnoDB表 Data_length + Index_length 值的總和. 設置 innodb_buffer_pool_size 為不超過總和值的110%. 如果有內存交換(swapping發生),需要將兩個參數適量地按減小一些.
執行下面的SQL語句查看適合的參數值. (如果有很多表,可能耗時幾分鐘.)
SELECT ENGINE,
ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
COUNT(*) "Num Tables"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema not in ("information_schema", "performance_schema")
GROUP BY ENGINE;
互斥鎖瓶頸
MySQL 是單核CPU時代設計的,且可以很容易移植到不同的硬件體系架構中. 不幸的是,這導致了對連結鎖(interlock)操作的凌亂. 在幾個重要的流程中存在少量(非常少)的“互斥(mutexes)”. 包括:
MyISAM的 key_buffer 查詢緩存(Query Cache) InnoDB的buffer_pool 隨著多核CPU的盛行,互斥問題引起了MySQL的性能問題. 一般來說,CPU超過 4~8 核越多,則MySQL變得越慢,而不會更快. MySQL 5.5 中 InnoDB 的增強版 Percona XtraDB 對多核CPU的支持要好很多; 實際的限制大致是 32核, CPU核心超過這個數後性能會達到瓶頸 ,但不再下降. MySQL 5.6版聲稱最多可以支持48核.
超線程和多核CPU
簡單的處理方式:
禁用超線程(HyperThreading) 停用超過8個核心以上的部分超線程這裡主要是指以前的超線程技術,因此此部分可能不一定正確. 超線程適合拿來做營銷宣傳,但對(專用應用的)性能極不友好. 有兩個處理單元在共享同一個物理緩存. 如果這兩個線程在做同樣的事情,緩存會相當高效. 如果這倆線程在干不同的事,他們會相互妨礙到另一個(超)線程的緩存項.
總的來說MySQL在多核處理上並不占優勢. 所以,如果禁用超線程(HT),剩下的核心將會運行得更快一點.
32位操作系統和MySQL
(譯者注: 肯定64位的MySQL在 32位OS上跑不起來...)
首先,操作系統(以及硬件?) 會限制進程不能使用4GB RAM中的全部,如果有 4G內存的話. 如果物理 RAM 超過 4 GB, 超過的部分在32位操作系統中不可訪問,也是不可用的.
其次,操作系統可能會限制單個進程最大使用多少內存.
例如:FreeBSD的 maxdsiz ,默認為512 MB.
示例:
$ ulimit -a
...
max memory size (kbytes, -m) 524288因此,確定了 mysqld有多少可用內存, 就可以設置為 20% ~ 70%,但需要適當的減少一些.
如果系統報錯,例如 [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes) , 可能是MySQL申請了超過操作系統允許的內存范圍. 需要減小緩存設置.
64位OS與32位MySQL
64位操作系統不受4 GB內存的限制,但32位MySQL依然受這個限制.
如果你有 4 GB以上的內存,那麼可以設置:
key_buffer_size = 20%(所有RAM的),但不要超過3 GB.buffer_pool = 3G 當然最好的辦法是將MySQL換成64位版本.
64位OS與64位MySQL
只使用MyISAM引擎: (5.0.52 ~ 5.1.23之前的)key_buffer_size有 4GB的硬性限制. 詳情請參考 MySQL 5.1 限制(restrictions) 在更高版本中,設置 key_buffer_size 為 20%的RAM. 在(my.cnf / my.ini)中加上 innodb_buffer_pool_size = 0. 只使用InnoDB引擎: 設置 innodb_buffer_pool_size = 70%的RAM. 如果內存很大,並使用 5.5(及以上)版本,可以考慮使用 多個緩存池. 推薦設置 1 - 16 個 innodb_buffer_pool_instances, 每個都不小於1 GB. (很抱歉,沒有最優設置為多少個的具體參考指標;但應該不能設置太多). 與此同時,設置 key_buffer_size = 20M(很小,但不是零)
如果你在數據庫中混合使用多個引擎,將兩個值都降低一些.
最大連接數,線程棧
(max_connections,thread_stack)
每個“線程”都要占用一定的內存. 通常為 200 KB左右; 因此 100個線程大概就是 20 MB. 如果設置 max_connections = 1000,那大概就需要 200 MB,或者更多. 同時連接數太大可能會引起其他某些問題,這點需要注意.
在5.6(或 MariaDB5.5)中,可以選擇線程池與 max_connections 交互. 這是一個高級話題.
線程棧溢出很少出現. 如果確實發生了,可以設置: thread_stack = 256K
點擊查看更多關於max_connections, wait_timeout,連接池的討論
table_cache(table_open_cache)
(某些版本中名字不一樣).
操作系統對單個進程能打開的文件數有限制. 打開每個表需要 1-3個文件. 每個表分區(PARTITION)等價於一個表. 在分區表上的多數操作都會打開所有的分區.
在 *nix中, ulimit 顯示文件限制是多少. 最大值一般是上萬,但有可能被設置為 1024. 這就限制了只能打開300個左右的表. 更多關於ulimit的討論請點擊這裡(這一段是有爭議的.) 另一方面,表緩存(過去?)的實現方式很低效 —— 查找通過線性掃描來完成. 因此,設置 table_cache 為幾千確實會使得 mysql變慢. (基准測試也證明了這一點.)
你可以通過 SHOW GLOBAL STATUS; 查看系統的性能信息, 並計算 每秒打開數(opens/second): Opened_files /Uptime , 如果這個值較大,例如大於 5, 那麼應該加大 table_cache; 如果很小,比如是 1,通過減小 table_cache 值,可能會對性能有所改善.
查詢緩存(Query Cache)
簡短的回答: 設置 query_cache_type = OFF 及 query_cache_size = 0
QC(Query Cache)實際上是將 SELECT語句與結果集(resultsets)進行散列映射.
詳細的回答…… 關於“查詢緩存”有許多種觀點; 其中許多是負面的.
新手警告! QC與key_buffer和buffer_pool完全無關. 當命中時, QC速度快如閃電. 要創建一個運行快1000倍的基准測試並不難. 在QC中只有一個互斥鎖(譯者注: 鎖越少,就是鎖鑰匙越少,高並發時就會激烈競爭/等待). 除非將QC設置為OFF與0,否則每次查詢都會去對比一遍.真相,互斥鎖會發生碰撞,即使 query_cache_type = DEMAND (2).真相,互斥鎖會發生碰撞,即便設置了 SQL_NO_CACHE.查詢語句只要變了一點點(即使多了個空格)都可能導致在QC中生成多個不同的緩存項. “修改”是代價高昂與頻繁的:
在一個表中發生任何 write 事件, QC中對應到這個表的所有條目都會被清除. 即便在只讀從服務器(readonly Slave)上也是這樣.清除使用的是線性算法來執行,所以QC較大(比如200MB)則會導致速度明顯地變慢. 要查看QC的執行效率如何,執行 SHOW GLOBAL STATUS LIKE 'Qc%'; 然後計算read的命中率: Qcache_hits / Qcache_inserts, 如果大於5,則 QC的效率還不錯.
如果QC適合你的應用,那麼我推薦:
query_cache_size = 不超過50M query_cache_type = DEMAND 在所有 SELECT 語句中指明 SQL_CACHE 或 SQL_NO_CACHE, 根據哪些查詢可能會從QC緩存中命中. 深入了解Query Cache
thread_cache_size
這是一個很小的調優項. 設置為 0 會降低線程(連接)創建的速度. 設置為較小的值(比如 10) 是比較好的. 該選項對RAM沒有多少影響.
它是服務器額外保持的線程數量,不會影響實際線程數; 起限制作用的是 max_connections.
二進制日志
如果為 復制(replication) 或 時間點恢復(point-in-time recovery) 啟用二進制日志(通過 og_bin開啟), 則服務器將一直記錄二進制日志(binary logs). 也就是說,可能慢慢地占用磁盤. 建議設置 expire_logs_days = 14 ,只保留14天的日志記錄.
swappiness
RHEL,非常英明地,允許用戶自己控制 OS 如何進行預先內存交換分配. 總的來說這是很好的策略,但對MySQL來說則是一個災難.
(感覺翻譯的有點不流暢，本段原文為: 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期望相當穩定的內存分配 —— 緩存(大部分)是預先分配的; 線程(大都)是限制數量的. 任何內存交換都可能極大地損害MySQL的性能.
設置很高的swappiness值,會丟失一些內存,因為操作系統試圖為以後的分配保留大量的自由空間(MySQL一般是不需要的).
設置swappiness = 0,不交換,在內存不足時操作系統可能會崩潰,. 我寧願MySQL一卡一卡的,也不希望他崩了.
對於MySQL-only(專用)服務器, 中間數(比如5 ?)可能是一個很好的值.
NUMA
OK,是時候了解一些CPU管理內存的架構了. 我們先看 NUMA(Non-Uniform Memory Access, 非統一內存尋址). 每個CPU(或多路服務器中的每個socket(CPU插座)) 都掛載有一部分內存. 這使得訪問本地(local) RAM 非常快, 而訪問掛載在其他 CPU下的RAM要慢上數十個周期.
接著看操作系統. 在(RHEL ?)很多情形下,有兩個行為:
OS分配的內存固定到 “first(第一個)” CPU名下. 接著分配的其他內存也默認分配到第一個CPU名下,直到它滿了. 現在問題來了.
OS與MySQL分配完了第一個 CPU的所有RAM. MySQL分配了第二個 CPU的部分內存. 操作系統OS還需要分配一些其他內存. Ouch —— 一個CPU需要分配內存,但自己名下控制的RAM已經耗盡了,所以它將MySQL的部分內存置換出去. 渣渣！
可能的解決方案:配置BIOS內存分配為 “interleave”(交錯). 這將防止過早交換(premature swapping),代價是有一半左右的 RAM 訪問要跨CPU(off-CPU). 嗯,不論如何訪問的代價都較大, 如果真的要使用所有內存的話.
整體性能損失/收益:幾個百分點.
大內存分頁(huge pages)
這裡有另一個硬件性能陷阱.
CPU訪問RAM,特別是將64位地址映射到某個地方, 比如 128 GB 或“真實”的RAM,會使用TLB. (TLB =Translation Lookaside Buffer,旁路轉換緩沖.) TLB是硬件實現的內存關聯查找表; 將64位的虛擬地址轉換到實際的物理地址.
因為TLB是一個小的,虛擬尋址的緩存,有時會發生 “misses”(未命中),那就會進入物理RAM來查找. 這是兩次查找是很費時的操作,所以應該避免.
通常,內存被 “分頁” 為 4 KB一頁,TLB實際上將高位的(64 - 12)位映射到一個特定頁面. 而低12位通過虛地址轉換得到完整的地址.
例如,128 GB的RAM按 4 KB分頁需要 32M(3200萬個) page-table條目. 這太大了, 遠遠超過TLB的容量. 所以陷入了“Huge page”的騙局.
隨著硬件與操作系統的支持,使部分RAM成為巨型頁面成為可能 ,比如說4 MB(而不是4 KB). 這使得TLB條目劇減,對這部分RAM來說分頁單元是4 MB. 因此,巨大的頁面相當於是不分頁的(non-pagable).
現在內存被分為 pagable 和 non pagable 兩部分; 哪些部分 non pagable 是合理的? 在MySQL中, innodb_buffer_pool 就是一個完美的使用者. 通過正確地配置這些,InnoDB能跑得更快一點:
啟用 Huge pages通知操作系統分配適當的數量(和 buffer_pool 個數一致) 通知MySQL使用huge pages innodb memory usage vs swap 該帖包含有很多需要關注點以及如何設置的細節.
整體性能收益:幾個百分點. Yawn.
MEMORY引擎(ENGINE=MEMORY)
這是一個不常用的存儲引擎,算是MyISAM和InnoDB的替代品. 其數據不是持久的,所以其應用范圍相當有限. 內存表的大小受限於 max_heap_table_size ,默認值是16 MB. 我提起它,以防你將此值修改得太大;這會偷偷地占用可用的RAM.
如何設置變量(VARIABLEs)
在文本文件my.cnf中(Windows上是my.ini),添加一行,例如
innodb_buffer_pool_size = 5G
即: 變量名,等號“=”,變量的值. 有些值允許縮寫,如M代表 million(1048576),G代表billion.
要讓服務器看到這些設置,必須將其放到配置文件的 “[mysqld]”節下.
對 my.cnf 或 my.ini的設置不會立即生效,需要你重啟服務器.
大多數的設置可以通過 root 賬號登陸後在線修改 (其他 SUPER權限賬號也可以),例如:
SET @@global.key_buffer_size = 77000000;
注意:此處不允許設置 M 或 G 等單位.
查看全局變量的設置信息：
mysql> SHOW GLOBAL VARIABLES LIKE "key_buffer_size";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 76996608 |
+-----------------+----------+注意,這部分設置MySQL會向下取整,對齊到一定的數字.
你可能需要修改兩個地方(執行SET 並修改my.cnf),以使修改立即生效,並且下次重啟後依然是同樣的值(不管是手動,還是其他原因重新啟動)
Web服務器
像Apache這樣的web服務器使用多線程來處理. 如果每個線程打開一個 MySQL連接,可能會超過允許的最大連接數. 確保將web服務器的 MaxClients (或類似參數) 設置為一個合理的值(如50以下).
工具
MySQLTuner
TUNING-PRIMER
上面是幾個對內存設置建議的工具. 其中有一個誤導性條目:
Maximum possible memory usage: 31.3G (266% of installed RAM)
可能使用的內存最大值為: 31.3G (可能是物理內存的 266%)
不要讓它嚇到你,這些工具使用的公式過於保守了. 他們假設所有 max_connections 都在使用並且處於活躍狀態,並正在執行一些內存密集型的工作.
Total fragmented tables: 23
有碎片的tables: 23 個
這意味著 OPTIMIZE TABLE 可能會有作用. 我建議對表設置高百分比的 “free space”(見SHOW TABLE STATUS) 或者你知道對什麼表做了大量的刪除/更新操作. 不過,不必費心頻繁地對table進行OPTIMIZE 優化整理. 一個月一次可能就夠了.
文章修改記錄
2010創建;2012年10月更新,2014年1月更新;
更深入的文章:
MySQL 5.6的調優
InnoDB性能優化的基本知識(終極版)
MySQL安裝後的10項優化設置
通過 MySQL論壇::性能 聯系作者 ——裡克·詹姆斯
裡克·詹姆斯的MySQL相關文檔
提示,調試、howto、優化相關等等……
Rick's RoTs (Rules of Thumb -- lots of tips)
Memory Allocation (caching, etc)
Character Set and Collation problem solver
Converting from MyISAM to InnoDB -- includes differences between them
Big DELETEs - how to optimize
Compound INDEXes plus other insights into the mysteries of INDEXing
Partition Maintenance (DROP+REORG) for time series
Entity-Attribute-Value -- a common, poorly performing, design patter; plus an alternative
Find the nearest 10 pizza parlors (efficient searching on Latitude + Longitude)
Alter of a Huge table
Latest 10 news articles -- how to optimize the schema and code for such
Pagination, not with OFFSET, LIMIT
Data Warehouse techniques (esp., Summary Tables)
Techniques on efficiently finding a random row (On beyond ORDER BY RAND())
GUID/UUID Performance (type 1 only)
IP Range Table Performance
MySQL Limits
Galera Limitations (with Percona XtraDB Cluster / MariaDB)
Rollup Unique User Counts
Best of MySQL Forum
