IE盒子

搜索
查看: 83|回复: 1

MySQL内存消耗

[复制链接]

3

主题

10

帖子

17

积分

新手上路

Rank: 1

积分
17
发表于 2023-1-5 14:54:27 | 显示全部楼层 |阅读模式
一般包含两中内存:

  • global级共享内存
  • session级私有内存
1. global级共享内存

包括的内容如下图所示


执行如下命令,即可查询示例的共享内存分配情况:
show variables where variable_name in (
'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','query_cache_size'
);
以下是一个mysql的输出结果
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_size | 5368709120 |
| innodb_log_buffer_size | 8388608 |
| query_cache_size | 1048576 |
+---------------------------------+------------+
innodb_buffer_pool_size

这是 Innodb 引擎最重要的缓存,也是提升查询性能的重要手段。一般是global共享内存中占用最大的部分。在进行 SQL 读和写的操作时,首先并不是对物理数据文件操作,而是先对 buffer_pool 进行操作,然后再通过 checkpoint 等机制写回数据文件。占用的内存启动后就不会自动释放,默认通过LRU的算法镜像缓存淘汰,每次的新数据页,都会插入buffer pool的中间,防止前面的热数据被冲掉,长时间没动静的冷数据,会被淘汰出buffer pool,但是是被其它新数据占用了,所以一般这里不会释放的,除非重启(5.7 开始支持动态调整,默认以128M的chunk单位分配内存块)。
innodb_buffer_pool

主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。
innodb_log_buffer_size

对应图中redo log_buffer
缓存 redo log 的信息,大小可以配置。redo log 会先写在这里,然后依照一定频率刷新回redo log文件中。
innodb_additional_mem_pool_size

存放 InnoDB 内的一些数据结构,一般在 buffer_pool 中申请内存的时候,还需要在此空间申请存储该对象的结构信息。该大小主要与表数量有关,表数量越大需要更大的空间。
query_cache_size

该部分是对查询结果做缓存以减少解析 SQL 和执行 SQL 的花销,主要适合于读多写少的应用场景,因为它是按照 SQL 语句的 hash 值进行缓存的,当表数据发生变化后即失效。
2. session级私有内存

session级私有内存,主要是数据库连接私有内存使用,查询命令如下:
show variables where variable_name in (
'tmp_table_size','sort_buffer_size','read_buffer_size','read_rnd_buffer_size','join_buffer_size','thread_stack', 'binlog_cache_size'
);
查询结果如下:
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| binlog_cache_size | 32768 |
| join_buffer_size | 134217728 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 2097152 |
| sort_buffer_size | 2097152 |
| thread_stack | 262144 |
| tmp_table_size | 536870912 |
+----------------------+-----------+
tmp_table_size

是MySQL的heap(堆积)表缓冲大小,表示内存中临时表的最大值。如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下
sort_buffer_size

执行ORDER BY和GROUP BY排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小。若存储量大于 sort_buffer_size,则会在磁盘生成临时表以完成操作。在 Linux 系统中,当分配空间大于 2 M 时会使用 mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
read_buffer_size

顺序读缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。
read_rnd_buffer_size

随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。
join_buffer_size

每次join操作都会调用my_malloc、my_free函数申请/释放join_buffer_size的大小的内存。
thread_stack

每个session连接线程被创建时,MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时,需要给它分配一定大小的内存堆栈空间,以便存放客户端的请求的Query及自身的各种状态和处理信息。
查看连接线程相关的系统变量的设置值: show variables like 'thread%';
binlog_cache_size

为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。表示的是binlog 能够使用的最大cache 内存大小。在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中,等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时就会报错:“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”
3. 理论内存消耗计算方式

这里先不考虑 innodb_buffer_pool_size 未被完全使用的情况。另外,max_connections 计算的是最高session消耗。
key_buffer_size + query_cache_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size
+ max_connections * (
sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size
)
在线计算工具 http://www.mysqlcalculator.com/
回复

使用道具 举报

4

主题

11

帖子

23

积分

新手上路

Rank: 1

积分
23
发表于 2025-5-17 06:53:15 | 显示全部楼层
有空一起交流一下
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表