Find innodb undo size

Question

I'm using MySQL 5.5.33 on RHEL. I have innodb_file_per_table enabled but in the ibdata1 is rather large. I've hypothesised that it's from me running a consistent backup meaning the UNDO space has grown. Is there anyway to confirm this? I'm not finding anything in information_schema that could lead me to this - ideally I'd like a break down of the sizes in ibdata1.

Any ideas?

Answer 1

all of My colleagues and My friends and me had tried how to figure out this. But we couldn't find anything about it. The only thing I know (you know) is mysqldump and delete all innodb files & restore.

MySQL Performance Blog gives us important 2 stories.

1. check what is being stored in the ibdata1

Unfortunately MySQL doesn’t provide information of what is being stored on that ibdata1 shared tablespace but there are two tools that will be very helpful. First a modified version of innochecksum made by Mark Callaghan and published in this bug report.

2. Is there any way to recover the used space?

No, it is not possible at least in an easy and fast way. InnoDB tablespaces never shrink… see the following 10-year old bug report recently updated by James Day (thanks):

OP has solved his question using the modified innochecksum script. excerpted from his comment.

I ended up using the modified innochecksum script and it worked on the live ibdata1 file. Turns out I was right, mostly UNDO with a large portion of insert buffers too