Buy Percona ServicesBuy Now!

Fix of InnoDB/XtraDB scalability of rollback segment

 | January 18, 2009 |  Posted In: Benchmarks, Percona Software


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

Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.


  • Vadim,

    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 ?

  • peter,

    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.

  • Dim,

    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.

  • 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.

  • 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.

  • Vadim,

    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.

  • 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.

Leave a Reply