INNODB_BUFFER_POOL_SIZE:设置最佳内存值

什么是INNODB BUFFER POOL

计算机使用它们的大部分内存来提升对经常访问的数据的性能。这就是我们所知的缓存,是系统的一个非常重要的组成部分，因为访问硬盘的数据可能会慢到100到100000倍，这取决你访问的数据量。
MyISAM是使用操作系统的文件系统缓存来缓存那些经常被查询的数据。然而InnoDB使用的是一种非常不同的方法。
不依赖操作系统的缓存，InnoDB自己在InnoDB Buffer Pool处理缓存。经过这篇文章你会学到它是如何工作的，为什么以那种方式来实施是一个不错的想法。

InnoDB缓冲池不仅仅是一个缓存

InnoDB缓冲池实际上用于多个目的，它用来：
* 数据缓存 – 这绝对是它的最重要的目的
* 索引缓存 – 这使用是的同一个缓冲池
* 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲
* 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池
下面是一个经典的把innodb-buffer-pool-size设置为62G的InnoDB缓冲池页的分布情况:

正如你所看到的，Buffer Pool大多是用于普通的InnoDB页面，但大约10%用作其它目的。
这张表的单位是InnoDB页。单个页面大小实际上是16K，所以你可以乘以16,384来得到以字节为单位更直观的使用情况。

InnoDB缓冲池的大小

那么innodb-buffer-pool-size的大小应该设置为什么呢？下面我们就开始谈到这个。

独立服务器

在一个独立的只使用InnoDB引擎的MySQL服务器中，根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的80%。
为什么不是90%或者100%呢？
因为其它的东西也需要内存：
* 每个查询至少需要几K的内存(有时候是几M)
* 有各种其它内部的MySQL结构和缓存
* InnoDB有一些结构是不用缓冲池的内存的(字典缓存，文件系统，锁系统和页哈希表等)
* 也有一些MySQL文件是在OS缓存里的(binary日志，relay日志,innodb事务日志等)
* 此处，你也必须为操作系统留出些内存

共享服务器

如果你的MySQL服务器与其它应用共享资源，那么上面80%的经验就不那么适用了。
在这样的环境下，设置一个对的数字有点难度。
首先让我们来统计一下InnoDB表的实际占用大小。执行如下查询：

SELECT engine,
  count(*) as TABLES,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;

这会给出一个参考，让你知道如果你想缓存整个数据集应该为InnoDB缓冲池设置多少内存合适。
不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。
设置好之后，我们来看看如何检查InnoDB缓冲池大小是否设置足够。
在终端中，执行如下命令：

$ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads                 | 1832098003     |
| Innodb_buffer_pool_reads                 | 595            |
| Innodb_buffer_pool_reads                 | 915            |
| Innodb_buffer_pool_reads                 | 734            |
| Innodb_buffer_pool_reads                 | 622            |
| Innodb_buffer_pool_reads                 | 710            |
| Innodb_buffer_pool_reads                 | 664            |
| Innodb_buffer_pool_reads                 | 987            |
| Innodb_buffer_pool_reads                 | 1287           |
| Innodb_buffer_pool_reads                 | 967            |
| Innodb_buffer_pool_reads                 | 1181           |
| Innodb_buffer_pool_reads                 | 949            |

你所看到的是从硬盘读取数据到缓冲池的次数(每秒)。上面的数据已经相当高了(幸运的是，这个服务器的IO设备能处理每秒4000的IO操作)，如果这个是OLTP系统，我建议提高innodb缓冲池的大小和如果必要增加服务器内存。

更改InnoDB缓冲池

最后，介绍如何更改innodb-buffer-pool-size。
如果你运行的是MySQL 5.7，那么非常幸运，你可以在线更改这个变量，只需要以root身份执行如下查询：

mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;

这还没完，你仍然需要更改my.cnf文件，不过至少你不需要重启服务器让它生效。从mysql的错误日志中我们可以看到它生效的过程：

[Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
[Note] InnoDB: Re-enabled adaptive hash index.

在更早的mysql版本就需要重启了，所以：
1. 在my.cnf中设置一个innodb_buffer_pool_size合适的值
2.重启mysql服务器