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


-----Original Message-----
From: Johan De Meersman [mailto:vegivamp@tuxera.be] 
Sent: Thursday, November 17, 2011 1:28 AM
To: Rozeboom, Kay [DAS]
Cc: mysql@stripped
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
> log?

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
Thread
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