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。如果不是需要怎么也样规避这个问题再次发生。