Free Space in MySQL After Deleting Tables & Columns

MySQL InnoDB not releasing disk space after deleting data rows from table

MySQL doesn't reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later.

An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file for the table is reduced after an optimize table.

Free space in MySQL after deleting tables & columns?

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.

How to reclaim disk space in InnoDB after dropping table

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.

Freeing up space from MyISAM table after deleting data

Copy the remaining data to a new table, then drop the old table. That will reclaim the storage.

How to reclaim MySql disk space

Edit: It turns out from comments below that the user's binary logs are the culprit. It makes sense that the binary logs would be large after a lot of DELETEs, and assuming that the MySQL instance is using row-based replication.

The answer is complex.

You can save space by using NULL instead of real values. InnoDB uses only 1 bit per column per row to indicate that the value is NULL (see my old answer to https://stackoverflow.com/a/230923/20860) for details.

But this will just make space in the page where that row was stored. Each page must store only rows from the same table. So if you set a bunch of them NULL, you make space in that page, which can be used for subsequent inserts for that table only. It won't use the gaps for rows that belong to other tables.

And it still may not be reused for any rows of your mediumblob table, because InnoDB stores rows in primary key order. The pages for a given table don't have to be consecutive, but I would guess the rows within a page may be. In other words, you might not be able to insert rows in primary key random order within a page.

I don't know this detail for certain, you'd have to read Jeremey Cole's research on InnoDB storage to know the answer. Here's an excerpt:

The actual on-disk format of user records will be described in a future post, as it is fairly complex and will require a lengthy explanation itself.
User records are added to the page body in the order they are inserted (and may take existing free space from previously deleted records), and are singly-linked in ascending order by key using the “next record” pointers in each record header.

It's still not quite clear whether rows can be inserted out of order, and reuse space on a page.

So it's possible you'll only accomplish fragmenting your pages badly, and new rows with high primary key values will be added to other pages anyway.

You can do a better effort of reclaiming the space if you use OPTIMIZE TABLE from time to time, which will effectively rewrite the whole table into new pages. This might re-pack the rows, fitting more rows into each page if you've changed values to NULL.

It would be more effective to DELETE rows you don't need, and then OPTIMIZE TABLE. This will eliminate whole pages, instead of leaving them fragmented.

Does the space occupied by deleted rows get re-used?

It is paranoia :)

DB's don't grow in size unnecessarily, but for performance issues space is not freed either.

What you've heard most probably is that if you delete records that space is not given back to the Operating System. Instead, it's kept as an empty space for the DB to re-use afterwards.

This is because:

DB needs to have some HD space to save its data; if it doesn't have any space, it reserves some empty space at first.
When you insert a new row, a piece of that space is used.
When you run out of free space, a new block is reserved, and so on.
Now, when you delete some rows, in order to prevent reserving more and more blocks, its space is kept free but never given back to the Operating System, so you can use it again later without any need of reserving new blocks.

As you can see, space is re-used, but never given back. That's the key point to your question.

mysql table size unchanged after rows deleted

Did you try to optimize it, to save disk operation the file is not optimize on every delete and hence the "Table" size remain unchanged.

You need to optimize it and sometime Re-index can also reset the table size.

When is disk space made available again after dropping a mySQL table?

It depends on whether you enabled the innodb_file_per_table server setting or not.

If innodb_file_per_table is enabled, then each table stores its data in a separate .ibd file. Otherwise the data for all tables is stored in a shared .ibd file.

With innodb_file_per_table:

When you drop a table the .ibd file is deleted and the disk space is immediately freed.
When you delete records it frees up space inside the .ibd file, but does not shrink it. Rebuilding the table (such as by running OPTIMIZE TABLE) will free up the disk space.

Without innodb_file_per_table:

Dropping a table or deleting rows from a table frees up space in the shared .ibd file to be used to store future data, but the shared .ibd file will never shrink