MySQL Forums
Forum List  »  InnoDB

ibdata1 and undo log
Posted by: Kay Rozeboom
Date: November 16, 2011 02:18PM

We are running MySQL 5.0.77, and implemented our first production InnoDB database a few months ago. The database is on a dedicated server and is composed of 14 tables. No tables have been added or dropped since creation.

The “ibdata1” file is growing much faster than the database itself. The ibdata1 file is currently 3.8G. The database takes up 533M. 3.2G of the file is composed of free extents. This matches the “InnoDB free” value.

Here is my understanding of what is stored in the ibdata1 file:
1) User data
2) Tablespace header
3) Data dictionary
4) Double-write buffer
5) Insert buffer
6) Rollback segment, AKA undo log.

I believe that the double-write buffer is always 2M. The insert buffer has been up 6.1M.

By a process of elimination, I think that the undo log (which can expand and contract) must account for the additional space, most of which is currently not in use.

One other mysterious clue: The InnoBD tablespace monitor shows 145 segments of 1 page each. I believe that a handful of these are used for “system” stuff like the data dictionary because I see them in every ibdata1 file. But I do not normally see so many.

Two questions:
1) Can anyone verify that the additional (presently unused) space was allocated for the undo log?
2) Are the many 1-page segments a leftover from a large undo log?

Please note that I am NOT asking how to reclaim the space, which has already been discussed many times. I don’t think there is any point in doing that until I understand what is happening here.

Options: ReplyQuote

Written By
ibdata1 and undo log
November 16, 2011 02:18PM
November 17, 2011 11:25PM
November 18, 2011 08:04AM
November 18, 2011 12:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.