MYSQL 8 已经上线一段时间了,每个数据库系统的内存方面都有自己的特点,MYSQL的内存的特点,下面总结了一些同学们经常会问的一些内存方面的问题.
1 为什么MYSQL 内存在使用完后并不释放
这点需要从LINUX系统的 malloc()内存分配函数来说起, LINUX 将内存分配给MYSQL 通过 malloc() 函数, 而malloc() 函数是不会将内存在进行FREE 的回收的,所以说MYSQL 使用内存后不release 是不对的,这是LINUX 的问题.当然好处是这样调用后,再有其他使用内存的情况下,调用的速度会很快.
2 如果我经常遇到 LINUX 由于MEMORY RUN OUT 导致 MYSQL 经常被KILL 在 restart的问题,我怎么办?
这个问题这里分为三个层次
1 innodb buffer pool size 分配的不合理, 具体innodb buffer pool size 应该配置成多少 是内存的 60% 还是 80% 这就的看第二个问题
2 你到底有多少连接在并发,并且这些连接使用的内存大概是多少, 你的机器上是否是单一的MYSQL 数据库服务器还是,混杂了其他的应用服务,他们占用的内存是多少.
3 MYSQL 的内存泄漏了
如果服务器包含了问题 2 那建议还是让服务器尽量能只服务于MYSQL 或者降低 innodb_buffer_pool_size 的方法,当然添加内存也是一种解决方法.
如果问题中一些比较差的语句导致一些MYSQL的SESSION 级别的内存方面的配置吃紧,那也会导致类似 MEMORY RUN OUT 的问题.
当然也有问题例如如果想清楚的知道自己的 INNODB BUFFER POOL 是否设置的OK, 也可以从 命中率, 脏页,等特性中调整自己的 INNODB BUFFER POOL的一些设置的参数调整. (之前写过,这里不赘述了)
3 MYSQL 服务器到底内存泄漏了没?
我们要判断内存到底泄漏了没有,
通过smem命令查看当前用户使用内存的情况,并且也可以查看到底走没有SWAP ,到底是那个用户在走SWAP
同时通过FREE -M 在 CENT OS 7 上可以通过 available - free = buff/cache 的方式来判断到底有没有内存泄漏,如果差距比较大说明有内存泄漏的情况. 就需要用更深层的方式来判断,到底是那个程序的内存在泄漏了.
4 到底MYSQL的内存是怎么分配的,怎么看?
实际上MYSQL 5.7 和 8 已经在 sys 中的提供了对于内存分配的查询
SEECT SUBSTRING_INDEX(event_name,'/',2) AS
code_area, sys.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;
其中可以通过上面看出当前内存使用的项目在哪里,使用的内存是多少等等.
5 对于 innodb_numa_interleave 参数在大内存的MYSQL 中是否有必要打开?
在大于128G 的内存的MYSQL 数据库中,到底有没有必要打开 innodb_numa_interleave 开关,答案是 YES
为什么要打开这个开关
下面这张图是非numa 的总线结构, 所以系统访问数据的内容的瓶颈在于 BUS
在使用了NUMA 方式的架构中CPU 与内存之间的瓶颈就消除了,打开这个参数后,innodb 数据库引擎就可以利用多核心CPU 在内存中进行跨CPU 的内存通道的使用方式,避免CPU 成为大内存使用时的瓶颈.
6 如果开发是第三方,并且无法修改语句,而语句的性能比较低,此时 DB 人员如何在无法优化语句写法的情况下,提高性能?
下面的参数类似于ORACLE 的 PGA
read_buffer_size =
read_rnd_buffer_size =
sort_buffer_size =
tmp_table_size =
join_buffer_size =
以上的参数可以在 读取数据后在 排序方面 sort_buffer_size 提高由于filesort 方面的性能问题,每个连接可以使用最大设置的值,避免在文件系统上成型数据结果. 对于查询中需要多表进行JOIN 的情况下尽量通过 JOIN_BUFFER 来在内存中将结果集合进行存储,降低通过文件体系的方法来进行结果的计算.
另外read_buffer_size 和 read_rnd_buffer_size 也是对于读取的顺序数据和随机数据在buffer中缓冲提高数据获取的速度.
但注意的问题是,将这些值开的比较大的情况下,SQL 又比较没有按照原理写的时候,对内存的占用会比较高,所以设置 innodb_buffer_size的时候就要考虑设置的值,以及整体内存的值之间的关系,避免 OOM 的发生. 当然也与MAX_CONNECTIONS 的关系, 以及系统平时大部分的connections.
最后要说的是上面的四个值,可不是INNODB 数据库引擎独享,而是在服务器层面的缓冲.
