There was several changes in InnoDB to fix scalabilty problems, so I ran benchmark to check
new results and also compare overall performance of InnoDB in 5.0 and 5.1 before and after fixes.
Problems in InnoDB that were fixed:
- Thread trashing issues with count of theads 100+. In this case
performance of InnoDB degraded dramatically. The problem was in the mutex impelementation
and was fixed in 5.1.12 (more info about InnoDB mutexes) - Scalabilty issue, the well know bug 15815, that was fixed in 5.0.30 and 5.1.14.
So I took for tests:
- 5.0.27, the release with both problems
- 5.0.32-bk (snapshot from 26 Dec 2006) – with 15815 bug fix
- 5.1.12 with fix of thread trashing
- 5.1.14 with all fixes
- 5.0.30 just for comparison.
- 4.1.22 old release, just for comparison of overall performance
All releases were compiled from sources with options:
1 | ./configure --prefix=/usr/local/mysqltest/mysql-<RELEASE> --with-innodb |
and next startup options were used:
1 | libexec/mysqld --no-defaults --user=root --innodb-buffer-pool-size=1500M --innodb-log-file-size=100M --innodb-thread-concurrency=8 --max-connections=1500 --table-cache=512 |
Description of benchmark
Specially for our needs I developed benchmark suite which support wide range of tables and queries.
The schema and used queries is avaiable here:
http://docs.google.com/View?docid=d7fzh8b_7dwd8m7
or on our page
https://www.percona.com/blog/files/benchmarks/phptestsuite.html
In short I used several SELECT queries which perform point and range access to table by PrimaryKey and usual key and one query for full table scan.
Range queries is especially interesting for bug 15815 as the same such queries were affected on multi-CPU systems.
Used hardware:
CentOS release 4.4 (Final)
2 Ñ… Dual Core Intel XEON 5130model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 4096 KB16GB of RAM
Disks do no matter, as I ran clear CPU-bound benchmarks.
The raw results of benchmarks are available here:
http://spreadsheets.google.com/pub?key=pOIo5aX59b6Z8eWjePZU-JQ
or on our page
https://www.percona.com/blog/files/benchmarks/innodb_scale.html
(Note: This benchmark is synthetic micro benchmarks focusing on particular simple data access patterns. Results for your workload are likely to be different.)
Couple of my comments:
- The test on our hardware did not show the scalability problem, but the main goal was the comparison of
overall performance of InnoDB before fix (in 5.0.27) and after fix (5.0.30/5.1.14 and later) - The fix of 15815 shows good results:
in 5.0.32 (comparing to 5.0.27) the improvement for READ_PK_RANGE is 5-16%.
most interesting is improvement by 30-39% in READ_KEY_RANGE queries
and by 10-23% for full table scan queries.
I unambiguously recommend to switch to 5.0.30 – 5.0.32 release when it is available. - You can see general performance degradation comparing 5.1.14 with 5.0.32
For example slow down by 12% in READ_PK_POINT with 1 thread.
And by 11-15% in READ_KEY_RANGE queries.
There is nothing extraordinary, this is a paid for new features.
Is it possible that the degradation in 5.1.14 compared with 5.0.32 is because it’s beta code that still has debugging code in place? I doubt that MySQL intended to slow down their database noticeably regardless of any new features.
I think it would be reasonable to file a bug report if you find a 5.0->5.1 regression of more than a couple percent (as you have almost across the board).
Pete,
I don’t think the reason of degradation is debug code.
If you look at our peresentation 5.0 vs 4.1
http://www.mysqlperformanceblog.com/files/presentations/UC2006-MySQL-5.0-vs-4.1.pdf
you will see 5.0 release has degradation comparing to 4.1.
Do the mutex fixes for InnoDB obviate the need to patch the code to use Solaris mutexes (in our case)? Patch code is below. Thank you in advance!
/storage/innobase/include/sync0sync.ic [mutex_exit() function]
/*=======*/
mutex_t* mutex) /* in: pointer to mutex */
{
+
+ os_fast_mutex_unlock(&(mutex->os_fast_mutex));
+ return;
+
#ifdef UNIV_SYNC_DEBUG
ut_ad(mutex_own(mutex));
/storage/innobase/include/sync0sync.ic [mutex_enter_func() function]
{
ut_ad(mutex_validate(mutex));
+ os_fast_mutex_lock(&(mutex->os_fast_mutex));
+
+ return;
+
/storage/innobase/sync/sync0sync.c [mutex_enter_nowait() function]
{
ut_ad(mutex_validate(mutex));
+ return os_fast_mutex_trylock(&(mutex->os_fast_mutex));
+
+
if (!mutex_test_and_set(mutex)) {
#ifdef UNIV_SYNC_DEBUG
Peter, you might try to see if turning off the adaptive hash index has any effect in your test. Work on the scalability issues is ongoing, so we’ll probably see significant additional improvement.
Jason,
InnoDB fixed mutex problem on Solaris in 5.1.12+ releases.
I have not tested it, so I cannot say how good it works.
James,
I know Adaptive mutex can be hotspot for certain workloads. I remember we disabled it a while back for this sort of workload – it becomes to scale better but performs worse.
Note we’re not paid to do MySQL performance profiling any more so there is limited free time we can spend on it 🙂
I know, but if you enjoy it, I’ll help your exploring. 🙂
It is good to see an improvement in performance compared to previous 5.0 releases, however it seems that in most tests there is a slowdown compared to 4.1 versions. Any chance of this being rectified as well?
Ilia,
Actually even 5.1 gains are uneven – in large portion of the cases it is slower than 5.0 (if you compare to 5.0.32)
Regarding reclaim slow down compared to 4.1 I would hope for that but I would not expect that. We profiled 5.0 compared to 4.1 and slowdowns were scattered across the code as it just gets larger and more complicated – this type of slowdown requires significant effort to fix and we shall see if resources will be allocated to it.
Peter, I realize that increased code complexity and an ever increasing feature set usually does take a toll on performance. I am glad to hear that you are not discounting the slowdown and hopefully MySQL team will find the time for those difficult optimizations.
About slower performance in 5.1 compared to 5.0 because of new feature.
This is strange that postgresql manage to have the same feature (or more) than mysql withoug being slower?!
This is what i understand from the tweakers.net benchmark in your previous blogpost.
SeBest,
Regarding benchmarks from Tweakers.net unfortunately the benchmark code itself is not available so the only thing we can say is there is something which runs faster on PostgreSQL than on MySQL. Without knowing what it is it is hard to say the reason for performance difference – might be it is “feature” related – some optimization is missing in MySQL.
To the question if you can increase features and performance at the same time – yes this is possible and this is the case with MySQL for some more complex cases. The simple cases as this benchmark uses there optimized down to the bones even with old MySQL versions so you only get slower because more fat new versions add.
I’m sure though MySQL can be brought back in terms of performance of simple queries if significant effort is applied to it – recent years focus of MySQL was more features and later bug fixes than performance.