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.