mysql内存使用 mysql内存使用率高分析

转载

卫斯理 2023-08-20 18:10:33

文章标签 mysql内存使用 MySQL mysql 缓存 文章分类 MySQL 数据库

概述

最近从监控发现某台主机内存使用率一直居高不下，检查发现是某台数据库的从库，下面介绍下问题的整体排查过程：

环境说明：

操作系统：CentOS 7.3 x86_64数据库：Mysql 5.7.24服务器：16核 32G

1、监控展示

数据库从库当前内存使用率达到97%，而主库内存使用率是80%，还算是比较正常

mysql内存使用 mysql内存使用率高分析_mysql

从库最近12h内存使用情况如下：

mysql内存使用 mysql内存使用率高分析_mysql内存使用_02

服务器top显示：

mysql内存使用 mysql内存使用率高分析_mysql_03

2、查看mysql里的线程，观察是否有长期运行或阻塞的sql

经查看，没有发现相关线程，可排除该原因

show full processlist;

mysql内存使用 mysql内存使用率高分析_MySQL_04

3、查看mysql内存，缓存的相关配置

( mysql内存计算器，具体地址为http://www.mysqlcalculator.com/ )

也可以用以下sql查看当前相关的配置项:

select VARIABLE_NAME,VARIABLE_VALUE/1024/1024 MB from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME in ('key_buffer_size','query_cache_size','tmp_table_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size')union allSELECT 'sort_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'sort_buffer_size' ) AS v2 union allSELECT 'read_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_buffer_size' ) AS v2 union allSELECT 'read_rnd_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_rnd_buffer_size' ) AS v2 union allSELECT 'join_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024  vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'join_buffer_size' ) AS v2 union allSELECT 'thread_stack',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'thread_stack' ) AS v2 union allSELECT 'binlog_cache_size',(V1.VARIABLE_VALUE*v2.vv) MBFROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'binlog_cache_size' ) AS v2

mysql内存使用 mysql内存使用率高分析_缓存_05

4、调整数据库实例参数

从上面可以知道sort_buffer_size、read_buffer_size、read_rnd_buffer_size、join_buffer_size、binlog_cache_size等参数均调的过高了。

mysql内存使用 mysql内存使用率高分析_缓存_06

mysql内存使用 mysql内存使用率高分析_缓存_07

各参数调整后修改如下：

1)key_buffer_size =32M

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。由于我的数据库引擎为innodb，大部分表均为innodb，此处取默认值一半32M。

2)query_cache_size = 0

查询缓存大小，当打开时候，执行查询语句会进行缓存，读写都会带来额外的内存消耗，下次再次查询若命中该缓存会立刻返回结果。默认改选项为关闭，打开则需要调整参数项query_cache_type=ON。此处不采用查询缓存。

3)tmp_table_size = 128M

范围设置为64-256M最佳，当需要做类似group by操作生成的临时表大小，提高联接查询速度的效果，调整该值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%，处于这样一个状态之下，效果较好，如果网站大部分为静态内容，可设置为64M，如果为动态页面，则设置为100M以上，不宜过大，导致内存不足I/O堵塞。此处我们设置为128M。

4)innodb_buffer_pool_size = 24G

这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。专用mysql服务器设置的大小：操作系统内存的70%-80%最佳。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

5)innodb_additional_mem_pool_size = 16M (忽略)

innodb_additional_mem_pool_size 是 InnoDB 用来保存数据字典信息和其他内部数据结构的内存池的大小，单位是 byte，参数默认值为8M。数据库中的表数量越多，参数值应该越大，如果 InnoDB 用完了内存池中的内存，就会从操作系统中分配内存，同时在 error log 中打入报警信息。

这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。innodb_additional_mem_pool_size 和 innodb_use_sys_malloc 在 MySQL 5.7.4 中移除。所以此处不做设置。

6)innodb_log_buffer_size = 8M

InnoDB的写操作，将数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据(a lot of huge blobs)，4MB-8MB已经足够了。此处我们设置为8M。

7)max_connections = 800

max_connections：针对所有的账号所有的客户端并行连接到MYSQL服务的最大并行连接数。简单说是指MYSQL服务能够同时接受的最大并行连接数。根据同时在线人数设置一个比较综合的数字，最大不超过16384，此处我们根据系统使用量综合评估，设置为800。

max_user_connections : 针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

max_connect_errors：针对某一个IP主机连接中断与mysql服务连接的次数，如果超过这个值，这个IP主机将会阻止从这个IP主机发送出去的连接请求。遇到这种情况，需执行flush hosts

8)sort_buffer_size = 2M

这是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M。

9)read_buffer_size = 2M

(数据文件存储顺序)是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率数据文件顺序。此处我们设置得比默认值大一点，为2M。

10)read_rnd_buffer_size = 2M

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

11)join_buffer_size = 2M

多表参与join操作时的分配缓存，适当分配，降低内存消耗，此处我们设置为2M。

12)thread_stack = 256K

每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。Thread Cache 命中率：Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率处于90%才算正常配置，当出现“mysql-debug: Thread stack overrun”的错误提示的时候需要增加该值。此处我们配置为256K。

13)binlog_cache_size = 4M

为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。作用是提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是1048576 --1M；后者建议是： 2097152 -- 4194304 即 2--4M。