473,394 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Changing Tables from MyISAM to InnoDB... Cautions?

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.

Dec 8 '05 #1
4 2917
>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
Dec 8 '05 #2
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.
Dec 8 '05 #3
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.
Dec 8 '05 #4
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.
Dec 9 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: knoak | last post by:
Hi there and a wonderful good morning! I was wondering something about mySQL, but i don't have a clue if it's possible (but it would be great!). Imagine i have two tables. One is called...
2
by: Marcus | last post by:
Hello, I recently converted all my existing MyISAM tables to InnoDB tables through phpmyadmin. I noticed some strange behavior whenever I would refresh the screen, as phpmyadmin would report...
0
by: Morten Gulbrandsen | last post by:
Dear MySQL developers, Could some experienced Database developer please take a look at this ? It is supposed to be plain SQL2. How can it be coded under MySQL Especially all referential...
0
by: Morten Gulbrandsen | last post by:
Hi Programmers, after this legal statement ALTER TABLE EMPLOYEE TYPE =3D InnoDB; I get=20 Warnings: 0 =20 and still MySQL claims to have to use Type =3D MyISAM,
0
by: Robert Oschler | last post by:
Is there something like HotBackup for MySQL InnoDB tables, for MyISAM tables? I'm using MyISAM tables for some of my tables because I needed FullText search. thx -- Robert Oschler "Let...
0
by: Stefan van Roosmalen | last post by:
I want to use constraints, so I will have to use innodb tables (according to the website mysql.com). But, what is the difference between myisam and innodb tables ?? And why is there a comment...
1
by: coyote2002 | last post by:
Hi, I have big problem with mysql 4.0.18 . Because my database is growing up, every night records that are older than some date are deleted. But size of database on disk isn`t smaller after this...
1
by: fuzzybr80 | last post by:
Hi, I am using MySQL version 5.0 on Linux Fedora Core 4. I recently got this error message: 060609 11:06:29 InnoDB: ERROR: the age of the last checkpoint is 9433557, InnoDB: which exceeds...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.