We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am able to reclaim space from big table with a ALTER TABLE mydb.mytable ENGINE=InnoDB.

Hopefully that works, but I don't know table will return useful unused space a with table will not. I mean some big table have sometimes NO useless space, and some have a lot, depending how much DELETE have been done (I guess). Is there a way to know in advance how much space is allowed for deleted record so I can run ALTER TABLE mydb.mytable ENGINE=InnoDB only on table with lot of useless space.

2 Answers 2


After digging the question, I went with a SELECT like this one:

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb' AND DATA_FREE > 0 ORDER by DATA_FREE DESC;

So I have a list of table to process.

a scripted form would be :

#! /bin/bash
[ -z "$1" ] && echo usage "$0 [database_name]"
[ -z "$1" ] && exit 1
for T in $(mysql -ss -e "SELECT table_name from information_schema.tables WHERE table_schema = \"$1\" AND DATA_FREE > 0 ORDER by DATA_FREE DESC;");do echo \"$T\";time mysql -e "SET sql_log_bin=0;alter table $1.$T ENGINE=InnoDB";done 2>&1
  • 1
    Beware of any innodb table without a separate file, running alters on it might make the ibdata1 file grow and that one cannot be shrunk without complete rebuild of the mysql instance. You might better just invest into bigger storage and just keep the free space for later inserts if you are running some insert-delete cycles anyway.
    – jkavalik
    Commented Jun 7, 2016 at 6:35
  • even in that case ibdata1 is still growing. (see bug #1341) and that should not happend with innodb_file_per_table anyway.
    – dominix
    Commented Jun 8, 2016 at 20:25
  • the application are made such a way that there could be some big delete in a table and big insert in others. In that case I need to claim lost space. I have no other issue than ALTER TABLE. (or by new storage of course, but it's not my question)
    – dominix
    Commented Jun 8, 2016 at 20:27

That ALTER will copy the table over, requiring briefly enough space for a copy of the table.

InnoDB does a pretty good job of keeping its BTrees somewhat free of wasted space. Hence the action won't do much good.

Be sure that innodb_file_per_table is still on; otherwise you will be moving the table into ibdata1, which is quite hard to shrink.

Certain tables will always have Data_free > 0, so you are generally wasting effort on them. I suggest you add LIMIT 10 to your select.

  • the LIMIT 10 is a good idea, no need to process all the table, but I confirm that a lot of space has been recovered. I have no doubt about the efficiency of InnoDB, I am sure our script launched time to time put some mess in the data so finally ALTER TABLE Xx Engine=InnoDB is a safe trick
    – dominix
    Commented Jul 8, 2016 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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