Shrink InnoDB (System-)Tablespace

Bug #1250133 reported by shinguz on 2013-11-11
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Wishlist
Unassigned

Bug Description

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?

Changed in percona-server:
status: New → Confirmed

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.

Changed in percona-server:
status: Confirmed → Triaged
importance: Undecided → Wishlist
tags: added: innodb upstream
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.