List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:December 4 2008 5:27pm
Subject:RE: Alter Table - InnoDB
View as plain text  
Actually, that will not reduce the size of the ibdata1 file at all.

Here is a GUARANTEED WAY to shrink that ibdata1 file, which YOU WILL NOW HAVE TO REPEAT
EVER AGAIN !!!

1. Perform mysqldump of all databases, routines and triggers from the mysql server to
/root/MyData.sql
2. Drop all databases from the mysql server
3. Add 'innodb_file_per_table' to [mysqld] section of /etc/my.cnf
4. Do 'service mysql stop'
5. Do 'rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_log*'
6. Do 'service mysql start' (ibdata1, ib_logfile 0, and ib_logfile1 are rebuilt)
7. Reload mysql sever from /root/MySQLData.sql

The only way ibdata1 will grow is with internal data dictionary information on InnoDB
tables. Data and Index Info will all reside in its own tablespace on a per-table basis.
When you run OPTIMIZE TABLE on an InnoDB table in its own tablespace, that table will
actually shrink.

Example (without innodb_file_per_table)
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;
Does two things
1. creates mytable.frm in /var/lib/mysql/mydb folder
2. writes table index, and data dictionary info about the table in /var/lib/mysql/ibdata1.
'OPTIMIZE TABLE mydb.mytable;' will actually append the entire mytable table in contiguous
blocks or pages to ibdata1, and then perform 'ANALYZE TABLE mydb.mytable;'

Example (with innodb_file_per_table)
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;
Does three things:
1. creates mytable.frm in /var/lib/mysql/mydb folder
2. creates mytable.ibd in /var/lib/mysql/mydb folder
3. writes data dictionary info about the table in /var/lib/mysql/ibdata1

'OPTIMIZE TABLE mydb.mytable;' Will actually shrink mytable.ibd and perform 'ANALYZE TABLE
mydb.mytable;' ibdata1 NEVER GROWS due to mytable table !!!

They only way to make ibdata1 grow it to create lots of InnoDB tables, which will just
data dictionary info for every InnoDB table. No more data and no more index info.

I once did this a job where the company had a 50 GB ibdata1 file, which collapsed quickly
to 500 MB (> 0.5 GB) due to wasted space from transactions and old data pages and old
index pages.

Give it a try and let me know if this worked. I know it will work.

-----Original Message-----
From: Chandru [mailto:chandru.dba@stripped] 
Sent: Thursday, December 04, 2008 7:10 AM
To: Shachi Govil; Jonas Genannt
Cc: Ananda Kumar; mysql@stripped
Subject: Re: Alter Table - InnoDB

Hi jones,
  Innodb does not release the space unless you optimize the tables. To dot
that you need to run dummy alter on all tables, by issuing "Alter table
<table name> engine=InnoDB"

but the space shall not regaing unless you start the table with
"innodb_file_per_table" option.
Then if you run the alter you shall gain space that was occupied.

But in your current scenario, there is space that is occupied that is not
shall not get released from OS even you run alter on the table. The space
shall be available in the table space "ibdata". If you have a option of
reimporting then take a dump, reimport the same with
"innodb_file_per_table" option enabled.

Regards,
Chandru.
www.mafiree.com

On Thu, Dec 4, 2008 at 4:19 PM, Shachi Govil <shachi.govil@stripped>wrote:

> I thought you always have to go to the physical location and delete the tmp
> files manually. These are created in tmp folder.
>
> I am not sure if restarting helps...
>
> Regards,
> Shachi Govil
> ----- Original Message ----- From: "Ananda Kumar" <anandkl@stripped>
> To: "Jonas Genannt" <jonasge@stripped>
> Cc: <mysql@stripped>
> Sent: Thursday, December 04, 2008 4:09 PM
> Subject: Re: Alter Table - InnoDB
>
>
>
> So, now u dont have free space in your file system.
>> Is this a production db.
>> I think, restarting the db, should not cause any harm. Which version of
>> mysql.
>>
>> regards
>> anandkl
>>
>>
>> On 12/4/08, Jonas Genannt <jonasge@stripped> wrote:
>>
>>>
>>> Hi Ananda,
>>>
>>> > Since u have cancled the job, those in-complete temp files can be
>>> > deleted from the file system.
>>>
>>> ok - but I'm using InnoDB. The IBdata file is bumped up. There are no
>>> temp files on the database directory.
>>>
>>> Greets,
>>>       Jonas
>>>
>>>
>>
>
>
>  --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
Thread
Alter Table - InnoDBJonas Genannt4 Dec
  • Re: Alter Table - InnoDBAnanda Kumar4 Dec
    • Re: Alter Table - InnoDBJonas Genannt4 Dec
      • Re: Alter Table - InnoDBAnanda Kumar4 Dec
        • Re: Alter Table - InnoDBShachi Govil4 Dec
          • Re: Alter Table - InnoDBJonas Genannt4 Dec
          • Re: Alter Table - InnoDBChandru4 Dec
            • RE: Alter Table - InnoDBRolando Edwards4 Dec
        • Re: Alter Table - InnoDBJonas Genannt4 Dec
  • Re: Alter Table - InnoDBMartijn Tonies4 Dec