Re: removing ibdata1 if some/all tables are not InnoDB?

[Chander Ganesan]

You can stop the auto-growth of your ibdata1 file, and add additional 
ibdata files (as needed) on different disks/partitions.  However, you 
cannot currently "shrink" the file without some work..

Check out the MySQL documentation for innodb_data_file_path (that is the 
config setting that you would use in the my.cnf file to set things up).

You'll have to find out the size in MB of your current file (ls -lh) 
when you do this (if you want to start a new innodb data file on a 
separate disk, etc.), since in my experience MySQL will complain if you 
specify the size of the file incorrectly.

InnoDB is great when you have a lot of transactions going on, need 
rollback capability (batch operations that should either succeed as a 
whole or fail as a whole) - or you need ACID compliance.  MyISAM is fast 
for lookups, but requires a table lock to be acquired for updates, and 
most inserts (except in certain cases) - so its fast for lookups, but 
not as good for updates.  Each have their own distinct advantages...  
HEAP is good when you don't care if your data sticks around, and you 
need fast access to it (such as web cookies...)

As far as purging - you'd be best off doing an export, trash your InnoDB 
tables, and then import .

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
http://www.otg-nc.com
Phone: [phone]
Fax: [phone]
Expert MySQL Training



Carl Brewer wrote:

>
>
> Hello,
>
> I'm stuck with a rapidly decreasing amount of available disk space and
> a requirement to keep a lid on the size of our databases.  We're
> using MySQL 4.1.12 as bundled with RHEL ES 4.  We do a lot of 
> transactions keeping short term track of webserver sessions, which
> we don't need to keep logs of for very long.
>
> I have a number of databases, almost all of which are using MyISAM or
> HEAP, and one database using InnoDB.  As such (or at least, as I
> understand it) we have a ibdata1 file that will grow forever and
> AFAIK there's no way to stop it growing forever for as long
> as we have that InnoDB database.  Am I correct?  I'm no MySQL
> guru, my parsing of TFM and googling around and finding bug and feature
> requests for ibdata1 purging suggests that this is the case.
>
> If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
> filesystem) the imdata1 file, restart mysqld and import a
> (modified to be MyISAM) dumped copy of the InnoDB database,
> will that work without damaging anything and then not leave me
> with another infinatly growing imdata1 file?
>
> Am I correct in assuming that InnoDB databases are meant
> for sites where disk space is not ever likely to be an
> issue, and MyISAM is a more suitable database engine for
> our much tighter disk space situation?  I may have missed
> a section of the doco that discusses why one would choose an
> engine over another?
>
> Thanks for any advice,
>
> Carl
>
>