I have several Wordpress sites on a 2 GB node, running Ubuntu Linux, Apache2, MySql. Periodically, MySql will crash and die, and does not autorecover, requiring me to either reboot then node, or, if I'm at a place I can open the shell, do a mysql restart. When this problem happens, I look at Longview information, and see a sudden spike in Disc I/O at the exact time mysql crashed. It's a very short spike, but very sharp (I guess that's the definition of a spike...). Everything else looks normal. CPU, Memory, Network, etc., are all low and normal. Looking at the Apache website logs shows nothing unusually (sure, the usually nonsense from bots etc., but not abnormally so). I would attach an image of Longview chart, but I don't see an option to upload an image. Anyway, my disk I/o is typically really low, like 1 op/s or less on read, sometimes up to 2 or 3, and 0 swap. The spike shoots up to 40 Read and 100+ Swap. And then MySql dies.

The only errors I have found are in the sql error log file, and shows this:

150815 9:49:37 [Warning] Using unique option prefix myisam-recover instead of $
150815 9:49:37 [Note] Plugin 'FEDERATED' is disabled.
150815 9:49:37 InnoDB: The InnoDB memory heap is disabled
150815 9:49:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150815 9:49:37 InnoDB: Compressed tables use zlib 1.2.3.4
150815 9:49:37 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
150815 9:49:37 InnoDB: Completed initialization of buffer pool
150815 9:49:37 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150815 9:49:37 [ERROR] Plugin 'InnoDB' init function returned error.
150815 9:49:37 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150815 9:49:37 [ERROR] Unknown/unsupported storage engine: InnoDB
150815 9:49:37 [ERROR] Aborting
150815 9:49:37 [Note] /usr/sbin/mysqld: Shutdown complete

The Initializing buffer pool, size = 128.0M and followed by InnoDB: Fatal error: cannot allocate memory for the buffer pool is clearly the problem and there is also an error about mmap(137363456 bytes) failed; errno 12 (but I have no idea what that means). However, at that exact time, memory was available. Only 443 MB of 2 GB were in use, and 100 MB of Swap, out of 256. Yet, mySql failed to start due to out of memory even though plenty of memory was available. And the spike was on Disk I/o. There was no spike in memory (or Network or CPU) at or near that time.

Any ideas or pointers would be appreciated. This is very frustrating.

#define ENOMEM          12      /* Out of memory */

Error 12 means that the program attempted to allocate memory, but the system responded that not enough was available to fulfil the request. Not sure why this would be when you say that there was a lot of free memory. You could try reducing innodb_buffer_pool_size.

The disk activity might not be the cause of the problem; it could just be MySQL reading in a bunch of files as it starts up.

Is an innode buffer pool of 128 MB high? I researched this a bit before my original post, and it seems people frequently have innode buffer pools in the gigabyte range. So I figured that 128 MB was reasonable for my node size. Obviously, with only 2 GB total, I would not go above 128 MB (which is what it was by default, I believe), but 128 MB it just 6% of my total memory.

I will reduce this to 64 MB, but I have already dropped my Apache2 MaxClients from 64 to 32, hopping that will help, too. But I am bewildered mainly because my server was not out of memory. It had over a gigabyte to spare, so none of this should have happened. Yet, it happens fairly frequently. That is what makes me think something else is going on, perhaps with the high disk I/O, since that was the only anomaly I saw. So could something else be causing this?

It only takes a fraction of a second for a few rogue PHP scripts to use up all your memory. It often doesn't even show up on any graphs, since graphs are generated at 5-minute intervals. You have to be looking at the terminal when it happens in order to catch it, or infer from the log files that are left behind.

Anyway, there is no need to reduce innodb_buffer_pool_size. MySQL is just an unfortunate victim of the memory hog that Apache is.

On a 2GB Linode, MaxClients 64 is definitely too high. Since the typical memory limit for a PHP script is 128MB, your Apache has the potential to use up to 64 x 128MB = 8GB of memory at any given time. When this happens, Apache will squeeze out all other programs from the memory, resulting in your MySQL crash.

The only thing that's keeping your memory usage relatively low is that most of the time, you have few visitors. A sudden spike, for example, caused by somebody opening your website in several browser tabs at the same time, is all it takes to run out of memory.

Reducing MaxClients to 32 will probably help, and reducing it to 16 will likely prevent it from ever happening again. But Apache tends to become slow if you constrain it too much, so you should look into other settings (such as disabling KeepAlive) in order to compensate for low MaxClients.

I think it's spam bots that are doing it. Or at least the crawler robots looking to find comment forms to post in. So if a bot hits a lot in quick succession trying to spam my site, I suppose that would do it.

As I understand it, with maxclients set to 16, that will mean only 16 people can see my site at the same time?

With MaxClients set to 16, the 17th visitor will wait in the network queue for the few milliseconds it takes Apache to fulfil one of the 16 users' requests. Then the 17th visitor's request will be fulfilled. (And then the 18th, and so on.)

Ah, that's good. That's what I was hoping, anyway. I'll keep MaxClients at 32 for now, but it it continues, I'll reduce it more, perhaps to 24, then 16, etc.

I've weathered several I/O spikes since setting it to 32 (it was 64) and so far no SQL crashes.

Thanks!

This is why it's very important to disable KeepAlive or set it to a very low value (5 or less) if your MaxClients setting is low.

MaxClients is like the number of lanes in a tollgate. At any given time, 16 cars can go through your tollgate. Even if there are a hundred cars, it's not a problem if each car only takes a few seconds. By the time the next car has slowed down to pay, the car before it should already be leaving. But if you use KeepAlive, the cars don't leave the tollgate after they're done. They stay and chat with the employee at the toll booth, causing a long line of frustrated drivers behind them.

Handling each visitor as quickly as possible and sending them on their way (at least until they come back to see another page) is the key to running an efficient website on a small server.

You should use a server monitoring program so that you can monitor your server for any issue which arises, I love zabbix as its the best monitoring system in will monitor every thing on your server.

_________________
http://paknode.com/ - The Ultimate Website