ibdata grows exponentially when file-per-table is configured using InnoDB

[Thomas A. Lekai]

Chris,

 

Have you ever heard of this?  I am working with a client that has some crazy growth in their ibdata files, even though they have configured file-per-table option.  They are using a Percona patch, and my gut is telling me that something is off on the binaries that they are using from Percona.  Let me know if you ever heard of this.

 

Regards,

 

Thomas.

[Sarah Sproehnle]

Hi Thomas,

I haven't used the Percona patches, but I have seen similar issues with the stock InnoDB. The UNDO log entries can get written to the ibdata file(s), even though you may be using innodb_file_per_table. It is possible that there are large transactions that create lots of undo log entries. It is also possible that the purge thread is not cleaning them up in a timely fashion.

[Thomas A. Lekai]

I hear you, and I am actually reading this is normal. I think what is going on is that my client is using a very large innodb log file size (2 GB), and once it flushes, it flushes in a big way. I also read that InnoDB still uses the ibdata files for shared tablespace and internal data dictionary.

Thanks for the quick response . . .

[Morgan Tocker]

To clarify the impact of the patches: there are two Percona patches which apply here - but both are disabled by default.

One is to increase the number of undo slots so that you can have more concurrent transactions:
http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_expand...

(If previously you didn't have the same level of concurrency, then it's completely conceivable with a patched version you can 'bloat' in shared tablespace size).

The second patch is to increase the number of rollback segments (to reduce mutex contention). I don't know whether this would incur any additional space overhead:

http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_extra_...

[Thomas A. Lekai]

Just wanted to post a small follow-up.

When my client originally reported the issue, they were using one ibdata file, and files-per-table was enabled. Their log was growing at 3 GB per day. The fix that I came up with was to use 3 (2GB) ibdata files, and the client decided to change their InnoDB isolation level. The last I heard, their space did not grow past the 3 2GB ibdata files.

Thanks for all the other info . . . will keep on file.