Introducing percona-patches for 5.1

Introducing percona-patches for 5.1

PREVIOUS POST
NEXT POST

Our patches for 5.0 have attracted significant interest.  You can read about SecondLife’s experience here, as well as what Flickr had to say on their blog.  The main improvements come in both performance gains and improvements to diagnostics (such as the improvements to the slow log output, and INDEX_STATISTICS).

Despite having many requests to port these patches to 5.1, we simply haven’t had the bandwidth as our main focus has been on developing XtraDB and XtraBackup.  Thankfully a customer (who prefers to stay unnamed) as stood up and sponsored the work to move the patches to 5.1.

To refresh, the most interesting patches are:

Two new features which not available for 5.0:

  • In slow.log for Stored Procedure call you can see profiling for each individial query from this procedure, not just
  • With userstat you can get additional THREADS_STATISTICS which show similar information to USER/CLIENT_STATISTICS but per THREAD granularity (it’s useful if you have connection pool)

On this stage the patches are available only in source code, you
can get them from Launchpad https://code.launchpad.net/~percona-dev/percona-patches/5.1.43.  Binaries are also on the way, and will be ready soon. We are running intensive stress testing loads on them to provide stable and quality packages.

And to finalize are results for tpce-like benchmark, where I compare MySQL-5.1.43 vs percona-5.1.43.

The results made for TPCE configuration with 2000 customers and 300 tradedays and 16 concurrent users on our R900 server. The dataset is about 25GB, fully fitting into buffer_pool, so disk does not really matter, but data was stored on FusionIO 320GB MLC card.

On chart with results I show amount of TradeResults transactions per 10 sec during 3600 session (more is better)
tpce-like_2000c_300d

As you see with percona patches you can get just about 10x improvement.
Yeah, that sounds too cool, but let me explain where difference comes from.

As I mentioned in tpce workload details the load is very SELECT intensive and these SELECTS are mainly scans by secondary keys ( not Primary Keys), so it hits problems in InnoDB rw-lock implementations and in buffer_pool mutex contention, which alredy fixed in percona-patches ( and in XtraDB and InnoDB-plugin also).

So you are welcome to try it!

PREVIOUS POST
NEXT POST

Share this post

Comments (21)

  • Shlomi Noach Reply

    To your unnamed customer:
    Well done, and thank you!

    February 9, 2010 at 11:49 pm
  • Harrison Fisk Reply

    What level would you say this patch set is currently, GA or some sort of pre-GA?

    February 10, 2010 at 12:19 am
  • Raine Reply

    Just don’t get it…What’s the difference between Percona patches, XtraDB and the plugin? Aren’t them all the same?

    And how about MySQL 5.1 + Percona patches for Windows? Does it works?

    I’m really confused. Sorry….

    Thanks for the support!

    February 10, 2010 at 12:47 pm
  • Vadim Reply

    Harrison,

    As I said we are doing QA right now, and when it is done, we will consider it GA quality.
    5.1 patches based on 5.0 patches which show stable work for long time already.

    February 10, 2010 at 4:40 pm
  • Vadim Reply

    Raine,

    Ok, let me try to explain, and sorry for confusion.

    The difference comes to fact that InnoDB has two versions, and that is root of all confusions.

    By default 5.1 comes with standard InnoDB, which long time have not had any significant fixes.
    Along with that there is InnoDB-plugin, which was announced 2 years ago, but still is in RC state.

    So Percona has improvements for both of them. Improved version of InnoDB-plugin is XtraDB.
    Patches to standard InnoDB you can get in percona-patches.

    We will prepare document which explains it to avoid more confusions.

    February 10, 2010 at 4:48 pm
  • peter Reply

    Harrison,

    I should mention something about quick time to market for Percona patches. A lot of changes besides Innodb and XtraDB are transparency – logging, monitoring etc. Such code changes do not affect internal logic significantly and generally relatively safe. Plus most of the code is enabled on demand.

    February 10, 2010 at 9:21 pm
  • Shlomi Noach Reply

    Peter (#6),

    Please correct me if I’m wrong (or delete this comment 🙂 ), but another point is that stored data in XtraDB is exactly the same as for InnoDB plugin, which allows for easy change back to InnoDB.

    February 11, 2010 at 2:35 am
  • Raine Reply

    Vadim, thanks a lot for the explanations! It’s all clear now!

    I have one more (the last one) confusion…

    Does this patch will work on MySQL 5.1 current Windows version? Or we’ll have to wait until Sun’s 5.5 realease ;_(

    Thanks again for all this great support!

    Regards,
    Raine

    February 11, 2010 at 6:10 am
  • Vadim Reply

    Raine,

    Windows is not our primary platform, so most likely patches will not work “out of box”.
    However we may look into Windows builds sometime, but I do not promise any ETA.

    February 11, 2010 at 10:27 am
  • Jason Reply

    Where would I find the RPMs for the latest update?

    February 11, 2010 at 4:41 pm
  • Vadim Reply

    Jason,

    I mentioned in post that patches are available only in source code form on Launchpad.
    Availability of binaries and RPMs will be announced later.

    February 11, 2010 at 4:57 pm
  • peter Reply

    Shlomi,

    From the data storage standpoint XtraDB is same as Innodb and you can go back and forth freely with Innodb plugin. There are some features in XtraDB which can break binary compatibility you however need to explicitly enable them.

    February 11, 2010 at 8:09 pm
  • Olivier B. Reply

    Great news ! And Debian backports have just upgrade to 5.1.43 too, so I will try to compile a Debian-Lenny version. Thanks for your job !

    February 12, 2010 at 5:28 am
  • Kay Roepke Reply

    I take it that your comparison with 5.1.43 is using the built-in InnoDB, right?
    How about comparing apples to apples and at least including the bundled plugin version from 5.1.43, as well? (Which is InnoDB Plugin 1.0.6 iirc).

    February 14, 2010 at 4:16 pm
  • Vadim Reply

    Kay,

    Yes, I compare to build-in InnoDB.
    If we spoke about InnoDB-plugin, then to compare apple to apple we need to compare
    InnoDB-plugin vs Percona-XtraDB.
    And some results you can see there
    http://www.mysqlperformanceblog.com/2010/01/13/innodb-innodb-plugin-vs-xtradb-on-fast-storage/

    February 14, 2010 at 5:15 pm
  • Michel Reply

    Vadim,

    “We will prepare document which explains it to avoid more confusions”

    Do you have this document already? I still a bit cofused.

    February 15, 2010 at 12:53 pm
  • Morgan Tocker Reply

    Kay – it is an apples-to-apples comparison. As Vadim writes, the motivation for backporting some of these patches from XtraDB to built-in InnoDB is for customers who have policies against using an RC in production.

    Having said that, it would be good to get a comparison to XtraDB and the plugin on a few different workloads.

    February 16, 2010 at 12:10 am
  • Gregory Youngblood Reply

    It appears there’s a problem with the patch when applied to 5.1.43 and compiled under Solaris 10.

    Initially I was trying to get it to compile using Sun Studio but that didn’t go too well, so I switched to gcc. I am getting closer, but still do not have a build under Solaris 10 that works.

    What I’m finding is that os_atomic_increment is not defined when HAVE_IB_SOLARIS_ATOMICS is set. It appears to be more likely an oversight in os0sync.h starting around line 315. It is defined in the previous block using the GCC builtins, but not Solaris and Windows.

    If I were to hazard a guess, I’d think the generic os_atomic_increment was not supposed to be used in the main code, just os_atomic_increment_lint or os_atomic_increment_ulint, which are defined in the Solaris and Windows blocks.

    February 23, 2010 at 5:19 pm
  • Joshua Reply

    I currently have a server on Ubuntu Hardy with the following installed:

    ii libmysqlclient15off 5.0.90-percona-b21.hardy.3 MySQL database client library
    ii libpercona-xtradb-client-dev 5.1.45-xtradb-1.0.6-10-80.hardy.24 Percona SQL database development files
    ii libpercona-xtradb-client16 5.1.45-xtradb-1.0.6-10-80.hardy.24 Percona SQL database client library
    ii percona-xtradb-client-5.1 5.1.45-xtradb-1.0.6-10-80.hardy.24 Percona SQL database client binaries
    ii percona-xtradb-common 5.1.45-xtradb-1.0.6-10-80.hardy.25 Percona SQL database common files (e.g. /etc
    ii percona-xtradb-server 5.1.45-xtradb-1.0.6-10-80.hardy.25 Percona SQL database server (metapackage dep
    ii percona-xtradb-server-5.1 5.1.45-xtradb-1.0.6-10-80.hardy.24 Percona SQL database server binaries

    Is the userstat patch included?

    mysql> set global userstat_running = ‘ON’;
    ERROR 1193 (HY000): Unknown system variable ‘userstat_running’

    I am hoping you can tell me what I must do to enable it.

    Thank you all for your work and results!

    Joshua

    May 18, 2010 at 1:09 am
  • Vadim Reply May 18, 2010 at 7:17 am
  • Joshua Reply

    Thank you very much sir!

    Greatly appreciated, as with all of percona’s efforts.

    Joshua

    May 20, 2010 at 5:00 pm

Leave a Reply