As you might have seen MySQL QA Team has published their benchmarks for MySQL 5.0.72 and 5.1.30.
It is interesting to compare with results I posted previously
The quote from the results mentioned above:
“Maybe you’ve seen some claims by others in the MySQL community that MySQL 5.1 runs slower than MySQL 5.0. Maybe you’ve also seen some claims by others in the MySQL community that MySQL 5.1 runs faster than MySQL 5.0.
Guess what? They’re both right. “
But is it really what results are telling us ?
I do not think so. When you’re doing benchmarks you should be comparing best performance settings for given application and conditions. For example it is unfair to compare results with different innodb_buffer_pool_size or innodb_flush_log_at_trx_commit but for innodb_thread_concurrency – you should be picking the value which makes your workload to run fastest.
Lets look at the graphs provided in these benchmarks and see what value is best for MySQL 5.1 and for MySQL 5.0 respectively ?
Both versions do their best with innodb_thread_concurrency=0 and 5.0 is slightly but consistently faster. Same as in my results.
So I would interpret these results differently.
MySQL 5.0 is faster if you configure it right. If you configure it wrong the regression is going to be worse than for MySQL 5.1.
You can’t really use this results to tell MySQL 5.1 will be winner in cases when small values of innodb_thread_concurrency get best performance. The things can be completely different in this case. They may or may not, there is simply no data at all.
It is also very interesting to see benchmark run with innodb_thread_concurrency=1000 – This is exactly the value which you should never use. The limit of 1000 threads inside the kernel is by far too large (so it is same as 0 – unlimited) but it will add another mutex to deal with for the queue implementation.
What would be really interesting to learn though is why MySQL 5.1 gets so better when threads get queued up, what kind of changes in MySQL 5.0 result in this behavior. It also would be good to run profiling for these results to see where these few percent are lost for MySQL 5.0 to see if they are possible to be reclaimed.
Finally – if I would be doing QA, and benchmarks as part of them I would try to use options which are close to what people would use in production. Or at least explain why they are set so. Is it because 5.1 does not show too good results with standard settings or is it just omission ? Again quite possibly nothing will change but may be not.
In particular: innodb_support_xa=0, innodb_doublewrite=0 are not normally used in production and they do add some overhead.
When looking for results more relevant for production I also would keep binary log enabled – most big installations of MySQL use replication or at least binary log to get point in time recovery from backup. Also Innodb log files of 2*650M are larger than practical for most applications because of too large recovery time.
Anyways. It is great to see MySQL QA Team has published some benchmarks now and I can’t wait to see more. If we have data and the good disclosure (settings, versions, hardware) as we have in this case we can make our own mind of results and draw our own conclusions.
Also indeed it is a good time to try MySQL 5.1 for your environment. If you spot any regressions from MySQL 5.0 it will likely take a time to get them fixed.
About the Author
Peter ZaitsevPeter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.
Yea I commented on the settings already… while the fact that something gets published is good, the configuration does not fill me with joy. Perhaps they should outsource this to acquire the necessary expertise, and contact Open Query or Percona to tune the QA servers 😉
Using big HBAs you dont have to be that strict.
Im surprised, that setting innodb_thread_concurrency=0 wins the game.
Erkan,
Why ? innodb_thread_concurrency=0 suppose to win the game because it is artificial limit which was needed because Innodb’s abilities to deal with contention in kernel are low. Plus it was done in a simple way which adds more contention and even more – latency. Thread will sleep for few milliseconds before even starting to wait.
Indeed to get the best performance from given amount of cores you want to manage threads carefully, though it is better done on the different level.
I compared the code between 5.1.30 and 5.0.72 that handles innodb_thread_concurrency (srv/srv0srv.c). I did not see a difference. It would be nice to know why that has such an impact on the results.
Peter,
on which different level?
Im used to tune innodb_thread_concurrency. I fact it would be great if I could get rid of this kind of “tuning”. It sucks:-)
So when you say it *was* limited Im wouldnt bother with it anymore *yippi*.
Would you say innodb_thread_concurrency is some kind of deprecated?
Mark,
This makes me even more curious. Good changes. Bad changes you want to understand them anyway at least to be aware of known side effects 🙂
Erkan,
If you get into the thread management they have to be globally managed inside the MySQL process with understanding when thread is really running and when it is not. For example right now the thread is considered “inside innodb kernel” both when it is really using CPU and when it performs disk IO. Not to mention thread gets multiple “tickets” which may keep slot blocked while thread is not really inside Innodb at all – for example storing row in temporary MyISAM table. Finally not all sections are protected by innodb_thread_concurrency – there is also innodb_commit_concurrency on Innodb stage, not to mention contentions on MySQL level which need to be management.
Smart Thread Management is really very complex for MySQL and I do not expect it to happen as it would need to have to be done cross storage engines to be really done properly.
Thread Pool is one simple way to solve this problem, in particular if you have reasonably short queries. The problem is it does not work for general use right now.
@Arjen, it looks like our comments got moderated to /dev/null.
I made a similar comment to what Peter said about the choice of thread concurrency setting on Thursday, and it doesn’t appear to have shown up yet:
==
How does it prove that scalability is improved?
Setting InnoDB thread concurrency is a hack that can be used to restrict the amount of work InnoDB is working on internally specifically because it doesn’t scale. In your case unrestricting InnoDB seems to be the best option (setting=0) – so the only chart worth looking at is the last one.
In which case 5.1 and 5.0 basically score the same. 5.0 is marginally better.
Unless I read something incorrectly?
==
Arjen, Morgan, (comments #1 and #8) I want to make sure: you are talking about comments that you posted to the Sun blog that didn’t get approved, right? You’re not talking about comments that you posted here, and got lost? If you posted here and it disappeared, I’ll go find them and rescue them.
Baron – yes, you are correct in my case – and I assume Arjen’s as well.
Do you folks know of any benchmarks that compare 4.1 to 5.1, skipping 5.0? Since 5.0 was 30% slower than 4.1, many high-volume OLTP production environments are still in 4.1. Does a 5.1 speed increase or at least non-decrease over 5.0 really means anything in comparison to 4.1 benchmarks?
Xan, We did not do 4.1 to 5.1 comparison benchmarks yet. Also note in later 5.0 were considerable parser improvements which made regression from 4.1 less in many cases.