mysql为哪些内容分配内存_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)