innodb_pool_buffer_size对innodb性能的影响

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data'; 1.但是当innodb做crash recovery的时候，大的pool buffer会让recovery奇慢无比。 一种折衷的解决方法就是：启动的时候用小的pool buffer，恢复完成以后改用大的pool bufer。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。 2.作用：这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。 3.方法一：

+-------------------------------+-------+

| Variable_name                 | Value | +-------------------------------+-------+ | Innodb_buffer_pool_pages_data | 1388  | +-------------------------------+-------+ 1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';

+--------------------------------+--------+ | Variable_name                  | Value  | +--------------------------------+--------+ | Innodb_buffer_pool_pages_total | 131071 | +--------------------------------+--------+ 1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | Innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)

'Innodb_buffer_pool_pages_data' X 100 / 'Innodb_buffer_pool_pages_total'

当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用 ram total 75%

当结果 < 95% 则减少 innodb_buffer_pool_size，  建议 'Innodb_buffer_pool_pages_data' X 'Innodb_page_size' X 1.05 / (1024*1024*1024)

4.方法二：

Innodb 存储引擎的缓存机制和 MyISAM 的最大区别就在于 Innodb 不仅仅缓存索引，同时还会缓存实 际的数据。所以，完全相同的数据库，使用 Innodb 存储引擎可以使用更多的内存来缓存数据库相关的信 息，当然前提是要有足够的物理内存。这对于在现在这个内存价格不断降低的时代，无疑是个很吸引人 的特性。

 

innodb_buffer_pool_size 参数用来设置 Innodb 最主要的 Buffer(Innodb_Buffer_Pool)的大小，也 就是缓存用户表及索引数据的最主要缓存空间，对 Innodb 整体性能影响也最大。无论是 MySQL 官方手册 还是网络上很多人所分享的 Innodb 优化建议，都简单的建议将 Innodb 的 Buffer  Pool 设置为整个系统 物理内存的 50%  ～ 80%  之间。如此轻率的给出此类建议，我个人觉得实在是有些不妥。

 

不管是多么简单的参数，都可能与实际运行场景有很大的关系。完全相同的设置，不同的场景下的 表现可能相差很大。就从 Innodb 的 Buffer  Pool 到底该设置多大这个问题来看，我们首先需要确定的是 这台主机是不是就只提供 MySQL 服务？MySQL 需要提供的的最大连接数是多少？MySQL 中是否还有 MyISAM 等其他存储引擎提供服务？如果有，其他存储引擎所需要使用的 Cache 需要多大？

 

假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G，MySQL 最大连接数为 500，同时还使用 了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？

内存分配为如下几大部分：

a)     系统使用，假设预留 800M；

b)     线程独享，约 2GB  = 500  * (1MB  + 1MB  + 1MB  + 512KB  + 512KB)，组成大概如下:sort_buffer_size：1MB join_buffer_size：1MB read_buffer_size：1MB read_rnd_buffer_size：512KB thread_statck：512KB

c)    MyISAM  Key Cache，假设大概为 1.5GB；

d)     Innodb Buffer  Pool 最大可用量：8GB  - 800MB  - 2GB  - 1.5GB = 3.7GB；

 

假设这个时候我们还按照 50%～80%的建议来设置，最小也是 4GB，而通过上面的估算，最大可用值 在 3.7GB 左右，那么很可能在系统负载很高当线程独享内存差不多出现极限情况的时候，系统很可能就 会出现内存不足的问题了。而且上面还仅仅只是列出了一些使用内存较大的地方，如果进一步细化，很 可能可用内存会更少。上面只是一个简单的示例分析，实际情况并不一定是这样的，这里只是希望大家了解，在设置一些 参数的时候，千万不要想当然，一定要详细的分析可能出现的情况，然后再通过不断测试调整来达到自 己所处环境的最优配置。就我个人而言，正式环境上线之初，我一般都会采取相对保守的参数配置策 略。上线之后，再根据实际情况和收集到的各种性能数据进行针对性的调整。

 

当系统上线之后，我们可以通过 Innodb 存储引擎提供给我们的关于 Buffer  Pool 的实时状态信息作 出进一步分析，来确定系统中 Innodb 的 Buffer  Pool 使用情况是否正常高效：

sky  : example 08:47:54> show status like  'Innodb_buffer_pool_%';

+-----------------------------------+-------+

| Variable_name    | Value |

+-----------------------------------+-------+

| Innodb_buffer_pool_pages_data     | 70    |

| Innodb_buffer_pool_pages_dirty    | 0    |

| Innodb_buffer_pool_pages_flushed | 0    |

| Innodb_buffer_pool_pages_free    | 1978    |

| Innodb_buffer_pool_pages_latched | 0    |

| Innodb_buffer_pool_pages_misc     | 0    |

| Innodb_buffer_pool_pages_total    | 2048    |

| Innodb_buffer_pool_read_ahead_rnd  | 1    |

| Innodb_buffer_pool_read_ahead_seq  | 0    |

| Innodb_buffer_pool_read_requests | 329    |

| Innodb_buffer_pool_reads    | 19    |

| Innodb_buffer_pool_wait_free    | 0    |

| Innodb_buffer_pool_write_requests | 0    |

+-----------------------------------+-------+

 

从上面的值我们可以看出总共 2048  pages，还有 1978 是 Free 状态的仅仅只有 70 个 page 有数据， read 请求 329 次，其中有 19 次所请求的数据在 buffer  pool 中没有，也就是说有 19 次是通过读取物理 磁盘来读取数据的，所以很容易也就得出了 Innodb Buffer  Pool 的 Read 命中率大概在为：(329 - 19)/ 329  * 100%  = 94.22%。

 

当然，通过上面的数据，我们还可以分析出 write 命中率，可以得到发生了多少次 read_ahead_rnd，多少次 read_ahead_seq，发生过多少次 latch，多少次因为 Buffer 空间大小不足而产 生 wait_free 等等。

 

单从这里的数据来看，我们设置的 Buffer  Pool 过大，仅仅使用 70  / 2048  * 100%  = 3.4%。

 

在 Innodb Buffer  Pool 中，还有一个非常重要的概念，叫做“预读”。一般来说，预读概念主要是 在一些高端存储上面才会有，简单来说就是通过分析数据请求的特点来自动判断出客户在请求当前数据 块之后可能会继续请求的数据快。通过该自动判断之后，存储引擎可能就会一次将当前请求的数据库和 后面可能请求的下一个（或者几个）数据库一次全部读出，以期望通过这种方式减少磁盘 IO 次数提高 IO 性能。在上面列出的状态参数中就有两个专门针对预读：

Innodb_buffer_pool_read_ahead_rnd，记录进行随机读的时候产生的预读次数； Innodb_buffer_pool_read_ahead_seq，记录连续读的时候产生的预读次数；