mysql参数之innodb_buffer_pool_size大小设置

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)

 

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

Innodb_buffer_pool_pages_data

The number of pages in the InnoDB buffer pool containing data. The number includes both dirty andclean 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 beeasily 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

 

---------------------

 

本文来自 想静静的搬运工 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/twj13162380953/article/details/70142206?utm_source=copy