MySQL Forums
Forum List  »  InnoDB

file_per_table and ibdata file
Posted by: Nishant Deshpande
Date: September 30, 2009 04:47AM

I have file_per_table set, and i see all my tables having their own files as expected.

But, the ibdata file keeps growing... which is exactly what i don't want.

after re-RTFMing, i came across this line
(http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html)

"""
InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.
"""

Am i right in thinking that if i have a big table.. say 100GB, and i do a "insert into foo select * from bigtable", this will effectively force a pretty huge undo log into the ibdata file? and now i have no way of recovering this space (apart from doing a mysqldump and load which in my case will take a *long* time..)

i want to load in big files, but i don't want the ibdata file to take up 20% of my disk (which would be the 'high water mark' of the size of the data i may be loading in).

any comments on if the above is correct and any solutions would be much appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
file_per_table and ibdata file
4069
September 30, 2009 04:47AM
1920
October 01, 2009 07:56AM
2575
October 01, 2009 02:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.