MySQL参数 之 innodb_buffer_pool_size

Innodb_buffer_pool_size

《深入浅出MySQL》一文中这样描述Innodb_buffer_pool_size：

  该参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同，MyISAM 的 key_buffer_size只缓存索引键， 而 innodb_buffer_pool_size 却是同时为数据块和索引块做缓存，这个特性和 Oracle 是一样的。这个值设得越高，访问 表中数据需要的磁盘 I/O 就越少。在一个专用的数据库服务器上，可以设置这个参数达机器 物理内存大小的 80%。尽管如此，还是建议用户不要把它设置得太大，因为对物理内存的竞 争可能在操作系统上导致内存调度。

 将书的话解释过来就是该参数代表了用来缓存索引数据和表数据的内存大小，数据在内存中的读写速度是磁盘读写速度的很多倍，当数据满足条件后才将内存中的数据刷入磁盘中，innodb利用缓存池来帮助延迟写入，合并多个写入操作顺序写入磁盘。

 先弄清以下几个点：

1.InnoDB缓存池存储什么

InnoDB缓存池缓存索引、行的数据、自适应哈希索引、插入缓存（Insert Buffer）、锁 还有其他的内部数据结构。（所以，如果数据库的数据量不大，并且没有快速增长，就不必为缓存池分配过多的内存，当为缓存池配置的内存比需要缓存的表和索引大很多也没什么必要，会造成资源浪费。）

2.缓存池过大也会带来一些困扰

  a.很大的缓存池可能会有很多脏页，导致InnoDB在关闭的时候会消耗很长的时间，因为要把脏页写回到数据文件里

  b.一个大的缓存池重启服务器可能也要比较长的时间用来预热缓冲池，当然Percona的Server有快速预热的功能可以节省很多时间。

合理设置Innodb_buffer_pool_size大小

   我们了解这个参数一般是想通过调整Innodb_buffer_pool_size的大小来优化数据库，毕竟对于使用Innodb引擎的数据库来说，Innodb_buffer_pool_size的大小直接影响到数据库的性能

   有朋友说这还用想，“书上不是说设置80%的物理内存大小就完事了，拿当硬件能支持的情况下越大越好嘛“。 其实也没有那么随意，如果想要将内存利用的非常好的话，建议根据实际情况做一下计算。

   网上的很多博客包括《深入浅出MySQL》里面也提到若是服务器独立运行一个Mysql实例的话，直接将Innodb_buffer_pool_size参数设置为物理内存的80%，当然我们在上面做了分析，如果Innodb_buffer_pool_size太大有可能会带来资源的浪费和一些困扰，而且百分之80这个数字乍一看来的有些突兀，忽然定义百分之80没有什么理论去支持。

   那么我们要怎么计算一个合适的Innodb_buffer_pool_size。

   考虑以下几点：

        1.操作系统需要使用的内存大小

            这个很好理解，操作系统自身运行也是需要内存的，所以Innodb_buffer_pool_size不能设置过大让操作系统无法运行。

        2.每个连接需要使用的内存

            个人觉得了解峰值时候的MYSQL需要烧多少内存是很重要的，我们可以通过峰值的时候去监控它需要多少内存，我们也可以简单的通过最坏情况进行打算，比如，我们的max_connections最大连接数设置为100，sort_buffer_size设置为100M，那么我们在最坏的情况下也就是，同时有100个连接在执行非常大的查询，虽然这种情况发生几率不大，那么我们此时需要的内存就是10000M = 9.7G，这个量是非常大的，所以我们有必要去了解每个连接需要使用的内存。

            那么内存又有哪些呢？我使用自己的测试机的MySQL输入语句  show variables like '%buffer_size%';

            以下是MySQL返回给我的结果：

            

            可以看到红框标出的四条字段就是我们需要注意的每个连接需要使用的内存大小了，可能会有些疑惑，为什么这么多条字段只标出四条出来，那么我们就一一研究一下，bulk_insert_buffer_size是仅作用于MyISAM引擎的参数，批量插入数据时临时缓存写入数据，它不是一个connection级参数所以不考虑，innodb_log_buffer_size为InnoDB的日志缓存，不算连接使用缓存。

            innodb_sort_buffer_size 这个参数是什么，我们去MySQL的官方文档里看一下：

          

            地址：https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

            这里介绍到它是在创建InnoDB索引期间用于对数据进行排序的排序缓冲区的大小。大小定义了读入内存以进行内部排序然后写入磁盘的数据量。并且下面的红框说到在创建索引的两种方式时将分配到三个缓存区，每一个都有此选项的大小。

            那么可以看出该参数的缓存只是在创建索引时候有效，非用户的连接时相关，而且不经常发生，所以我们不考虑。

            key_buffer_size 这个参数所有人都很熟了就不解释了

            preload_buffer_size：（官方解释：The size of the buffer that is allocated when preloading indexes.）预加载索引时分配的缓冲区大小。

            那么我们需要考虑的这四个参数分别是什么：

            join_buffer_size   每个线程使用连接缓存区大小

            sort_buffer_size   每个线程排序缓存区的大小

            read_buffer_size   对myisam表全表扫描时的读缓存大小 

            read_rnd_buffer_size  索引缓存区大小

            这四个参数都是connection级参数，当每一个连接需要使用到这个buffer的时候会一次性分配设置内存，若是设置的过大，当高并发的时候会耗尽内存资源，而且他们使用的内存和Innodb_buffer_pool_size区别开，他们使用的内存是MySQL服务器层分配将从系统可用内存分配，所以我们在设置Innodb_buffer_pool_size的时候不得不去考虑连接使用内存。

合适的Innodb_buffer_pool_size计算公式

Innodb_buffer_pool_size = 系统可用内存 - 系统正常运行内存 - （峰值时的连接数 * 每个连接需要的内存）

若以上算出的Innodb_buffer_pool_size远远大于数据库需要缓存的数据，并且，数据库不需要快速增长，可压低Innodb_buffer_pool_size的值

如何判断MySQL使用内存会不会过高

可能还有有一些担心，所有参数设置完毕后MySQL的占用会过高导致内存溢出，那么我们可以算一下他会不会太高。

通过下面的SQL语句：

SELECT ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size)/1024/1024)+((@@read_rnd_buffer_size+@@read_buffer_size+@@myisam_sort_buffer_size+@@sort_buffer_size+@@join_buffer_size)/1024/1024*@@max_connections);
最终单位为MB

若该值不超过系统可用内存，说明OK

（当然这条公式只是理论上的公式，实际情况如何决定根据实际的场景，因为用该公式计算初始配置的阿里云RDS也会超过内存上限）

以上为innodb_buffer_pool简单理解

转载出处：https://blog.csdn.net/qq_36652619