（十）大白话在生产环境中，如何基于机器配置来合理设置Buffer Pool？

最新推荐文章于 2023-09-05 11:44:05 发布

IT社团

最新推荐文章于 2023-09-05 11:44:05 发布

阅读量616

点赞数

分类专栏：大白话MySQL底层原理剖析文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/zht245648124/article/details/128508178

版权

大白话MySQL底层原理剖析专栏收录该内容

101 篇文章 114 订阅

订阅专栏

文章目录

1、生产环境中应该给buffer pool设置多少内存？

今天这篇文章我们接着上一次讲解的Buffer Pool的一些内存划分的原理，来给大家最后总结一下，在生产环境中到底应该如何设置Buffer Pool的大小呢？

首先考虑第一个问题，我们现在数据库部署在一台机器上，这台机器可能有个8G、16G、32G、64G、128G的内存大小，那么此时buffer pool应该设置多大呢？

有的人可能会想，假设我有32G内存，那么给buffer pool设置个30GB得了，这样的话，MySQL大量的crud操作都是基于内存来执行的，性能那是绝对高！

但是这么想就大错特错了，你要知道，虽然你的机器有32GB的内存，但是你的操作系统内核就要用掉起码几个GB的内存！

然后你的机器上可能还有别的东西在运行，肯定也需要内存？
然后你的数据库里除了buffer pool是不是还有别的内存数据结构，肯定是不是也要内存？所以上面那种想法是绝对不可取的！如下图所示。

如果你胡乱设置一个特别大的内存给buffer，会导致你的mysql启动失败的，他启动的时候就发现操作系统的内存根本不够用了！

所以通常来说，我们建议一个比较合理的、健康的比例，是给buffer pool设置你的机器内存的50%~60%左右。

比如你有32GB的机器，那么给buffer设置个20GB的内存，剩下的留给OS和其他人来用，这样比较合理一些。

假设你的机器是128GB的内存，那么buffer pool可以设置个80GB左右，大概就是这样的一个规则。

2、buffer pool总大小=(chunk大小 * buffer pool数量)的倍数

接着确定了buffer pool的总大小之后，就得考虑一下设置多少个buffer pool，以及chunk的大小了

此时要记住，有一个很关键的公式就是：buffer pool总大小=(chunk大小 * buffer pool数量)的倍数

比如默认的chunk大小是128MB，那么此时如果你的机器的内存是32GB，你打算给buffer pool总大小在20GB左右，那么你得算一下，此时你的buffer pool的数量应该是多少个呢？

假设你的buffer pool的数量是16个，这是没问题的，那么此时chunk大小 * buffer pool的数量 = 16 * 128MB = 2048MB，然后buffer pool总大小如果是20GB，此时buffer pool总大小就是2048MB的10倍，这就符合规则了。

当然，此时你可以设置多一些buffer pool数量，比如设置32个buffer pool，那么此时buffer pool总大小（20GB）就是（chunk大小128MB * 32个buffer pool）的5倍，也是可以的。

那么此时你的buffer pool大小就是20GB，然后buffer pool数量是32个，每个buffer pool的大小是640MB，然后每个buffer pool包含5个128MB的chunk，算下来就是这么一个结果了。

3、一点总结

1、我们再来做一点总结，就是说你的数据库在生产环境运行的时候，你必须根据机器的内存设置合理的buffer pool的大小，然后设置buffer pool的数量，这样的话，可以尽可能的保证你的数据库的高性能和高并发能力。

2、然后在线上运行的时候，buffer pool是有多个的，每个buffer pool里多个chunk但是共用一套链表数据结构，然后执行crud的时候，就会不停的加载磁盘上的数据页到缓存页里来，然后会查询和更新缓存页里的数据，同时维护一系列的链表结构。

3、然后后台线程定时根据lru链表和flush链表，去把一批缓存页刷入磁盘释放掉这些缓存页，同时更新free链表。

4、如果执行crud的时候发现缓存页都满了，没法加载自己需要的数据页进缓存，此时就会把lru链表冷数据区域的缓存页刷入磁盘，然后加载自己需要的数据页进来。

整个buffer pool的结构设计以及工作原理，就是上面我们总结的这套东西了，大家只要理解了这个，首先你对MySQL执行crud的时候，是如何在内存里查询和更新数据的，你就彻底明白了。

接着我们后面继续探索undo log、redo log、事务机制、事务隔离、锁机制，这些东西，一点点就把MySQL他的数据更新、事务、锁这些原理，全部搞清楚了，同时中间再配合穿插一些生产经验、实战案例。

4、SHOW ENGINE INNODB STATUS

当你的数据库启动之后，你随时可以通过上述命令，去查看当前innodb里的一些具体情况，执行如下命令就可以了。

SHOW ENGINE INNODB STATUS

此时你可能会看到如下一系列的东西：

Total memory allocated xxxx;
Dictionary memory allocated xxx
Buffer pool size   xxxx
Free buffers       xxx
Database pages     xxx
Old database pages xxxx
Modified db pages  xx
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young xxxx, not young xxx
xx youngs/s, xx non-youngs/s
Pages read xxxx, created xxx, written xxx
xx reads/s, xx creates/s, 1xx writes/s
Buffer pool hit rate xxx / 1000, young-making rate xxx / 1000 not xx / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: xxxx, unzip_LRU len: xxx
I/O sum[xxx]:cur[xx], unzip sum[16xx:cur[0]