Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Free space in MySQL after deleting tables & columns?

I have a database of around 20GB. I need to delete 5 tables & drop a few columns in some other 3 tables.

Dropping 5 tables with free some 3 GB and dropping columns in other tables should free another 8GB.

How do I reclaim this space from MySQL.

I've read dumping the database and restoring it back as one of the solution but I'm not really sure how that works, I am not even sure if this only works for deleting the entire database or just parts of it?

Please suggest how to go about this. THanks.

like image 241
Gaurav Avatar asked Dec 02 '10 18:12

Gaurav


People also ask

Does drop table free up space MySQL?

drop command will free disk space automatically. Note: Assuming you are using innodb_file_per_table as you are able to free space by optimize table syntax.

Does delete free space in table?

After you use a DELETE statement in Microsoft SQL Server to delete data from a table, you may notice that the space that the table uses is not completely released.

How do I completely delete a table in MySQL?

To permanently remove a table, enter the following statement within the MySQL shell: DROP TABLE table1; Replace table1 with the name of the table you want to delete. The output confirms that the table has been removed.

How do I shrink a MySQL database?

Run this query: mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED; After running it, you can see that the size of the table has reduced from 26 MB to 11 MB due to the compression. By compressing the tables, you can save much disk space on your host.


2 Answers

From the comments, it sounds like you're using InnoDB without the file per table option.

Reclaiming space from the innodb tablespace is not generally possible in this mode. Your only course of action is to dump the whole database, turn on file-per-table mode, and reload it (with a completely clean mysql instance). This is going to take a long time with a large database; mk-parallel-dump and restore tools might be a bit quicker, but it will still take a while. Be sure to test this process on a non-production server first.

like image 168
MarkR Avatar answered Oct 05 '22 11:10

MarkR


EDIT: Doesn't apply without file_per_table, Mark is right there.

What's going on is that once MySQL takes space, it won't give it back. This is so that if you delete 500 rows and then immediately insert 500, it doesn't have to give that space back to the file system and then request it back. It's an optimization to avoid filesystem overhead, and it works well when you delete little bits.

If you delete a large amount, it will take a long time to end up using all that space again, which can be annoying. This can be fixed two ways: dropping the table and reloading the contents, or optimizing the table (which I believe basically reloads the table internally).

All you have to do to get space back from a table is:

OPTIMIZE TABLE my_big_table;

Note that this can take a while, it's not a near instant operation. Basically, plan for a some downtime. If your tables are just a few gigs, it shouldn't be too long (probably a few minutes). This also rebuilds the indexes and does some other housekeeping.

You can see more about optimize on the MySQL site. Here is it's advice:

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

like image 25
MBCook Avatar answered Oct 05 '22 09:10

MBCook