MYSQL 内存机制分析与监控

种数据库都有它自己的内存机制，如果说汽车的三大件，发动机，变速箱，底盘。数据库的内存机制算是数据库核心的核心，一个没有好的内存管理和分配的数据库，一定是不会有好的性能。

MYSQL 作为数据库中的一只奇葩，（为何说奇葩，因为ORACLE SQL SERVER PG ）这老三位的数据库引擎是不可以更换的，虽然SQL SERVER 201X后支持 内存系列的方式，列方式的存储等等，但这还是在原有的数据库引擎上做出的进步， PG 虽然说未来（12 perview）会支持插件式的存储引擎方式，但至少现在没有。ORACLE 嘛，no any words for it.

MySQL 从出生开始，就是SERVER 层与 引擎层分离的，这虽然导致很多被别的数据库诟病，例如 DDL 不原子性，操作容易出问题（大表）等等的问题，但问题都是人要解决的，8.0 人家已经 原子性了，没有 FRM 文件了。

关于MYSQL的内存，这个话题比较大，只能限定INNODB 操作因为的数据库内存的分析。

我们先的深入几件事情

1 为什么要有buffer pool 或者内存，缓存在MYSQL 数据库中到底是为了什么？

估计很多人很快的回答，性能。是的内存在数据库中其实就是为了性能，试想如果磁盘的速度和内存一样快，还需要内存，buffer pool ,缓存吗？

2 Buffer pool 缓存，内存，到底在MYSQL 中起到了什么作用？

首先所有的数据处理和变化都是在内存中，磁盘中的数据仅仅是为了持久化而，所以最复杂的处理关系，类似于死锁，block ,互斥，数据字典，bulabula 等等都存在于内存中。所以一问道死锁，千万别和别人说是因为磁盘慢了之类的 ，因为他不发生在磁盘，而是在内存中。

3 磁盘的数据page 和 内存的数据page 有关吗？

是的是有关系的，磁盘的数据页面是要映射到内存中去的，这也就牵扯到另外一件事情，就是page页面的大小，在PG 中磁盘的页面大小是可以调整的，MYSQL 默认是16KB ，ORACLE 是 8 KB， SQL SERVER 也是 8KB。换句话也就是说，有些数据库的页面大小是可以调整的，有些是不可以的，同时他们的磁盘的页面会直接映射到内存中。

4 页面的大小对数据库的性能有什么影响？

这个问题其实我也是一直在书籍和，google中去找寻，但一直没有一个答案或者可以让人信服的答案，这里就尝试自己分析一下，以MYSQL的页面16KB为为例，如果一次读入的数据是8KB，那16KB只能使用其中一半，如果后期有在读入8KB的数据，可以在使用另外一半的内存，但是如果读入的是4KB的数据，则可能会浪费 4KB的cache，所以分配的页面太大会有浪费的情况，那有人说，8KB的比16KB的要小，所以浪费的空间少，但从另一个角度来分析，如果一次我要读入12KB的数据呢，那是不是要分成两个page？效率上是否比一个页面能承载的数据在处理上要麻烦，并且一个数据页分成两个，是否还有其他需要进行的事情，例如连接 等等？

所以一个数据页的大小，见仁见智，不是非黑即白，需要根据具体的问题具体分析，如果你的MYSQL使用的是压缩页面，16KB 或更大的页面或许会更好。所以有的时候，数据库的页面可以被调整，根据业务或者根据某种特性来进行设置，会保证更好的性能，而类似 SQL SERVER ,ORACLE 这样的数据库是不可以调整的。

5 内存中的数据是否有退出机制？

回答是一定的，任何数据库的内存的数据都有退出的机制，否则内存难道要和数据的大小一致，在目前的硬件技术上，这是不大可能的，所以内存必须有退出的机制。从另外一个事情上，也反应出硬件的变化，其实对数据库的软件设计是有联动性或者部分决定性的。

6 内存中涉及管理内存页的是怎么做的

MYSQL 中的内存管理主要使用的是 LRU 算法(变种)，Least Recently Used，在MYSQL 的 BUFFER POOL 中是包含 New SUBLIST 和 Old Sublist

上面的图大家都看见了，大致LRU （进化）的算法， 新读入内存的都放入到 young 区域 5/8 buffer pool ，而经常被调用的页面则会在一个算法后从5/8 放入到 3/8中，具体其实如果读过 ORACLE 的内存管理，其中有一段和MYSQL 是大致相同的，都是队列的概念+频繁提取的数据提前。

当然数据页中的数据是变化的，也就是说变化的数据页就是脏页，而脏页就要刷入到磁盘中进行合并，进行永久化，当然这个过程可以单独写一篇甚至多篇文字，这里就不展开了。

这里提出一个问题，如果数据页变化了，我在去读这个数据页怎么办?

这里大概率是你要去读change buffer ,(insert buffer replacement)，而不会等待你的脏页刷新到磁盘，在读到内存，在进行后续的工作。

那如何查询这些内存的信息，就对系统的性能变得尤为重要了

查看当然可以被收集的内存 ITEM 信息

SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%'

对某些不进行收集的内存信息进行收集

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory/innodb/buf_buf_pool'

同时通过语句可以获得，内存在MYSQL 中的分配信息

其实关于MYSQL的内存管理,当然我们也可以通过另外的方法去访问，见下图，当然这应该是最基本的应该被掌握的。怎么查看 Do you get it ?