mysql參數(shù)中innodb_buffer_pool_size大小怎么設(shè)置

這篇文章主要為大家展示了“mysql參數(shù)中innodb_buffer_pool_size大小怎么設(shè)置”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“mysql參數(shù)中innodb_buffer_pool_size大小怎么設(shè)置”這篇文章吧。

mysql> show global variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

那么如何設(shè)置該參數(shù)大小呢？首先查看運(yùn)行時(shí)buffer pool相關(guān)數(shù)據(jù)指標(biāo)：

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)

上述三項(xiàng)指標(biāo)的含義如下：

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

計(jì)算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
當(dāng)結(jié)果 > 95% 則增加 innodb_buffer_pool_size， 建議使用物理內(nèi)存的 75%
當(dāng)結(jié)果 < 95% 則減少 innodb_buffer_pool_size， 

建議設(shè)置大小為： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)

命令如：SET GLOBAL innodb_buffer_pool_size= 32423423：?jiǎn)挝籯b

以上是“mysql參數(shù)中innodb_buffer_pool_size大小怎么設(shè)置”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！