Friday, November 21, 2008

DTrace, opensolaris and MySQL Performance

Currently I'm working hard to find and remove scalability
bottlenecks in the MySQL Server. MySQL was acquired by Sun
10 months ago by now. Many people have in blogs wondered what
the impact has been from this acquisition. My personal
experience is that I now have a chance to work with Sun
experts in DBMS performance. As usual it takes time when
working on new challenges before the flow of inspiration
starts flowing. However I've seen this flow of inspiration
starting to come now, so the fruit of our joint work is
starting to bear fruit. I now have a much better understanding
of MySQL Server performance than I used to have. I know fairly
well where the bottlenecks are and I've started looking
into how they can be resolved.

Another interesting thing with Sun is the innovations they have
done in a number of areas. One such area is DTrace. This is a
really interesting tool which I already used to analyse some
behaviour of MySQL Cluster internals with some success. However
to analyse other storage engines inside MySQL requires a bit more
work on inserting DTrace probes at appropriate places.

To work with DTrace obviously means that you need to work with
an OS that supports DTrace. Solaris is such a one, I actually
developed NDB Cluster (the storage engine for MySQL Cluster) on
Solaris the first 5-6 years. So one would expect Solaris to be
familiar to me, but working with Linux mainly for 6-7 years means
that most of the Solaris memory is gone.

So how go about developing on Solaris. I decided to install a virtual
machine on my desktop. As a well-behaved Sun citizen I decided to
opt for VirtualBox in my choice of VM. This was an interesting
challenge, very similar to my previous experiences on installing
a virtual machine. It's easy to get the VM up and running, but how
do you communicate with it. I found some instructions on how to
set-up IP links to a virtual machine but to make life harder I
have a fixed IP address on my desktop so this complicated life
quite a bit. Finally I learned a lot about how to set-up virtual
IP links which I already have managed to forget about :)

The next step is to get going on having a development environment
for opensolaris. I soon discovered that there was a package
manager in opensolaris which could be used to get all the needed
packages. However after downloading a number of packages I
stumbled into some serious issues. I learned from this experience
that usage of Developer Previews for OS's is even worse than newly
released OS's which I already know by experience isn't for the

So I decided to install a released opensolaris version instead
(the OpenSolaris2008.05 version). After some googling I discovered
a very helpful presentation at opensolaris developer how-to
which explained a lot about how to install a development
environment for opensolaris.

After installing opensolaris 2008.05, after following the
instructions on how to install a development environment
I am now equipped to develop DTrace probes and scripts and
try them out on my desktop.

I definitely like the idea that opensolaris is looking more
like yet another Linux distribution since it makes it a
lot simpler to work with it. I would prefer GNU developer
tools to be there from scratch but I have the same issue
with Ubuntu.

That the system calls are different don't bother me as a
programmer since different API's to similar things is
something every programmer encounters if he's developing
for a multi-platform environment. I even look forward to
trying out a lot of Solaris system calls since there are
lots of cool features on locking to CPU's, controlling
CPU's for interrupts, resource groups, scheduling
algorithms and so forth. I recently noted that most of
these things are available on Linux as well. However
I am still missing the programming API's to these


arjen said...

Mikael, I noticed that specifying a single partition on a table significantly slows things down compared to having no partitions.

Now, I appreciate you normally wouldn't use just a single partition in a table, but it indicates that there's serious overhead in having partitions, and that's a concern to me.

While the benefits may outweigh the slowdown (and essentially offer a net-speedup), I would prefer seeing less overhead in the partitioning layer.
Can you please look into this? Thanks

Mikael Ronstrom said...

Hi Arjen,
I have done tests like that and haven't seen any significant slow-down. In my tests the overhead was more or less within the error margin, so less than 2-3%. So what is the test case you claim to see this.

With many partitions there are some overhead when a table is opened since the table consists of many internal tables. We're working on improving that, but this problem we haven't seen before so need more info to react on it.

arjen said...

I encountered this when doing high-volume inserts, so lots of multi-row inserts. A serious mysqldump import should have a similar effect.

Anyway, I mainly spotted it in insert performance, and it was rather more than a few perfect, which is why I found it so worrying.