暂无图片
暂无图片
14
暂无图片
暂无图片
暂无图片

Mysql性能专题分析

原创 黄超 2022-06-21
2574

一、CPU使用率过高

1.问题排查

1)通过TOP命令确认是否是mysqld进程占用过高cpu

#top
Tasks: 102 total,   1 running, 101 sleeping,   0 stopped,   0 zombie
%Cpu(s):  90.0 us,  9.0 sy,  0.0 ni, 89.4 id,  0.5 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1867048 total,   397904 free,  1219676 used,   249468 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.   462424 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                              
  1821 mysql     20   0 12.327g 1.075g  12004 S  375 60.4   0:13.96 mysqld                                                                                               
    32 root      39  19       0      0      0 S   8.6  0.0   0:00.65 khugepaged                                                                                           
    12 root      rt   0       0      0      0 S   0.7  0.0   0:00.02 migration/1                                                                                          
    46 root      20   0       0      0      0 S   0.7  0.0   0:00.16 kworker/0:2                                                                                          
     1 root      20   0  193700   6820   4044 S   0.0  0.4   0:00.91 systemd                                                                                              
     2 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kthreadd

2)查看CPU飙高的mysql线程,top -H -p <mysqld进程id>

#top -H -p 1821
top - 21:04:24 up 10 min,  1 user,  load average: 0.00, 0.02, 0.04
Threads:  31 total,   0 running,  31 sleeping,   0 stopped,   0 zombie
%Cpu(s):  90.0 us,  0.2 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1867048 total,   390212 free,  1227176 used,   249660 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.   454824 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                               
  1942 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.26 mysqld                                                                                                
  1821 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:13.02 mysqld                                                                                                
  1924 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.00 mysqld                                                                                                
  1927 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.01 mysqld                                                                                                
  1928 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.02 mysqld                                                                                                
  1929 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.01 mysqld                                                                                                
  1930 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.02 mysqld

3)根据具体PID,定位问题SQL

mysql>SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;

2.问题处理

CPU使用率过高最常见场景的处理方法

1)SQL执行成本高,SQL运行时间长,大事务

show processlist:命令的输出结果显示了有哪些线程在运行,可以帮助识别出有问题的SQL语句
实时运行中的SQL里面超过10秒的按时间倒序列出,定位运行时间长SQL:

mysql>select * from information_schema.processlist where command != 'Sleep'  and time >10 order by time desc;

这些运行时间长的SQL需要优化,比如适当建立某字段的索引。

线上环境,紧急时候,可以 kill 会话:
通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件,然后执行临时文件中生成的指令

mysql>select concat('KILL ',id,';') from information_schema.processlist where time>10  and db is not null and command!='sleep' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql>source  /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

2)跑批任务,并发高

联系应用人员,看这些会话都是在干啥的,问他们能不能杀或者停掉任务。
杀掉指定用户运行的连接,例如这里为usera

#mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "usera")print $2}'|xargs -n 1 mysqladmin -uroot -p kill

3.建议与总结

1)升级实例规格,增加 CPU 资源
2)跑批任务,建议在业务低谷定时执行,以免影响线上业务
3)show processlist实时查看执行时间过长的SQL,优化这些SQL
4)打开慢查询日志,针对慢SQL ,explain分析执行计划,优化改进
5)定期分析表,使用optimize table,整理碎片,回收空间
6)开启查询缓存或者使用缓存产品,减轻实例压力
7)考虑读写分离,增加只读库
8)定期归档历史数据、采用分库分表或者分区的方式减小查询访问的数据量

二、内存占用过大

1.问题排查

1)查看系统内存

#free -m
total        used        free      shared  buff/cache   available
Mem:      49152     5898         242           8         250         310
Swap:          2047           0        2047

2)查看mysqld进程占用系统内存情况

#top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3018 mysql 20 0 44.2g 41g 4232 S 0.0 86.8 981:52.36 mysqld

3)计算mysql当前配置最大的内存消耗

mysql>SELECT (
@@key_buffer_size +
@@table_open_cache +
@@innodb_buffer_pool_size +
@@innodb_log_buffer_size +
@@max_connections * (  
@@read_buffer_size +
@@read_rnd_buffer_size +
@@sort_buffer_size +
@@join_buffer_size +
@@binlog_cache_size +
@@tmp_table_size +
@@thread_stack ) ) /
(1024 * 1024 * 1024) AS MAX_MEMORY_GB;

sys 模式查询通过 current_alloc() 代码区域聚合当前分配的内存:

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;

performance_schema下的几个表跟内存相关:
memory_summary_by_account_by_event_name
memory_summary_by_host_by_event_name
memory_summary_by_thread_by_event_name
memory_summary_by_user_by_event_name
memory_summary_global_by_event_name

2.问题处理

内存占用过高最常见场景的处理方法

1)参数配置有误

mysql的配置参数中,有的是配置全局的内存使用大小,有的是配置单个线程的内存大小。
全局内存:
innodb_buffer_pool_size:缓冲池大小
innodb_log_buffer_size:重做日志缓存大小
key_buffer_size:索引缓冲区的大小,只用于MyISAM引擎
线程内存:
read_buffer_size:顺序读缓存大小
read_rnd_buffer_size:随机读缓存大小
sort_buffer_size:排序缓存大小
join_buffer_size:join联接缓存大小
tmp_table_size:临时表缓存大小
max_connections:最大连接数

A)innodb_buffer_pool_size设置过大

innodb_buffer_pool_size设置过大,会占用太多内存,修改innodb_buffer_pool_size的值,可以减少内存占用。
例如,系统8G 内存,设置mysql缓冲池为4G

mysql> SET GLOBAL innodb_buffer_pool_size=4*1024*1024*1024;
    Query OK, 0 rows affected (0.10 sec)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4294967296 |
+---------------------------+
1 row in set (0.00 sec)

在线动态修改生效,重启mysql 后修改会丢失,若是想修改的永久有效,需要编辑my.cnf

B)线程内存参数设置过大

线程内存参数是单个线程所占用大小,乘以连接数,得到所占用内存。并发高,连接多,会占用非常大的内存。
例如,sort_buffer_size是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB。

mysql> SET GLOBAL sort_buffer_size=2*1024*1024;

2)table cache相关的内存占用大

业务低峰时清空所有表的缓存或者降低table_open_cache的值

mysql >flush tables;

3)存在Session过多

如果空闲Session过多,可以kill掉

mysql> select concat('kill ', id, ';') from information_schema.processlist 
where command = 'Sleep' into outfile '/tmp/a.txt';
Query OK rows, 2 affected (0.00 sec)
mysql>source  /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

3.建议与总结

1)增大内存,合理分配内存
2)减少创建临时表、sort或join等操作
3)释放操作系统内存 echo 1 >/proc/sys/vm/drop_caches
4)重启mysql(生产环境谨慎操作),释放内存。若是实例是双机环境下的主库,需要先切换为从库

三、IO过高

1.问题排查

1)用iostat监测到的IO利用率过高

#iostat -k -d -x 1 10
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00   83.00     0.00   650.50    15.67     2.55   30.66    0.00   30.66  12.02  99.80
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00   83.00     0.00   636.50    15.34     2.55   30.66    0.00   30.66  12.02  99.80

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00   88.00     0.00   684.50    15.56     2.60   29.42    0.00   29.42  11.35  99.90
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00   87.00     0.00   677.00    15.56     2.60   29.76    0.00   29.76  11.49 100.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00   87.00     0.00   687.50    15.80     2.48   28.77    0.00   28.77  11.51 100.10
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00   88.00     0.00   699.00    15.89     2.48   28.48    0.00   28.48  11.36 100.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00   88.00     0.00   756.50    17.19     2.48   28.22    0.00   28.22  11.35  99.90
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00   86.00     0.00   740.00    17.21     2.48   28.87    0.00   28.87  11.62  99.90 

2)通过iotop工具可以看到当前IO消耗最高的mysql线程

 #iotop
Total DISK READ :       0.00 B/s | Total DISK WRITE :     683.85 K/s
Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     721.19 K/s
TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                        
 7061 be/4 mysql       0.00 B/s  153.28 K/s  0.00 % 72.27 % mysqld --defaults-file=/etc/mysq~mysql/3306 mysql.sock --port=3306

2.问题处理

IO过高最常见场景的处理方法

1)Mysql的各种日志刷盘频繁

可以修改以下mysql参数
innodb_flush_log_at_trx_commit=2(值0,性能最好;主库为了数据一致性,一般值为1,速度最慢)
sync_binlog=1000((值0,性能最好;值1,最慢)
例如,修改innodb_flush_log_at_trx_commit值

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ' innodb_flush_log_at_trx_commit ';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit     | 2     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set global sync_binlog=1000;
Query OK, 0 rows affected (0.00 sec)

2)数据批量更新插入写盘

修改mysql参数:

innodb_write_io_threads=8(根据机器核数修改)
innodb_io_capacity=2000 (ssd盘改大)

mysql> set global  innodb_write_io_threads =8;
mysql> set global  innodb_io_capacity =2000;

3)临时表刷盘

一些有问题的sql语句生成了较大的临时表,内存放不下,于是全部刷到磁盘,导致IO飙升,可以增大tmp_table_size值

mysql> set global  tmp_table_size=512*1024*1024; 

3.建议与总结

1)选用性能好的SSD磁盘
2) 增大内存,缓存足够大,减少物理IO
3)根据数据重要程度,选择适合的日志刷盘方式。
4)单条insert语句优化为批量insert语句,减少事务commit次数
5)优化SQL,减少产生临时表,优化索引。

最后修改时间:2022-06-21 23:06:07
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论