#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103

    INNODB storage engine - reclaim space


    Hello everyone,

    Currently we are using MySQL 5.0.51a version and also using the INNODB storage engine. Now if we drop a table from the tablespace, can you reclaim that space.

    In Oracle one can Coalesce a tablespace after a large table has been dropped from a tablespace. Thus the system reclaims the space. I understand currently at least with the version that we are on that when we drop a table from INNODB, that the data is dropped and reusable but that the tablespace does not shrink down in size after the table has beed dropped. Is there any way to do this, except taking a export and dropping the whole schema and reimporting it back into a new tablespace?

    Thoughts or advice here? Also is there any other commands or ways to do this in either 5.1 or 5.5 and what about 5.6 Beta Test. Also is there actually a 3rd Storage Engine (yet unknown name) on the future as well, or a change to INNODB that would allow this?

    Thanks in advance...

    Penn
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Note: that it is our intent here shortly to Upgrade our shop to Enterprise edition 5.5 Now it looks like one can Coalesce a tablespace within that version, is that true? Are there any bugs or how exactly does it work?

    Thanks...
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    Is there any way to do this, except taking a export and dropping the whole schema and reimporting it back into a new tablespace?
    Not unless you enabled innodb_file_per_table before you created your tables (it's off by default).

    Also is there actually a 3rd Storage Engine (yet unknown name) on the future as well, or a change to INNODB that would allow this?
    I'm not aware of any plans for a 3rd storage engine, although it's not something I pay a lot of attention to. There is an open feature request (http://bugs.mysql.com/bug.php?id=1341) to add this feature to InnoDB; don't hold your breath though, it's already been open for 9 years. You can set innodb_file_per_table, but of course that isn't retroactive.

    Note: that it is our intent here shortly to Upgrade our shop to Enterprise edition 5.5 Now it looks like one can Coalesce a tablespace within that version, is that true? Are there any bugs or how exactly does it work?
    I don't know
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    You could do a DB migration as part of the 5.5 maintenance window. Depending on data size, it could be a non starter, but that would be the time to get to file_per_table.

    I'd want a sql dump anyway besides a binary dump before a major update.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Haifa, Israel
    Posts
    17
    Rep Power
    0

    RE: INNODB storage engine - reclaim space


    Hey @ByGoneYrs,
    In InnoDB there is a single tablespace by default and all data is stored in only one file. The innodb_data_file_path parameter defines the file. In order to reclaim space on InnoDB storage engine, you pretty much have to first export all and re-import:
    1. First, of course, backup all your InnoDB tables and export.
    2. Change the setting in my.ini/my.cnf as innodb_file_per_table file. There are two options you can try:
    a. One option provided by InnoDB is having separate files per table, where the data and indexes of each table can be stored in a separate file using a global variable innodb_file_per_table.
    b. Anothe r option is having a fixed tablespace size, which is done by removing autoextend and setting the tablespace size to an extrapolated value. When the limit is reached, you need to clean data.
    3. Restart your MySQL server and import the reconfigured tables.
    Each of the tables should have its own tablespace and it should be able to can shrink when data is deleted.
    Note: There is another option, which is moving all tables, or even whole databases, that contain non-crucial data to MyISAM.
    It does require some fiddling, but I hope this helps you a bit
    Ronny
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    ..that contain non-crucial data to MyISAM
    This will cause more problems than it is worth on top of going from transactional to non-transactional storage engine and a much worse locking mechanism.

    Mysql 5.6(beta) expands innodb-file-per-table to allow you to put each table file in its own directory(could be different disk). This will allow much more flexibility with innodb and is a long waited for enhancement. You could prepare for this by going file per table in advance.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Originally Posted by shumifan50
    This will cause more problems than it is worth on top of going from transactional to non-transactional storage engine and a much worse locking mechanism.

    Mysql 5.6(beta) expands innodb-file-per-table to allow you to put each table file in its own directory(could be different disk). This will allow much more flexibility with innodb and is a long waited for enhancement. You could prepare for this by going file per table in advance.
    I'm also excited to play with InnoDB full text search in MySQL 5.6. I have a lot of MyISAM tables due to this fact..

    I also sat in on a presentation on using Sphinx during Percona Live NYC last week, and I plan on learning, testing, benchmarking, and possibly integrating that into our forums.

IMN logo majestic logo threadwatch logo seochat tools logo