Innodb undo segment size and transaction isolation

You might know if you have long running transactions you’re risking having a lot of “garbage” accumulated in undo segment size which can cause performance degradation as well as increased disk space usage. Long transactions can also be bad for other reasons such as taking row level locks which will prevent other transactions for execution, this however only applies to read transactions while problem with undo space growth exists for read only transactions as well.

This is how long transaction looks:

It was running over 4 hours so Innodb could be preventing purge from happening for long time and there could be a lot of garbage in undo segment. If you’re running Percona Server it is easy to check:

The curr_size specifies current size in pages, so we’re looking at about 2GB of waste in this case.
If not you can check history size in SHOW INNODB STATUS:

Mind however this size is in transactions and so very workload dependent. You may have a lot of waste even with small history size if transactions performed large amount of changes.

There is something else you need to know – not all changes are created equal from their impact on undo space usage. Inserts only have a small record in undo space so row can be deleted on rollback, update and delete however need to put the old row version in undo space and so will require more space, depending on row size. Long blobs (the ones which are not stored on the page) may require less space than you think – only portion of row which is stored on the data page need to be placed in undo space. Blob does not need to be copied as if it is updated the new blog version is placed in the new location. If rollback is to be performed just on-the-page part of the row needs to be recovered from undo space and it will contain correct pointer to appropriate blob version.

Now lets move on to the main topic – how does transaction isolation affects purging ? Innodb is smart and trying to purge data as soon as possible. In default (REPEATABLE-READ) mode Innodb has to maintain versions back to the first data read the oldest transaction has performed because it is possible for that transaction to require state of any row in Innodb table at that point in time. If you use READ-COMMITTED mode Innodb does NOT need to maintain row versions going back to the first data access in transaction, but rather can purge the data after statement is completed. Note long running queries will cause problems in this case anyway, even if you set transaction isolation to lowest READ-UNCOMMITTED level Innodb will not purge the rows from undo space until the statement/query is completed.

As result if you’re dealing with long running transactions consisting of many queries setting isolation mode to READ-COMMITTED can be good idea if your application supports it. Note you can set isolation mode on transaction start so you do not have to just pick one and Innodb is smart enough to figure how far it can purge the data, it even can be seen in SHOW INNODB STATUS:

but whatever isolation mode you’re using beware of long running selects as they prevent purging activities in any isolation mode.

Share this post

Comments (3)

  • Yingkuan

    Frankly I don’t know why MYSQL default transaction isolation to REPEATABLE-READ
    Oracle default to READ-COMMITTED.

    REPEATABLE-READ is too strict and too expensive in term of purging undo segments.

    January 20, 2011 at 9:56 pm
  • Steven Reimer

    Where/how is the transaction history stored?

    December 15, 2011 at 1:27 pm
  • Baron Schwartz

    “Mind however this size is in transactions” — I don’t think this is correct. I think it is the length of the undo log, in pages.

    August 10, 2012 at 7:48 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.