Bug #79850 Overcommitted memory of innodb buffer pool size too high to be real
Submitted: 5 Jan 2016 13:24 Modified: 12 Feb 2016 6:46
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6.27, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[5 Jan 2016 13:24] Shahriyar Rzayev
Description:
Hi dear experts,
This report intended to clarify situation where i can't find any valuable information related to buffer pool size. All articles are about calculating "gold" size for buffer pool. It is great to have 80% of physical memory as buffer pool. OK.

So the general description is -> "I can start MySQL with 300GB buffer pool size in my 16G laptop"

Config:

innodb_buffer_pool_size=300G
innodb_buffer_pool_instances=64

Started as:

2016-01-05 15:23:58 16211 [Note] InnoDB: Initializing buffer pool, size = 300.0G
2016-01-05 15:24:31 16211 [Note] InnoDB: Completed initialization of buffer pool

I believe that i can give much more pool size if I could increase buffer pool instances from maximum 64. For eg, 300G with 8 buffer pool instances will fail.

With 350G:

2016-01-05 13:04:47 13232 [Note] InnoDB: Initializing buffer pool, size = 350.0G
InnoDB: mmap(6009651200 bytes) failed; errno 22
2016-01-05 13:05:27 13232 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-01-05 13:05:27 13232 [ERROR] Plugin 'InnoDB' init function returned error.
2016-01-05 13:05:27 13232 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-01-05 13:05:27 13232 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-01-05 13:05:27 13232 [ERROR] Aborting

In some conditions OOM will kill mysqld process:

[21544.882217] Out of memory: Kill process 17791 (mysqld) score 858 or sacrifice child
[21544.882220] Killed process 17791 (mysqld) total-vm:343077552kB, anon-rss:14787388kB, file-rss:0kB
[21544.881582] Call Trace:
[21544.881722]  [<ffffffffc0700b12>] i915_gem_execbuffer_reserve_vma.isra.16+0x62/0xf0 [i915]
[21544.881733]  [<ffffffffc0700e61>] i915_gem_execbuffer_reserve+0x2c1/0x300 [i915]
[21544.881744]  [<ffffffffc0701e37>] i915_gem_do_execbuffer.isra.22+0x4e7/0xd10 
[21544.881791]  [<ffffffffc04a9a4c>] drm_ioctl+0x1ec/0x660 [drm]
[21544.881797]  [<ffffffff8102144b>] ? __restore_xstate_sig+0x8b/0x670
[21544.881800]  [<ffffffff811ffd28>] do_vfs_ioctl+0x2f8/0x510
[21544.881802]  [<ffffffff810818f2>] ? __set_task_blocked+0x32/0x70
[21544.881805]  [<ffffffff811fffc1>] SyS_ioctl+0x81/0xa0
[21544.881809]  [<ffffffff817b770d>] system_call_fastpath+0x16/0x1b

It is clear that Virtual Memory(mmap() usage) is an OS topic, but how about just not to allow over-allocating buffer pool size?

How to repeat:
Ubuntu 14.04 with 16G available ram: 
Linux percona-XPS-15 3.19.0-42-generic #48~14.04.1-Ubuntu SMP Fri Dec 18 10:24:49 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

sh@percona-XPS-15:~$ free -m
             total       used       free     shared    buffers     cached
Mem:         15951       2921      13029        515         37       1074
-/+ buffers/cache:       1809      14141
Swap:        16283       1136      15147

Try to increase buffer pool instance and size to be as large as possible. 

Suggested fix:
Calculating buffer pool size against available physical memory.
[8 Jan 2016 9:00] Shahriyar Rzayev
Also see:

http://bugs.mysql.com/bug.php?id=79880
[12 Jan 2016 16:22] Sinisa Milivojevic
This is a behavior in extreme circumstances, that should be changed.

Hence, this makes it a fully verified feature request.
[12 Feb 2016 6:46] Shahriyar Rzayev
Added affected version 5.7.10