To shrinking a shared InnoDB tablespace is:
1. Backup *all* InnoDB tables with mysqldump.
2. Drop all of the InnoDB tables.
3. Physically delete the ibdata1 file at the filesystem interface.
4. Restart MySQL Server, which recreates a new, small tablespace file.
5. Restore all your InnoDB tables, which expands the tablespace file as needed.
To shrink an .ibd file it"s enough to run ALTER TABLE t1 ENGINE INNODB
. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE
does exactly the same internally. ALTER TABLE
however is preferred because you can use pt-online-schema-change to avoid blocking the table.
To get better understanding how data use the tablespace I would recommend InnoDB tools https://github.com/jeremycole/innodb_ruby
For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:
# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate
Start Page ╭────────────────────────────────────────────────────────────────╮
0 │███▄▂░░ │
╰────────────────────────────────────────────────────────────────╯
Legend (█ = 1 page):
Page Type Pages Ratio
█ System 3 42.86%
█ Index 15 1 14.29%
█ Index 16 1 14.29%
░ Free space 2 28.57%