Mysql restarted automatically because of Out of memory

[Manoj Chauhan]

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, tom 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: 3*4kB 8*8kB 2*16kB 2*32kB 0*64kB 0*128kB 0*256kB 0*512kB 1*1024kB 1*2048kB 2*4096kB = 11436kB
Node 0 HighMem: empty
Node 1 DMA: 3*4kB 1*8kB 4*16kB 4*32kB 5*64kB 2*128kB 1*256kB 1*512kB 1*1024kB 0*2048kB 2*4096kB = 10772kB
Node 1 DMA32: 16*4kB 6*8kB 0*16kB 0*32kB 1*64kB 1*128kB 1*256kB 0*512kB 0*1024kB 0*2048kB 13*4096kB = 53808kB
Node 1 Normal: 36*4kB 3*8kB 4*16kB 3*32kB 0*64kB 0*128kB 0*256kB 1*512kB 0*1024kB 0*2048kB 2*4096kB = 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:
 [<ffffffff8000f576>] __alloc_pages+0x2ef/0x308
 [<ffffffff80012ed7>] __do_page_cache_readahead+0x96/0x179
 [<ffffffff8001386c>] filemap_nopage+0x14c/0x360
 [<ffffffff8000898c>] __handle_mm_fault+0x1fa/0xfaa
 [<ffffffff8000ce24>] do_sync_read+0xc7/0x104
 [<ffffffff80067b55>] do_page_fault+0x4cb/0x874
 [<ffffffff8003390e>] lock_sock+0xa7/0xb2
 [<ffffffff80065b29>] _spin_lock_bh+0x9/0x14
 [<ffffffff800310b1>] release_sock+0x13/0xaa
 [<ffffffff802298ef>] sock_setsockopt+0x4db/0x4ed
 [<ffffffff8005ede9>] error_exit+0x0/0x84
 

Thanks
Manoj 

[Tim Chadwick]

Hi Manoj,

It looks like the OOM got your mysqld. My experience suggests that at
5% you are very likely threatened by the OOM killer, (if swap hasn't
already brought down your process).

With a buffer pool of 28GB, I think you would be right on the edge of
safety. Consider some other portions of mysqld that will require
memory, such as the dictionary, or the sort buffer. If you have any
large operations going on you could be pushing on the limits of that
5% rule. To be safe, I typically consider all of the components that
will in memory at any one time and then give the kernel a full 10% of
space on top of that. Posting the section "BUFFER POOL AND MEMORY"
contents the from the command: SHOW ENGINE INNODB STATUS, will help
determine what is specifically being used in memory. Also, posting
the relavent my.cnf configs would help diagnose. See the pt-summary
or pt-mysql-summary tool for more information on getting some of these
infos easily.

In summary, I'd strongly consider making your buffer pool something
more like 25GB in order to get that allocated memory consistently
below 30GB. Also, do not forget about the swappiness kernel value. I
typically set it to 0, but use your own judgement in that regard
depending on the sort of activity that might be on the server.

~tim

> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discuss...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.

[Ernie Souhrada]

Hi Manoj--

There does not have to be a query running in order for MySQL to be
holding a lot of memory. You've allocated 28GB to your buffer pool;
that memory is going to be sucked up by MySQL as soon as the server
starts, even if it's empty. Add in the rest of the memory that MySQL
uses for things besides the buffer pool, and it's not difficult to see
how you could be at 97.3% of your available memory even under no load.

The kernel OOM-killer typically looks for the process that has the
largest amount of memory in use - and in your case, that's MySQL, so
it's no surprise that it killed it when your system ran out of resources.

I don't know what else you have running on this server or what the rest
of your MySQL configuration settings are, but as a start, I would
suggest that you turn down your buffer pool allocation. Something in
the 24G-26G range would probably be a good starting point.

Hope that helps--
e.

On 06/20/2012 03:19 AM, Manoj Chauhan wrote:
> 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, tom 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*

> --
> You received this message because you are subscribed to the Google
> Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discuss...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.

--
Ernie Souhrada, CMDBA, RHCE, CCNA
Senior Consultant, Percona, Inc. [ http://www.percona.com ]
email: ernest....@percona.com
phone: +1-888-401-3401 x543 [ US/Arizona : GMT-7 ]
skype: ravyn440

[Manoj Chauhan]

Hi Tim,

Thanks for swift response and valuable information. Please find below my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

server-id=1

sync_binlog=0
innodb_lock_wait_timeout = 600
long_query_time = 10
log-slow-queries=/logs/log-slow-queries.log
innodb_purge_threads=1
innodb_buffer_pool_size=28G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_log_buffer_size=64M
innodb_log_file_size=1000M
innodb_thread_concurrency=16
innodb_buffer_pool_instances=8
innodb_flush_method=O_DIRECT
innodb_write_io_threads=8
innodb_read_io_threads=8
#innodb_io_capacity=500
max_connections=1000
skip-name-resolve
table_cache=10000
thread_cache_size=100
log-bin=/logs/mysql-bin
relay-log=/logs/relay-bin
log_queries_not_using_indexes=1

tmp_table_size=32M
max_heap_table_size=32M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=2
innodb_additional_mem_pool_size=64M
sort_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=1M
thread_cache_size=512
query_cache_limit=64M
query_cache_size=128M
join_buffer_size=1M
tmpdir=/dev/shm
innodb_io_capacity=800
#innodb_use_native_aio=FALSE

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

max_allowed_packet=64M

[mysqldump]
quick
max_allowed_packet=128M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[client]
#default-character-set=utf8

Thanks
Manoj

[Tim Chadwick]

Manoj,

Thanks for the my.cnf. It looks mature and there is nothing glaring
about it that I would obviously point towards something out of scope
for the memory on the box. Sometimes removing the query cache can
help identify when there is something performing poorly, but hidden
due to repeated identical requests. The max_allowed_packet is a
larger than I usually set but perhaps that's indicative of your data
load.

With that writ, your initial top still illustrates 30g RES memory
allocation, so while there may not be anything from the my.cnf which
is obviously causing the problem: your mysqld is wavering quite close
to that critical threshold for a Linux kernel and tuning down the
buffer pool by a GB or 2 is a good idea. Note that even if it is not
the actual process which tips the kernel OOM threshold, because it is
the largest PID it will always get prioritized as the process to kill.

~tim

[Bryan O'Neal]

While I am not experienced with oom death and percona server, I do deal with percona MySQL and memory issues regularly. Percaon has a number of ways you can cap other memory allocations that you may look into, as the buffer pool is just one of the uses MySQL has for ram. And while it greatly depends on your set up, you usually have some key suspects, like dictionary cache. And while I understand running your physical ram at 90-95% for normal operation, please do not set your bufferpool to nearly 90%. I personally suggest total ram, any allocated to a ramdisk (Always cap your tmpfs) and then 80% of that should be your buffer pool. Up to half of which will be allocated just to inserts by default. Then drop a generous swap partition (10-20% of total ram if you can) so that you have breathing room to fix things if you find you have over allocated. If you do that and you are still having problems try setting up pt-stalk and some kind of multi peramitor trend system (Zabbix, graphite, ganglia, whatever) and try to get a more detailed view of what is going on before your box tips over.

On Thu, Jun 6, 2013 at 2:29 PM, Rafael G <raga...@gmail.com> wrote:

Hi,

I'm having a similar issue. I'm running MySQL-server-5.5.28_wsrep_23.7-1.rhel5.x86_64, percona-xtrabackup-2.1.3-608.rhel6.x86_64 using xtrabackup as the SST method. I'm running CentOS 6.4 and my VM has 6.5GB of memory (I started from 4GB) and every once in a while I hit the OOM issue. I have 2662MB allocated to the buffer pool, and disabled the query cache. I have a TON of tables using the setup of 1 file per table - 20951 tables. I have attached the /etc/my.cnf file we use. Maybe I have a parameter that it's too high? I set the open-files-limit and relates entries that way since I couldn't run backups until I increased those values (along with the kernel settings for the user for open files). Funny thing is that when I run tuning tools, like mysqltuner.pl, I can trigger the OOM a lot faster (it seems to run some queries to obtain table data which results in a lot of memory being used, while it takes a long time to get past that section of the analysis.

Thanks in advance!

Rafael

>> > percona-discussion+unsub...@googlegroups.com.

>> > For more options, visit this group at
>> > http://groups.google.com/group/percona-discussion?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Percona Discussion" group.

>> To post to this group, send email to percona-d...@googlegroups.com.

>> To unsubscribe from this group, send email to

>> percona-discussion+unsub...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/percona-discussion?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.

> To post to this group, send email to percona-d...@googlegroups.com.

> To unsubscribe from this group, send email to

> percona-discussion+unsub...@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.

To post to this group, send email to percona-d...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.