Mysql性能瓶颈深度定位分析

       我们在性能测试过程中,经常会遇到Mysql出现性能瓶颈的情况,对于数据库来说,所谓的性能瓶颈无非是慢SQL、CPU高、IO高、内存高,其中前三个举实际例子来进行性能分析,最后内存高只是方法性说明(实际测试项目中没遇到过):

       首先我们要保证没有数据库配置方面的性能问题,毕竟在性能测试前,对一些基本配置要撸一遍,避免犯低级错误。

       本文结合实际项目例子进行分析(绝对硬核),包括内容:一、慢SQL定位分析;二、高CPU定位分析;三、高IO定位分析;四、高内存定位分析。

一、慢SQL定位分析

       首先业务系统慢,肯定是体现在响应时间上,所以在性能测试中,如果发现慢我们就从响应时间上进行拆分,最后拆到mysql,那就是分析慢SQL,同样如果在高并发时发现mysql进程占CPU很高,也是优先分析是否存在慢SQL,而且判断慢SQL还是比较简单的,对于Mysq就是看慢日志查询。

1、首先是开启慢日志查询:

#查看是否开启,以及存放路径
show variables like '%slow_query_log%';

#开启
set global slow_query_log = 1;

#记录慢日志的时间,默认情况下为10秒
show variables like '%long_query_time%'

#查看慢日志条数
show global status like '%slow_queries%'

使用set global slow_query_log=1;开启慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。想要永久生效,就必须修改配置文件,其实没这必要,我们都是临时开启,分析性能问题而已(分析完了,还得关了)。

2、测试过程获取慢SQL

要手工分析日志,查找和分析SQL,显然是个体力活,MySql提供了日志分析工具mysqldumpslow

#得到返回记录集最多的10个SQL
Mysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow.log
#得到访问次数最多的10个SQL
Mysqldumpslow –s c –t 10 /usr/local/mysql/data/localhost-slow.log
#得到按照时间排序的前10条里面含有左连接的查询
Mysqldumpslow –s t –t 10 –g “left join” /usr/local/mysql/data/localhost-slow.log
#另外建议在使用这些命令时结合|和more使用,否则可能出现爆破情况
Mysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow.log | more
参数含义
s: 表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
t:返回前面多少条的数据
g:后面搭配一个正则表达式

除此之外,你们也可以通过APM监控(全链路监控),也是能监控到慢SQL(当然压测过程中不建议依赖一些重型工具):

3、初步Explain分析

这是最基础的功能,获取到慢SQL,当然是要实际验证一下有多慢,是否索引配置了,拿一条实际测试项目的SQL语句来分析:

explain SELECT count(c.id)
        FROM administrative_check_content c
        LEFT JOIN administrative_check_report_enforcers e ON c.report_id=e.report_id
        LEFT JOIN administrative_check_report r ON c.report_id = r.id
        WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592';

可以分析出这条语句,86%的时间是花在了Sending data(所谓的“Sending data”并不是单纯的发送数据,而是包括“收集 [检索] + 发送数据”):

通过Explain解释也能看出索引已经加了(enforcer_id_index),而且通过索引几乎全表检索了30084条数据,如下:

 一般如果索引没加或是加的不合理,通过这么一分析也就能马上看出来,可以说索引问题是导致慢SQL的最主要原因之一,也是影响业务系统性能的关键因素,以下以本次压测项目的例子来说,没加索引时最高TPS只有200,加了索引最高TPS达到900,如下所示:

在Explain解释语句中,跟索引有关的列我们主要关注以下几个:

(1)type
这列很重要,显示了连接使用了哪种类别,有无使用到索引。一般从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。

(2)possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询。
(3) key
key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
(4)key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用索引的长度,在不损失精确性的情况下,长度越短越好。
(5)ref
ref列显示使用哪个列或常数与key一起从表中选择行。这一列涉及到多表关联的字段,const表示常数关联。 ref很多时候也是和索引有关联影响的地方。

4、用show profile进行sql分析

开启分析也很简单,使用临时开启执行set profiling=1即可(这个功能会缓存最近查询的分析语句,默认15条,最多100条,适合在压测结束后开展sql分析,用完后再设成0关闭),如下:

#显示是否开启Profiling,以及最多存储多少条
show variables like '%profil%';

#开启Profiling
set profiling=1;

#执行你的SQL
#在这里我们主要是执行前面所找到的慢SQL

#查看分析
show profiles;

通过show profiles我们可以看到我们上面执行的那条SQL(Query_ID=18):

 执行:show profile cpu,memory,block io for query 18;

可以看出也是Sending data总共消耗0.39秒,其中CPU_user时间占比较高(简单的一条SQL语句消耗这些时间就算很高了),另外还能看到这条SQL的IO开销(因为查询,都是ops out块输出)。

另外说明一下这个show profile语句:

show profile cpu, block io, memory,swaps,context switches,source for query [Query_ID];

# Show profile后面的一些参数:
# - All:显示所有的开销信息
# - Cpu:显示cpu相关开销
# - Block io:显示块IO相关开销
# - Context switches: 上下文切换相关开销
# - Memory:显示内存相关开销
# - Source:显示和source_function,source_file,source_line相关的开销信息

结论:通过一条慢SQL我们就能追本溯源找到它慢的原因,这样就能很好的指导性能调优了(性能测试工程师可以不会调优,但是不会性能分析还真不行,一遇到问题只会说慢,却不会告诉开发人员到底哪慢的测试工程师,应该很难让开发人员敬仰,甚至难以收获尊重!)。

二、高CPU定位分析

1、SQL引起的高CPU

在性能压测过程中,导致数据库CPU很高的原因有很多种,一般和慢SQL也有关(因为每条SQL要么占CPU高,要么占IO高,大体是这样),那么如何分析到是某些SQL引起的呢?

(1)首先定位占用CPU高的进程

通过TOP命令找到Mysql占用CPU高,再看mysql进程下有多少线程是占用CPU高的:

# top -p [pid] H
top -p 44662 H

可以看到有6个在Running,CPU都挺高的,36个在Sleeping,其中两个Sleeping的CPU也挺高的。

(2)我们在mysql中使用 SHOW FULL PROCESSLIST; 查询(通过FULL不仅能显示所有连接的线程,而且能显示出正在执行的完整SQL语句),如下:

可以看到有不少是Sending data状态的,我们挑选其中最复杂的一条语句来分析:

(SELECT r.id,c.check_action_name,check_date,check_end_date,c.id AS check_content_id,c.check_object_name AS checkObjectName,c.update_time,c.verify	
        FROM administrative_check_content c	
        LEFT JOIN administrative_check_report r ON c.report_id=r.id	
        LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id	
        WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'	      	      	
        )		
            UNION ALL	
            (	
            SELECT r.id,r.check_action_name,check_date,check_end_date,'0','无' AS checkObjectName,r.update_time,false	
            FROM administrative_check_report r	
            LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id	
            WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'	
            AND r.check_content_id='0'	
             	
            )	    	
        ORDER BY update_time DESC,check_content_id	
        LIMIT 0, 15	

(3)把这条语句用前面提到show profile进行分析:

 可以看到有两Sending data占用时间都挺高的,花费时间也高,其实这语句用了联合查询,我们可以把SQL语句拆分了继续分析(复杂语句都是由简单语句组成,比较不爽的是有时候分离出来的语句也很慢),拆出一条语句继续分析:

SELECT r.id,c.check_action_name,check_date,check_end_date,c.id AS check_content_id,c.check_object_name AS checkObjectName,c.update_time,c.verify	
        FROM administrative_check_content c	
        LEFT JOIN administrative_check_report r ON c.report_id=r.id	
        LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id	
        WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'	 

执行这条拆分出来的语句,查询时间都需要0.8秒多,如下:

执行 EXPLAIN 分析,看到通过索引查询到的内容多达30084行,如下所示:

一般像这样的系统进行压测,多半是数据分布不合理,测试数据没有反应真实的业务场景,明显数据分布不均衡,一个ID号就关联三万条数据,使用索引的效率都没能体现出来。

总结:通过SHOW PROCESSLIST;我们可以知道Mysql当前的线程状态,以及主要资源消耗在哪方面;再结合show profile分析具体占用CPU高的SQL,可以进一步定位出SQL引起高CPU的原因,到这一步无疑就能指导开发人员的优化方向了。

2、其他原因引起的高CPU

基本上和上面的分析思路差不多,排除SQL原因(SQL引起的问题主要集中于CPU或IO,IO高有时候也会间接导致CPU高),其他原因引起的高CPU,可通过mysql show processlist + show status + kill Id的方式进行定位。

(1)首先,通过SHOW PROCESSLIST查询mysql线程状态,我们需要重点了解State列不同状态所代表的含义:

Checking table
 正在检查数据表(这是自动的)。
Closing tables
 正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
 复制从服务器正在连接主服务器。
Copying to tmp table on disk
 由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存(如果临时表过大会导致mysql将临时表写入硬盘的时间过长,会影响整体性能)。
Creating tmp table
 正在创建临时表以存放部分查询结果。
deleting from main table
 服务器正在执行多表删除中的第一部分,刚删除第一个表。
deleting from reference tables
 服务器正在执行多表删除中的第二部分,正在删除其他表的记录。
Flushing tables
 正在执行FLUSH TABLES,等待其他线程关闭数据表。
Killed
 发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查 kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。
Locked
 被其他查询锁住了。
Sending data
 正在处理SELECT查询的记录,同时正在把结果发送给客户端。
Sorting for group
 正在为GROUP BY做排序。
 Sorting for order
 正在为ORDER BY做排序。
Opening tables
 这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
 正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。
Reopen table
 获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。
Repair by sorting
 修复指令正在排序以创建索引。
Repair with keycache
 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
 正在等待客户端发送新请求.(Sleeping过多也是问题,比如wait_timeout设置过大,导致MySQL里大量的SLEEP进程无法及时释放,拖累系统性能,不过也不能把它设置的过小,否则你可能会遭遇到“MySQL has gone away”之类的问题)。
System lock
 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
 INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
 正在搜索匹配的记录,并且修改它们。
User Lock
 正在等待GET_LOCK()。
Waiting for tables
 该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个 表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
 INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

        以上大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。
     还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

(2)其次,通过show status查询当前Mysql的运行状态

了解以下状态值及含义,如果在日常运维过程有做这方面的记录,那么当系统出现性能异常时,能做个状态值的比较对,偏离过大的就是需要关注的点(其实可以把这些参数值加入到运维监控系统,作为关注指标),如下:

Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 服务器启动后同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 当前打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载)
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠(激活)的线程数量。
Uptime 服务器工作了多少秒。
Uptime_since_flush_status 最近一次使用FLUSH STATUS 的时间(以秒为单位)

3)最后,可以尝试kill id(id在SHOW PROCESSLIST中显示 ),关掉疑似占CPU高的线程,以确认是否能让CPU降下来。

对于mysql来说,慢SQL及死锁以外的CPU问题确实不好定位,要求对数据库系统及性能非常了解,而对于我们做性能测试的,能做的就是逐层分析,缩小问题范围,实在不行,只能用kill id的方式来试错排查。

三、高IO定位分析

       其实高IO也可能导致CPU高,因为磁盘I/O比较慢,会导致CPU一直等待磁盘I/O请求。分析数据库IO属于基本技能(毕竟大部分数据库调优到了极致,最后的瓶颈也可能会是IO,而且IO调优的难度会高一些)。

(1)首先用万能的top命令查看进程

[root@localhost ~]# top
top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47
Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.7%us,  3.4%sy,  0.0%ni, 77.6%id, 11.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  49374024k total, 32018844k used, 17355180k free,   115416k buffers
Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld
40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明显是前面两个mysqld进程导致整体负载较高。而且,从 Cpu(s) 这行的统计结果也能看的出来,%us 和 %wa 的值较高,表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。
(2)我们先分析下磁盘I/O的情况

执行 sar -d 1或(iostat -d -x -k 1)命令(每秒刷新) 确认磁盘I/O是否真的较大:

[root@localhost ~]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (localhost.localdomain)     06/05/2020  _x86_64_        (8 CPU)
11:54:31 AM     DEV      tps    rd_sec/s    wr_sec/s    avgrq-sz   avgqu-sz  await     svctm   %util
11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00
11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10
11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00
11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00
11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00
11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

%util 达到或接近100%,说明产生的I/O请求太多,I/O系统已经满负荷。 

(3)再利用 iotop 确认到底哪些进程消耗的磁盘I/O资源最多:

[root@localhost ~]# iotop
Total DISK READ: 59.52 M/s | Total DISK WRITE: 598.63 K/s
TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
16397 be/4 mysql       7.98 M/s    0.00 B/s  0.00 % 95.67 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
 7295 be/4 mysql      9.95 M/s    0.00 B/s  0.00 % 93.72 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
14295 be/4 mysql      9.86 M/s    0.00 B/s  0.00 % 94.53 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
14288 be/4 mysql      13.38 M/s    0.00 B/s  0.00 % 92.21 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
14292 be/4 mysql      13.54 M/s    0.00 B/s  0.00 % 91.96 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306

可以看到,端口号是3306的实例消耗的磁盘I/O资源比较多,那就看看这个实例里都有什么查询在跑。

(4)可以用上面提到的SHOW PROCESSLIST方法,也可以用mysqladmin命令工具

我们需要看到当前都有哪些SQL在运行:

(以下用mysqladmin的方式,该命令mysql自带,可创建软链接方便调用,ln -s /usr/local/mysql/bin/mysqladmin /usr/bin):

[root@localhost ~]# mysqladmin -uroot -p123456 pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host     | db |Command|Time | State        | Info                                                                                          |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 |root| 172.16.1.133:45921 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 |root| 172.16.1.133:45923 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 |root| 172.16.1.133:45928 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
| 27 |root| 172.16.1.133:45930 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
| 36 |root| 172.16.1.133:45937 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查询还未完成,从slow query log中也能发现,这类SQL发生的频率很高。
这是一个非常低效的SQL写法,导致需要对整个主键进行扫描,但实际上只需要取得一个最大值而已,从slow query log中可看到:

Rows_sent: 1  Rows_examined: 5413058

每次都要扫描500多万行数据,却只为读取一个最大值,效率非常低。

经过分析,这个SQL稍做简单改造即可在个位数毫秒级内完成,提升了N次方。
改造的方法是:对查询结果做一次倒序排序,取得第一条记录即可。而原先的做法是对结果正序排序,取最后一条记录。

总结:mysql的IO分析思路挺简单,首先通过top关注%wa(指CPU等待磁盘写入完成的时间,平时为0,越高表示磁盘越忙)的波动是否较大;其次分析下磁盘I/O情况,并找到哪些进程占用IO资源最多;最后还是用SHOW PROCESSLIST或mysqladmin查看哪些语句的频繁调用在占用IO。

四、高内存定位分析

       要在linux下分析内存占用高低,对于新手来说不太容易,因为涉及MemFree、虚拟内存(Swap)和Buffers、Cached的概念要搞明白,直观性不如windows,另外mysql本身默认没有开启内存 / 缓存监控(只对performance_schema进行了内存开销的统计),一般的mysql监控软件在这方面也很难直观的暴露问题。

(1)首先分析内存也可以用万能的TOP命令,看看是否mysql进程占用内存高 :

       以上这张图,比较容易看出是内存占用高,因为free、buffers、cached都不高,那么大部分被used掉的内存就属于被进程占用,而且没有Swap(被禁用了,一般正常情况下,swap交换分区的used值如果在不断的变化,就说明内核在不断进行内存和swap的数据交换,很可能是内存不够用了),那么现在mysql占用63.3%就可以判断确实内存高了。

(2)排查是否大量SQL运行占用内存高

查看mysql里的线程,观察是否有长期运行或阻塞的sql,也是用到万能的show full processlist;,如果没有发现相关线程(具体参考上面提到的State列不同状态含义)异常现象,就可以排除该原因。

(3)查看mysql内存/缓存的相关配置,以便排查mysql连接使用完后是否没有真正释放内存

Mysql的内存消耗一般分为两种:global级共享内存、session级私有内存。

执行如下命令,即可查询global级共享内存分配情况:

show variables where variable_name in (
'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','query_cache_size'
);

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'
);

按理用mysql查询命令,就能查到当前各项内存或缓存的使用情况,但是mysql默认是没有开启内存监控的,通过以下语句就能查出大部分监控项都是未开启的:

SELECT * FROM performance_schema.setup_instruments
       WHERE  NAME LIKE '%memory%' and NAME not LIKE '%memory/performance_schema%';

我们可以用update语句批量开启(属于临时性开启,重启mysql后又还原为关闭):

mysql> update performance_schema.setup_instruments set enabled = 'yes'
       WHERE  NAME LIKE '%memory%' and NAME not LIKE '%memory/performance_schema%';
> Affected rows: 310
> 时间: 0.002s

然后通过以下语句就可以查出mysql所有内存的使用:

SELECT 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;

查到占用内存最高的是memory/innodb,如下:

 可以进一步细化查询memory/innodb:

SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/innodb%';

对于内存使用预估,在网上有人推荐了一款内存计算器,统计网址:http://www.mysqlcalculator.com/ 

(说明:上图左列为mysql默认配置,右列为当前数据库的配置  [通过show variables可以查到],可以预估出内存使用最大值,如上图,轻微调大一些配置,就能达到7119MB的内存量;如果预计到的结果不符合要求,就说明当前配置不合理,需要进行调整)。 

mysql的数据库内存/缓存优化真没什么经验,以下是网上提供的一个优化过程配置项(实际要设置多大,得看自己机器的内存有多大,结合内存计算器算一算,看看有没有超标):

key_buffer_size = 32M //key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。由于我的数据库引擎为innodb,大部分表均为innodb,此处取默认值一半32M。
query_cache_size = 64M //查询缓存大小,当打开时候,执行查询语句会进行缓存,读写都会带来额外的内存消耗,下次再次查询若命中该缓存会立刻返回结果。默认改选项为关闭,打开则需要调整参数项query_cache_type=ON。此处采用默认值64M。
tmp_table_size = 64M //范围设置为64-256M最佳,当需要做类似group by操作生成的临时表大小,提高联接查询速度的效果,调整该值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%,处于这样一个状态之下,效果较好,如果网站大部分为静态内容,可设置为64M,如果为动态页面,则设置为100M以上,不宜过大,导致内存不足I/O堵塞。此处我们设置为64M。
innodb_buffer_pool_size = 8196M //这个参数主要作用是缓存innodb表的索引,数据,插入数据时的缓冲。专用mysql服务器设置的大小: 操作系统内存的70%-80%最佳。由于我们的服务器还部署有其他应用,估此处设置为8G。此外,这个参数是非动态的,要修改这个值,需要重启mysqld服务。设置的过大,会导致system的swap空间被占用,导致操作系统变慢,从而减低sql查询的效率。
innodb_additional_mem_pool_size = 16M //用来存放Innodb的内部目录,这个值不用分配太大,系统可以自动调。不用设置太高。通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在error log有中显示的。此处我们设置为16M。
innodb_log_buffer_size = 8M //InnoDB的写操作,将数据写入到内存中的日志缓存中,由于InnoDB在事务提交前,并不将改变的日志写入到磁盘中,因此在大事务中,可以减轻磁盘I/O的压力。通常情况下,如果不是写入大量的超大二进制数据(a lot of huge blobs),4MB-8MB已经足够了。此处我们设置为8M。
max_connections = 800 //最大连接数,根据同时在线人数设置一个比较综合的数字,最大不超过16384。此处我们根据系统使用量综合评估,设置为800。
sort_buffer_size = 2M //是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB,这里我们设置为2M。
read_buffer_size = 2M //(数据文件存储顺序)是MySQL读入缓冲区的大小,将对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区,read_buffer_size变量控制这一缓冲区的大小,如果对表的顺序扫描非常频繁,并你认为频繁扫描进行的太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能,read_buffer_size变量控制这一提高表的顺序扫描的效率 数据文件顺序。此处我们设置得比默认值大一点,为2M。
read_rnd_buffer_size = 250K //是MySQL的随机读缓冲区大小,当按任意顺序读取行时(列如按照排序顺序)将分配一个随机读取缓冲区,进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要大量数据可适当的调整该值,但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值,以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。此处设置为跟默认值相似,250KB。
join_buffer_size = 250K //多表参与join操作时的分配缓存,适当分配,降低内存消耗,此处我们设置为250KB。
thread_stack = 256K //每个连接线程被创建时,MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时,需要给它分配一定大小的内存堆栈空间,以便存放客户端的请求的Query及自身的各种状态和处理信息。Thread Cache 命中率:Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率处于90%才算正常配置,当出现“mysql-debug: Thread stack overrun”的错误提示的时候需要增加该值。此处我们配置为256K。
binlog_cache_size = 250K // 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。作用是提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。前者建议是1048576 –1M;后者建议是: 2097152 – 4194304 即 2–4M。此处我们根据系统实际,配置为250KB。
table_definition_cache = 400 // 开发模式:从1400设置为400,内存从150M降到90M;服务模式:从1400设置为400,内存从324M降到227M

总结:默认情况下按默认配置,很少能出现内存不足问题(毕竟现在的数据库产品,管理内存还是挺成熟的),因为按照默认的配置,占内存总计576.2MB,只是在使用过程中,很多人配置了不合理的参数(为了追求高性能,没有平衡好配置和硬件的关系)或是运行实例异常,导致内存爆了。

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页