Related

Author

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.

How does it relate to Percona’s performance build of MySQL? There was evidence that 5.4 was better performing than Percona’s 5.1 optimizations, is that the case for 5.5 too, or does Percona have optimizations for the 5.5 build?

This is topic for another blog post. As I mention current bottleneck I see is rollback segment, and we have patch for this, so absolute results in XtraDB is better, but performance drop with many threads is still in place.

That statement was based on a presentation given in May 2009 (almost a year ago) that I wrote about here – http://www.pythian.com/news/3494/video-giuseppe-maxia-presents-mysql-54/ – I’m looking at the slides and it doesn’t look like a comparison was done, though….so I’ve confused myself now. Maybe back then the Percona build was mostly just the Google patches plus extra debugging info?

I remember seeing similar slides in a presentation by Giuseppe – iirc 5.4 was outperforming XtraDB in a read-only sysbench. I believe Giuseppe took his slides from one of Dimitri’s benchmarks that was posted about the same time. In which case, a more up to date post shows how the numbers have changed (spoiler, XtraDB wins):

I would be careful with scalability claims. You’ve done this benchmark and you have results for this benchmark. Things has gotten a lot better and it is great. However it does not mean all workloads will scale as well too.

Workloads expose different bottlenecks which can affect scalability differently.

Also I think there are 2 different aspects – one is showing improvement with multiple threads and cores and not failing down too much with many connections, another is scalability factor.

In your benchmark we see performance with 16 threads which is the peak is 7 times better than with one thread. Assuming single thread uses a bit over 1 core (most of the work is done by connection thread) we get up to double potential capacity.

It is hard to judge without CPU usage data but I suspect there is some CPU which is free (or wasted doing spinlocks etc due to scalability limitations)

Well, this “scales well” with 16 threads because there are 16 cores, so the contention is minimal as global mutexes can be acquired as soon as they are freed by other threads. If you had 32 cores, it would “scale well” with 32 threads.

I put “scale well” between quotes because the scale of your graph is misleading. The scaling looks linear (and even better than linear) but is not because X-axis is logarithmic. In fact, if you draw the same graph with a linear X-axis you will see that it doesn’t really scale well. The improvement comes from a better usage of your CPU.

thanks for pointing at the obvious, it was missed very much. I’m surprised noone else pointed out the scaling of the graphs x-axis is not OK.

actually, from my POV what we see is that – scalability is still far from linear – there must be still some evil issues lurking, the performance hit from adding more threads should not bring 16-core performance down to the performance of 2 threads (== 2 cores)

luckily oracle is licensed per socket, otherwise one would be able to plot in the point (number of threads) where the hardware cost for “Scaling” breaks even with an oracle license. I hope you understand my point considering that even midrange servers can hit 48cores today.

If I look at it from the other end, I wonder what hardware would be needed to scale innodb up to the performace of a 128core unix iron + better scaling database.

anyway, I think such scalabilty benchmarks are highly neccessary and will help (continued) improvement a lot, removing locks here and there along the way 😉

How does Oracle scale on sysbench? I assume it isn’t legal to publish those results. I know Oracle has many great benchmark results and many nice features, but I would still love to see results for simple benchmarks like sysbench.

Vadim: you say 16 threads pretty well. I think this only for one table request. If there have many query request different table ,I think innodb currency more 16.So I think innodb threads can more than 256.