I am having a problem with MySQL on my Rackspace Centos 6.4 server instance. The problem is similar to the one described in this StackOverflow question. MySQL is being restarted automatically at some point by mysqld_safe, and the restart fails because InnoDB tries to allocate 128Mb of RAM, which fails. The output of mysqld.log is as follows:
140129 18:05:26 mysqld_safe Number of processes running now: 0
140129 18:07:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140129 18:07:30 InnoDB: Compressed tables use zlib 1.2.3
140129 18:07:30 InnoDB: Using Linux native AIO
140129 18:07:35 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140129 18:07:46 InnoDB: Completed initialization of buffer pool
140129 18:07:46 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140129 18:07:47 [ERROR] Plugin 'InnoDB' init function returned error.
140129 18:07:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140129 18:08:07 [ERROR] Unknown/unsupported storage engine: InnoDB
140129 18:08:10 [ERROR] Aborting
140129 18:08:53 [Note] /usr/libexec/mysqld: Shutdown complete
140129 18:18:18 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The solution provided in that other question seemed to be one of "create a swap file". I have checked my server and it seems there is already an active swap file:
# swapon -s
Filename Type Size Used Priority
/dev/xvdc1 partition 499992 34876 -1
and, looking at that output, it is the size I was thinking I needed (512Mb). For completeness, here are the contents of my /etc/fstab file:
/dev/xvda1 / ext3 defaults,noatime,barrier=0 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/xvdc1 none swap sw 0 0
So am I missing something, or do I already have a working swap file, of about 512Mb, which is reasonably empty and therefore should be capable of handling a request for 128Mb? Should I reduce the size of the InnoDB buffer to, say, 64Mb? Will there be any issues related to shrinking this buffer?
(My Rackspace server is the smallest one available, which has 512Mb RAM. Whenever I do a top on the server, it seems to have between 50 and 80 Mb free.)
From the output it does look like you have approximately 488MB of swap space.
I am not sure if MySQL allocates the innodb buffer pool based off how much memory+swap is free. Even if it did, you would want to avoid it going to swap as it is slower than keeping things in RAM. My guess is that it does not include swap.
The error "InnoDB: mmap(137363456 bytes) failed; errno 12" lets us know that you could not allocate the memory.
I would reduce the size of the innodb buffer pool to 64MB; see if that works. If it does not, either increase the size of the cloud server or reduce the size of the buffer pool again.
In general InnoDB likes memory. It tries to keep as much of the data in memory as possible and reduce disk IO. By reducing the buffer size, you decrease how much MySQL can keep in memory. MySQL will go to disk more often to retrieve data.
This number should reflect the size of your data set. No point in having a buffer much larger than your actual data set.
You may be able to use some of the queries here to determine the size. http://www.mysqlperformanceblog.com/2008/03/17/researching-your-mysql-table-sizes/
If your data set is much larger than the amount of RAM you normally have on hand, you may need to increase the size of the server itself.