请教一个innodb_buffer_pool_size的问题

参数配置如下:
SQL code

INNODB STATUS
Current InnoDB index space = 3.25 G
Current InnoDB data space = 5.27 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 6.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory



当前状态如下:
SQL code

__ InnoDB Buffer Pool __________________________________________________
Usage           6.00G of   6.00G  %Used: 100.00
Read hit      100.00%
Pages
  Free              7            %Total:   0.00
  Data        358.17k                     91.09 %Drty:   0.00
  Misc          35039                      8.91
  Latched                                  0.00
Reads          37.79G  405.4k/s
  From file   349.74k     3.8/s            0.00
  Ahead Rnd                 0/s
  Ahead Sql                 0/s
Writes          3.38M    36.3/s
Flushes       163.70k     1.8/s
Wait Free           0       0/s
 



问题是:
当InnoDB Buffer Pool=1G的时候,Read hit=99.98%
当InnoDB Buffer Pool=6G的时候,Read hit=100%

数据库全是INNODB的表。按道理来说,存入Buffer Pool的数据越多,HIT的就会越大。极限情况下是把DATA和INDEX全部存入BUFFER POOL中,这样查询就全在BUFFER中命中了。

现在的情况是1G下,命中99.98,和6G下相差不大。我能想到的情况是,数据库只有1G的“热数据”,当他们全部进入BUFFER后,就能够达到99.98%的命中了。在6G配置下,其中5G不是“热点数据”。这么来看,只需要设置1G的BUFFER就足够了,多了也是浪费内存。

不知道我的理解是否正确?
请高手解释一下这个值应该怎么设置??书上说设置为物理内存的60%--80%,是根据什么来建议的?

作者: zuoxingyu   发布时间: 2011-05-20

这个物理内存设置并没有个死规定,命中率够高就代表当前1G的内存足够

但并不能保证以后数据量增大的情况下还能保持这么高的命中率

作者: rucypli   发布时间: 2011-05-20

引用 1 楼 rucypli 的回复:

这个物理内存设置并没有个死规定,命中率够高就代表当前1G的内存足够

但并不能保证以后数据量增大的情况下还能保持这么高的命中率


举个极限例子
比如buffer=1G,SELECT * FROM A;,把A的数据和索引全部加载到BUFFER中,假设A有1G大,这时候BUFFER就全满了。这时候再来一个SELECT * FROM B,B有500M,这时候BUFFER是怎么样处理的?不知道INNODB_BUFFER_POOL是遵循什么规则进行加入和移除的。

作者: zuoxingyu   发布时间: 2011-05-20

引用 2 楼 zuoxingyu 的回复:

引用 1 楼 rucypli 的回复:

举个极限例子
比如buffer=1G,SELECT * FROM A;,把A的数据和索引全部加载到BUFFER中,假设A有1G大,这时候BUFFER就全满了。这时候再来一个SELECT * FROM B,B有……


innodb_buffer_pool_size会把SELECT * FROM B的500M数据存入这个缓存里面。。去掉A表里面的500M数据
 使用的是 LRU 算法~~~

作者: m582445672   发布时间: 2011-05-20

简单说是替换出最远未使用的数据,留下最近使用的数据

作者: rucypli   发布时间: 2011-05-20

sqlserver的释放和写入缓冲区页 

在 SQL Server 2005 中,有一个机制负责下列操作: 

将修改后的缓冲区页写入磁盘。


将一段时间未被引用的页标记为可用。


SQL Server 有一个单向链接列表,其中包含可用缓冲区页的地址。任何需要缓冲区页的线程都使用这个可用缓冲区列表中的第一页。

缓冲区高速缓存是一个内存中的结构。每个缓冲区页都有一个页眉,其中包含一个引用计数器和一个显示该页是否为脏页的指示器。这表示该页包含尚未写入磁盘的修改。Transact-SQL 语句每引用一次缓冲区页,引用计数器就增加 1。系统会定期从头到尾扫描缓冲区高速缓存。由于缓冲区高速缓存都在内存中,因此这些扫描非常快,而且不需要 I/O。在扫描期间,每个缓冲区页页眉中的引用计数器都除以 4 并舍去余数。当引用计数器变为 0 时,将检查脏页指示器。如果该页为脏页,则安排写操作,将修改写入磁盘。SQL Server 的实例使用预写日志,这样可以在首次将记录修改的日志页写入磁盘时阻止写入脏数据页。将修改后的页刷新到磁盘中之后,或者如果该页不是脏页时,将释放该页。缓冲区页和它包含的数据页之间的关联将被删除,缓冲区将被放入可用列表。

使用此算法,经常引用的页保留在内存中,而存储未被引用的页的缓冲区最终返回到可用缓冲区列表中。SQL Server 的实例根据缓冲区高速缓存的大小在内部确定可用缓冲区列表的大小。无法配置该大小。

作者: rucypli   发布时间: 2011-05-20

引用 4 楼 rucypli 的回复:

简单说是替换出最远未使用的数据,留下最近使用的数据


SELECT * FROM B,这次查询应该不算命中吧??当B加入到BUFFER后,INSERT INTO B VALUES(10),然后再来一次SELECT * FROM B,还是没有命中吧?因为表数据已经改变了。

我的疑问是,INNODB_BUFFER_POOL是怎么样失效的?是和QC一样的么?

作者: zuoxingyu   发布时间: 2011-05-20

SELECT * FROM B,这次查询应该不算命中吧??
不算命中,但是需要数据从硬盘进入缓存

当B加入到BUFFER后,INSERT INTO B VALUES(10),
insert之后的数据需要保持在缓存,有机制会刷新到磁盘

然后再来一次SELECT * FROM B,还是没有命中吧?因为表数据已经改变了。
数据命中,你说的表结构改变没有命中说的是query cache吧

我的疑问是,INNODB_BUFFER_POOL是怎么样失效的?是和QC一样的么?
INNODB_BUFFER_POOL中的页长时间没有被使用就会失效,不知道qc是什么 汗

作者: rucypli   发布时间: 2011-05-20