[06][03][11] INNODB_BUFFER_POOL_SIZE 设置最佳内存值

1. 什么是 INNODB BUFFER POOL

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

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

InnoDB 缓冲池实际上用于多个目的,它用来

• 数据缓存 – 这绝对是它的最重要的目的
• 索引缓存 – 这使用是的同一个缓冲池
• 缓冲 – 更改的数据 (通常称为脏数据) 在被刷新到硬盘之前先存放到缓冲
• 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在 InnoDB 缓冲池

下面是一个经典的把 innodb-buffer-pool-size 设置为 62G 的 InnoDB 缓冲池页的分布情况

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

3. InnoDB 缓冲池的大小

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

3.1 独立服务器

在一个独立的只使用 InnoDB 引擎的 MySQL 服务器中,根据经验,推荐设置 innodb-buffer-pool-size 为服务器总可用内存的 80%
为什么不是 90% 或者 100% 呢？
因为其它的东西也需要内存:

• 每个查询至少需要几 K 的内存 (有时候是几 M)
• 有各种其它内部的 MySQL 结构和缓存
• InnoDB 有一些结构是不用缓冲池的内存的 (字典缓存,文件系统,锁系统和页哈希表等)
• 也有一些 MySQL 文件是在 OS 缓存里的 (binary 日志,relay 日志,innodb 事务日志等)
• 此处,你也必须为操作系统留出些内存

3.2 共享服务器

如果你的 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 缓冲池的大小和如果必要增加服务器内存

4. 更改 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 版本就需要重启了,所以

• 在 my.cnf 中设置一个 innodb_buffer_pool_size 合适的值
• 重启 mysql 服务器