MySQL: ERROR 1286 (42000): Unknown table engine ‘InnoDB’

I was asked to review a application recently that had some performance issues. The db engine was MySQL and the database was getting hammered. Changing the behavior of the queries the app used was not a short term option so I started looking tuning at the MySQL configuration.

The box in question was a “large” EC2 instance (two cores, 8 GB RAM). With the MySQL data dir mounted on an EBS volume. One of parameters I tweaked was the “innodb_buffer_pool_size”. Out of the box it was set to 8MB which is fairly tiny. After reading some docs and blog posts on the subject, I decided to go with the suggested 50-80 percent. After setting the value to 4GB, I restarted and things were humming along.

I came back a while later and noticed this error in the app logs: Unknown table engine ‘InnoDB’ .

I logged into the mysql console and got the same error trying to run some simple SELECTs on data that I knew should be there. As it turns out MySQL will go ahead and shutdown the InnoDB engine if it has trouble allocating memory for the innodb buffer pool. I suspect the pool started off small and progressively grew and failed to find needed RAM. At which point it turned off InnoDB. Nice! Of course restarting MySQL would “fix” this issue for a while till it happened again, so I ended up turning it down a bit to reduce the chance of this happening again.

A couple of related posts I found useful:

InnoDB engine disabled when specifying a buffer pool size too high

Choosing innodb_buffer_pool_size