0

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.

In the MySql workbench, when I query for the databases used space, with this query

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Data     Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema;

It says that I have 47 GB of data. However the size of ibdata1 is 99 GB...

I know that ibdata1 contains a bunch of other things other that table data, like Table Indexes, MVCC (Multiversioning Concurrency Control) Data and Table Metadata

So my question is: Is it normal that supposedly 52 GB of ibdata1 is medatada and a bunch of other things? Usually, how much data beside table data should the ibdata1 file contains?

Mathieu
  • 1,638
  • 2
  • 27
  • 50

2 Answers2

2

No, it is not normal that you would have that much metadata. It is normal though that the ibdata file can grow to a ridiculous size if you aren't using innodb_file_per_table.

Your ibdata file will grow when your database grows, but it will never actually shrink.

So, for example, if you had 130 GB of data at one point and deleted a bunch of it, the ibdata file would still be 130 GB after the data was purged. It will just have a bunch of "free space" that it will then use for subsequent inserts.

As for shrinking the file, there's not much you can really do aside from wiping out your database and restoring it. This answer has some good instructions on how to do that.

Howto: Clean a mysql InnoDB storage engine?

You also might want to consider using innodb_file_per_table as deleting data from a table and later optimizing that table will actually shrink the size of the individual table files

Community
  • 1
  • 1
Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • `innodb_file_per_table` can introduce performance penalties (it did for me and for several other people). While I agree with your answer (+1 from me), I just wanted to comment that turning `innodb_file_per_table` can cause performance issues (more likely - WILL cause them under heavier load). – N.B. Jan 07 '13 at 15:47
  • @N.B. - I agree, it certainly can, but it can also improve performance if you have multiple disks and symlink the table files to different spindles. So it's a mixed bag in that regard. – Eric Petroelje Jan 07 '13 at 15:49
  • Thanks for the answer and comments. Any idea how to explain the big difference of space (47GB of data VS a ibdata1 file size of 99GB)? Is my sql query alright? I also often delete millions of rows in different tables to free space. Could that be a problem? – Mathieu Jan 07 '13 at 16:51
  • 1
    @Tigel - deleting rows is exactly the problem. The problem isn't that you are deleting the rows though, it's that the ibdata file doesn't actually shrink when you remove them. – Eric Petroelje Jan 07 '13 at 17:27
2

There are a few reasons for having a bunch of "extra" space in ibdata1, but the most likely cases are:

  • You have deleted large amounts of data in the past. When you delete rows or drop tables, although free space will be made available in the file, the file itself will never shrink.
  • You may have an excessive amount of undo log space (or have at some point in the past). Undo logs are kept during DELETE and UPDATE operations, and for very long-running operations touching many rows can grow quite large. Again, if the file is expanded to hold this data it will never shrink.

As previously mentioned using innodb_file_per_table can help with this if you expect to regularly drop tables and want to get the disk space back. My blog post The basics of InnoDB space file layout may help you understand what is included in the ibdata1 file.

jeremycole
  • 2,741
  • 12
  • 15
  • Thanks for the explanation. When the delete operation is completed, does the undo log space get flagged as available and can be used for table data or does it stay forever reserved for future undo operations? – Mathieu Jan 08 '13 at 13:28
  • It is automatically made available for reuse once the transactions that required it are completed. However, it can take a bit of time before the space is totally freed. – jeremycole Jan 08 '13 at 16:06