Purge Thread Spiral of Death

I just wrote a large post on reasons for innodb main tablespace excessive growth and I thought it would make sense to explain briefly of why it is so frequently you have purge not being the problem at all and when out of no where you can see purge thread being unable to keep up and undo table space explodes and performance drops down. Here is what happens.

When you have typical OLTP system with small transactions your UNDO space is small and it fits in buffer pool. In fact most of the changes do not need to go to the disk at all – the space for undo space is allocated, used and freed without ever needing to go to the disk.

Now when you have spike in writes or long running transactions which increases your undo space size it may be evicted from buffer pool and stored on disk. This is when problems often starts to happen. Now instead of purge thread simply operating in memory it has to perform IO which slows it down dramatically and makes it unable to handle amount of changes coming in.

The solution to this problem may range from pacing the load (which is helpful for batch job operations as it can be controlled), using innodb_max_purge_lag or enable separate purge thread (or threads) via innodb_use_purge_thread if you’re running Percona Server.

Share this post

Comments (5)

  • Mark Callaghan

    When will Percona support multiple purge threads? The docs claim that more than one is experimental — https://www.percona.com/docs/wiki/percona-server:features:innodb_purge_thread

    February 14, 2011 at 11:08 am
  • Peter Zaitsev


    I think the point is just it needs more broad user testing. It seems to be working but we have not seen workloads where one would not be able to keep up so we would really need 2 threads to keep up

    February 18, 2011 at 2:36 pm
  • Claudio Nanni

    is there anyway to control the ‘UNDO space’ size in the buffer pool?
    What’s the status of ‘multiple purge threads’?
    Is it a feature of Percona Server or of XtraDB?

    October 27, 2012 at 2:09 pm
  • Peter Zaitsev


    No. Undo pages are just pages when it comes to caching. There is no way to prefer it compared to other data.

    October 27, 2012 at 5:10 pm
  • Laurynas Biveinis

    Claudio –

    Multiple purge threads is a feature of Percona Server 5.1 (but not 5.5 as of this time) and MySQL 5.6.

    October 28, 2012 at 2:41 am

Comments are closed.