Mysql restarted automatically because of Out of memory

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

We have Percona Mysql Server version: 5.5.16 with Master - Slave replication. Mysql on Master server got restarted automatically, we checked the Kernel and system logs and found that it was restarted because of Out of memory. After restart, top command shows that 97.3% memory is holding my Mysql even there is no query running.

Total Memory is 32G
Memory assigned to innodb buffer pool = 28G

As The Linux kernel has a functionality called Out Of Memory Killer (or OOM Killer) responsible for dealing with memory exhaustion. If system reaches a point where it may soon run out of all memory, OOM Killer looks for a process it could kill and ends its life.

Please suggest??

top output
###################
top - 03:05:16 up 39 days, 16:56, 3 users, load average: 0.01, 0.05, 0.07
Tasks: 277 total, 1 running, 276 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.0%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32949816k total, 32613172k used, 336644k free, 83848k buffers
Swap: 524280k total, 267588k used, 256692k free, 295836k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21988 mysql 15 0 31.0g 30g 4540 S 1.7 97.3 362:58.01 mysqld
5318 root 15 0 12872 1260 832 R 0.7 0.0 0:00.02 top
23532 gsc 18 0 12872 1196 764 S 0.3 0.0 15:43.64 top
1 root 15 0 10348 84 52 S 0.0 0.0 4:32.92 init

[root@avl-db10 logs]# free -m
total used free shared buffers cached
Mem: 32177 31850 326 0 84 289
-/+ buffers/cache: 31476 700
Swap: 511 261 250

Kernel Logs
Node 1 HighMem per-cpu: empty
Free pages: 85048kB (0kB HighMem)
Active:6438386 inactive:1742978 dirty:0 writeback:0 unstable:0 free:21262 slab:7758 mapped-file:984 mapped-anon:7948245 pagetables:16991
Node 0 DMA free:0kB min:0kB low:0kB high:0kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve: 0 0 16160 16160
Node 0 DMA32 free:0kB min:0kB low:0kB high:0kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve: 0 0 16160 16160
Node 0 Normal free:11436kB min:11508kB low:14384kB high:17260kB active:11474648kB inactive:4991256kB present:16547840kB pages_scanned:28277512 all_unreclaimable? yes
lowmem_reserve: 0 0 0 0
Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve: 0 0 0 0
Node 1 DMA free:10772kB min:4kB low:4kB high:4kB active:0kB inactive:0kB present:10388kB pages_scanned:0 all_unreclaimable? yes
lowmem_reserve: 0 3243 16121 16121
Node 1 DMA32 free:53808kB min:2308kB low:2884kB high:3460kB active:1540788kB inactive:1656616kB present:3321540kB pages_scanned:13029052 all_unreclaimable? yes
lowmem_reserve: 0 0 12877 12877
Node 1 Normal free:9032kB min:9172kB low:11464kB high:13756kB active:12738108kB inactive:324096kB present:13186560kB pages_scanned:32260117 all_unreclaimable? yes
lowmem_reserve: 0 0 0 0
Node 1 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve: 0 0 0 0
Node 0 DMA: empty
Node 0 DMA32: empty
Node 0 Normal: 34kB 88kB 216kB 232kB 064kB 0128kB 0256kB 0512kB 11024kB 12048kB 24096kB = 11436kB
Node 0 HighMem: empty
Node 1 DMA: 34kB 18kB 416kB 432kB 564kB 2128kB 1256kB 1512kB 11024kB 02048kB 24096kB = 10772kB
Node 1 DMA32: 164kB 68kB 016kB 032kB 164kB 1128kB 1256kB 0512kB 01024kB 02048kB 134096kB = 53808kB
Node 1 Normal: 364kB 38kB 416kB 332kB 064kB 0128kB 0256kB 1512kB 01024kB 02048kB 24096kB = 9032kB
Node 1 HighMem: empty
234104 pagecache pages
Swap cache: add 33789697, delete 33795868, find 10561663/14701131, race 0+185
Free swap = 0kB
Total swap = 524280kB
Free swap: 0kB
8585216 pages of RAM
347762 reserved pages
4453 pages shared
98 pages swap cached
Out of memory: Killed process 12651, UID 101, (mysqld).
mysqld: page allocation failure. order:0, mode:0x201d2

Call Trace:
[] __alloc_pages+0x2ef/0x308
[] __do_page_cache_readahead+0x96/0x179
[] filemap_nopage+0x14c/0x360
[] __handle_mm_fault+0x1fa/0xfaa
[] do_sync_read+0xc7/0x104
[] do_page_fault+0x4cb/0x874
[] lock_sock+0xa7/0xb2
[] _spin_lock_bh+0x9/0x14
[] release_sock+0x13/0xaa
[] sock_setsockopt+0x4db/0x4ed
[] error_exit+0x0/0x84

Thanks
Manoj

2

You can adjust the way the OOM killer handles the MySQL process, however the real issue is that you are giving MySQL too much memory.

Based on the information you gave, you are giving MySQL 87.5% of your total memory just for the InnoDB buffer pool. On a dedicated MySQL server, it is normally recommended that you set the InnoDB buffer pool at 60-80% of your total memory at most. MySQL uses memory for other things besides the InnoDB buffer pool, so if you set that too high you will max yourself out pretty quickly.

So before messing with the OOM killer, I would first address your memory usage which is the root of the problem.