Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have deleted several GB of MySQL data, but it has not been returned to the filesystem. My disks are getting full so how do I do that? All the data is in the ibdata1 file since it's InnoDB.

I have tried mysqloptimize -A -o but it did not help.

Edit: Server version: 5.0.51a-24+lenny2+spu1 (Debian)

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

since you seem to have monolithic innodb file - you cannot shrink it.

if you can afford downtime do as follows:

  1. stop mysql.
  2. take a file-based backup of all files / directories from mysql data directory [ including ibdata and innodb log files ].
  3. start mysql, run mysqldump -uroot -ppass -A > file.sql
  4. stop mysql again
  5. reconfigure mysql to use file-per-table for innodb [ add to my.cnf innodb_file_per_table ]
  6. remove innodb ibdata / log files / directory with your database
  7. start mysql and recover data from your backup from point #3 - cat file.sql|mysql -uroot -ppass

in the future you will be able just to backup & drop single table and restore it afterwards - thanks to file-per-table option.

share|improve this answer
add comment

When you put data in a table that is allowed to expand, it increases in size. When you DELETE data from the table, that size remains the same.

There are a couple of things you can do:

1) ALTER the table, like Warner suggested, to reduce the size.

2) OPTIMIZE the table (if this is supported). That should also reduce the size.

3) Back up the table, drop the table, then re-import the data into a new table.

In every case, you should back up the data first. You should run a cronjob to do optimize or alter periodically, if you remove data on a regular basis. That remains true whether you're running MySQL, MSSQL, or Oracle. They all require maintenance.

@Warner: Well, mostly preference. I'll preface this by saying I don't use MySQL for anything where performance is a factor, and if I did, I'd optimize, then alter the table to make it larger so there isn't an expansion penalty.

For my needs it's 100% about automation and ease of maintenance. Running optimize keeps everything neat and tidy.

share|improve this answer
    
I'm not sure I'd agree with you on regularly running optimization against InnoDB tables. In fact, there's performance advantages to preallocating the InnoDB space. With MyISAM, it makes more sense. Do you have something to support this practice or is it your personal preference? –  Warner Mar 26 '10 at 21:07
add comment

If you have innodb_file_per_table enabled, following the instructions below. However, if this was not already in use, your schema will need to be recreated in full for the space to be freed.

With InnoDB, you need to alter the table. Newer versions of MySQL also perform the same function with optimize table as well.

Be careful with large tables, as these commands lock the table.

share|improve this answer
    
I tried "alter table history;". Is that what you meant? There was no effect. –  Thomas Mar 26 '10 at 20:16
    
ALTER TABLE table ENGINE = InnoDB; –  Warner Mar 26 '10 at 20:25
    
If you need to alter lots of tables this answer may be helpful. serverfault.com/questions/124797/… –  Zoredache Mar 26 '10 at 20:29
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.