MySQL 内存那点事你还不会--PS分析+自动历史SQL分析（2）

AustinDatabases

发布于 2025-03-07 15:36:56

3600

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

上期MySQL的分析和自动脚本的基础是基于MySQL的show engine innodb status;

今天我们进一步进行分析，现在大部分同学应该开始使用了MySQL8,截止2025年MySQL已经发布了MySQL 9.2，虽然MySQL在走下坡路的事情已经是人尽皆知了。但是现在MySQL的保有量和存量还是有的，另外一些蹩脚的开发者还就只会个MySQL，虽然这样的开发者也会被淘汰，但还是那句话MySQL的存量还是有的。

这里说句题外话，前两天在丁奇的直播间听了半个小时，我个人觉得丁奇丁老师，在数据库方面还是很实话实说的，丁老师的直播间有人在问，现在学MySQL还是PostgreSQL,丁老师不带有个人利益或个人倾向性的说了一下，我记得大致意思是

1 MySQL还是要学的，如果你去的是互联网企业或类似的企业

2 MySQL和PG这两个数据库不是二选一，是都要会，一个要精通，另一个至少要熟悉和会。

3 类MySQL的数据库性能已经超过开源MySQL （此篇发布较晚，实际上是2月份某天听的直播）

我觉得这样的人才能称为数据库专家，没有误导大家，点赞！

话归正传，今天说说Performance_schema中的系统表来分析MySQL的内存使用情况。上一篇是非常粗狂的对MySQL的使用内存的方式进行分析和通过自动脚本来进行打印，来获取命中率和刷新率等信息。

这里performance_schema 来分析MySQL的内存可以通过更多的维度来进行分析，并且细化到底内存用到哪里了。这里画了一个分析图。

语句1：通过语句来分析全局的MySQL在使用内存的情况

SELECT 
    EVENT_NAME, 
    COUNT_ALLOC, 
    SUM_NUMBER_OF_BYTES_ALLOC AS TOTAL_ALLOCATED_BYTES, 
    SUM_NUMBER_OF_BYTES_FREE AS TOTAL_FREED_BYTES, 
    SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE AS CURRENT_MEMORY_USAGE 
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_MEMORY_USAGE DESC
LIMIT 10;

+-----------------------------------------------------------------------------+-------------+-----------------------+-------------------+----------------------+
| EVENT_NAME                                                                  | COUNT_ALLOC | TOTAL_ALLOCATED_BYTES | TOTAL_FREED_BYTES | CURRENT_MEMORY_USAGE |
+-----------------------------------------------------------------------------+-------------+-----------------------+-------------------+----------------------+
| memory/innodb/buf_buf_pool                                                  |           8 |            1096286208 |                 0 |           1096286208 |
| memory/performance_schema/events_statements_summary_by_digest               |           1 |              41600000 |                 0 |             41600000 |
| memory/mysys/KEY_CACHE                                                      |           3 |              33555920 |                 0 |             33555920 |
| memory/innodb/hash0hash                                                     |          32 |              30212544 |                 0 |             30212544 |
| memory/innodb/ut0link_buf                                                   |           2 |              25165888 |                 0 |             25165888 |
| memory/innodb/buf0dblwr                                                     |        1151 |              48074720 |          27608288 |             20466432 |
| memory/innodb/ut0new                                                        |           6 |              16859962 |                 0 |             16859962 |
| memory/performance_schema/events_statements_history_long                    |           1 |              14560000 |                 0 |             14560000 |
| memory/performance_schema/events_errors_summary_by_thread_by_error          |         257 |              12390400 |                 0 |             12390400 |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name |           1 |              10268672 |                 0 |             10268672 |
+-----------------------------------------------------------------------------+-------------+-----------------------+-------------------+----------------------+

上面的信息中，我们可以得到的分配内存是多少，总的释放内存是多少，这些信息是从开启数据库后的累计数据。同时还有当前的内存使用值是多少。

其中最主要的有着几部分 memory/innodb/buf_buf_pool: 这个部分是我们非常熟悉的innodb_buffer_pool 的部分这里显示分配了 1.2G

memory/performance_schema/events_statements_summary_by_digest: 这个部分是给出语句收集部分组件的消耗的内存，在40MB

memory/innodb/buf0dblwr:这里还有MySQL向磁盘刷新数据时锁消耗的内存。

语句2：分析用户或线程使用内存的情况按照用户来分析

SELECT 
   USER, 
   EVENT_NAME, 
   SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE AS CURRENT_MEMORY_USAGE 
FROM performance_schema.memory_summary_by_user_by_event_name
ORDER BY CURRENT_MEMORY_USAGE DESC
LIMIT 10;

按线程来分析或用户我们可以得到如下的信息，这里可以注意到一点，我们平时在分析系统的内存的时候，在一个MySQL中分别有不同的项目来使用数据库的INNODB BUFFER POOL 此时如果问到底那个应用的系统使用的内存更多，可能很难回答。这里我们使用这条语句可以清晰的看到不同的用户使用innodb buffer pool 内存的情况，这样对于我们后续在内存添加和拆库上有很多可以给出有力的数据。

同时我们还可以注意到下面的信息中有两个重要的部分 innodb的部分和SQL层的部分。SQL层的部分memory/sql/dd::objects，memory/sql/dd::String_type

+------+--------------------------------------+----------------------+
| USER | EVENT_NAME                           | CURRENT_MEMORY_USAGE |
+------+--------------------------------------+----------------------+
| NULL | memory/innodb/memory                 |               436048 |
| NULL | memory/sql/dd::objects               |               353536 |
| NULL | memory/sql/dd::String_type           |               227922 |
| NULL | memory/mysqld_openssl/openssl_malloc |               150302 |
| root | memory/innodb/memory                 |               116640 |
| NULL | memory/sql/THD::main_mem_root        |               116272 |
| NULL | memory/sql/NET::buff                 |                65564 |
| NULL | memory/innodb/ha_innodb              |                64957 |
| NULL | memory/mysys/TREE                    |                38936 |
| root | memory/sql/THD::main_mem_root        |                28696 |
+------+--------------------------------------+----------------------+
10 rows inset (0.01 sec)

SELECT 
    THREAD_ID, 
    EVENT_NAME, 
    SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE AS CURRENT_MEMORY_USAGE 
FROM performance_schema.memory_summary_by_thread_by_event_name
ORDER BY CURRENT_MEMORY_USAGE DESC
LIMIT 10;

语句 3：分析INNODB的内存分配

如果要整体的内存分配进行统计和计算，可以使用下面的语句。

SELECT 
    EVENT_NAME, 
    COUNT_ALLOC, 
    SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE AS CURRENT_MEMORY_USAGE 
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/%'
ORDER BY CURRENT_MEMORY_USAGE DESC;

+-----------+--------------------------------------+----------------------+
| THREAD_ID | EVENT_NAME                           | CURRENT_MEMORY_USAGE |
+-----------+--------------------------------------+----------------------+
|         1 | memory/innodb/memory                 |               308912 |
|         1 | memory/sql/dd::String_type           |               225564 |
|         1 | memory/sql/dd::objects               |               193312 |
|        37 | memory/sql/dd::objects               |               160224 |
|         1 | memory/mysqld_openssl/openssl_malloc |               150302 |
|        37 | memory/innodb/memory                 |               120984 |
|        53 | memory/innodb/memory                 |               116640 |
|        37 | memory/sql/THD::main_mem_root        |               108072 |
|         1 | memory/sql/NET::buff                 |                65564 |
|         1 | memory/innodb/ha_innodb              |                41309 |
+-----------+--------------------------------------+----------------------+

语句 4：分析系统内存占用情况

下面的语句可以给出总体的MySQL的使用中的内存情况，总体分配了多少内存，当前释放了多少内存，当前正在使用的内存是多少，分别是已经分配了1.47G内存，释放了80MB的内存，当前正在使用的内存是1.4G等，通过这个部分可以动态监控内存的使用情况。

SELECT 
    SUM(SUM_NUMBER_OF_BYTES_ALLOC) AS TOTAL_ALLOCATED_BYTES, 
    SUM(SUM_NUMBER_OF_BYTES_FREE) AS TOTAL_FREED_BYTES, 
    SUM(SUM_NUMBER_OF_BYTES_ALLOC) - SUM(SUM_NUMBER_OF_BYTES_FREE) AS CURRENT_MEMORY_USAGE 
FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------+-------------------+----------------------+
| TOTAL_ALLOCATED_BYTES | TOTAL_FREED_BYTES | CURRENT_MEMORY_USAGE |
+-----------------------+-------------------+----------------------+
|            1556430074 |          84261392 |           1472168682 |
+-----------------------+-------------------+----------------------+
1 row in set (0.00 sec)

SELECT CONCAT('KILL QUERY ', t.THREAD_ID, ';') AS kill_command
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_current AS s 
ON t.THREAD_ID = s.THREAD_ID
WHERE t.NAME LIKE 'thread/sql/%'
AND t.PROCESSLIST_TIME > 300;

以上的语句为自动打印超过5分钟的还在执行的语句，答应KILL 语句

SELECT EVENT_NAME, SUM(CURRENT_NUMBER_OF_BYTES_USED) AS memory_used
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/lock_sys'
GROUP BY EVENT_NAME;

以上的语句分析当前的锁分配是否有异常，是否存在大量的SQL锁之间的竞争，潜在说明SQL之间存在死锁，BLOCKED，或长时间运行的SQL等。

下面通过一个语句将系统中thread中运行的语句时间进行打印。通过ctrl+c停止这个脚本。这里展示的是历史的信息，如果要展示当前的语句执行的信息还需要配合 show processlist来进行观察。

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'mysql',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

const readline = require('readline');

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout
});

console.log('Press Ctrl+C to stop the script.');

rl.on('SIGINT', () => {
  console.log('Stopping script...');
  pool.end((err) => {
    if (err) {
      console.error('Error closing connection pool:', err);
    } else {
      console.log('Connection pool closed.');
      process.exit(0); 
    }
  });
});

pool.getConnection((err, connection) => {
if (err) {
    console.error('Error getting connection from pool:', err);
    return;
  }

  connection.query('SELECT * FROM performance_schema.threads WHERE THREAD_ID != connection_id()', (err, rows) => {
    if (err) {
      console.error('Error querying threads:', err);
      connection.release();
      return;
    }

    rows.forEach(row => {
      if (row.USER !== 'system') { 
        connection.query(`
          SELECT 
            s.PROCESSLIST_USER, 
            e.DIGEST_TEXT, 
            e.TIMER_WAIT / 1000000 AS duration_ms 
          FROM 
            performance_schema.events_statements_history_long e 
          JOIN 
            performance_schema.threads s ON e.THREAD_ID = s.THREAD_ID 
          WHERE 
            e.THREAD_ID = ${row.THREAD_ID}
          ORDER BY e.TIMER_WAIT DESC 
          LIMIT 1; 
        `, (err, slowQuery) => {
          if (err) {
            console.error(`Error querying slow queries for thread ${row.THREAD_ID}:`, err);
          } elseif (slowQuery.length > 0) { 
            console.log(`Thread ID: ${row.THREAD_ID}, User: ${slowQuery[0].USER}, SQL: ${slowQuery[0].DIGEST_TEXT}, Duration: ${slowQuery[0].duration_ms} ms`);
          }
        });
      }
    });

    connection.release(); 
  });
});