MySQL Performance Blog

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.

I’m just back from OpenSource Database Conference and PHP International Conference which took place in Frankfurt.

I’ve uploaded slides for two talks I’ve been giving which you might want to check out.

In general Database portion of the conference was a bit boring. May be because it was not widely announced or may be for some other reason. There were number of talks about MySQL by Arjen Lentz, Me and Giuseppe Maxia . There also were talks about Firebird, Apache Derby, Ingres and DB40. There however were no talks about PostgreSQL which is probably second most popular OpenSource Database or any others.

There were number of nice talks in PHP section - I especially enjoyed talks about PHP6 localization and DateTime handling in PHP 5.2+ I wish there would be some hard core performance optimization sessions for PHP applications which did not exist.

Attending Arjens session about typical MySQL/PHP Mistakes I was surprised how few people were able to catch even rather simple mistakes in my mind. It is encouraging as it means there will be enough work for consultants like us but it is also frustrating as it explains why it is hard to hire knowledgeable people to work with you.

In general it was worth the visit. Too bad the hotel was in the middle of nowhere so I could not see Frankfurt itself.