mysql参数之innodb_buffer_pool

一、什么是INNODB BUFFER POOL

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

二、InnoDB缓冲池作用

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

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

三、那么如何设置该参数大小呢？首先查看运行时buffer pool相关数据指标：


mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 314   |
+-------------------------------+-------+
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 | 8191  |
+--------------------------------+-------+
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)

上述三项指标的含义如下：

1、Innodb_buffer_pool_pages_data

The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.

2、Innodb_buffer_pool_pages_total

The total size of the InnoDB buffer pool, in pages.

3、Innodb_page_size

InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes

五、设置方法

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size， 
建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)
命令如：SET GLOBAL innodb_buffer_pool_size= 32423423：单位kb