详解分析MySQL8.0的内存消耗(2)-木庄网络博客

这张表，找到你关注的内存变量的名称（直接搜索，结果有490多条，分为好几个大类，一定记得过滤自己关注的参数）。举个例子，我们搜索memory/innodb相关参数，代表innodb存储引擎占用的内存，结果如下：

mysql> SELECT * FROM performance_schema.setup_instruments  WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| NAME          | ENABLED | TIMED | PROPERTIES  | VOLATILITY | DOCUMENTATION |
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| memory/innodb/adaptive hash index   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/log and page archiver  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/buf_buf_pool    | YES  | NULL | global_statistics |   0 | NULL   |
| memory/innodb/buf_stat_per_index_t  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/clone      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_bg_recalc_pool_t | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_index_map_t  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/dict_stats_n_diff_on_level | YES  | NULL |     |   0 | NULL   |
| memory/innodb/other      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/partitioning    | YES  | NULL |     |   0 | NULL   |
| memory/innodb/row_log_buf     | YES  | NULL |     |   0 | NULL   |
| memory/innodb/row_merge_sort    | YES  | NULL |     |   0 | NULL   |
| memory/innodb/std       | YES  | NULL |     |   0 | NULL   |
| memory/innodb/trx_sys_t::rw_trx_ids  | YES  | NULL |     |   0 | NULL   |
| memory/innodb/undo::Tablespaces   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/ut_lock_free_hash_t   | YES  | NULL |     |   0 | NULL   |
| memory/innodb/api0api      | YES  | NULL |     |   0 | NULL   |
| memory/innodb/api0misc     | YES  | NULL |     |   0 | NULL   |
| memory/innodb/btr0btr      | YES  | NULL |     |   0 | NULL   |

2、在配置文件中写上相关的参数，开启统计，以memory/innodb/row_log_buf为例，配置文件修改的如下：

performance-schema-instrument='memory/innodb/row_log_buf=COUNTED'

3、启动实例，并在performance_schema数据库的memory_summary_global_by_event_name表中查看内存统计结果。

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G

当然，你还可以根据sys表中的结果，查看每个大类的聚合结果，如下：

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
  code_area, FORMAT_BYTES(SUM(current_alloc))
  AS current_alloc
  FROM sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(event_name,'/',2)
  ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area     | current_alloc |
+---------------------------+---------------+
| memory/innodb    | 843.24 MiB |
| memory/performance_schema | 81.29 MiB  |
| memory/mysys    | 8.20 MiB  |
| memory/sql    | 2.47 MiB  |
| memory/memory    | 174.01 KiB |
| memory/myisam    | 46.53 KiB  |
| memory/blackhole   | 512 bytes  |
| memory/federated   | 512 bytes  |
| memory/csv    | 512 bytes  |
| memory/vio    | 496 bytes  |
+---------------------------+---------------+

更详细的信息，请参见官方文档。

以上就是详解分析MySQL8.0的内存消耗的详细内容，更多关于MySQL8.0 内存消耗的资料请关注其它相关文章！

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容