List:General Discussion« Previous MessageNext Message »
From:Kay [DAS] Rozeboom Date:November 16 2011 8:19pm
Subject:ibdata1 and undo log
View as plain text  
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.

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: 515.281.6139   Fax: 515.281.6137
Email:  Kay.Rozeboom@stripped

ibdata1 and undo logKay [DAS] Rozeboom16 Nov
  • Re: ibdata1 and undo logJohan De Meersman17 Nov
    • how do I make utf8 the default encoding for my MySQL installationMartin Mueller18 Nov
      • Re: how do I make utf8 the default encoding for my MySQL installationDuane Hill18 Nov
      • Re: how do I make utf8 the default encoding for my MySQL installationReindl Harald18 Nov
    • RE: ibdata1 and undo logKay [DAS] Rozeboom18 Nov
      • Re: ibdata1 and undo logJohan De Meersman19 Nov