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:
---TRANSACTION 17402D749, ACTIVE 15867 sec, process no 19349, OS thread id 1630148928
MySQL thread id 188790, query id 14796224615 host.domain.com 127.0.0.1 root
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:
mysql> select * from information_schema.innodb_rseg;
| rseg_id | space_id | zip_size | page_no | max_size | curr_size |
| 0 | 0 | 0 | 6 | 4294967294 | 134528 |
1 row in set (0.00 sec)
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:
History list length 4000567
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:
Trx read view will not see trx with id >= F58464, sees < F58462
but whatever isolation mode you’re using beware of long running selects as they prevent purging activities in any isolation mode.