September 23, 2014

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

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. peter says:

    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 ?

  2. Yasufumi says:

    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.

  3. C.J. says:

    Wow. I like the red line more. :)

  4. dim says:

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

    Rgds,
    -Dim

  5. Vadim says:

    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.

  6. paul says:

    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.

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

  8. Shirish says:

    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.

  9. Mark Callaghan says:

    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.

Speak Your Mind

*