2

I need your advice. Need to reclaim disk space on live server with minimum downtime. We are using:

mysql -- 5.5
innodb table per file -- on

Was a huge table(70% insers/30% deletes -- it means that sometimes we delete rows from this table), this table was dropped with "drop table" command, as we expect, mysql didn't release disk space to OS, but now we need to release free space. OPTIMIZE command is possible way -- but now we don't 100% sure if we will have enough free space on disk to do this command, because previous table was huge. But in mysql documentation written:

You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one.

I highlighted confusing moment in documentation, does it means, that "OPTIMIZE dropped_table;" will use only space for data that really needed, for this situation there is no actual data if we drop table early ?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
user2746626
  • 73
  • 2
  • 6
  • I think you already dropped the table. Why and how do you optimize it? – Tim3880 May 24 '15 at 14:02
  • yes, it is a fair comment, you are totally right i think, i can't run optimize table on dropped table. But questions same, how reclaim disks space after drop table. – user2746626 May 24 '15 at 14:11
  • This may help http://stackoverflow.com/questions/5983814/reclaim-disk-space-after-drop-database-in-mysql – Tim3880 May 24 '15 at 14:13

1 Answers1

1

If the table was created when innodb_file_per_table was OFF, then the table was written to ibdata1, and the space is not returned to the OS by DROP TABLE.

If it had been ON, then there would be a .ibd file. Did you see that file before the DROP? Does it still exist? (It should not still exist.)

When DROP removes the .ibd file, there is a slight lag (depending on the OS) while waiting for the data to be actually freed up by the OS. du and/or df reflects this lag.

OPTIMIZE TABLE will copy the table to tmpdir (I think), then drop the old table and play RENAME games. This requires extra space -- enough for a compete copy of the table. Assuming you really have a .ibd file, it will create another .ibd file (with a temp name) and shuffle files. If tmpdir points to a different "filesystem", the shuffle will involve a copy, not just a move.

If I have not explained things adequately, please provide more details -- actual commands you are proposing, size of table in question, whether the .ibd exists, etc.

Rick James
  • 135,179
  • 13
  • 127
  • 222