April 20, 2014

Undo area size restriction needed for Innodb

As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can cause a troubles as it happened for me today:

InnoDB: 11 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 292735956 row operations to undo
InnoDB: Trx id counter is 0 96267520

So MySQL Server was restarted (it likely was admin mistake in this case) and spend hours to undo almost 300.000.000 of row operations being unavailable during all of this time.

This was MySQL 4.1, MySQL 5.0 would do better by performing roll back in background but affected data still might not be fully available.

Why one would use such large transaction ? Well it was development mistake. Long and complex data load process was performed in single transaction. Funny enough Innodb handled it so well no one noticed it until rollback was needed during crash recovery.

The bad thing is – there is no way in Innodb to protect from such runaway transactions. You can find them by looking at SHOW INNODB STATUS however.

It would be great and I expect not so complex to add innodb_max_transaction_undo_rows or something similar which will protect from runaway transactions. Setting this setting to appropriate value system administrators could ensure data recovery can be completed withing limited timeframe and catch development errors. Having is SESSION variable would allow to raise the limit if some large transaction needs to be executed which would violate the limit. It is also possible to use global limit for undo entries but I think it is less valuable.

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. Xaprb says:

    innotop (http://www.xaprb.com/innotop/) shows undo size in the InnoDB Transaction mode. Press capital T to enter this mode, and c to choose columns in case it is not already visible. This is also a good way to watch the progress of long queries; you can see how done the query is in case you know how many rows it should affect.

    I regularly watch transactions this way to see how long they are and how many undo log entries they generate.

  2. peter says:

    Xarb,

    This only shows undo space for active queries which would help in described case but may does not show it in all cases. Imagine for example transaction which is open for a week – it does not do changes itself so its undo entries number is zero. However it also does not allow Innodb to purge undo entries for transactions which have been committed in the meanwhile.

    Such transaction can affect performance a lot, however it does not affect recovery speed as such entries need to be purged rather than rolled back.

  3. Xaprb says:

    That’s true. Another related thing I watch in innotop (this isn’t directly relevant, but my “fuzzy logic” reminds me of it) is old versions kept around. You can do a little subtraction and see how many versions InnoDB is keeping in memory (snapshots created by selecting some rows in default isolation level). Until the transaction commits, InnoDB has to keep the old version in memory to present a consistent view to the transaction, so long-running transactions keep using memory. Like I said not completely relevant, but sort of related.

    (For those who want to use this feature, it is in the header in T mode — press ‘h’ to show the header).

Speak Your Mind

*