Changing Tables from MyISAM to InnoDB... Cautions?

[Good Man]

Hi there

I have a database with about 20 or so tables, maybe a few thousand rows
in each. I am starting to do more complex things with my insertions etc,
and I want to start to use transactions, so I imagine I should change
some table types in the database from MyISAM to InnoDB.

I know that InnoDB tables are a completely different beast than MyISAM in
terms of data storage etc... ie: instead of using folders on the server,
everything is stored in a file (ibdata1).

Is there anything I should be aware of when changing a bunch of tables
from MyISAM to InnoDB? Any hidden gotchas like changing 'ibdata1' in any
way, table slow downs, index changes, or ANYTHING? Or can I just alter
the tables and go on my merry way?

Something tells me I need to start learning a bit more about that ibdata1
file.... is there a size limit or anything? It seems to me that because
MyISAM stores its data in directories on the server, that accessing the
data would be much quicker. Having all my data in that one file seems
like it would slow things down... does it?

Much thanks.

[Gordon Burditt]

>I have a database with about 20 or so tables, maybe a few thousand rows

in each. I am starting to do more complex things with my insertions etc,
and I want to start to use transactions, so I imagine I should change
some table types in the database from MyISAM to InnoDB.

I know that InnoDB tables are a completely different beast than MyISAM in
terms of data storage etc... ie: instead of using folders on the server,
everything is stored in a file (ibdata1).
Make sure you have enough room for the file. You can't easily
split up the data with symlinks to directories on other disk partitions
with InnoDB tables. Also be aware that you'll briefly need BOTH
the InnoDB and MyISAM tables to exist during the ALTER TABLE, so
allow for enough disk space.
Is there anything I should be aware of when changing a bunch of tables
from MyISAM to InnoDB? Any hidden gotchas like changing 'ibdata1' in any
way, table slow downs, index changes, or ANYTHING? Or can I just alter
the tables and go on my merry way?
When making any major change like this, make a backup first.
Something tells me I need to start learning a bit more about that ibdata1
file.... is there a size limit or anything?
How big is a file in your OS? Some systems start having problems around
2GB or 4GB unless MySQL is compiled with an option for large files.
It seems to me that because
MyISAM stores its data in directories on the server, that accessing the
data would be much quicker. Having all my data in that one file seems
like it would slow things down... does it?

Why would it slow it down? You can seek in a large file; you don't
have to read it sequentially. If your tables are of any size, you'll
have to deal with indirect blocks anyway, even with MyISAM.

Gordon L. Burditt

[Good Man]

go***********@burditt.org (Gordon Burditt) wrote in
news:11*************@corp.supernews.com:

Something tells me I need to start learning a bit more about that
ibdata1 file.... is there a size limit or anything?

How big is a file in your OS? Some systems start having problems
around 2GB or 4GB unless MySQL is compiled with an option for large
files.

I was poking around the MySQL Administrator GUI, and I noticed a system
variable called "innodb_file_per_table", meaning that an innodb file would
be created for each innodb table. This seems like something I might be
interested in, as I doubt any table on its own will hit 2GB, and for some
reason, it just makes me feel better to have my tables split up into
seperate data storage entities. I'm guessing that for a major change like
this, I should backup my tables, erase the database, shut down MySQL, start
it up again with this new change in 'my.cnf', make a database, and then
restore my tables. thoughts?

Thanks Gordon.

[Bill Karwin]

Good Man wrote:

Is there anything I should be aware of when changing a bunch of tables
from MyISAM to InnoDB? Any hidden gotchas like changing 'ibdata1' in any
way, table slow downs, index changes, or ANYTHING? Or can I just alter
the tables and go on my merry way?

I don't think the single-file versus multi-file issue has a big impact
on performance. But InnoDB _does_ have some significant performance
costs.

InnoDB does a lot more, for instance, regarding referential integrity,
multi-versioning records to provide greater concurrency, etc. This all
incurs some cost to maintain, especially during write operations
(INSERT/UPDATE). For instance, I have observed that doing bulk data
loads to InnoDB tables without tuning it properly makes loading take
about 10x as long as with MyISAM.

Read the page on InnoDB performance tips carefully, since there are a
lot of options to configure:
http://dev.mysql.com/doc/refman/5.0/...db-tuning.html

I generally wouldn't recommend switching to InnoDB unless you actually
need the benefits that it gives you in referential integrity,
transaction support, concurrency, etc. A large number of typical
applications don't need these features, and the cost of providing them
is pretty high.

Regards,
Bill K.

[Bill Karwin]

Good Man wrote:

I'm guessing that for a major change like
this, I should backup my tables, erase the database, shut down MySQL, start
it up again with this new change in 'my.cnf', make a database, and then
restore my tables. thoughts?

A mysqldump script often has the table type specified in the "CREATE
TABLE" statements within. You may need to edit the mysqldump file to
change these. Or read the docs on mysqldump to see if you can make it
omit the "TYPE=MyISAM" part when it makes the backup.

Regards,
Bill K.