Oracle recently announcent MySQL-5.5.2-m2 milestone, and I have a predition that MySQL-5.5 will be announced as GA on MySQL UC 2010 in April.
So let’s make quick on scalability characteristics we should expect from new release.
I made sysbench oltp benchmarks on 10 mln rows (worth 2.5GB of data), on our Dell R900 system ( 16 cores, 32GB of RAM, FusionIO + RAID10, but disk in this case does not matter).
There is results for various threads (results are in tps, more is better)
I think it is good results, and there couple conclusions we can come with
- InnoDB now can scale up to 16 threads pretty well
- InnoDB can keep load up to 128 threads, and for 256 threads the result is still acceptable, but drops by 25% comparing to peak in 16 threads
If you interested what is issue in scaling over 16 threads, that in current
benchmarks – this is concurrency on rollback segment (which is global shared structure in InnoDB)
sysbench command for reference:
1 | ./sysbench --test=oltp --oltp-table-size=10000000 --init-rng=1 --num-threads=$i --max-requests=0 --oltp-dist-type=uniform --max-time=180 run |
Once we fixed a few other problems we have begun to notice dogpiles on our servers from contention on the rollback segment mutex. I am glad you noticed this first.
How does MySQl 5.5 compare to XtraDB?
Let’s hope they actually make it available then. The empty announcement of last year’s 5.4 being “available soon” was a total failure 🙂
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?
Sheeri,
For 5.5 we propose the same XtraDB plugin as and for 5.1.
5.5 is based on InnoDB-plugin, the same for XtraDB.
If you have evidence that XtraDB is slower, please give me link.
All results I saw where XtraDB is not performing the same or better than InnoDB-plugin
just have incorrect tuning for XtraDB.
Andy,
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.
Vadim — as I said, there *was* evidence, it may be out of date.
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?
Then again, you mentioned it yourself:
http://www.mysqlperformanceblog.com/2009/07/14/performance-improvements-in-percona-5-0-83-and-xtradb/
You specifically made changes to “align with 5.4” — because it wasn’t before. Of course, that was months ago now, so I apologize for my outdated information….
Still, it would be good to see performance against the Percona performance build and against Xtradb.
In fact, it would be nice to see a blog post on how the Percona performance build is different from Xtradb, and if there’s a version # at which they’re the same? or are they 2 different code brancheS?
Vadim, have you tried to bind mysqld to the cores of single socket, leaving other idle?
Here is a quite interesting post about cross-core and cross-socket performance of modern CPUs –
http://kevinclosson.wordpress.com/2010/01/05/an-intel-xeon-5400-system-that-outperforms-an-intel-5500-nehalem-ep-system-believe-it-or-know-it
Sheeri,
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):
http://dimitrik.free.fr/blog/archives/2009/12/entry_101.html
Morgan — as I said, my information was likely out of date.
Still, benchmarks against 5.5 would be good.
Also, many people are confused about xtradb vs. percona performance build of mysql. (heck, I am a bit, I know they’re different in many 5.0 versions but I’m not sure if they merge at some point).
Vadim,
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)
Here is a shell script wrapper which uses Vadim’s sysbench command.
Assumptions:
1) You have a mysql instance with a sbtest database up and properly configured to allow 512 connections.
# Runs sysbench increasing thread count each time to 512 threads
# http://www.mysqlperformanceblog.com/2010/02/28/mysql-5-5-m2-scalability/
sysbench –test=oltp –oltp-table-size=10000000 –mysql-user=sbtest –mysql-password=yourpasswd \
–mysql-socket=/var/lib/mysql/mysql.sock –init-rng=1 –num-threads=1 –max-requests=0 \
–oltp-dist-type=uniform –max-time=180 prepare
for i in 1 2 4 8 16 32 64 128 256 512
do
sysbench –test=oltp –oltp-table-size=10000000 –mysql-user=sbtest –mysql-password=yourpasswd \
–mysql-socket=/var/lib/mysql/mysql.sock –init-rng=1 –num-threads=$i –max-requests=0 \
–oltp-dist-type=uniform –max-time=180 run
done
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.
lifo2,
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.
What kind file system were you using ?
Thanks