Mysql regularily crashing – Cannot allocate memory for buffer pool

Sep 15th, 2016 | By | Category: Linux / Freebsd

So every day now on one of my Debian servers, Mysql is crashing with the following message:

Fatal error: cannot allocate memory for the buffer pool
160915 6:38:10 [ERROR] Plugin ‘InnoDB’ init function returned error.
160915 6:38:10 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
160915 6:38:10 [ERROR] Unknown/unsupported storage engine: InnoDB
160915 6:38:10 [ERROR] Aborting

Seems to happen around the same time of day and I think I’ve tracked it down to when I automatically restart apache.  It’s not always the same time of day so I don’t know for sure if it is 100% related but best guess so far.    Now I’m able to restart mysql just find and dandy and it stays up for another 24 hours until it crashes again so I assumed that the databases weren’t completely hosed

After reading up on this I found a lot of people telling me that Apache might be going berserk and swallowing up all of my memory and that the best course of action was to reduce these values in apache2.conf:

<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 75
MaxRequestsPerChild 1200
</IfModule>

Before I had maxrequests per child around 10000 and max clients at 150 so I’ve dropped those considerably although I have to imagine I set the perchild value so high for a specific reason but it seems so wildly above every example config that I’ve read that I have to assume I added an extra zero by mistake.  Either way the server was running fine until recently so it wasn’t doing me any harm, however better safer than sorry so I’ve dropped it here in hopes of solving the problem

This did not solve my issue, so the next course of action most people recommended was more ram.. Now I have 4 GB of ram in this box and most people complain here had 1 or 2 so I figured I would be safe however just to be sure I doubled the ram up to 8GB and left it for a day.  This also did not prevent my problem as mysql crashed this morning 🙁

 

Going through the logs I also notice a lot of these error message “/wp_wfVulnScanners’ is marked as crashed and should be repaired” .  I assumed that these were just unrelated error messages being spit out while the server was crashing a result of the crashing, not a cause so I’ve mostly ignored them.  I immediately ran mysqlcheck -r –all-databases and -o but that didn’t resolve the issue.  Today however, exhausted all of the other issues I focused more on these log entries and checked to see if there was any other means of repairing the databases I could do.  Back in my days of managing Datacenters I remembered that when mysqlcheck failed to do anything there was another command we would run, couldn’t for the life of me remember it until I started googling today.  That command is “myisamchk”. 

So the first thing I did was went into my /var/lib/mysql/databasename directory and checked things with myisamchk –check *.MYI and myisamchk –analyze *.MYI.  They didn’t result in anything too useful, just a bunch of these:

Checking MyISAM file: wp_options.MYI
Data records: 767 Deleted blocks: 3
myisamchk: warning: 1 client is using or hasn’t closed the table properly
– check file-size
– check record delete-chain
– check key delete-chain
– check index reference
– check data record references index: 1
– check data record references index: 2
– check record links
MyISAM-table ‘wp_options.MYI’ is usable but should be fixed

Pretty much par for the course here, nothing surprising and nothing that looked to be causing a complete crash every day but who knows, so I started doing some repair work with the following commands:

#myisamchk –force –correct-checksum  *.MYI

#myisamchk -o -r *.MYI

The first one didn’t seem to do much, but the second one came back with a lot of these results:

– recovering (with sort) MyISAM-table ‘wp_comments.MYI’
Data records: 1
– Fixing index 1
– Fixing index 2
– Fixing index 3
– Fixing index 4
– Fixing index 5
– Fixing index 6

 

Nearly all tables came back with indexes being fixed so maybe it was more serious than I thought.. Anyways, I will leave it running here for a couple of days and see if it becomes stable again.. IF so I will reduce the ram on that box back down to 4GB to have more in my pool.  If it doesn’t work I’ll add on to this post with what I try next but if this is the end of the post when you’re reading this then assume the above worked and did the trick

 

Tags: , , ,

Leave a Comment