EmergencyEMERGENCY? Get 24/7 Help Now!

Fix of InnoDB/XtraDB scalability of rollback segment


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

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 Button
PREVIOUS POST
NEXT POST


Vadim Tkachenko

Vadim leads Percona's development group, which produces the Percona Server, Percona Server for MongoDB, Percona XtraDB 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.



Tags:

,

Categories:
Benchmarks, Percona Software


Comments
  • 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 ?

    Reply

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

    Reply

  • Wow. I like the red line more. :)

    Reply

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

    Rgds,
    -Dim

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.

Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.

No, thank you. Please do not ask me again.