MySQL故障排查汇总-摩杜云开发者社区

1.MySQL因为内存升高、CPU升高、磁盘空间不足,数据文件损坏导致无响应crash的情况

1.1 内存泄漏排查方法

1)操作系统的内存检查
#freee -m
如果OOM还未发生，MySQL还在不断的需求内存时，可以看到此时内存已经不足，并且可能已经使用了swap空间。
如果OOM已经发生，MySQL已经crash，并且被MySQLd_safe重新拉起，此时内存使用量应该风平浪静。
使用top监控当前内存的使用：
#top

Mem:  132031556k total, 131418864k used,   612692k free,   212104k buffers
Swap: 16777212k total,        0k used, 16777212k free, 14648144k cache
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14920 MySQL     20   0  125g 109g 6164 S  6.6 88.0  27357:08 MySQLd

2)查SQL：通过参数分配给MySQL的内存

mysql>select (@@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + 
@@innodb_log_buffer_size + (select count(host) 
from information_schema.processlist)*(
@@read_rnd_buffer_size+ @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size+ @@thread_stack)
) / (1024*1024) AS MAX_MEMORY_MB;

#MySQL8.0以上版本应除掉query_cache_size:
mysql> select ( @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + 
(select count(host) from information_schema.processlist)*
(@@read_rnd_buffer_size+ @@sort_buffer_size + 
@@join_buffer_size + 
@@binlog_cache_size+ 
@@thread_stack)) / (1024*1024) as MAX_MEMORY_MB;

如果分配的内存本身已经超越了系统内存是很容易造成OOM的，此时需要查出哪些参数设置比较大，适当降低内存分配。

3)查MySQL数据文件打开的数量
innodb_buffer_pool在MySQL中占有很大的内存部分，调小innodb_buffer_pool_size可以降低OOM问题。innodb_buffer_pool_size为系统内存的50%～60%。
如果这些参数设置都很合理，还是出现了OOM的问题，怀疑是MySQL运行时候需要的内存无法被满足，可能的原因是MySQL由于MVCC特性，虽然减少了锁的争用，但是保存的表镜像太多（也会出现大量的proccesslist中长时间连接的线程作为佐证）。
ps -ef|grep MySQL
//15746是ps -ef|grep MySQL查出的MySQL进程号

lsof -p 15746 |grep ibd|wc -l

此时如果发现lsof -p 15746 |grep ibd|wc -l查询的值较大：
应当检查MySQL和文件系统对于打开文件数量的限制：

#ulimit -a
open files                      (-n) 6553


MySQL> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 256   |
+------------------+-------+
1 row in set (0.00 sec)

查参数table_open_cache，当打开一个表后会缓存文件描述符

mysql> show global variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4096  |
1 row in set (0.00 sec)


mysql> show global status like '%open%tables%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_tables            | 276   |
| Opened_tables          | 307   |
+------------------------+-------+
4 rows in set (0.01 sec)

当缓存中的值open_tables 临近到了 table_open_cache 值的时候，说明表缓存池快满了，但Opened_tables 还在一直增长，这说明还有很多未被缓存的表。
用SHOW OPEN TABLES FROM database_name命令，可以查看table_open_cache中缓存的表。

mysql> show open tables from employees;
+-----------+--------------+--------+-------------+
| Database  | Table        | In_use | Name_locked |
+-----------+--------------+--------+-------------+
| employees | departments  |      0 |           0 |
| employees | salaries     |      0 |           0 |
| employees | dept_manager |      0 |           0 |
| employees | dept_emp     |      0 |           0 |
| employees | titles       |      0 |           0 |
| employees | employees    |      0 |           0 |
+-----------+--------------+--------+-------------+
6 rows in set (0.02 sec)

In_use显示当前正在使用此表的线程数，如果大于0也意味着此表被锁。
Name_locked只适用于DROP和RENAME，在执行DROP或RENAME时，table_open_cache中的表文件描述符会被移除，所以不会看到除0以外的其他值。
一般在库表比较多的情况下（分库分表）很容易出现内存占用较大的情况。如果要解决根源，还是需要对库表进行拆分。
查看是否有除innodb引擎外的其他引擎在消耗内存：

MySQL>select * 
from information_schema.tables 
where engine!='innodb' 
and  engine!='performance_schema' 
and TABLE_SCHEMA !='information_schema';

通过以上排查能大体知道哪些占用内存较多，针对内存占用较多的地方再做具体优化。
原因分析:
1.MySQL分配的内存超越主机内存，参数设置不合理。
2.MySQL同时打开的文件数量过大，并性量过大。
3.MySQL中存在锁。
4.系统和MySQL打开文件的设置过大。
5.系统内存被其他进程消耗。

解决方法:
1.调整参数配置：innodb_buffer_pool_size为系统内存的50%～60%，调整table_open_cache。
2.处理存在锁问题的表：kill对应的会话，优化SQL。
3.增大内存。

1.2 CPU负载升高

执行计划成本高是常常导致CPU变高的原因，问题SQL往往有order by或group by语句或多表连接造成的性能问题，有很大的优化空间。另外一种情况是QPS很大，即使SQL已经质量足够好，但是由于负载很大，所以CPU变高，这种情况下优化空间较小，建议进行分库分表。
排查方法:
(1)top监控查看CPU高的进程：

#top

(2)MySQL中执行SHOW FULL PROCESSLIST

mysql> show full processlist;

(3)找到对应的SQL，进行优化。
当执行完show processlist后出现大量的语句，通常其状态出现sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result, Using filesort 都是sql有性能问题。

sending data表示：sql正在从表中查询数据，如果查询条件没有适当的索引，则会导致sql执行时间过长。
Copying to tmp table on disk：出现这种状态，通常情况下是由于临时结果集太大，超过了数据库规定的临时内存大小，需要拷贝临时结果集到磁盘上，这个时候需要用户对sql进行优化。
Sorting result, Using filesort：出现这种状态，表示sql正在执行排序操作，排序操作都会引起较多的cpu消耗，通常的优化方法会添加适当的索引来消除排序，或者缩小排序的结果集。

1.3 磁盘空间满

(1)Binlog太多的处理方法
方法一：
MySQL5.7及以下版本：

MySQL>  set global expire_logs_days=7;

MySQL 8.0及以上设置：

mysql>set global  binlog_expire_logs_seconds =  604800;

方法二：
手动清除binlog.000003之前的binlog：

mysql> purge binary logs to 'binlog.000003'

(2)临时表空间太大清理方法
1.监控临时表的使用状态：

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 7     |
| Created_tmp_tables      | 2     |


mysql>  SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
+----------+---------------+--------+------------+
| TABLE_ID | NAME          | N_COLS | SPACE      |
+----------+---------------+--------+------------+
|     1079 | #sql3d82_1f_6 |      5 | 4294501266 |

+----------+---------------+--------+------------+
1 row in set (0.00 sec)

2.有关临时表的的限制参数：

max_heap_table_size
这个参数主要针对用户创建的内存表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。如果超出阀值，则报错。ERROR 1114 (HY000): The table ‘xxx’ is full
tmp_table_size
对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数internal_tmp_disk_storage_engine指定。

1.4 数据文件损坏

排查方法：
现象一：
从表中选择数据是报错
Incorrect key file for table: ‘…’. Try to repair it

现象二：
在表中查询数据的时候无法返回完整的行或数据
Error: Table ‘p’ is marked as crashed and should be repaired

现象三：
打开表失败
Can’t open file: ‘×××.MYI’ (errno: 145)

现象四：
当MySQL发生数据文件损坏时，MySQL无法正常启动。

解决方法：
在MySQL中有参数innodb_force_recovery：
innodb_force_recovery可以设置为1-6，大的数字包含前面所有数字的影响。
(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
在MySQL的配置文件my.cnf中[MySQLd]下添加如下参数：
innodb_force_recovery =1
重新启动后，使用MySQLdump导出对应表的数据：

#mysqldump --single-transaction --flush-logs --master-data=2 database_name table_name >table_name.sql
mysql>drop table table_name;

将数据文件保存之后，修改参数innodb_force_recovery=0，并重新启动数据库。
重新创建表并导入数据。

mysql -u user -p < table_name.sql

2.排查方法

(1)确定服务器是否崩溃，检查主机是否关机或者重启过uptime命令。
(2)查看错误日志，或者借助监控或者服务器命令确认MySQL是否存活（命令 ps -ef｜grep MySQL）。如果服务器同时运行着MySQLd_safe守护进程的时候，MySQL会被自动拉起，错误日志也将包含MySQL重启的消息。
(3)查看日志中包含[ERROR]的信息，和对应MySQL发生故障的时间，将会得到MySQL究竟为什么发生故障的原因。这可以解决大多数的MySQL crash故障。
(4)如果得到了大致问题的方向并且确定了原因之后，应该去查看一下这个问题是否是bug造成的，如果是，是否能够通过数据库升级避免这个bug。如果不是需要怎么也样规避这个问题再次发生。