•  

Announcement

Announcement Module
Collapse
No announcement yet.

ibdata file size

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • ibdata file size

    #1
    I have a ibdata file and i have 3 databases. In one of them i had tables with size around 10 GB, each one.

    Now i made a truncate for both tables but the space in the ibdata file never shrinks.

    I ran a optimize table, alter table and the ibdata size looks the same.

    How can i shrinks ibdata file without make a dump a restore again?

    Thanks for your help.
    Tags: None
  • #2
    You can't shrink an InnoDB tablespace.

    You will have to dump/restore to reclaim the space:
    0) dump mysql database
    1) shut down mysql
    2) move away any InnoDB data and log files
    3) start mysql and import database


    If you want to reclaim space on drop/truncate in the future, then you will need to use --innodb-file-per-table:
    http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces. html

    --Swany

    Comment

    • #3
      Hi Justin,

      Thanks for your answer.

      is this issue considered as a bug? Why the space is not eliminated?

      the only solution is to apply the steps that you give me?. Other alternative?

      Thanks,

      Comment

      • #4
        gjuan wrote on Thu, 12 August 2010 22:47

        Hi Justin,

        Thanks for your answer.

        is this issue considered as a bug? Why the space is not eliminated?


        No, it's not considered a bug, it's just that there is no feature implemented to shrink the innodb table space.

        And I'm guessing one of the main reasons why it hasn't been implemented is that this tablespace contains a lot of tables and to reclaim space you would first have to perform a sort of defrag on the entire tablespace so that you rearrange all alive data at the beginning of the file and then you should be able to reduce the size of the file.
        And this is _not_ something that you would like to perform on a live server since it would probably have to lock the entire tablespace during this time.

        Hence you need to bring the server down anyway and then you can use the "backup - delete tablespace - restore backup" routine.

        gjuan wrote on Thu, 12 August 2010 22:47


        the only solution is to apply the steps that you give me?. Other alternative?


        No

        That said, would it be nice to have such a feature?
        Yes it would! But if you are using the innodb-file-per-table that Swany mentions the problem more or less goes away so the necessity of it is still open for debate.

        Comment

        • #5
          Thanks sterin for your answer and help.

          Comment

          • #6
            Keep in mind that the global InnoDB tablespace (ibdata1.ibd) is still utilized by InnoDB even when --innodb-file-per-table is used.

            It will be used for the:
            * insert buffer
            * doublewrite area
            * data dictionary
            * rollback segments

            If you use long transactions and have a high update rate you might still see idbata1 growth.

            Comment

            Working...
            X