Production environment MySQL memory overflow restart simple analysis

Ideas

1. View the database log

2. View slow query

3. View the system log

4. View monitoring

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-- abnormal restart

[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 is 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.

by looking at the Open-falcon monitoring, the memory usage 100% is discovered when the database restarts. Mysqld_safe has a security protection mechanism, for the exception of the shutdown database,themysqld_safe process can be mysqld Process to pull up. Our situation was that the database was pulled up, but the main library parameter was set to read-only and affected some of the business. After adjusting the parameters, the read-only parameter of the main library is set to Read_only=off. Of course the culprit is caused by slow queries. Find slow query, submitted to development colleagues, after modifying the SQL, stable, but the memory is really too tense, can not hold again, ready to expand a little memory, the cost can not be saved.

This article is from the "ROIDBA" blog, make sure to keep this source http://roidba.blog.51cto.com/12318731/1890527

Production environment MySQL memory overflow restart simple analysis