98%的DBA不知道的数据库内存知识点
腾讯云数据库
共 9184字，需浏览 19分钟
·
2021-02-25 19:34
一、如何看懂内存指标
total used free shared buff/cache available
Mem: 8008704 5234876 157920 640 2615908 2467292
Swap: 2047 0 2047
二、MySQL如何使用内存
全局共享
1. innodb_buffer_pool_size：InnoDB缓冲池的大小
2. innodb_additional_mem_pool_size：InnoDB存放数据字典和其他内部数据结构的内存大小，5.7已被移除
3. innodb_log_buffer_size：InnoDB日志缓冲的大小
4. key_buffer_size：MyISAM缓存索引块的内存大小
5. query_cache_size：查询缓冲的大小，8.0已被移除
线程独占
1. thread_stack：每个线程分配的堆栈大小
2. sort_buffer_size：排序缓冲的大小
3. join_buffer_size：连接缓冲的大小
4. read_buffer_size：MyISAM顺序读缓冲的大小
5. read_rnd_buffer_size：MyISAM随机读缓冲的大小、MRR缓冲的大小
6. tmp_table_size/max_heap_table_size：内存临时表的大小
7. binlog_cache_size：二进制日志缓冲的大小
内存分配器
三、到底是谁占用了内存
在绝大多数情况下，我们是不需要花费过多精力，去关注MySQL内存使用情况的；但是，也不能排除确实存在内存占用异常的情况，这个时候我们应该如何去进行深入排查呢？其实，MySQL官方就提供了强大的实时监控工具——performance_schema库下的监控内存表，通过这个工具，我们可以很清晰地观察到MySQL内存到底是被谁占用了、分别占用了多少。
开启内存监控
实例启动时开启
vi my.cnf
performance-schema-instrument='memory/%=ON'
vi my.cnf
performance-schema-instrument='memory/%=OFF'
实例运行时开启
我们也可以选择，在实例运行时，动态开启内存监控采集器，具体方法如下：
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';
内存监控表
mysql> select USER,HOST,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,CURRENT_COUNT_USED,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_account_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
+------+-----------+----------------------------+-------------+------------+--------------------+---------------------------+--------------------------+------------------------------+
| USER | HOST | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | CURRENT_COUNT_USED | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+------+-----------+----------------------------+-------------+------------+--------------------+---------------------------+--------------------------+------------------------------+
| NULL | NULL | memory/innodb/buf_buf_pool | 32 | 0 | 32 | 4500488192 | 0 | 4500488192 |
| NULL | NULL | memory/innodb/os0event | 1573559 | 0 | 1573559 | 214004024 | 0 | 214004024 |
| NULL | NULL | memory/innodb/hash0hash | 82 | 6 | 76 | 397976480 | 227067024 | 170909456 |
| NULL | NULL | memory/innodb/log0log | 10 | 0 | 10 | 33565840 | 0 | 33565840 |
| root | localhost | memory/innodb/std | 3650638 | 3043111 | 607527 | 160778066 | 141334898 | 19443168 |
| NULL | NULL | memory/mysys/KEY_CACHE | 3 | 0 | 3 | 8390768 | 0 | 8390768 |
| NULL | NULL | memory/innodb/ut0pool | 2 | 0 | 2 | 4194480 | 0 | 4194480 |
| NULL | NULL | memory/innodb/sync0arr | 3 | 0 | 3 | 2506184 | 0 | 2506184 |
| NULL | NULL | memory/innodb/lock0lock | 33 | 0 | 33 | 2245040 | 0 | 2245040 |
| root | localhost | memory/innodb/mem0mem | 9897784 | 9896793 | 991 | 8845389160 | 8843147749 | 2241411 |
+------+-----------+----------------------------+-------------+------------+--------------------+---------------------------+--------------------------+------------------------------+
10 rows in set (0.01 sec)
mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name | current_alloc | high_alloc |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool | 7.29 GiB | 7.29 GiB |
| memory/sql/sp_head::main_mem_root | 3.21 GiB | 3.62 GiB |
| memory/innodb/hash0hash | 210.16 MiB | 323.63 MiB |
| memory/sql/TABLE | 183.82 MiB | 190.28 MiB |
| memory/sql/Query_cache | 128.02 MiB | 128.02 MiB |
| memory/mysys/KEY_CACHE | 64.00 MiB | 64.00 MiB |
| memory/innodb/log0log | 32.08 MiB | 32.08 MiB |
| memory/innodb/parallel_doublewrite | 30.27 MiB | 30.27 MiB |
| memory/performance_schema/table_handles | 27.19 MiB | 27.19 MiB |
| memory/innodb/mem0mem | 19.14 MiB | 20.79 MiB |
| memory/performance_schema/events_statements_history_long | 13.66 MiB | 13.66 MiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB | 9.77 MiB |
/** @file include/os0event.h
The interface to the operating system condition variables
Created 2012-09-23 Sunny Bains (split from os0sync.h)
*******************************************************/memory/innodb/hash0hash
/** @file include/hash0hash.h
The simple hash table utility
Created 5/20/1997 Heikki Tuuri
*******************************************************/
四、总结
关于专栏
《腾讯云数据库专家服务》是由腾讯云数据库技术服务团队维护的社区专栏，涵盖了各类数据库的实际案例，最佳实践，版本特性等内容。目前专栏文章仍在持续丰富中，欢迎在文章末尾留言互动，给出宝贵的建议。
往期推荐
IO问题成顽疾，鹅厂专家来教你
CPU占用又爆了？MySQL到底在干什么
评论