生产环境mysql内存溢出重启简单分析

876543e

思路

1. 查看数据库日志

2. 查看慢查询

3. 查看系统日志

4. 查看监控

2017-01-03 03:03:48 0 170103 03:03:45 mysqld_safe Number of processes running now: 0

170103 03:03:45 mysqld_safe mysqld restarted   --异常重启

[Note] /usr/sbin/mysqld (mysqld 5.6.25-log) starting as process 104767 ...

2017-01-03 03:03:48 7f9bcd3f4720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the opt

ion innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2017-01-03 03:03:48 104767 [Note] InnoDB: Using atomics to ref count buffer pool pages

2017-01-03 03:03:48 104767 [Note] InnoDB: The InnoDB memory heap is disabled

2017-01-03 03:03:48 104767 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-01-03 03:03:48 104767 [Note] InnoDB: Memory barrier is not used

2017-01-03 03:03:48 104767 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-01-03 03:03:48 104767 [Note] InnoDB: Using Linux native AIO

2017-01-03 03:03:48 104767 [Note] InnoDB: Using CPU crc32 instructions

2017-01-03 03:03:48 104767 [Note] InnoDB: Initializing buffer pool, size = 4.0G

2017-01-03 03:03:48 104767 [Note] InnoDB: Completed initialization of buffer pool

2017-01-03 03:03:48 104767 [Note] InnoDB: Highest supported file format is Barracuda.

2017-01-03 03:03:48 104767 [Note] InnoDB: Log scan progressed past the checkpoint lsn 3021323914038

2017-01-03 03:03:48 104767 [Note] InnoDB: Database was not shutdown normally!

2017-01-03 03:03:48 104767 [Note] InnoDB: Starting crash recovery.

通过查看open-falcon 监控，发现数据库重启的时间内存使用率100%。mysqld_safe具有安全保护机制，对于异常的关闭数据库，mysqld_safe 进程可以把mysqld 进程拉起来。我们当时的情况是，数据库被拉起来了，但是主库参数设置的是只读，影响了部分业务。后续调整了参数，把主库的只读参数设置为read_only=off。当然罪魁祸首是因为慢查询引起的。找到慢查询，提交给开发同事，修改sql之后，稳定下来，不过内存也确实太紧张了，没法再挺着了，准备扩一点内存，这个费用没法再节省了。