There is much excitement today at the
launch of MySQL 5.4, so I will
relate my story about a project I contributed to this new version.
When we started looking at performance improvements for MySQL, we were
interested in "low hanging fruit", or fixes and changes that could
reap measurable benefits for users in the short term.
An obvious candidate at that time was the now well-known
Google SMP patch. I had seen Mark Callaghan present on this at
the MySQL User Conference in 2008, and was interested to
I was pretty new to InnoDB at that time, and was soon to discover that
InnoDB was possibly experiencing poor scalability around its mutexes
and read-write locks because InnoDB had a private implementation of
adaptive mutexes and read-write locks, and this was probably not the
best implementation on all or even most platforms MySQL is available
Now InnoDB's "private" mutexes and rw-locks were a good way to get
spin-locks on all platforms, which may be a win in many cases, but as
the Google team had demonstrated, it could be improved on. Indeed, I
knew that adaptive spin-locks are available on Solaris, and they offer
an extra advantage - if the holder of a lock is found to be off CPU,
we don't bother spinning, but instead put the thread wanting the lock
straight to sleep.
So, I decided to undertake a couple of performance studies of InnoDB's
The second step turned out to be quite complicated. I could not even
change all of InnoDB's RW-locks to POSIX ones, as the InnoDB
sychronization objects offer functionality not available via POSIX.
It also meant we would be diverging more significantly from the InnoDB
in 5.1, so this option - although looking promising - was shelved.
within how InnoDB is licensed to MySQL. Phew./em >
This left the Google SMP patch. It also looked promising. It was a
less dramatic change, and offered scaling benefits in all the testing
There was one last snag though - the mutex and RW-lock improvments in
the Google SMP patch would only be applied if you were building on
x86/x64 with GCC 4.1 or later, as they relied on
GCC's atomic built-ins.
You can consider that we have a two-dimensional matrix of platforms
that MySQL supports, being a compiler, then an Operating System. To
make a feature portable across this matrix, you need to find a
portable API, write code that is portable, or write code that uses a
choice of different portable API's depending on what is available.
Now we definitely wanted to get a similar benefit for InnoDB on SPARC,
and not necessarily just with GCC. In any case, GCC did not offer all
of the built-in atomics for SPARC at the time. Happily, there are
atomic functions available in Solaris that fit the job fine. MySQL
5.4 uses the functions if you build on Solaris without a version of
GCC that supports built-in atomics.
Just so you understand though, here is (a simplified version of) what
happens when you build MySQL 5.4 on your chosen platform with your
use GCC built-in atomics
use atomic functions
use traditional InnoDB synchronization objects, based on pthread_mutex\*.
Neel points out in his blog, it was an exercise we learnt
something from, even if we did
develop functionality that will not be used. The important thing is
we know we have improved the performance of MySQL, by extending the
Google SMP improvements to all Solaris users, regardless of chosen