一、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，减少产生临时表，优化索引。
