Thanks for the response, Johan.
It would really help if I could determine when the ballooning is occurring. Do you know of any way to do that?
From: Johan De Meersman [mailto:email@example.com]
Sent: Thursday, November 17, 2011 1:28 AM
To: Rozeboom, Kay [DAS]
Subject: Re: ibdata1 and undo log
----- Original Message -----
> From: "Kay Rozeboom [DAS]" <KAY.ROZEBOOM@stripped>
> 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
I'm not too hot on the InnoDB internals, but yes, the undo log is one possibility for the ballooning of your tablespace. If you have huge transactions (or huge amounts of concurrent ones), that's the likely culprit. Cutting down transaction size (if possible) will help.
Another possibility is automated maintenance jobs from your application. I found that Cacti, for instance, has the rather annoying tendency to optimize it's tables every night, which is a bit of a bugger if you have a few multi-gigabyte tables.
You might benefit from innodb-file-per-table; that way your actual tablespace gets separated from the metadata and undo logs, giving you a much clearer view of what exactly is ballooning. Obviously, as discussed many times before, that's going to require a full export/import to be useful, though.
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel