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
#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(生产环境谨慎操作)，释放内存。若是实例是双机环境下的主库，需要先切换为从库
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，减少产生临时表，优化索引。