Cloud computing makes it very affordable to get your own private virtual server on the Internet. Digital Ocean offers an entry level droplet for USD$5 per month, and Amazon.com has a micro instance tier on the EC2 platform that is free for the first year. These instances are rather useful if you want to test out some new technologies (multi source replication?) or put together a proof of concept.
However, these servers comes with a very low amount of RAM, usually between half GB to one GB. It is common to see production grade database servers with literally 100x that amount. 100MB of memory usage makes no significant difference to one of these servers, but definitely noticible to a 512MB virtual machine.
I recently encountered an issue where MySQL would not start up. The interesting part was the lack of any errors from MySQL:
140521 08:26:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140521 08:26:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Yet if we check our kernel messages, we clearly see an out of memory event:
May 21 08:26:41 aes2 kernel: Out of memory: Kill process 24774 (mysqld) score 842 or sacrifice child May 21 08:26:41 aes2 kernel: Killed process 24774, UID 0, (mysqld) total-vm:549180kB, anon-rss:437324kB, file-rss:44kB
Or in other cases, we see errors about InnoDB not being able to allocate buffer pool:
2014-05-21 08:33:23 25042 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 2014-05-21 08:33:23 25042 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
But we do have RAM available:
[root@aes2 ~]# free -m
total used free shared buffers cached
Mem: 490 86 403 0 7 32
-/+ buffers/cache: 46 443
Swap: 0 0 0
[root@aes2 ~]#
Well, the issue here is Performance Schema, and not making it obvious.
When starting up, it allocates all the RAM it needs. By default, it will use around 400MB of RAM, which isn't noticible with a database server with 64GB of RAM, but it is quite significant for a small virtual machine. If you add in the default InnoDB buffer pool setting of 128MB, you're well over your 512MB RAM allotment and that doesn't include anything from the operating system.
We can easily disable Performance Schema by putting this under the [mysqld] section configuration:
performance_schema = off
It is possible to customize which monitors are used in Performance Schema to reduce the memory footprint. But with limited RAM, it will probably be much better to use all of that for other things, like the buffer pool.
There is a MySQL bug report that provides a pretty decent description about Performance Schema: http://bugs.mysql.com/bug.php?id=68514
And there is a feature request to print out the amount of memory used for Performance Schema, that would have made this whole problem more visable: http://bugs.mysql.com/bug.php?id=69665
