生產環境mysql記憶體溢出重啟簡單分析

標籤：生產環境mysql記憶體溢出重啟

思路

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之後，穩定下來，不過記憶體也確實太緊張了，沒法再挺著了，準備擴一點記憶體，這個費用沒法再節省了。

本文出自 “roidba” 部落格，請務必保留此出處http://roidba.blog.51cto.com/12318731/1890527

生產環境mysql記憶體溢出重啟簡單分析