Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

Question

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5

We have always(I am sure)

innodb_file_per_table = ON 

option in our mysql config. So I drop big table with confidence that file that represent this table will be deleted and disk space will be freed immediately, seems to be this file was deleted(this file not exist in DB directory in datadir, all other DB tables files exist), but in munin(disk usage) graph I don't see that disk space get free(my bad that I didn't notice disk usage before dropping table). Also ibdata1 seems to be is quite big for this situation(~ 200 GB).

1. There is way to get some additional info about old table (that was dropped) ?
2. There is some way to reduce disks usage without dumping and then restoring DB, maybe create new table with old_name(that was dropped) and try to make ALTERING ?

UPDATE:

1) ~100-110GB, or 20% of total disk space(ssd disk)

2) files_ | 124151382016

3) 10 tables, but only 1 is big table

4) Prehistory: we had big table(busy, inserts/updates/reads) that continue growing up, but some data in this table became old, and must be deleted, so we run delete queries in infinity loop( DELETE FROM files WHERE condition LIMIT 1000-100000, I don’t know how much rows were deleted with this method, but Innodb_rows_deleted was approx. ~600kk, and Innodb_rows_inserted nearly 1 billion or so) we deleting at night when load is very low — after we calculate that deleting takes too much time, so we use persona pt-archiver to move(only data that we need) from old table to new table(files_), and then DROP old table.

Yes, old one table had key constraints to separate table — “ON DELETE CASCADE ON UPDATE CASCADE”(in new table we have not constraints), also was in old table(and in current table)UNIQUE KEY on some table field. I am also now making backup with xtrabackup of current db to check what takes much space in ibdata1 file with innodb_space app. Seems to be only 1 possible way — to make logical backup, and then import, sql file is 40GB, so it would be not fast despite that server pretty powerful(128GB ram).

Thank you!

Answer 1

I would recommend you to run the following

mysqlcheck -u ROOTUSERNAME -p --auto-repair --optimize DBNAME_HERE

This will prompt for MySQL Root Password, type in and it will reclaim the disk space. Time taken for this process will depend on the size of the database. You may try this first in your test env and then proceed.