MySQL 内存那点事你咋就不会--问来问去 --分析+自动分析脚本（1）

AustinDatabases

发布于 2025-02-06 19:39:25

10900

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

最近一段时间，经常有人问MySQL的内存分析，内存到底用到了哪里，为什么内存持续的下降，为什么内存持续的上升。今天我们来通过此文来分析一下MySQL的内存都去哪了。

这里我们先把基准说一说，

1 我们此次的分析对象版本为8.025 MySQL的开源版本和 PolarDB MySQL 8.02 （相当于MySQL8.018）的版本

2 此次分析我们尽力将可以使用到的方法来进行一个一网打尽。

下面我们先祭出第一个方法，最老的一个方案

show engine innodb status;

| InnoDB |      | 
=====================================
2025-01-25 21:30:49 140356048033536 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 8957 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1549
Purge donefor trx's n:o < 1549 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421831178706328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421831178705472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421831178704616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1191 OS file reads, 408 OS file writes, 100 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
Hash table size 276707, node heap has 3 buffer(s)
Hash table size 276707, node heap has 3 buffer(s)
Hash table size 276707, node heap has 2 buffer(s)
Hash table size 276707, node heap has 4 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          18118356
Log buffer assigned up to    18118356
Log buffer completed up to   18118356
Log written up to            18118356
Log flushed up to            18118356
Added dirty pages up to      18118356
Pages flushed up to          18118356
Last checkpoint at           18118356
103 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1096286208
Dictionary memory allocated 429842
Buffer pool size   65536
Free buffers       64203
Database pages     1320
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1166, created 158, written 239
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1320, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   8192
Free buffers       7952
Database pages     239
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 231, created 8, written 15
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 239, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   8192
Free buffers       7989
Database pages     201
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 199, created 2, written 4
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 201, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   8192
Free buffers       8078
Database pages     112
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 8, written 15
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 112, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   8192
Free buffers       8144
Database pages     46
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 44, created 2, written 4
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 46, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   8192
Free buffers       8117
Database pages     74
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73, created 1, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 74, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   8192
Free buffers       8073
Database pages     117
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50, created 67, written 81
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 117, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   8192
Free buffers       7909
Database pages     281
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 216, created 65, written 85
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 281, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   8192
Free buffers       7941
Database pages     250
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 245, created 5, written 25
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 250, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4662, Main thread ID=140354489517824 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 433, updated 324, deleted 168, read 5580
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

内存的信息主要分布在 buffer pool and memory 这部分，首先 Total large memory allocated 1096286208 这个部分是整体的数据库被分配的内存空间 1096286208、1024/1024/1024 = 1.02G 这里总体说明物理内存分配给数据库大约1.02G的内存空间

Dictionary memory allocated 429842 其中数据字典的部分分配了 419KB

Buffer pool size 65536 实际上我们关心的innodb_buffer_pool 到底分配了多大的内存，这里 65536*16KB/1024 = 1G，这里innodb_buffer_pool 被分配了1G的内存空间。Free buffers 64203 其中 64203/65536* 100% = 97% 的innodb_buffer_pool中的页面都是空的未被利用。

那么一般情况下，我们通过这些数据的持续跟踪就可以了解到我们的内存给定的值是否合适。

举例 free buffers 持续都是0 说明我们的给的innodb_buffer_pools的配置需要增加。再如 old database page 0 的这个部分在一定时间经常突然增大，说明LUR 在大量淘汰数据页面，此时如果你没有大量的数据灌入并参与查询的操作等等，那么就有可能在告知你，你的数据库中给定共享的内存部分不足了。

Modified db pages 0

这位置的modified db page 说明到底有多少数据脏页还未刷新的磁盘中。这里我们有一个标定我们 innodb_buffer_pools 的关键的缓冲区命中率。在这里我们也可以进行计算

总页访问次数 (Buffer pool reads + Buffer pool read requests) 包括所有从缓冲池读取的页和从磁盘读取的页。

缓冲池中的读取请求 (Buffer pool read requests) 表示从内存中满足的读取请求。

从磁盘读取的次数 (Buffer pool reads) 表示因为缓冲池中未找到数据而从磁盘读取的次数。

缓冲区命中率 = (Buffer pool read requests / (Buffer pool read requests + Buffer pool reads)) * 100%

这里我们通过node.js的脚本来完成一个简单的内存信息的跟踪过程

const mysql = require('mysql2/promise');

// 配置数据库连接
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'your_database',
};

async functionanalyzeInnoDB() {
  try {
    const connection = await mysql.createConnection(dbConfig);

    console.log('Connected to database.');

    // 定时任务，每隔 10 秒执行一次
    setInterval(async () => {
      try {
        // 获取 InnoDB 状态信息
        const [rows] = await connection.query('SHOW ENGINE INNODB STATUS');
        const innodbStatus = rows[0]?.Status || '';
        
        // 解析缓冲池信息
        const bufferPoolSizeMatch = innodbStatus.match(/Buffer pool size\s+(\d+)/);
        const dirtyPagesMatch = innodbStatus.match(/Modified db pages\s+(\d+)/);
        const pagesReadMatch = innodbStatus.match(/Pages read\s+(\d+)/);
        const pagesWrittenMatch = innodbStatus.match(/Pages written\s+(\d+)/);
        const readRequestsMatch = innodbStatus.match(/Pages made young\s+(\d+)/);
        const diskReadsMatch = innodbStatus.match(/Pages read\s+(\d+)/);

        // 数据提取
        const bufferPoolSize = bufferPoolSizeMatch ? parseInt(bufferPoolSizeMatch[1]) : 0;
        const dirtyPages = dirtyPagesMatch ? parseInt(dirtyPagesMatch[1]) : 0;
        const pagesRead = pagesReadMatch ? parseInt(pagesReadMatch[1]) : 0;
        const pagesWritten = pagesWrittenMatch ? parseInt(pagesWrittenMatch[1]) : 0;
        const readRequests = readRequestsMatch ? parseInt(readRequestsMatch[1]) : 0;
        const diskReads = diskReadsMatch ? parseInt(diskReadsMatch[1]) : 0;

        // 计算
        const dirtyPagesMB = dirtyPages * 16 / 1024; // 每页 16KB 转换为 MB
        const hitRate = (readRequests / (readRequests + diskReads)) * 100 || 0; // 命中率 (%)
        const readWriteRate = pagesRead + pagesWritten; // 页面流速 (读+写)

        // 输出结果
        console.log(`--- InnoDB Memory Analysis ---`);
        console.log(`Buffer Pool Size: ${(bufferPoolSize * 16 / 1024).toFixed(2)} MB`);
        console.log(`Dirty Pages: ${dirtyPagesMB.toFixed(2)} MB`);
        console.log(`Hit Rate: ${hitRate.toFixed(2)}%`);
        console.log(`Page Flow Rate (Read+Write): ${readWriteRate} pages/s`);
        console.log('--------------------------------');
      } catch (error) {
        console.error('Error analyzing InnoDB:', error);
      }
    }, 10000); // 每隔 10 秒运行一次
  } catch (error) {
    console.error('Database connection failed:', error);
  }
}

// 启动脚本
analyzeInnoDB();

然后运行这个脚本，我们查看结果，这里脚本是循环的可以持续观察内存的动态。