patternsqlMinor
Any gotchas at all with converting from MyISAM to InnoDB?
Viewed 0 times
allwithinnodbanygotchasmyisamconvertingfrom
Problem
I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running
DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I need to know about before converting over. I thought I'd be fine with my queries but apparently not.Solution
Here are some gotchas
Memory Usage
MyISAM
InnoDB
Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.
FULLTEXT Indexes
MyISAM
InnoDB
MySQL 5.5 and back
To locate which MyISAM tables have a FULLTEXT index run this query:
Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.
OPTIMIZE TABLE
MyISAM
InnoDB
Memory Usage
MyISAM
- only caches index pages.
- shared keycache (sized by key_buffer_size).
- You can also set up dedicated keycache, one or more tables per cache table.
InnoDB
- caches data pages and index pages.
- one buffer pool and one size before MySQL 5.5
- 1 or more buffer pools starting with MySQL 5.5
Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.
FULLTEXT Indexes
MyISAM
- Supports FULLTEXT indexes
InnoDB
- Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
- Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.
MySQL 5.5 and back
To locate which MyISAM tables have a FULLTEXT index run this query:
select tbl.table_schema,tbl.table_name from
(
select table_schema,table_name
from information_schema.tables
where engine='MyISAM'
and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
select table_schema,table_name
from information_schema.statistics
where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.
OPTIMIZE TABLE
MyISAM
- The MyISAM table is shrunk
- ANALYZE TABLE runs index statistics on all indexes
InnoDB
- ANALYZE TABLE is totally useless because index stats are always being recollected
- With innodb_file_per_table disabled, ibdata1 will grow bigger
- With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk
Code Snippets
select tbl.table_schema,tbl.table_name from
(
select table_schema,table_name
from information_schema.tables
where engine='MyISAM'
and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
select table_schema,table_name
from information_schema.statistics
where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);Context
StackExchange Database Administrators Q#10407, answer score: 7
Revisions (0)
No revisions yet.