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:
- Performance patches for InnoDB ®. Although many patches are present in XtraDB / InnoDB-plugin, the RC status of plugin does not allow to install it on product for some customer’s policies.
Important fixes are: - Diagnostic patches.
- – We provide much more statistics in slow.log, i.e. execution plan, InnoDB timing, profiling info
- Different patches to help with day to day usage of MySQL ®
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 just1call storproc()
- 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)
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!
To your unnamed customer:
Well done, and thank you!
What level would you say this patch set is currently, GA or some sort of pre-GA?
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!
Regards,
Raine
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.
Where would I find the RPMs for the latest update?
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
http://www.mysqlperformanceblog.com/2010/01/13/innodb-innodb-plugin-vs-xtradb-on-fast-storage/
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!
Joshua
Joshua,
Please try 10.1, it is fixed there.
https://www.percona.com/downloads/Percona-Server-5.1/Percona-Server-5.1.45-10.1/
Thank you very much sir!
Greatly appreciated, as with all of percona’s efforts.
Joshua