InnoDB engine disabled when specifying a buffer pool size too high

The InnoDB engine will (silently) not start if innodb_buffer_pool_size too big.

You modified your my.cnf to increase the performance of your DB by tweaking the InnoDB variables.
Then you restarted MySQL for the changes to take effect, everything seems fine, no error message.

Except your InnoDB tables are no longer working.

mysql> SELECT VERSION();
+-----------------------+
| VERSION()             |
+-----------------------+
| 5.1.37-1ubuntu5.1-log |
+-----------------------+
1 row in set (0.00 sec)

mysql>
mysql> SHOW CREATE TABLE users;
ERROR 1286 (42000): Unknown table engine 'InnoDB'
mysql>

You can’t believe your eyes and start double checking for confirmation:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW ENGINES \G
*************************** 1. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
7 rows in set (0.00 sec)

mysql>

Yep, InnoDB is definitely gone.

If you try to create an InnoDB table unfortunately the command will not fail:
MySQL will create a MyISAM table behind your back without telling you it couldn’t create an InnoDB table. Ouch.

Examining your /etc/mysql/my.cnf and you see you typed some extra zeros:

innodb_buffer_pool_size=10000M

Probably you were thinking assigning 1G to the buffer pool when in fact it was 10GB, and your server only has 2GB of memory.
Change that to:

innodb_buffer_pool_size=1000M

Restart MySQL and you get your InnoDB tables working again.

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

It’s amazing but MySQL will actually not complain about a value too high when you restart it.
It looked like this to me when the value was too high:

bash# /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld    [ OK ]
 * Starting MySQL database server mysqld    [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
bash#

On an Amazon EC2 Standard Default Small Instance running Ubuntu server, I was surprised to find the limit is very low.
It’s a 32-bit platform with 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), and 160 GB of local instance storage.

InnoDB could not start when innodb_buffer_pool_size was set to 512M.
However it worked with 256M.

Tags: , , ,

7 Responses to “InnoDB engine disabled when specifying a buffer pool size too high”

  1. Daniel P says:

    Thank you for this post! It was most helpful even though the innodb engine in my case became disabled due to a change of the innodb_log_file_size attribute.

    If you change this attribute and forget to remove the old /var/lib/mysql/ib_logfile* the same problem will arise.

  2. Zane says:

    Thanks Danel P, your solution of deleting the logfiles worked for me after I had copied the data directory to another location on windows

  3. Hannes B says:

    Hey Daniel P

    Thanks for your post, saved my life. I had played around with all sorts of engines and stuff trying to increase the performance and unfortunately the leftover innodb log files killed my mysql. :)

  4. Rayo says:

    In some cases, the InnoDB-engine will not start even though it was starting before, because the necessary memory is being used by another process. This happened to me after I let my server run into memory (swapping) problems. After restarting the MySQL-server the InnoDB-engine wouldn’t start. I tried to investigate the problem, but 10 minutes (and various restarts) later the MySQL-server started with InnoDB-engine without complaining. I realized the problem after calling “free -m” and seeing that the necessary memory was actually being used by another process in the moment of starting the MySQL-server.

  5. Morten S says:

    Thanks to everyone here. I’m just curious: Does MySQL print the reason for failing to bring up InnoDB? I checked /var/log/syslog and /var/log/mysql.log and mysql.err, but found nothing. And, while we’re at it…isn’t it a pretty stupid behaviour? It could for example move the old logfiles and make new ones, or start the engine with a warning telling you that it didn’t get all the memory it wanted. What do you think?

  6. dave says:

    I too had the same error.
    Thanks for the post. This is a lifesaver – surprising how InnoDB wont even use 512M for innodb_buffer_pool_size out of 2G RAM on a Cpanel / WHM enabled server.

    I read this: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ where Peter suggests using 70% of total RAM for innodb_buffer_pool_size.

    I carefully applied 25% instead of 70% and still it crashed !
    I guess this means that the OS + other software typically need 1-2 G of RAM and above that 70% of all space can be utilised for Innodb – but only if you have ugly amounts of RAM like 16GB, 32 GB or 64 GB.
    When you have 2G total, on a small dedicated / shared / VPS server, the 70% rule clearly does not apply.