Mysql – InnoDB engine is not loading after tuning parameters

innodblinuxMySQLmysql-5.1performanceperformance-tuning

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below are the parameters which I changed.

[mysqld]
key_buffer_size = 298M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 50
query_cache_size = 32M
query_cache_limit = 1M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

and uncommented these variables for InnoDB.In default file, it was commented even INNODB was active.

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1127M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Now, to verify whether InnoDB is available or not, I ran show engines command. But, InnoDB is not listed in that. Then, I ran command below command:

mysql> show variables like '%innodb%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_innodb           | NO    |
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

I am unable to find where I did the mistake? Can you please help?

Adding the way, issue has been resolved But don't know the way is correct or not!:

Checking error log certainly helped here. On the basis of error log:

InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
InnoDB: 133760 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 128000 pages!
InnoDB: Could not open or create data files.
Then, I commented - #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

After this, I got below error message in error log file:

InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
140430 17:13:37 [ERROR] Plugin 'InnoDB' init function returned error.
140430 17:13:37 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Then, I commented #innodb_log_file_size = 256M By default #innodb_log_file_size = 5M.

Now, I again started the mysql service and found that Innodb has been loaded as default engine.

Best Answer

You found in the error log that InnoDB refuses to start if the config file sets the innodb_log_file_size to a size different from the physical size of the ib_logfile* files in your data directory.

The reason for this is that the log file are used during crash recovery. If InnoDB tries to initialize, but the log files are suspiciously the wrong size, it's possible they're the wrong files. InnoDB decides not try to crash recovery using the wrong log files, because applying the wrong files could irreparably corrupt your data. So believe it or not, shutting down the storage engine is the best outcome.

You can add another line to your config file to make mysqld refuse to start if InnoDB fails to start:

innodb=force

In MySQL 5.6, they added more flexibility so that InnoDB resizes the log files if you change the config file entry. Here's a blog that explains this in more detail: http://www.mysqlperformanceblog.com/2013/09/05/my-favorite-secret-mysql-5-6-feature/

A couple of other tips

The default InnoDB log file size of 5MB is totally undersized for most production sites. You can read this blog for a method to estimate the log file size your site needs: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
You're not enabling the InnoDB plugin. MySQL 5.1 ships with two implementations of InnoDB. You can get more performance and scalability for free without upgrading if you switch from the "built-in" InnoDB (statically linked with MySQL 5.1) to the InnoDB plugin. The plugin is where all the new engineering is implemented. If you don't enable it, you're virtually using InnoDB as it existed in MySQL 5.0. See http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html for details.

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

Mysql – Error ‘Unknown table engine ‘InnoDB” on query. after restarting thesql

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.