October 24, 2014

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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Yingkuan says:

    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.

  2. Steven Reimer says:

    Where/how is the transaction history stored?

  3. “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.

Speak Your Mind

*