Re: New to InnoDB (want to convert from MyISAM)
Posted by:
Rick James
Date: December 01, 2009 11:34PM
The basic method to change a table:
ALTER TABLE foo ENGINE=InnoDB;
It will work. Mostly.
* If you were happy without "transactions" before, then you can (as you say) delay implementing them now. But note that "autocommit" being on is an implied BEGIN + COMMIT around each statement. That is fine. Autocommit being off is probably the wrong way to go.
* Your data will be 2x-3x bigger.
* InnoDB must have a PRIMARY KEY on each table. If you don't have one on a table (nor do you have a suitable UNIQUE key), a hidden BIGINT will be provided for you. I think it is better for you to provide your own
INT UNSIGNED AUTO_INCREMENT NOT NULL PRIAMARY KEY
if you don't have a suitable PK. Furthermore use MEDIUMINT / SMALLINT / etc as needed.
* The performance of existing MyISAM "PRIMARY KEY" may (in limited circumstances) perform poorly when blindly changed to InnoDB. The case: EXPLAIN says "Using index" and refers to the PRIMARY KEY.
* There are several cases where InnoDB may perform dramatically better.
* There could be other subtle performance difference for the worse.
* Be sure to shrink key_buffer_size, but not to 0, and increase innodb_buffer_pool_size.
* Both MyISAM and InnoDB provide a high level of concurrency; the granularity of the locks is radically different, but both are so fast that you (usually) can't tell the difference.
* If you currently have any LOCK/UNLOCK TABLES statements, they should probably be changed to BEGIN/COMMIT -- this section of the code should improve.
* There is (virtually) no problem mixing MyISAM tables and InnoDB tables, so you could simply walk through the live system, doing the ALTERs one at a time. Nanda's question applies here -- the conversion time is proportional to the table size.
* If you backup with mysqldump, then the restore could be ...
Plan A: restore the tables into another database, then do the ALTER.
Plan B: edit the dump to change the ENGINE clause on the CREATE STATEMENTs
Plan C (perhaps best): use the dump option to output only the data (not the CREATEs), do the CREATEs separately (with extra PKs, as I advise), then load the data.
* Test! Dump your data, load it on a test platform, make the ALTERations, etc, then see if you game still works.
* Triggers/Stored procs/Views -- these may be troublesome; check.
* Yes, SELECT COUNT(*) FROM tbl; -- note the lack of WHERE clause -- is much slower in InnoDB. With a WHERE clause, the performance might be similar.
* I like the slowlog for determining slow queries. This indirectly says which tables are being pounded. After converting to InnoDB, they will still be pounded, but in different ways.
For further analysis, please provide
* SHOW CREATE TABLE tbl\G -- indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]