ibdata1 and undo log

MySQL & MariaDB Other MySQL® Questions
1

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. I don’t think there is any point in doing that until I understand what is happening here.

2

Have you seen these?

http://www.mysqlperformanceblog.com/2010/06/10/reasons-for-r un-away-main-innodb-tablespace/
http://www.mysqlperformanceblog.com/2011/01/12/innodb-undo-s egment-siz-and-transaction-isolation/
http://mysqlha.blogspot.com/2011/08/this-percona-patch-saved -me.html

3

Yes, and they were very helpful in pointing me in what I hope is the right direction, i.e. that the additional space was allocated for the undo log.

I am still wondering about the many 1-page segments. I have not been able to find any information about undo log internals that would explain whether that is what they are.