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

[Chander Ganesan]

> InnoDB does purge deleted rows from the ibdata files. Certain 
> PostgreSQL advocates have been spreading a claim that InnoDB would not 
> do that, but the claim is false.
>
> If your ibdata file keeps growing indefinitely, please check with SHOW 
> INNODB STATUS that you do commit all your transactions. If a 
> transaction stays open for months, then the purge cannot remove 
> deleted rows.
>
> If you convert ALL your tables from InnoDB to MyISAM, then you can 
> remove the ibdata files and ib_logfiles. If you put skip-innodb to 
> my.cnf, then those files will not be created again.
>
I don't think there is a question as to whether or not InnoDB will purge 
data and re-use space, the question is whether or not the ibdata files 
will be "shrunk" when that space is purged.

My understanding (and experience) has always shown that ibdata files  - 
while they may purge and re-use unused space, will not shrink themselves 
based on the actual space usage.  Is that not correct?

I.e., if I have 100 MB of table data, and say - delete 6 tables (which 
would result in InnoDB recovering all that space), that results in only 
10MB of space being "used", the file will be re-sized to 10MB - or 
something smaller than 100 MB.

The practical example would be if I were to accidentally add 1GB of data 
to my InnoDB tablespace, and then remove it.  Would my total ibdata file 
sizes total less than 1GB of space (now I'm just using 100MB)? 

If that were the case it would be a simple matter of switching to 
'tablespace per table', migrating the data to the individual tables 
(which would shrink the ibdata files), re-structuring the ibdata files 
(to use other partitions, etc.) an the moving the data back into the 
tablespace.

Thanks

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



> Best regards,
>
> Heikki
>
> Oracle Corp./Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
>
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
> MyISAM tables
> http://www.innodb.com/order.php
>
>
> ----- Original Message ----- From: "Alex" <alexj@stripped>
> Newsgroups: mailing.database.myodbc
> Sent: Tuesday, January 03, 2006 6:55 AM
> Subject: Re: removing ibdata1 if some/all tables are not InnoDB?
>
>
>> HI Carl,
>>
>>        The ibdata file growth can be stopped by removing the autoextend
>> keyword in the my.cnf file.
>>
>> In your my.cnf file the entry might be
>>
>> innodb_data_file_path = ibdata1:256M:autoextend
>>
>> If you want to stop the growth of that file and add another file then 
>> this
>> is what you want to do.
>>
>> 1. Stop the mysql server
>> 2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size)
>> 3. edit the my.cnf file and replace
>>
>> innodb_data_file_path = ibdata1:256M:autoextend
>>
>> with
>>
>> innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend
>>
>> 4. Start the server.
>>
>> This will stop that file from growing and a new file will be added that
>> can pushed on to a different disk and symlinked into the ibdata 
>> directory.
>>
>> Data growth is a problem in all table types. Even if you migrate to 
>> MyISAM
>> you need space.
>>
>> See whether there is log_bin turned on the server. If so there might be
>> lots of bin log files that you can do a cleanup on. Bin logs occupy a
>> great deal of space.
>>
>> Thanx
>> Alex,
>> MySQL DBA
>> Yahoo!
>>
>>
>>
>> On Tue, 03 Jan 2006 08:28:24 +0530, Carl Brewer <carl@stripped>
>> 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/mysql?unsub=1
>>
>
>