Enabling InnoDB on an existing MySQL production server

Question

We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases.

Can I just enable/configure InnoDB in /etc/my.cnf and restart MySQL, or is there more to this than meets the eye?

I should add that this is a MySQL environment built from source (running on CentOS 5.5 x64) and we plan to use the InnoDB plugin rather than the built-in InnoDB capability.

The plan is to follow steps as described on this page:

http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html

We also want to use innodb_file_per_table.

Apart from tuning parameters such as pool sizes and so on, is there anything else I should take into consideration?

score 3 · Accepted Answer · edited Apr 13 '17 at 12:42

InnoDB should already available. You can check with

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

or

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Make sure skip-innodb is not in /etc/my.cnf

Since you are using the InnoDB Plugin, you need to activate new features to greatly enhance InnoDB Performance. For example, did you know there are new options for making InnoDB use multiple CPUs? See my past posts on this:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

This is important because there are occasions when InnoDB in older versions of MySQL is faster than newer versions. I wrote about this as well:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Additional Reference from MySQL Performance Blog : http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/

Enabling InnoDB on an existing MySQL production server

1 Answers1