July 30, 2014

Introducing percona-patches for 5.1

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!

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. To your unnamed customer:
    Well done, and thank you!

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

  3. Raine says:

    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!

  4. Vadim says:

    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.

  5. Vadim says:

    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.

  6. peter says:

    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.

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

  8. Raine says:

    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

  9. Vadim says:

    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.

  10. Jason says:

    Where would I find the RPMs for the latest update?

  11. Vadim says:

    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.

  12. peter says:

    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.

  13. Olivier B. says:

    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 !

  14. Kay Roepke says:

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

  15. Vadim says:

    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/

  16. Michel says:

    Vadim,

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

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

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

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

  19. Joshua says:

    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

  20. Vadim says:
  21. Joshua says:

    Thank you very much sir!

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

    Joshua

Speak Your Mind

*