Ubuntu 12.04 Server - MySQL keeps crashing - Possible InnoDB problems

Question

I have been searching for a solution but nothing seems to work. I have a single Drupal 6 website up on this server.

Server:Ubuntu 12.04 Apache/2.2.22 (Ubuntu) 5.3.10-1ubuntu3.8 MySQL 5.5.32 Drupal 6

Here is a link to my Apache2 and MySQL error files and my config file for MySQL. Please have a look. https://drive.google.com/folderview?id=0By8qja37RfvzWUt1a0lGSzZqR3M&usp=sharing Those files are huge, that is why I linked to them instead of pasted them in here. If I need to I can paste in here. Just let me know. Thanks.

I have done a lot of searching. I mostly try to Google the errors themselves but can't seem to find the right solution. I think I have narrowed it down to a problem with innodb but that is it. I check the tables in the error log and phpMyAdmin say they are ok and I repair them anyway and still get the crashing.

When my site crashes, I have to restart Apache and then restart MySQL and then the site comes back up. It has been crashing 2 - 6 times a day.

Any help would be greatly appreciated. Thanks.

Answer 1

I feel reasonably confident that there's nothing wrong with your MySQL installation, which is why repairing the tables shows no problems found. Those are artifacts of the real problem:

You simply don't have enough memory on your machine to do everything you're trying to do.

From your configuration:

key_buffer              = 384M

Between that and the default for innodb_buffer_pool_size of 128M, that's 512M that has to be available all of the time and if it can't be allocated at startup or restart of MySQL, it's not going to start.

131210 13:06:25 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
131210 13:06:25 InnoDB: Completed initialization of buffer pool
131210 13:06:25 InnoDB: Fatal error: cannot allocate memory for the buffer pool
131210 13:06:25 [ERROR] Plugin 'InnoDB' init function returned error.

This isn't a MySQL crash -- it's MySQL failing to start -- or restart -- because there aren't 128MB of free memory available to allocate.

Error 12 is "Out of Memory" -- system memory.

The other messages in your error log are all essentially side-effects.

I suspect that also in your log you will see this:

YYMMDD HH:MM:SS mysqld_safe Number of processes running now: 0

If that error message is not immediately preceded by a crash dump ("MySQL caught signal xx" followed by a stack trace and a lot of debugging information) then it's not likely to be MySQL crashing -- it's MySQL being killed by the kernel to try to keep the whole system from crashing due to a crisis situation related to available memory. Apache's forking model is notorious for this -- as your site becomes busy, Apache spawns more children, each of which has an appetite for memory.

You will probably find evidence of this in your syslogs.

Xxx xx xx:xx:xx [machine name] kernel: Out of memory: Killed process xxxx, UID xx, (mysqld).

The options are to add more memory, or reduce MySQL's memory footprint via configuration, or reduce the number of children Apache can spawn, or eliminate other processes from the system, or separate the database and web server onto different machines, or add more swap -- but a MySQL machine that is swapping is not going to perform optimally.

For similar scenarios, see also:

• Intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
• MySQL InnoDB Crash Post-Mortem