MySql crashing due to high I/O?
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.
8 Replies
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 innodbbufferpool_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.
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?
Anyway, there is no need to reduce innodbbufferpool_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.
As I understand it, with maxclients set to 16, that will mean only 16 people can see my site at the same time?
I've weathered several I/O spikes since setting it to 32 (it was 64) and so far no SQL crashes.
Thanks!
@Vance:
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.)
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.