2

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)

3 Answers 3

4

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.

after removing the data (step 6) you need to boot with "mysqld --skip-grant-tables", otherwise mysqld will not boot because there aren't grant tables.

1

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.

3
  • I tried "alter table history;". Is that what you meant? There was no effect.
    – Thomas
    Mar 26, 2010 at 20:16
  • 2
    ALTER TABLE table ENGINE = InnoDB;
    – Warner
    Mar 26, 2010 at 20:25
  • If you need to alter lots of tables this answer may be helpful. serverfault.com/questions/124797/…
    – Zoredache
    Mar 26, 2010 at 20:29
1

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.

1
  • 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, 2010 at 21:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .