MySQL Architect at Oracle

I have worked as an architect in the MySQL/NDB world for more than 20 years and I am still working at Oracle and I like it here. Given all the FUD spread about MySQL I thought it might be a good idea to spread the word about all the great things we're doing to MySQL at Oracle.

#1 We are working on improving modularity in MySQL code base
In the early days of MySQL the MySQL development had serious issues with its development model. It was a model designed for a small code base. I used to work at Ericsson which is developing telecom switches that have systems with tens of millions lines of code. Such large systems require modularity. The Ericsson switches was developed with modularity built into the programming language already since the 70's. Even with this modularity a second level of modularity was required. The learnings from this reengineering project that span over more than a decade has given me valuable insights that can now be put to use for the development of the MySQL architecture.

When we're developing MySQL at Oracle we have a long-term view on the development, we've taken a lot of steps to make the code more modular. This means more work in developing a feature from a short-term point of view, but it pays back very quickly. As an example of this we did rearchitect the meta-data locking model in MySQL over a period of almost 5 years. Due to this rearchitecting effort we were able in MySQL 5.6 to split one of the crucial locks in the MySQL Server, the LOCK_open. This meant payback time as this improved our top performance of more than 100% with one small incremental step.

The principal idea is that new areas of development we try to put into separate modules which are as independent as possible from the rest of the MySQL Server code. Old code cannot be modularised in one swift move, this code has to be improved upon in small reengineering steps. Eventually those steps lead into more modularity and easier changability also of the old code base. We expect to continuosly improve the MySQL architecture in this manner.

Does this work benefit the entire MySQL community. Yes, modular code is the very foundation for successful open source code development also by the MySQL community. MySQL has a thriving development community, we expect it to thrive even more by the improved modularity we add to the MySQL code base.

#2 We have an improved development model
One of the first things I got engaged in 2007 when I was assigned to Senior MySQL Architect was to improve the MySQL development model. Previously we had a model where features were developed as projects and then pushed to the next release branch, often new features were pushed in several steps. This model had issues in quality and in development speed. This model often led to prioritisation issues when coming close to GA releases and often code was pushed very close to GA releases without proper quality. After MySQL was acquired by Sun and Oracle we have managed to get this under control using a new development model.

In the new development model we wanted to ensure that we took small steps forward (called milestone releases), always with retained quality. So in the new model we develop in 3-6 month steps, each step is quality assured and we use a lot of QA resources to ensure each milestone release is ok from a quality point of view. Some of these milestone releases are then taken out to become new GA releases. The GA releases are then put into our QA grind from where most bugs are squeezed out of the code.

We introduced this new model in 2009, it affected positively the MySQL 5.5 development and MySQL 5.6 has been completely developed according to this model. One only needs to look at what we achieved with MySQL 5.6 to understand that the new model works very well. We're continuing to use this model for new steps in the MySQL development.

The model is described in http://dev.mysql.com/doc/mysql-development-cycle/en/index.html

Our MySQL development is divided into a number of parts, we have a team taking care of InnoDB, another team taking care of MySQL Cluster (with the NDB storage engine), one team dealing with partitioning, an optimizer team, a replication team, a runtime team taking care of things such as metadata handling and other things related to query execution and a general team taking care of support code, networking, client code.

We also have teams handling MySQL Connectors, utilities and other things needed around the MySQL Server. There is also a performance and architecture team focusing on scalability, special projects and all sorts of improvements to ensure that our users get a good experience of using MySQL. Naturally we also have teams focusing entirely on the quality of the code. We have also a separate team working on MySQL Workbench and related tools to support MySQL management and development.

#3 We have great performance experts for the MySQL development
The community has lots of performance experts that understand how to help users in getting the best out of a MySQL Server. The community also has a number of people that are able to pinpoint performance issues in the MySQL Server. The patches these people develop are very useful in showing what can be improved in the MySQL codebase. The community is here very useful to the development of the MySQL Server. In this area we see a very good cooperation between our developers and the MySQL community developers.

We have also been able to do numerous performance improvements independent through the combination of MySQL experts and InnoDB experts that we have internally in Oracle. If you need proof of this just go to the MySQL 5.6 page and see demonstrated performance improvements we've done in MySQL compared to MySQL 5.5. This is an area I have contributed a lot to personally and I find it interesting how we managed to increase scaling of the MySQL Servers from 4 CPU threads to almost 64 CPU threads in just a few years.

#4 We continously develop MySQL partitioning
We continously improve scalability of our partitioning solution and integrate it further into the main storage engines as evidenced by the new set of partitioning features in MySQL 5.6.

#5 We have the most serious QA team in the MySQL world
Through the use of a large QA team we do our best to ensure that our community users are shielded from bugs in our released code. We also work with the community through milestone releases, lab releases and RC releases to ensure that our GA releases have top notch quality. We tripled the size of the QA teams as part of the MySQL 5.6 development.

Anyone can of course add new features to these MySQL trees, but it will be the community that mainly becomes the QA team for these additional features. We strive hard to ensure that our community users are shielded from being our QA team.

Although the MySQL is continously growing, the code coverage of our tests has continously improved going from MySQL 5.1 to MySQL 5.6.

#6 We are continously developing InnoDB inside Oracle
The major storage engine for MySQL is InnoDB, the InnoDB developers are also located within Oracle. We've seen many great benefits of being together inside the same company. This means that we can integrate InnoDB even better and improve scalability and functionality than ever before.

#7 We have a thriving MySQL Cluster development in Oracle
All MySQL Cluster development currently happens inside Oracle. I am still very much involved in this development and I sit next to a number of the key Cluster developers in my office in Stockholm with a beatiful view over the Stockholm City centre. We have shown magnitude improvements in scalability, performance per node and so forth in a regular and steady development. MySQL Cluster even has the capability to execute parallel queries as of version 7.2.

#8 We are making great efforts to make MySQL manageable
Many community developments are centered around getting more access to internal statistics about MySQL Server behaviour. Since a few years back we have a focused effort in this area to provide anything that a user could be interested in through the performance schema tables. These tables gather data internally and provide access to this statistical data through a set of performance schema tables. This effort is continuing, there is still more work to be done in this area.

#9 We have the largest and best optimizer team in the MySQL world
Our team of optimizer developers consists of developers that have loads of experience on developing DBMSs. Many of the developers have a background in developing SQL databases in the past and also have a scholar background which is almost a requirement to understand the fairly complex optimizer algorithms required in a relational DBMS. The team has made a tremendous effort in preparing a load of new optimizer features for MySQL 5.6.

#10 A thriving team working on MySQL Replication
The MySQL success have always been closely intertwined with the ability to replicate between MySQL servers using the master-slave model. The requirements on better failover capabilities and manageability of large sharded environments have led to an ever increasing set of new functionality in the replication area. We're continuing this development and working to provide even more functionality for the MySQL community that will be useful in building large MySQL installations.

#11 We have many other teams
The runtime team is doing a lot of work on handling metadata changes online, making the MySQL Server more scalable and making the server more modular.

The general team is ensuring that MySQL becomes more modular, ensuring that our support code is further developed, ensuring that we also have more unit tests on our code.

We have a thriving utilities team that is developing various tools useful for people managing MySQL Servers.

Each of the teams mentioned above have developed a great number of new features in the respective MySQL versions making it quite clear that MySQL development is thriving as never before.

So if you're looking for a MySQL version to use that have the best performance, the best stability and the most future-proof code base, then use MySQL 5.6.

The MySQL community continues to be an interesting place to work in. Oracle leads the development of new MySQL versions, we have a number of large companies that are building massive infrastructure components based on MySQL (Google, Facebook, Twitter and so forth) and we have a number of forks that also develops their own versions. There is also many new interesting requirements on MySQL to behave well also in a highly scalable environment and this requires even more work to be done on the MySQL replication architecture.

As a MySQL architect I am able to work with many parts of the MySQL engineering organisation and also the MySQL support organisation. There is a lot of interesting work going on that will continue to increase the use cases of MySQL for the MySQL community. So my work continues to be interesting and it's very rewarding to develop new functionality knowing that it will be used by many organisations in the world.

When I tell my kids or their friends about my work I tell them that I work on things that makes their computer games continue to tick since this is what most kids care about in the IT community. Personally I find it even more interesting the use cases for genealogy and other cool stuff.

Follow-up Analysis of Split Rollback Segment Mutex

I performed a new set of tests of the patch to split the
rollback segment mutex on Linux. All these tests gave
positive results with improvements in the order of 2%.

One could also derive from the results some conclusions.
The first conclusion is that this split mainly improves
things when the number of threads is high and thus
contention of mutexes is higher. At 256 threads a number
of results improved up to 15%.

The numbers on lower number of threads were more timid
although in many cases an improvement was still seen.

What was also noticeable was that the sysbench read-write
with less reads which makes the transactions much shorter
the positive impact was much greater and the positive
impact on long transactions was much smaller (+0.4%
versus +2.5%). The impact on the short transaction test
with less reads was very positive also on lower number
of threads, the result on 32 threads improved 7%.

So the conclusion is that this patch is a useful contribution
to improvements and in particular improves matters on high
number of threads and with short transactions. According to
a comment on the previous blog it is also very positive in
insert benchmarks.

Results of shootout on split page hash in InnoDB

I have now tried out the buffer split page hash patches on
both a Linux/x86 box and a SPARC/Solaris server (tests done
by Dimitri).

The three variants in short description are:
1) The Google v3 derived patch. This introduces a new array
of mutexes that only protect the buffer page hash. Thus some
extra checking is needed to ensure the page hasn't been
removed from the hash before using it. This is a very simple
and attractive patch from that point of view. The patch uses
an array of 64 mutexes.

2) A variant I developed with some inspiration from the Percona
patches. This patch uses an array of page hashes which each has
its own read-write lock. I've tried this with 1, 4 and 16 page
hashes and 4 is the optimum number. The rw-lock protects the
page hash long enough to ensure that the block hasn't been
possible to remove from the hash before the mutex is acquired.

3) The last variant is a mix of the two first which uses the
simplicity of the Google patch, uses a rw-lock instead and
separate page hashes (to ensure read ahead doesn't have to
go into all mutexes). Used an array of 4 page hashes here.

The conclusion is that the only version that has consistently
improved the MySQL 5.4.0 numbers is the version I originally
developed (2 above).

On sysbench read-write all versions improve numbers compared to
MySQL 5.4.0. 2 and 3 improve 2% whereas the original Google
patch improved with 1%.

On sysbench read-only on Linux it was much harder to beat the
MySQL 5.4.0 version. Only 2) did so and only by 0.5%. This is
not so surprising since this mutex is not a blocker for read-only
workloads. 1) gave -1% and 3) gave -0.3%.

On a write intensive workload on Linux 1) and 3) performed 0.5%
better than MySQL 5.4.0 whereas 2) gave 2% improvement.

Finally on a sysbench read-write with less reads on Linux, all
variants lost to MySQL 5.4.0. 1) by 2%, 2) by 0.1% and 3) by
1%.

Also the numbers from SPARC/Solaris give similar data. The major
difference is that the positive impact on SPARC servers is much
bigger, all the way up to 30% improvements in some cases. The
most likely reason for this is that SPARC servers
have bigger CPU caches and are thus more held back by lack of
concurrency and not so much by increased working set. The x86
box had 512kB cache per core and a 2MB L3 cache and is likely
to be very sensitive to any increase of the working set.

So the likely rationale for worse numbers in some cases is that
more mutexes or rw-locks gives more cache misses.

So given the outcome I will continue to see if I can keep the
simplicity of the Google patch and still maintain the improved
performance of my patch.

Some ideas on InnoDB kernel_mutex

I've noted that one reason that InnoDB can get difficulties
when there are many concurrent transactions in the MySQL Server
is that the lock time of the kernel_mutex often increases
linearly with the number of active transactions. One such
example is in trx_assign_read_view where each transaction
that does a consistent read creates a copy of the transaction
list to be able to deduce the read view of the transaction or
statement.

This means that each transaction is copied to the local transaction
list while holding the critical kernel_mutex.

Another such case is that most operations will set some kind of
intention lock on the table. This lock code will walk through
all locks on the table to check for compatible locks and the
first time it will even do so twice. Thus if all threads use the
same table (as they do in e.g. sysbench) then the number of locks
on the table will be more or less equal to the number of active
transactions.

Thus as an example when running with 256 threads compared to 16
threads the kernel_mutex lock will be held for 16 times longer
and possibly even more since with more contention the mutex is
needed for even longer time to start up waiting transactions.

So this is an obvious problem, so what is then the solution?
Not extremely easy but one thing one can do is to make the
kernel_mutex into a read-write lock instead of a mutex. Then
many threads can traverse those lists in parallel. It will
still block others needing write access to the kernel_mutex
but it should hopefully improve things.

Another solution that is also going to improve the problem is
to use thread pools. Thread pools ensure that not as many
threads are active at a time. However we still have a problem
that transactions can still be as many active in parallel as
there are connections (although InnoDB has a limit of 1024
concurrent active transactions). So the thread pool needs
to prioritize connections with active transactions in cases
where there are too many threads active at a time.

This type of load regulation is often used in telecom systems
where it is more important to give priority to those that have
already invested time in running the activity. Those that are
newcomer comes in when there are empty slots not taken by
already running activities.

Increasing log file size increases performance

I have been trying to analyse a number of new patches we've
developed for MySQL to see their scalability. However I've
have gotten very strange results which didn't at all compare
with my old results and most of changes gave negative impact :(
Not so nice.

As part of debugging the issues with sysbench I decided to go
back to the original version I used previously (sysbench 0.4.8).
Interestingly even then I saw a difference on 16 and 32 threads
whereas on 1-8 threads and 64+ threads the result were the same
as usual.

So I checked my configuration and it turned out that I had changed
log file size to 200M from 1300M and also used 8 read and write
threads instead of 4. I checked quickly and discovered that the
parameter that affected the sysbench results was the log file size.
So increasing the log file size from 200M to 1300M increased the
top result at 32 threads from 3300 to 3750, a nice 15% increase.
The setting of the number of read and write threads had no
significant impact on performance.

This is obviously part of the problem which is currently being
researched both by Mark Callaghan and Dimitri.
Coincidentally Dimitri has just recently blogged about this and
provided a number of more detailed comparisons of the
performance of various settings of the log file size in InnoDB.

Patches ready for buf page hash split shootout

Today I created a patch that builds on the Google v3
patch where I added some ideas of my own and some ideas
from the Percona patches. The patch is here.

Here is a reference to the patch derived from the Google
v3 patch.

Here is a reference to my original patch (this is likely to
contain a bug somewhere so usage for other than benchmarking
isn't recommended).

So it will be interesting to see a comparison of all those
variants directly against each other on a number of benchmarks.

Analysis of split flush list from buffer pool

In the Google v3 patch the buffer pool mutex have been
split into an array of buffer page hash mutexes and a
buffer flush list mutex and the buffer pool mutex also
remains.

I derived the patch splitting out the buffer flush list
mutex from the Google v3 patch against the MySQL 5.4.0
tree. The patch is here.

I derived a lot of prototype patches based on MySQL 5.4.0
and Dimitri tried them out. This particular patch seems
to be the most successful in the pack of patches we
tested. It had a consistent positive impact.

The main contribution of this patch is twofold. It
decreases the pressure on the buffer pool mutex by
splitting out a critical part where the oldest dirty
pages are flushed out to disk. In addition this patch
also decreases the pressure on the log_sys mutex by
releasing the log_sys mutex earlier for the mini-
transactions. In addition it removes interaction
between the buffer pool mutex and the log_sys mutex.
So previously both mutexes had to be held for a
while, this is no longer necessary since only the
flush list mutex is needed, not the buffer pool
mutex.

The new patch is the b11 variant which is red in
the comparison graphs.



As we can see the read-write tests have a pretty significant boost
from this patch, it improves top performance by 5% and by 10-20%
on higher number of threads. It also moves the maximum from 16 to
32 threads.



Even on read-only there are some positive improvements although
it is very possible those are more random in nature.



Finally the above picture shows that this patch also moves the
optimal InnoDB thread concurrency up to 24 from 16 since it
allows for more concurrency inside InnoDB. This is also visible
by looking at the numbers for InnoDB Thread Concurrency set to 0
as seen below.

Shootout of split page hash from InnoDB buffer pool mutex

One of the hot mutexes in InnoDB is the buffer pool mutex.
Among other things this mutex protects the page hash where
pages reside when they are in the cache.

There is already a number of variants of how to split out
this mutex. Here follows a short description of the various
approaches.

1) Google v3 approach
Ben Hardy at Google took the approach of using an array of
mutexes (64 mutexes) and this mutex only protects the
actual read, insert and delete from the page hash table.
This has the consequence of a very simple patch, it means
also that when the block has been locked one has to check
that the owner of the block hasn't changed since we didn't
protect the block between the read of the hash and the
locking of the block, thus someone is capable of coming in
between and grabbing the block for another page before we
get to lock the block. In addition this patch focuses
mainly on optimising the path in the buf_page_get_gen
which is the routine used to get a page from the page
cache and thus the hot-spot.

2) Percona approaches
Percona has done a series of approaches where the first
only split the page hash as one mutex and still protecting
the blocks from being changed while holding this mutex.
Next step was to change the mutex into a read-write lock.

3) My approach
My approach was inspired by Percona but added two main
things. First it split the page hash into a number of
page hashes and had one RW-lock per page hash (this
number has been tested with 4, 8 and 16 and 4 was the
optimal on Linux at least). In addition to avoid having
to lock and unlock multiple pages while going through
the read ahead code the hash function to decide which
page hash to use decided on the same page hash for all
pages within 1 MByte (which is the unit of read ahead
in InnoDB).

Pros and Cons

The simplest patch is the Google patch which makes for
a very simple patch and also by only focusing on
buf_page_get_gen avoids a lot of possible extra traps
that are likely if one tries to solve too much of the
problem.

Using a RW-lock instead of a mutex seems like at least
a manner of improving the concurrency but could of
course impose a higher overhead as well so here
benchmarking should show which is best here.

When using an array of locks it makes sense to optimise
for read ahead functionality since this is a hot-spot
in the code as has been shown in some blogs lately.

4) Mixed approach
So a natural solution is then to also try a mix of the
Google variant with my approach. So still using an
array of locks (either mutex or RW-locks, whatever
has the optimal performance) but ensuring that the
pages within a read ahead area is locked by the same
lock.

This approach reuses the simplicity of the Google
approach, the total lack of deadlock problems for
the Google approach with the optimised layout from
my approach and the idea of RW-locks from Percona.

We don't have any results of this shootout yet.
This shootout should also discover the optimum number
of areas to split the page cache into, Google has
used 64, but my results so far indicates a number of
4 seems more appropriate.

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.

Analysis of Split of Rollback Segment Mutex

When I read the blog about Split Rollback Segment Mutex,
I was interested to verify those results in the context of MySQL 5.4.0.

The patch can be found here.

We've analysed this patch both on a large SPARC system and on my
benchmark x86/Linux machine. Our results tend to be positive for
readwrite benchmarks but sometimes negative for readonly
benchmarks. Also the gain is much smaller than found in the
blog.

Also this patch has two negative effects, the first is that it
provides an upgrade problem, this can probably be handled in the
InnoDB code, but requires quite some digging. The other is that
instead of writing UNDO results to one UNDO log, we write it to
several UNDO logs, thus decreasing the buffering effect to the
file system.

On Linux I found on readwrite benchmarks up to 7-8%
improvements of the top results. On readonly it sometime dropped
about 1-3%. I also tried with varying numbers of rollback
segments and found 4 and 8 to be better than 16. So from the
above point of view the number of rollback segments set to 4 is
probably best. The patch uses 8 (it's actually set to 9 since
the system rollback segment is a bit special).

Here are some graphs from Dimitri running it on some fat SPARC
server (MySQL-5.Perfb9-gcc43 is 5.4.0 plus the above patch).

The first graph shows the behaviour when InnoDB Thread Concurrency
is 0, here we see a speedup in the range of 3-5%.



The same results for the readonly benchmark shows positive results as well.



When one sets InnoDB Thread Concurrency equal to 16, 24 or 32
the behaviour is different. It turns out that we get worse
performance using 16 but get more positive impact using 24 and
even more using 32. So it seems that this patch requires less
limits to parallelism to get the best behaviour.

So one impact of this patch is that it can sustain a higher
number of concurrent threads and there is a small positive impact
on the performance as well.

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.

Analysis of Google patches on 4,8 and 12 cores

One of the goals we had originally with the MySQL 5.4
development was to improve scaling from 4 cores to
8 cores. So in my early testing I ran comparisons of
the Google SMP + IO + tcmalloc patches on 4, 8 and 12
cores to see how it behaved compared with a stock
MySQL 5.1.28 version (Note the comparison here was
done on a very early version of 5.4, 5.4.0 have a
set of additional patches applied to it).



What we can see here is that the Google SMP patch and use
of tcmalloc makes a difference already on a 4-core server
using 4 threads. On 1 and 2 threads the difference is only
on the order of 1-2% so not really of smaller significance.

An interesting note in the graph is that 8-core numbers using
the Google improvements outperform the 12-core stock MySQL
5.1.28.

So what we concluded in those graphs is that the scaling from 4-cores
to 8-cores had improved greatly and that there also was a good scaling
from 8 cores to 12 cores. This improvement increased even more with
the 5.4 release. The main purpose of showing these numbers is to show
the difference between 4, 8 and 12 cores.

All benchmarks were executed on a 16-core x86 box with 4 cores
dedicated to running sysbench.

MySQL 5.4 Patches: Improvements to spin-loop

In InnoDB there is an implementation of both mutexes
and RW-locks. The RW-locks implementation have been
improved by the Google SMP patches. Both of these
implementation relies on spin-loops as part of their
implementation. The defaults in InnoDB is to check
the condition, if it's not ok to enter to spin for
about 5 microseconds and then come back to check the
condition again.

If one reads the Intel manual how to do spin-loops
they propose to use a PAUSE instruction and then
check the condition again, so a much more active
checking of the condition. When we tried this out
using the sysbench benchmark we found that using
the Intel approach worsened performance. So instead
we tried an approach of putting the PAUSE instruction
into the InnoDB spinloop instead.

This approach turned out to be a success. Even on
machines with only one thread per core we were able
to get a 3-4% increase in throughput. We also tried
various settings of the defaults of the time of
spinning in the spinloop and found that the original
default values were very close to the optimum values.
We found the optimum about 20% from the old default
values and made this slight change to the default
values of the spinloop.

It's my expectation that as we remove locks and the
mutexes and RW-locks gets less contended and there
are more locks where the threads are waiting that
this optimum value will change. The current best
setting is very likely to be governed by the fact
that the most waiting happens on very hot locks.
So with improvements of the mutexes and RW-locks
we should expect to see better performance with
a shorter time in the spinloop.

On the new SPARC CPU's that Sun has developed, the
CMT boxes, we used the results from the paper:
www.ideal.ece.ufl.edu/workshops/wiosca08/paper2.pdf
which stated that the optimum instruction to use
is a cache miss instruction, however as I don't
know how to program a cache miss instruction we
opted for the second best instruction which was a
dummy test-and-set instruction. So the PAUSE
instruction is replaced by a test-and-set instruction
on SPARC CPU's.

We expect that the improvements due to this small
change is even bigger when there are multiple
threads per core since the contention on the
CPU pipeline is higher in those cases and it is
important that the spinloop stays away as much
as possible from being active executing
instructions.

MySQL 5.4 Scaling to 16 way x86 and 64-way CMT Servers

The release of the MySQL 5.4 contains patches which
increases the scalability of the MySQL Server. I am planning to blog
about those changes in some detail over the next few days. This blog
will give an introduction and show what the overall results we have
achieved are.

The changes we have done in MySQL 5.4 to improve scalability and
the ability to monitor the MySQL Server are:

1) Google SMP patch
2) Google IO patches
3) Update of many antiquated defaults in the MySQL Server
4) New InnoDB Thread Concurrency algorithm
5) Improved Spinloop in InnoDB mutexes and RW-locks
6) A couple of performance fixes backported from 6.0
7) Operating system specific optimisations
8) Ported the Google SMP patch to Solaris x86 and SPARC and work
underway for Windows and Intel compiler as well
9) Introducing DTrace probes in the MySQL Server
10) A build script to make it easier for community to build an efficient
MySQL Server based on source code

As an example of the improvements made available through this work we
have some benchmarks using sysbench readwrite and readonly.

We have consistently seen improvements in the order of 30-40% of
sysbench top numbers and on large number of threads 5.4.0 drops
much less in performance than 5.1. The new InnoDB Thread Concurrency
patch makes the results on high number of threads even more
impressive where the results have gone up by another 5-15% at the
expense of 1% less on the top results (there are even some DBT2
runs that gave 200% improvement with the new algorithm).

There is also a benchmark on EAStress which shows a 59% increase in
performance from 5.1 to 5.4 using the new 16-way x86 Nehalem servers.