请教一个innodb_buffer_pool_size的问题

Question

参数配置如下：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
当前状态如下：__ 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%，是根据什么来建议的？

Answer 1

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

Answer 2

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

Answer 3

sqlserver的释放和写入缓冲区页 在 SQL Server 2005 中，有一个机制负责下列操作： 将修改后的缓冲区页写入磁盘。
将一段时间未被引用的页标记为可用。
SQL Server 有一个单向链接列表，其中包含可用缓冲区页的地址。任何需要缓冲区页的线程都使用这个可用缓冲区列表中的第一页。缓冲区高速缓存是一个内存中的结构。每个缓冲区页都有一个页眉，其中包含一个引用计数器和一个显示该页是否为脏页的指示器。这表示该页包含尚未写入磁盘的修改。Transact-SQL 语句每引用一次缓冲区页，引用计数器就增加 1。系统会定期从头到尾扫描缓冲区高速缓存。由于缓冲区高速缓存都在内存中，因此这些扫描非常快，而且不需要 I/O。在扫描期间，每个缓冲区页页眉中的引用计数器都除以 4 并舍去余数。当引用计数器变为 0 时，将检查脏页指示器。如果该页为脏页，则安排写操作，将修改写入磁盘。SQL Server 的实例使用预写日志，这样可以在首次将记录修改的日志页写入磁盘时阻止写入脏数据页。将修改后的页刷新到磁盘中之后，或者如果该页不是脏页时，将释放该页。缓冲区页和它包含的数据页之间的关联将被删除，缓冲区将被放入可用列表。使用此算法，经常引用的页保留在内存中，而存储未被引用的页的缓冲区最终返回到可用缓冲区列表中。SQL Server 的实例根据缓冲区高速缓存的大小在内部确定可用缓冲区列表的大小。无法配置该大小。

Answer 4

SELECT * FROM B，这次查询应该不算命中吧？？当B加入到BUFFER后，INSERT INTO B VALUES(10),然后再来一次SELECT * FROM B，还是没有命中吧？因为表数据已经改变了。我的疑问是，INNODB_BUFFER_POOL是怎么样失效的？是和QC一样的么？

Answer 5

SELECT * FROM B，这次查询应该不算命中吧？？
不算命中，但是需要数据从硬盘进入缓存当B加入到BUFFER后，INSERT INTO B VALUES(10),
insert之后的数据需要保持在缓存，有机制会刷新到磁盘然后再来一次SELECT * FROM B，还是没有命中吧？因为表数据已经改变了。
数据命中，你说的表结构改变没有命中说的是query cache吧我的疑问是，INNODB_BUFFER_POOL是怎么样失效的？是和QC一样的么？
INNODB_BUFFER_POOL中的页长时间没有被使用就会失效，不知道qc是什么 汗

Answer 6

QC=query cache，呵呵
INSERT先是写入到BUFFER，再刷写到磁盘，DELETE,UPDATE都是一样的操作的话，就好理解了。
它的失效，只可能是被别的数据给“排挤”出去。如果我钱多多，把内存加到256G，这时候
Current InnoDB index space = 3.25 G
Current InnoDB data space = 5.27 G
数据只要被访问过，就都加载到内存了，而且“永远”不会失效了。

Answer 7

应该是按LRU算法来实现的，SELECT * FROM B这次查询应该不会命中，只是将他存入buffer该参数的分配原则没有定死，刚放官方文档好像建议50-80%，它也只是建议，大体分配如下
1需要预留一部分系统使用
2线程独享
假如线程最大n，它的组成如下（大概）
sort_buffer_size  x
join_buffer_size   y
read_buffer_size    z
read_rnd_buffer_size  a
thread_stack  b
这部分就会占用 n(x+y+z+a+b)3 inno_buffer_pool_size
4其他参数
在上面做一个权衡