Buy Percona ServicesBuy Now!

Introducing percona-patches for 5.1

 | February 9, 2010 |  Posted In: MySQL, Percona Software


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

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!

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.


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

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

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

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

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

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


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

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

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

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

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

  • 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

  • Vadim,

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

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

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

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

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


Comments are closed