Innode内存优化 （INNODB_BUFFER_POOL_SIZE）

1.innodb_buffer_pool_size

innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够的内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O就越少，性能也就越高。

 

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

 

2.如何设置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缓冲池的大小和如果必要增加服务器内存。

增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，
则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

• innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
• innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
• innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。

查看各值大小：

show variables like 'innodb_buffer_pool%';

innodb_buffer_pool_size默认大小为128M。

修改innodb_buffer_pool_size大小：

SET GLOBAL innodb_buffer_pool_size = 268435456;

这里我简单修改成256M，单位是byte。

 

参考：https://www.centos.bz/2016/11/innodb_buffer_pool_size-set-the-right-size/