It’s been a busy and exciting week since we announced RethinkDB. Of all the feedback we received, the most common request was for performance numbers. Before the launch our top priority was correctness. We spent most of our time testing RethinkDB with Wordpress and adding the missing features. As a result, performance suffered. In the past week we tuned the engine back up to high performance. We’re still far from finished with the improvements we want to make, but we feel that we’ve reached a level of performance we can be proud to display.

We wrote our original benchmarking tool in Python, but during our latest benchmarks, we noticed that it was taking about as much time as the engine itself, hiding our real performance numbers. We now have a very small Objective-C program (<900 lines) that uses prepared statements in a tight loop, and times only across the mysql_stmt_execute() call.  For inserts, the …

Inspired by NILFS: A File System to Make SSDs Scream and some customers asked if they should try NILFS on their SSD disks I decided to run quick tests to see how it performs.

Installation on our Ubuntu 8.10 with SSD disk (Intel X25-E, 32GB) was pretty plain and I got partition with NILFS without problem. After that I run script for sysbench fileio:

PLAIN TEXT CODE:

1. for size in 256M 16G; do
2.    for mode in seqwr seqrd rndrd rndwr rndrw; do
3.       ./sysbench --test=fileio --file-num=1 --file-total-size=$size prepare
4.       for threads in 1 4 8; do
5.          echo PARAMS $size $mode $threads> sysbench-size-$size-mode-$mode-threads-$threads
…

So during preparation of XtraDB template for EC2 I wanted to understand what IO characteristics we can expect from EBS volume ( I am speaking about single volume, not RAID as in my previous post). Yasufumi did some benchmarks and pointed me on interesting behavior, there seems several level of caching on EBS volume.

Let me show you. I did sysbench random read IO benchmark on files with size from 256M to 5GB with step 256M. And, as Morgan pointed me, I previously made first write, to avoid first-write penalty:

dd if=/dev/zero of=/dev/sdk bs=1M

for reference script is:

PLAIN TEXT CODE:

1. #!/bin/sh
2. set -u
3. set -x
…

During preparation of Percona-XtraDB template to run in RightScale environment, I noticed that IO performance on EBS volume in EC2 cloud is not quite perfect. So I have spent some time benchmarking volumes. Interesting part with EBS volumes is that you see it as device in your OS, so you can easily make software RAID from several volumes.

So I created 4 volumes ( I used m.large instance), and made:

RAID0 on 2 volumes as:
mdadm -C /dev/md0 --chunk=256 -n 2 -l 0 /dev/sdj /dev/sdk


RAID0 on 4 volumes as:
mdadm -C /dev/md0 --chunk=256 -n 4 -l 0 /dev/sdj /dev/sdk /dev/sdl /dev/sdm

RAID5 on 3 volumes as:
mdadm -C /dev/md0 --chunk=256 -n 3 -l 5 /dev/sdj /dev/sdk /dev/sdl


RAID10 on 4 volumes in two steps:

mdadm -v --create /dev/md0 --chunk=256 --level=raid1 --raid-devices=2 …

There was small delay in our releases, part of this time we worked on features I mentioned before:
- Moving InnoDB tables between servers
- Improve InnoDB recovery time
and rest time we played with performance trying to align XtraDB performance with MySQL 5.4 ® and also port all performance fixes to 5.0 tree.

So basically we made: new split-buffer-mutex patch, which separate global buffer pool mutex into several small mutexes, and ported some Google IO fixes.
Here are results what we have so far. As usually for benchmarks I used our workhorse Dell PowerEdge R900 with 16 cores and 32GB of RAM and RAID 10 on 8 SAS disks. And again as usually our tpcc-mysql scripts with 100W …

With new updates of FusionIO drivers I was able to test it on our Dell R900 with Ubuntu 8.10 without pain of compiling drives myself and downgrading to older kernel, so I was decided to test it in strict_sync mode.

As I understand FusionIO in default mode, like Intel SSD, is "lying" to application, and fsync() is not real, it still commit only to internal memory, not to final media. And FusionIO has "strict_sync" mode to guarantee fsync is trustworthy.

So let's benchmark it - as usually I do tpcc-mysql benchmark on 100W (9GB data) with 3GB buffer_pool in O_DIRECT access.

The raw number are here

and graph is
.
Results are in TPM (Transactions Per Minute), more is better, and graph shows how result is changing in time ( axis X ).
It is too obvious from graph to comment it...

…

For one our project I needed proxy solution, and mysql-proxy is one of well-known, so it was logical first choice. The obvious question which come in mind is what performance penalty we have using mysql-proxy, version 0.7.1.
So it is easy to test. (By the way sysbench recently was pushed to Launchpad, see lp:sysbench, and Percona is going to be active developer of this project and scripting benchmarks).

I took lp:sysbench with LUA script oltp_complex_ro, and tested for couple connections, here are results (in transactions per second, more is better)

There is an article with a cute title that is gaining popularity:
Do Androids Count Electric Sheep with DB2 or MySQL?
Allegedly, DB2 is demonstrated as incredibly faster than MySQL, with a benchmark that repeats the same COUNT query 100 times.

This is a naive (at best) benchmark that doesn’t tell me nothing about the database potential. But anyway, if you enable the query cache in MySQL, the repetition of 100 queries is at least three times faster than DB2.

Try
set global query_cache_size=1024*1024;
and repeat that test.

Moreover: the table structure doesn’t correspond to the data from the freebase project.

CREATE TABLE `people` (
 `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
 `name` varchar(255) NOT …

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.

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 …