Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file.

I have truncated the table now it has zero record. But the watchdog.ibd have same size.

What's the issue.? Does truncate does not work for removing the data from the .ibd file..?

I don't want to drop the table because in need that table.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Please keep in mind that TRUNCATE TABLE is a DDL command that tells the InnoDB storage engine to pretend there are no records.

According to the MySQL Documentation on TRUNCATE TABLE :

When fast truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

However, doing TRUNCATE TABLE does nothing for space already allocated.

To quickly drop the space down to zero rows, you can do the following:

#
# Algorithm 1
#
CREATE TABLE watchdog_new LIKE watchdog;
ALTER TABLE watchdog RENAME watchdog_zap;
ALTER TABLE watchdog_new RENAME watchdog;
DROP TABLE watchdog_zap;

Since TRUNCATE TABLE tells mysql that the table is considered empty, you can do this:

#
# Algorithm 2
#
TRUNCATE TABLE watchdog;
ALTER TABLE watchdog ENGINE=InnoDB;

These two steps behave the same way as Algorithm 1.

These steps also can be done with

#
# Algorithm 3
#
TRUNCATE TABLE watchdog;
OPTIMIZE TABLE watchdog;

Internally, OPTIMIZE TABLE would perform Algorithm 1 and then perform ANALYZE TABLE. For InnoDB, the ANALYZE TABLE phase of OPTIMIZE TABLE is bypassed. In fact, it is unnecessary because index statistics are recomputed on each query executed against an InnoDB table.

share|improve this answer
1  
a correction, index statistics are not updated after every query. They are updated after every 2 billion row operations, when 1/16th of the rows change, plus on certain metadata operations (ANALYZE TABLE, SHOW INDEXES FROM, SHOW TABLE STATUS) and most DDL operations. See mysqlperformanceblog.com/2011/10/06/… for more information . –  Aaron Brown Jan 18 '12 at 16:29
    
My mistake. Index statistics for InnoDB are computed by approximation via dives into an index. This is quickly seen when running SHOW INDEX FROM command on an InnoDB table multiple times and watching the Cardinality column change with each successive run. –  RolandoMySQLDBA Jan 18 '12 at 16:36
    
@AaronBrown your link is a good one since real statistics must be compiled under certain situations. –  RolandoMySQLDBA Jan 18 '12 at 16:38
1  
SHOW INDEX FROM is a metadata operation and actually causes the index statistics to recompute unless you have innodb_stats_on_meatadata=0. It's a case where observation changes the behavior. I recommend turning innodb_stats_on_metadata off in most cases. –  Aaron Brown Jan 18 '12 at 19:06
    
For the first example, you can use RENAME TABLE which will move tables atomically: RENAME TABLE watchdog TO watchdog_zap, watchdog_new to watchdog –  Romuald Brunet Feb 22 '12 at 11:32
add comment

Space isn't normally removed from the InnoDB files

To reclaim space, you have to OPTIMIZE TABLE which creates temporary tables/files and switches them. Or otherwise force this process via another operation using a temporary table/DROP+CREATE/CREATE TABLE..LIKE etc

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.