More analysis of InnoDB Thread Concurrency

When I worked with Dimitri on the analysis of the
Split Rollback Segment Mutex he came up with numbers
on InnoDB Thread Concurrency set to 16 and 32 and I was curious
to see if 24 was the optimal setting. So he made some new runs and
some new graphs that I found interesting.

The first graph analyses behaviour of MySQL 5.4.0 on a SPARC
Server using InnoDB Thread Concurrency set to 0, 16, 24 and 32.
Interestingly for both readonly and readwrite benchmarks the
optimal setting for concurrency is 16 whereas the top numbers
(at 32 threads) is achieved with concurrency set to 24 or 32.





So on the current MySQL 5.4.0 on this particular benchmark and
platform it seems that 16 is the optimal setting. However Dimitri
also analysed the same thing using the new patch for Splitting the
Rollback Segment Mutex and now the story changes.





So with this patch setting InnoDB Thread Concurrency to 24
is now the optimum setting. So it's clear that as we get more
and more improvements to the scalability of the MySQL Server and
InnoDB it will be optimal with more and more parallel threads
inside InnoDB as well. So this means that this setting is quite
likely to change as development proceeds but for MySQL 5.4.0 a
setting of around 16-24 is often a good one. To actually change
the default setting requires much more testing of various
workloads on many different computer architectures.

Similar testing I have performed on Linux using sysbench implies
that the optimal setting is around 24-28. Also the difference
between setting it to 0 and 24 is much smaller on Linux (15%
on 256 threads as shown in blog yesterday). We haven't analysed
the big difference on these SPARC Servers.

More data on InnoDB Thread Concurrency

Here is the performance graph comparing using
InnoDB Thread Concurrency equal to 0 and
InnoDB Thread Concurrency equal to 24 using
sysbench readwrite with the new InnoDB
Thread concurrency algorithm as introduced
in MySQL 5.4.0.

MySQL 5.4 Patches: InnoDB Thread Concurrency

When benchmarking MySQL with InnoDB we quickly discovered
that using InnoDB Thread Concurrency set to 0 was an
improvement to performance since the implementation of
InnoDB Thread Concurrency used a mutex which in itself was
a scalability bottleneck.

Given that InnoDB Thread Concurrency is a nice feature that
ensures that one gets good performance also on an overloaded
server I was hoping to find a way to make the implementation
of this more scalable.

I tried out many different techniques using a combination of
mutexes and atomic variables. However every technique fell to
the ground and was less performant than setting it to 0 and not
using the InnoDB Thread Concurrency implementation. So I was
ready to give up the effort and move on to other ideas.

However after sleeping on it an inspirational idea came up.
Why use a mutex at all, let's see how it works by using the
OS scheduler to queue the threads that need to blocked. This
should be more scalable to use than a mutex-based approach.
There is obviously one bad thing about this approach and this
is due to that new arrivees can enter before old waiters. To
ensure we don't suffer too much from this a limit on the wait
was necessary.

So I quickly put together a solution that called yield once
and slept for 10 milliseconds twice at most and every time it
woke up it was checking an atomic variable to see if it was ok
to enter. After those three attempts it would enter without
checking.

I tried it and saw a 1% decrease on low concurrency and 5%
improvement on 32 threads and 10% on 64 threads and 15% on 128
threads. Voila, it worked. Now I decided to search for the
optimal solution to see how many yields and sleeps would be best.
It turned out I had found the optimal number at the first attempt.

The implementation still has corner cases where it provides less
benefits so I kept the possibility to use the old implementation by
adding a new variable here.

So currently the default in MySQL 5.4 is still 0 for InnoDB Thread
Concurrency. However we generally see optimal behaviour using
InnoDB Thread Concurrency set to around 24, setting it higher is
not bringing any real value to MySQL 5.4.0 and setting it lower
decreases the possible performance one can achieve. This seems
to be a fairly generic set-up that should work well in most cases.
We might change the defaults for this later.