MySQL内存分配_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语句查看适合的参数值. (如果有很多表,可能耗时几分钟.)

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<i style="color:transparent">本文来源gaodai$ma#com搞$$代**码)网8</i> "Total MB",	COUNT(*) "Num Tables"FROMINFORMATION_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

喜欢 (0)赏

[搞代码]

分享 (0)