This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab: a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card.

To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, 52G, an 144G on a tpcc-mysql workload with 1000W (around 100GB of data). This combination of buffer pool sizes gives us different data/memory ratios (for 13G - an I/O intensive workload, for 52G - half of the data fits into the buffer pool, for 144G - the data all fits into memory). For the cases when the …

As we can see, MySQL 5.5.8 comes with great improvements and scalability fixes. Adding up all the new features, you have a great release. However, there is one area I want to touch on in this post. At Percona, we consider it important not only to have the best peak performance, but also stable and predictable performance. I refer you to Peter's post, Performance Optimization and Six Sigma.

In Percona Server (and actually even before that, in percona-patches builds for 5.0), we added adaptive checkpoint algorithms, and later the InnoDB-plugin included an implementation of  "adaptive flushing". This post shows the differences between them and MySQL.

The post also answers the question of whether we are going to have releases of Percona Server/XtraDB based on the …

A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people wrote in to suggest alternative ways of implementing this.  I just got around to benchmarking those alternatives today across two large EC2 machines:

(Measured in transactions/second – higher is better)

What is the conclusion?  With the exception of my original option2, they actually all perform fairly similar.  The Flickr and Option1 tests perform marginally better.  Test “arjen2″ is option2, but with a MyISAM table — it suffers a little because EC2 can be a little high for latency, and there’s …

The parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above 256kB the behavior changes and becomes slower. After reading a post from Ronald Bradford (here), I decide to verify and benchmark performance while varying the size of the sort_buffer. It is my understanding that the sort_buffer is used when no index are available to help the sorting so I created a MyISAM table with one char column without an index:

PLAIN TEXT CODE:

…

The title is SEO bait – you can’t do it. We’ve seen a few recurring patterns trying to achieve similar – and I thought I would share with you my favorite two:

Option #1: Use a table to insert into, and grab the insert_id:

CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;

# each insert does one operations to get the value:
INSERT INTO option1 VALUES (NULL);
# $connection->insert_id();

Option #2: Use a table with one just row:

CREATE TABLE option2 (id int not null primary key) engine=innodb;
INSERT INTO option2 VALUES (1); # start from 1

# each insert does two operations to get the value:
UPDATE option2 SET id=@id:=id+1;
SELECT @id;

So which is better? I don’t think it’s that easy to tell at a first glance, since option 2 does look more elegant – but if the next value is fetched as part of a transaction – I can see a potential …

We now have hardware in our test lab that represents the next generation of commodity servers for databases. It’s a Cisco UCS C250 server, powered by two Intel Westmere CPUs (X5670 @ 2.93GHz). Each CPU has 6 cores and 12 threads. The most amazing part is the amount of memory. It has 384GB of RAM, which is actually more space than the disks contain.  The disks are 270GB in total, with the underlying configuration RAID10 over eight 2.5″ 15K RPM disks. To make the system even more powerful, I put a FusionIO 320GB SLC card in the PCI-E slot. Here is a link to the box specs.

The server was generously provided by Cisco Systems, Inc.

So, obviously I’m anxious to …

There is a lot of discussions whenever running MySQL storing data on NFS is a good idea. There is a lot of things for and against this and this post is not about them.
The fact is number of people run their databases on NetApp and other forms of NFS storage and this post is about one of discoveries in such setup.

There are good reasons to have binary logs on NFS volume - binary logs is exactly the thing you want to survive the server crash - using them you can do point in time recovery from backup.

I was testing high volume replication today using Sysbench:

PLAIN TEXT SQL:

1. sysbench --test=oltp --oltp-table-size=10000000 --db-driver=mysql --mysql-user=root --mysql-db=sbsmall --init-rng=1 --max-requests=100000000 --max-time=600 --oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey --num-threads=8 run

On this box I got …

(Note: The review was done as part of our consulting practice, but is totally independent and fully reflects our opinion)

In my talk on MySQL Conference and Expo 2010 "An Overview of Flash Storage for Databases" I mentioned that most likely there are other players coming soon. I actually was not aware about any real names at that time, it was just a guess, as PCI-E market is really attractive so FusionIO can't stay alone for long time. So I am not surprised to see new card provided by Virident and I was lucky enough to test a pre-production sample Virident tachIOn 400GB SLC card.

I think it will be fair to say that Virident targets where right now FusionIO has a monopoly, and it will finally bring some competition to the market, which I believe is good for …

You might be familiar with Six Sigma business management strategy which is employed by variety of the companies in relationship to managing quality of its product. Six Sigma applies to number of defects - when you have reached six sigma quality in your production you would see 99.99966% of the products manufactured with no defects, or in other words there is less than 3 defects per million.

One of principles of six sigma is what customers tend to be concerned about variance a lot more than average. For example if you produce tomato soup and the average difference from declared weight is going to be 0.1 gram or 0.5 gram, probably nobody would not notice the difference. What would worry people however is significant number of very large differences, such as half empty tomato soup can.

You can see …

This run is very similar what I had on Intel SSD X25-M card, but now I use FusionIO 80GB SLC card. I chose this card as smallest available card (and therefore cheapest. On Dell.com you can see it for about $3K). There is also FusionIO IO-Xtreme 80GB card, which is however MLC based and it could be not best choice for FlashCache usage ( as there high write rate on FlashCache for both reading and writing to/from disks, so lifetime could be short).

Also Facebook team released WriteThrough module for FlashCache, which could be good trade-off if you want extra warranty for data consistency and your load is mostly read-bound, so I tested this mode also.

All …