MySQL記憶體調優

標籤：

原文連結: MySQL Memory Allocation -- by Rick James
原文日期: Created 2010; Refreshed Oct, 2012, Jan, 2014 
翻譯人員: 鐵錨
翻譯日期: 2014年5月28日

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時代設計的,且能夠非常easy移植到不同的硬體體系架構中. 不幸的是,這導致了對連結鎖(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中相應到這個表的全部條目都會被清除. 
• 即便在僅僅讀從server(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沒有多少影響. 
它是server額外保持的線程數量,不會影響實際線程數; 起限制作用的是 max_connections. 
二進位日誌
假設為 複製(replication) 或 時間點恢複(point-in-time recovery) 啟用二進位日誌(通過 og_bin開啟), 則server將一直記錄二進位日誌(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(專用)server, 中間數(比方5 ?)可能是一個非常好的值.
NUMA
OK,是時候瞭解一些CPU管理記憶體的架構了. 我們先看 NUMA(Non-Uniform Memory Access, 非統一記憶體定址). 每一個CPU(或多路server中的每一個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

22,384707,385002">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. 
要讓server看到這些設定,必須將其放到設定檔的 “[mysqld]”節下.
對 my.cnf 或 my.ini的設定不會馬上生效,須要你重新啟動server. 
大多數的設定能夠通過 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),以使改動馬上生效,而且下次重新啟動後依舊是相同的值(無論是手動,還是其它原因又一次啟動)
Webserver
像Apache這種webserver使用多線程來處理. 假設每一個線程開啟一個 MySQL串連,可能會超過同意的最大串連數. 確保將webserver的 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 

MySQL記憶體調優