原文链接: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语句查看适合的参数值. (如果有很多表,可能耗时几分钟.)
selectengine, 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 tablesfrominformation_schema.tableswheretable_schema not in (information_schema, performance_schema)group byengine;
互斥锁瓶颈
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 pagesinnodb 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月更新;