InnoDB: Fatal error: cannot allocate memory for the buffer pool

I'd appreciate assistance in helping me understand a few errors I see after mysqld crashes.

The MySQL log shows the following

180113  3:31:13 InnoDB: The InnoDB memory heap is disabled
180113  3:31:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180113  3:31:13 InnoDB: Compressed tables use zlib 1.2.7
180113  3:31:13 InnoDB: Using Linux native AIO
180113  3:31:13 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180113  3:31:13 InnoDB: Completed initialization of buffer pool
180113  3:31:13 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180113  3:31:13 [ERROR] Plugin 'InnoDB' init function returned error.
180113  3:31:13 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180113  3:31:13 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180113  3:31:13 [Note] Plugin 'FEEDBACK' is disabled.
180113  3:31:13 [ERROR] Unknown/unsupported storage engine: InnoDB
180113  3:31:13 [ERROR] Aborting


MySQL was setup with default my.cnf settings which allocates 128M to innoDB buffer pool. I've recently changed this to a larger size, but it's too early to tell if the new setting will have any affect. (I've increased the buffer pool size to 512M going against many suggestions to reduce it's size. My thinking is that the 128M buffer pool is inadequate to handle the demands of indexes etc, and that is why mysql is crashing in the first place. The reason mysql reports out of memory could just be the default error event for memory related issues.)

My my.cnf file currently looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_open_cache = 256
max_allowed_packet = 1M
key_buffer_size = 256M
innodb_buffer_pool_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d


My problem initially was in the the fact that my server is not reaching close to the memory limit before the crash as you can see this from this virtualmin system stats graph. Could anyone explain why mysql would be crashing and reporting out of memory but memory usage is nowhere near the servers limit? I'm assuming if apache were experiencing excessive load it would show in memory usage and CPU usage, but there is no CPU spike when the server crashes. As mentioned above, mysql might be throwing a default memory exception error when the buffer pool is too small to handle demand.

A side query in handling this issue is to allocate a swap file. In line with a response in this article, how do I allocate a system swap to mysql? A swap was suggested as another option to avoid mysqld crashing. I have checked in Webmin--> Disk and Network Filesystems and there are already 4 RAM/Swap Disk (tmpfs). Why would they not be assisting with the mysql memory issue? Do I need to do set up some specific configuration for a swap to help in my scenario?

I referenced the following articles in researching this problem:

https://www.digitalocean.com/community/questions/mysql-server-keeps-stop... https://stackoverflow.com/questions/12114746/mysqld-service-stops-once-a... https://askubuntu.com/questions/457923/why-did-installation-of-mysql-5-6... https://dba.stackexchange.com/questions/10496/mysql-said-innodb-initiali... https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

A previous issue I have had relating to mysql and reported on Virtualmin:

https://www.virtualmin.com/node/48585

Status: 
Active

Comments

Howdy -- thanks for contacting us!

Before we dig into those parameters, I'm interested in seeing the output of a few commands. Could you paste in what these commands show:

free -m
uptime
mount
dmesg | tail -30

Output of requested commands:

free -m

              total        used        free      shared  buff/cache   available
Mem:          11854        3132        4814        3284        3907        5011
Swap:             0           0           0

Uptime

07:15:56 up 23:18,  1 user,  load average: 0.07, 0.22, 0.24

mount

sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
proc on /proc type proc (rw,nosuid,nodev,noexec,relatime)
devtmpfs on /dev type devtmpfs (rw,nosuid,size=6058320k,nr_inodes=1514580,mode=755)
securityfs on /sys/kernel/security type securityfs (rw,nosuid,nodev,noexec,relatime)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)
tmpfs on /run type tmpfs (rw,nosuid,nodev,mode=755)
tmpfs on /sys/fs/cgroup type tmpfs (ro,nosuid,nodev,noexec,mode=755)
cgroup on /sys/fs/cgroup/systemd type cgroup (rw,nosuid,nodev,noexec,relatime,xattr,release_agent=/usr/lib/systemd/systemd-cgroups-agent,name=systemd)
pstore on /sys/fs/pstore type pstore (rw,nosuid,nodev,noexec,relatime)
cgroup on /sys/fs/cgroup/net_cls,net_prio type cgroup (rw,nosuid,nodev,noexec,relatime,net_prio,net_cls)
cgroup on /sys/fs/cgroup/perf_event type cgroup (rw,nosuid,nodev,noexec,relatime,perf_event)
cgroup on /sys/fs/cgroup/blkio type cgroup (rw,nosuid,nodev,noexec,relatime,blkio)
cgroup on /sys/fs/cgroup/cpu,cpuacct type cgroup (rw,nosuid,nodev,noexec,relatime,cpuacct,cpu)
cgroup on /sys/fs/cgroup/pids type cgroup (rw,nosuid,nodev,noexec,relatime,pids)
cgroup on /sys/fs/cgroup/devices type cgroup (rw,nosuid,nodev,noexec,relatime,devices)
cgroup on /sys/fs/cgroup/freezer type cgroup (rw,nosuid,nodev,noexec,relatime,freezer)
cgroup on /sys/fs/cgroup/memory type cgroup (rw,nosuid,nodev,noexec,relatime,memory)
cgroup on /sys/fs/cgroup/hugetlb type cgroup (rw,nosuid,nodev,noexec,relatime,hugetlb)
cgroup on /sys/fs/cgroup/cpuset type cgroup (rw,nosuid,nodev,noexec,relatime,cpuset)
configfs on /sys/kernel/config type configfs (rw,relatime)
/dev/mapper/cos-root on / type xfs (rw,noatime,attr2,inode64,usrquota,grpquota)
systemd-1 on /proc/sys/fs/binfmt_misc type autofs (rw,relatime,fd=29,pgrp=1,timeout=0,minproto=5,maxproto=5,direct,pipe_ino=11515)
debugfs on /sys/kernel/debug type debugfs (rw,relatime)
hugetlbfs on /dev/hugepages type hugetlbfs (rw,relatime)
mqueue on /dev/mqueue type mqueue (rw,relatime)
/dev/sda1 on /boot type ext2 (rw,noatime)
tmpfs on /run/user/0 type tmpfs (rw,nosuid,nodev,relatime,size=1213888k,mode=700)

dmesg | tail -30

[   17.748834] bridge: filtering via arp/ip/ip6tables is no longer available by default. Update your scripts to load br_netfilter if you need this.
[   17.801059] Netfilter messages via NETLINK v0.30.
[   17.809692] ip_set: protocol 6
[   20.201638] tun: Universal TUN/TAP device driver, 1.6
[   20.201643] tun: (C) 1999-2004 Max Krasnyansky <maxk@qualcomm.com>
[  133.056191] sched: RT throttling activated
[ 3587.737552] sh (23261): drop_caches: 3
[ 7186.842233] sh (29791): drop_caches: 3
[10786.389010] sh (3837): drop_caches: 3
[14386.003510] sh (16855): drop_caches: 3
[17986.849493] sh (1317): drop_caches: 3
[21586.085092] sh (11331): drop_caches: 3
[25186.539033] sh (20690): drop_caches: 3
[28786.867682] sh (2006): drop_caches: 3
[32386.910420] sh (8283): drop_caches: 3
[35013.136664] Peer 0000:0000:0000:0000:0000:ffff:290d:6495:44773/443 unexpectedly shrunk window 4248605361:4248605561 (repaired)
[35991.044091] sh (14435): drop_caches: 3
[39586.667918] sh (21532): drop_caches: 3
[43193.314364] sh (28840): drop_caches: 3
[46786.565195] sh (4942): drop_caches: 3
[50386.499182] sh (14796): drop_caches: 3
[53986.328182] sh (22648): drop_caches: 3
[57586.119561] sh (30312): drop_caches: 3
[61186.665676] sh (6492): drop_caches: 3
[64787.062348] sh (14516): drop_caches: 3
[68387.391564] sh (21746): drop_caches: 3
[71987.388671] sh (12993): drop_caches: 3
[75587.287092] sh (19743): drop_caches: 3
[79187.530568] sh (26219): drop_caches: 3
[82787.938178] sh (32001): drop_caches: 3

Hmm, I hadn't run into MySQL crashing with that error before when not relating to RAM issues. However, it's worth exploring whether any of the ideas described here help in your situation. In particular, you may want to take a peek at the last comment:

https://askubuntu.com/questions/422037/optimising-mysql-settings-mysqld-...

It doesn't currently look like you have any swap space setup, and the tmpfs partitions I see aren't setup for use as extra memory.

However, you can always add one, there's information on doing that from within Webmin here:

https://doxfer.webmin.com/Webmin/Disk_and_Network_Filesystems#Adding_vir...