It been sometime since we benchmarked MySQL/Galera with sysbench, using it mostly for testing. Our recent visit to Percona Performance Conference showed that sysbench is probably most widely used tool for MySQL benchmarking in the community and besides it is the only benchmark I know that correctly measures response times. So I just gave it a shot with our 0.6 release.
I ran OLTP test on 1-4 large EC2 instances. At first I tried 100K row table and it was good except that the deadlock rate was too high to my taste:

nodes   users   trx/s   deadlks 95%lat
--------------------------------------
4       40      840     28.13   0.099
4       60      866     86.34   0.150
4       80      781     194.8   0.240

Note how deadlock rate escalates with the number of concurrent connections. But what is 100K rows by modern standards? Kids play. So I tried 1M rows. And it just shows that Galera cluster is cut for big tables:

In preparation for an upcoming demo, we decided to use Sphinx for the full text search. We want to do this with Drizzle but hit an obvious problem - Sphinx does not know how to speak to Drizzle to grab documents for indexing. At least that was the case until last night when I created a patch to do so using the new libdrizzle library.

You can download the patch here: sphinx-0.9.8.1-drizzle.patch

I’m releasing this patch BSD, so do with it what you want. I’m hoping Andrew (the Sphinx author) will include this in the main package in an upcoming release. :)

So for now, to get things working, we first need to patch the sphinx-0.9.8.1 source and build/install:

Slashdot news post from today: Monty is doing MySQL refactoring and doing MySQL alliance.
Oh come on... MySQL is a trademark by Sun, to start with. Monty's product is MariaDB. MySQL started refactoring half a year ago. Does a MySQL engineer have to explain this kind of things to a journalist?

The following was in the just released monthly bug report for the Falcon storage engine:

“With the news that Sun has aggreed to be purchaced by Oracle, Some inevitable changes will occur. Once the acquisition is made, the need for Falcon as a MySQL storage engine will be re-evaluated. Until then, Falcon will continue to improve stability and performance. The team will also evaluate other technical niches that may be unique to Falcon.”

I for one would be very disappointed to see Falcon not supported by Oracle. I know they have worked very hard to create a next-generation storage engine.  While it could be argued that InnoDB can fill all use cases, I believe that choices are a good thing and having one less choice is not a good thing.

Good luck all on the team. You have been nothing but kind and generous when answering my dumb questions via email and in person. You can count my vote for “keep it!!”.

The talk I gave at the Percona Performance Conference at the MySQL
Users Conference in April 2009 can be found
at http://tokutek.com/images/blog/mysqluc09/kuszmaul-mysqluc-percona-09-slides.pdf.

This talk provides some examples where covering indexes help, and
then describes a performance model that can be used to understand and
predict query performance.  It covers clustering indexes (which are a
kind of “universal” covering index), and describes the asymptotic
performance of Fractal Tree indexing (but sorry, it doesn’t yet
explain how Fractal Tree indexes work.) We’re working on writing a
white paper to explain how they work, but we’ve simply been too
busy.  The talk concludes with the graph (shown above) that
illustrates iiBench …

So in my previous post I showed some benchmarks showing a large drop off in performance when you fill the x-25e. I wanted to followup and say this: even if you do everything correctly ( i.e. leave 50%+ space free, disable controller cache etc ) you may still see a drop in performance if your workload is heavily write skewed.  To show this I ran a 100% random read sysbench fileio test over a 12GB dataset (37.5% full ) , the tests were run back-to-back over a several hours , here is what we see:

*Note the scale is a little skewed here ( i start at 2500 reqs ).

Each data point represents 2 million IO’s, so somewhere after about 6 million IO’s we start to drop.  At the end it looks like we stabilize around2900-3000 requests per second, an overall drop of about 25%.

It appears that my presentation isn’t available at the conference site. I’ve added it below.   Sorry for the delay. 

Without some context that I talked about at the conference the presentation may not make sense.

Partitioning creates a grain in the table.  Select queries that go with that grain can be quicker, at times much faster, but select queries that go against that grain can be slower, at times much slower. 

An example is a table that is partitioned by date and has an orderId primary key.  Queries that select data by date will either be almost as fast (partitioning can add a slight overhead) as the non-partitioned table to much faster.   But queries that don’t query by date will have to query all the partitions.  A table partitioned 12 times, one partition for each month, results in 12 index partitions.  As the indexes are partitioned by date, a query by the …

You guys are generating an amazing amount of feedback on your blogs. Matt mentioned in the April Wrap-Up that there were 8.6 million comments! Comments are flying in every second of the day.

And have you ever had one of those blog posts that was good, but the real action was in the comments? The blog post is only half the story, it’s the feedback from everyone else that fills in the rest. To make it easier to find the second half of these stories we’ve added comment search to WordPress.com search.

Select the comments options from the WordPress.com search page and we’ll hunt through the millions of comments that have been added to WordPress.com blogs to find what you are looking for. To …

For a couple weeks now, we've had a MySQL server at work running MySQL 5.1.34 and the Percona XtraDB 1.0.3-5 plug-in. I'm testing an upgrade path for our current MySQL 5.0.xx based servers.

Aside from some confusion about the initial setup (getting the built-in InnoDB to stay out of the way), things have gone very well. All of our largest and most active tables have been converted to the new Barracuda file format and I tested compression on the two largest. The first didn't fare so well, but it's a fairly over-indexed table with small rows. The second, however, contains a decent sized TEXT column (classified posting bodies) and it compresses quite nicely. Any change in CPU utilization is not significant.

I hope to soon …

One of our colleagues, Dimitri, at the Paris Sun solution center has developed a real neat and useful tool called dim_STAT. To make it short it's a tool for both high-level and detailed, monitoring and performance analysis of Solaris and Linux systems.

Data is collected and saved in a MySQL database, and it provides a very functional web base user interface. It allows real time or off line monitoring, multi-host etc.

Actually what is really interesting with dim_STAT is that, when I'm benchmarking or tryinng to find a performance bottleneck, I can collect all the data I need and come back later for analysis.

Recently, Dimitri has added a new tool db_STRESS, that allows us to put load on a database system and gives a high level metric (TPS: Transactions per seconds) and therefor allows us to compare how different systems compare together.
The point of this post is …