mysql poolsize_mysql参数之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)

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

Innodb_buffer_pool_pages_data

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

clean pages.

Innodb_buffer_pool_pages_total

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

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