Shrink InnoDB (System-)Tablespace

[shinguz]

As discussed with Valeriy K. today it would be nice to have the possibility to shrink InnoDB System-Tablespace (or also normal tablespaces) again when they are much underfilled.

Many customers have TS big because of OPTIMIZE/ALTER TABLE and not big UNDO SEGMENTS.

This could be done online or even offline.

ALTER TABLE test RELOACTE TO BOTTOM;
...
ALTER SYSTEM RESIZE TABLESPACE <ts_name> TO 10M;

or OPTIMIZE TABLESPACE <ts_name>;

or offline with a new tool based on xtrabackup?

[Valerii Kravchuk (valerii-kravchuk)]

I think this is a reasonable feature request (see upstream http://bugs.mysql.com/bug.php?id=1341) that can be implemented at least partially with the following tools/approaches/stages:

1. Some tool/command to just truncate free space at the end of ibdata1 (assuming some tables were dropped etc) safely
2. Some tool/command to "shrink" free space allocated for a table (ideally something better than just dump the table, drop it and then create new one), maybe by dropping extents allocated that are entirely "free".
3. Some tool/command to "de-fragment" a table, that is, move data allocated into the smaller number of extents/pages possible.
4. Some tool maybe based on/in frames of data recovery tools to move pages from one extent to the other getting all structures/checksums/pointers etc in sync.

If nothing of the above works easily, maybe just create a tool/script to automate the process of dumping all InnoDB tables, stopping server and deleting ib*, then creating small ibdata and loading tables back. For 5.6 we may move tables to individual .ibd files instead and them re-importing them into newly created ibdata using transportable tablespace feature.

See somewhat related http://bugs.mysql.com/bug.php?id=59783 also.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2438