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: 877-258-8987/919-463-0999
Fax: 919-386-0158
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
