【mySQL】提升mysql性能的关键参数之innodb_buffer_pool_size、innodb_buffer_pool_instances

mysql开发中文博客：https://imysql.cn/

1.为什么需要innodb buffer pool？

在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。

2. MySQL InnoDB buffer pool 里包含什么？

• 数据缓存
  InnoDB数据页面

• 索引缓存
  索引数据

• 缓冲数据
  脏页（在内存中修改尚未刷新(写入)到磁盘的数据）

• 内部结构
  如自适应哈希索引，行锁等。

3. 如何设置innodb_buffer_pool_size?

innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为2**32 -1,在64-bit平台上最大值为2**64-1。当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。

大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。

 

实战

转自：https://blog.csdn.net/qing_gee/article/details/42742241 （作者太罗嗦了，复制好多遍）

提升mysql性能的关键参数之innodb_buffer_pool_size、innodb_buffer_pool_instances。

首先我们先看一下，我的电脑（i3-3220，4G内存，没有配置innodb_buffer_pool_size参数的情况下，抛开程序的性能，经过1个线程-8个线程的测试结果）环境下，大家可以看到如下的图，线程并发情况下，时间不断的攀升，这让人好苦恼，说好的高性能，高并发呢？

结果：

 

   不过经过一个多星期的努力，不经意间，我在同事的电脑上发现，同样的代码测试后，时间和我的不是一个等量级的，要比我的电脑快很多，他的电脑（其他条件等同，CPU为i3-4150），8个商品8000挂单下能达到135ms，首先说明CPU的提升对程序处理来说是非常有效果的，但是依然在高并发的情况下，表现的很差劲。

 

时至今日，通过比对mysql的配置文件my.ini，不经意间，我们发现innodb_buffer_pool_size在8M和48M的配置下，性能简直就不在一个等级上，OK，那就继续调查吧，最终得出如下结论

innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=1

详细的解释，可以看如下介绍

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.

简单来说，就是pool-size可以缓存索引和行数据，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置到电脑物理内存的80%，哇，这个参数简直太棒了。紧接着，我们经过大量的测试发现，在innodb_buffer_pool_size=2G和innodb_buffer_pool_instances=1的参数设置情况下，效果最优。

OK上图

发生了什么事请，该参数下，20个商品，各下1000单，平均时间在55ms，别逗我了，mysql，你怎么不早一点告诉我。

所以，推荐如下，mysql在高并发读写情况下，并且为innodb数据表时，请设置

innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=1