Fix of InnoDB/XtraDB scalability of rollback segment

Recently I wrote about InnoDB scalability on 24-core box, and we made research of scalability problems in sysbench write workload (benchmark emulates intensive insert/delete queries). By our results the problem is in concurrency on rollback segment, which by default is single and all transactions are serialized accessing to segment.
Fortunately InnoDB internally has mechanism to support multiple rollback segments – and Yasufumi just made patch to enable it.

I rerun benchmarks on different server (Dell PowerEdge R900, 16-way Intel Xeon, 32GB of RAM, RAID 10 on 8 disks) to compare mysql-5.1.30-XtraDB-1.0.2-pre-release3 with default (1) and 16 rollback segments.

For reference
MySQL parameters:

sysbench parameters

Share this post

Comments (9)

  • peter


    How stable is this multiple rollback segments code ?

    Also how flexible is this variable ? Can it be set to any value as a startup option or you need to set it before Innodb tablespace is created ?

    I also assume you use this option you can’t run unpatched Innodb on same data files any more, right ?

    January 18, 2009 at 6:10 pm
  • Yasufumi


    InnoDB already has multiple rollback segments code (but not used).
    It seems to be stable at least while we use the number of segments statically.
    So I implemented this parameter as create_new_db only parameter currently.
    (the value must be 0 ~ 255)

    We may be able to use the datafile created by this option for unpatched InnoDB also, because it uses InnoDB’s unused code.

    January 18, 2009 at 6:40 pm
  • C.J.

    Wow. I like the red line more. 🙂

    January 18, 2009 at 9:12 pm
  • dim

    Did already you integrate this patch into XtraDB source?
    and if no – when?..


    January 20, 2009 at 6:27 am
  • Vadim


    It is not included yet, we are testing this. Along with this we are testing fix for group-commit.
    Should be included in XtraDB-release3 in few weeks.

    January 20, 2009 at 10:19 am
  • paul

    I realize the innodb functionality already exists, and it “appears” to work when enabled, but given the critical nature of this data structure is there any information available publicly for why the innodb owners haven’t already done this? I failed to find any when just looking. If there is any chance of it reducing the ability for recovery or proper transaction rollback I would be very unlikely to use a build including it. Apologies if I sound like I’m putting it down, on the flip side, the results are quite impressive.

    January 22, 2009 at 12:37 am
  • Baron Schwartz

    Paul, the InnoDB owners generally decline to say why they do or don’t do anything, and give no information about what they’re working on until after they release it. And though their developers are very very smart, and they write good code, they are also insulated from real world needs. Sometimes there’s a serious need for something in the real world and they don’t see the need for it, so it doesn’t happen even after years of asking.

    January 22, 2009 at 6:37 am
  • Shirish


    Peter has a point regarding unpatched innodb compatibility.
    My group has recommended may times in increasing the rollback segments, but my concern is that in a mixed server environment (where some servers have patched innodb and some don’t) the user will not be able to move a data files from a crashed (patched) innodb server to an unpatched innodb server and recover. An the unpatched code will not recover from the new segments defined.
    Also, giving my.cnf parameter to control segments gives some flexibility but also increases risks if the user didn’t backup my.cnf file then they will not be able to recover (unless you store this somewhere in system space as well).

    These may be rare cases but a disclosure that goes along with this feature to educate user on this will be helpful.

    January 25, 2009 at 11:25 am
  • Mark Callaghan

    Just ran into a pileup created by contention on the rollback segment mutex. The thread holding it is blocked on disk IO to read a page from the rollback segment. It doesn’t give up the rollback segment mutex while waiting for IO to complete. That won’t scale. Thanks for finding this.

    February 25, 2010 at 1:32 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.