一、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，減少產生臨時表，優化索引。
