I’ve been working with Clustrix team for long time on the evaluation of Clustrix product, and this is the report on performance characteristics of Clustrix under tpcc-mysql workload.

I tested tpcc 5000W (~500GB of data in InnoDB) on Clustrix systems with 3, 6, 9-nodes and also, to have base for comparison, ran the same workload on HP ProLiant DL380 G6 powered by Fusion-io card, and on SuperMicro server powered by 7 Intel SSD 320 cards (this server is equal to hardware that Clustrix uses for its nodes).

The full report is available on our page with whitepapers, and in this post I would like to highlight the most interesting points.

The chart with comparison

I wrote about Intel 320 SSD write performance before, but I was not satisfied with these results.

Somewhat each time on Intel 320 SSD I was getting different write performance, so it made me looking into this with details.

So let’s run experiment as in previous post, this is sysbench fileio random write on different file size, from 10GiB to 140GiB with 10GiB step. I use ext4 filesystem, and I perform filesystem format before increasing filesize.

The results are pretty much as in previous post, the throughput drops as we increase filesize:

Our latest MySQL white paper is Improving Percona Server performance with Flashcache on the Virident tachIOn Drive. (Virident funded the research, but as always, we wrote the report ourselves.)

The conclusion is that Flashcache can be good for read-heavy workloads, but more research is needed to understand its performance characteristics on write-heavy workloads. We explain the details of exactly how good and under what circumstances. We also developed some guidelines for sizing and pricing, to serve as advice for those interested in deploying Flashcache as a way of getting some of the benefit of flash without all of the cost, or the size limitations.

We raised topic of problems with flushing in InnoDB several times, some links:

InnoDB Flushing theory and solutions
MySQL 5.5.8 in search of stability

This was not often recurring problem so far, however in my recent experiments, I observe it in very simple sysbench workload on hardware which can be considered as typical nowadays.

Hardware: HP ProLiant DL380 G6, with 72GB of RAM and RAID10 on 8 disks.

I took sysbench multi-tables workload, with 20 tables, 10,000,000 rows each. Total database size ~58GB.
MySQL version: 5.5.16

Initial

Dealing with MySQL you might need to deal with RAID recovery every so often. Sometimes because of client lacking the proper backup or sometimes because recovering RAID might improve recovery, for example you might get point in time recovery while backup setup only takes you to the point where last binary log was backed up. I wanted for a chance to write instructions for recovery for long time
and finally I had gotten the problems with my ReadyNAS Pro 6 which I was setting up/testing at home for use for backups. I got it doing initial sync while it spotted the problem with one other drive and as such RAID volume failed. ReadyNAS has Debian inside and as you can get root login via SSH it can be recovered as

One of the typical problems I see setting up ext2/3/4 file system is sticking to defaults when it comes to behavior on errors. By default these filesystems are configured to Continue when error (such as IO error or meta data inconsistency) is discovered which can continue spreading corruption. This manifests itself in a worst way when device have some “flapping” problems returning errors every so often as this would cause some random pieces of data and meta data to be lost. Not good for system running mySQL Server. As far as I understand this problem is limited to EXT2/3/4 while over systems like XFS will not continue if consistency problems are discovered.

So how can you check what error behavior mode your file system has ? Run dumpe2fs /dev/sda1 and you will get something like this:

dumpe2fs

As you may know, Kristian Nielsen made a fix for the Group Commit Problem which we many times wrote about. The fix came into MariaDB 5.3 and Mark Callaghan tested it recently . We ported this patch to Percona Server (it is not in the main branch yet), and here are the results of my testing of the new Group Commit in Percona Server 5.1.

As background information, the problem appears when you have strict durability and recover-ability requirements, that is innodb_flush_log_at_trx_commit=1, sync_binlog=1 and you do not have storage that provides fast syncs (i.e. you do not have a battery-backed cache on your RAID card). This scenario may also appear when being on battery and your RAID card dies, automatically switching from

This is cross-posted from http://www.ssdperformanceblog.com/2011/07/fusionio-720gb-write-performance/

I’ve got a FusionIO card with 720GB capacity on my hands.

It came with a HP ProLiant DL380 G6 server. Interesting that this card is not listed on FusionIO’s products page, and neither I see such card in the list of available configurations on HP’s site. I guess this card comes as some customization option.

It seems to be a MLC card (I did not hear about FusionIO SLC cards with a capacity greater than 320GB) and cost is always an interesting question. On HP.com I can find a HP IO Accelerator

Now that flash storage is becoming more popular, IO alignment question keeps popping up more often than it used to when all we had were rotating hard disk drives. I think the reason is very simple – when systems only had one bearing hard disk drive (HDD) as in RAID1 or one disk drive at all, you couldn’t really have misaligned IO because HDDs operate in 512-byte sectors and that’s also the smallest amount of disk IO that systems can do. NAND flash on the other hand can have a page size of 512-bytes, 2kbytes or 4kbytes (and often you don’t know what size it is really) so the IO alignment question becomes more relevant.

It was and still is, however, relevant with HDD RAID storage – technology we have been using for many years – when there’s striping like in RAID0, 5, 6 or any variation of them (5+0, 1+0, 1+0+0

For a long time I’ve wanted to know how MySQL scales as you add more memory to the server. Vadim recently benchmarked the effects of increasing memory and CPU core count. He looked for a balance between utilizing the hardware as much as possible, limiting the system complexity, and lowering the price-to-performance ratio.

The outcome of the research, which was sponsored by Virident, is that as you add CPUs and increase memory size, MySQL doesn’t scale as well as we would like, and solid-state storage — specifically, the Virident tachIOn drive — has more bandwidth than MySQL can fully utilize at present. Therefore, to decrease the price-to-performance ratio and increase the utilization of the tachIOn drive, Vadim sharded the database into smaller instances and colocated them on the same machine. It’s not a

As continuation of my CPU benchmarks it is interesting to see what is scalability limitation in MySQL 5.6.2, and I am going to check that using PERFORMANCE SCHEMA, but before that let’s estimate what is potential overhead of using PERFORMANCE SCHEMA. So I am going to run the same benchmarks (sysbench read-only and read-write) as in previous post with different performance schema options and compare results.

I am going to use Cisco UCS C250
with next settings:

• PERFORMANCE SCHEMA disabled (NO PS)
• PERFORMANCE SCHMEA enabled, with all consumers ON (PS on)
• PERFORMANCE SCHMEA enabled, but only global_instrumentation

Having two big boxes in our lab, one based Intel Nehalem (Cisco UCS C250) and second on AMD Opteron (Dell PowerEdge R815), I decided to run some simple sysbench benchmark to compare how both CPUs perform and what kind of scalability we can expect.

It is hard to make apples to apples comparison, but I think it is still interesting.
Cisco UCS C250 has total 12 cores / 24 threads of Intel Nehalem X5670, and Dell PowerEdge R815 has 48 cores of AMD Opteron 6168.
One of biggest difference is that Cisco is running CentOS 5.5 and Dell R815 is based on RedHat EL 6. I will probably will need to rerun benchmark after upgrade Cisco to CentOS 6 ( will be it even released or

You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current problems, and then we will go to solutions.

The basic problem is that, unfortunately, none of the existing flushing implementations (including both MySQL native adaptive flushing and that in Percona Server) can handle it properly. Our last invention, “keep_average”, is doing a very good job on systems based on SSD/Flash storage, but it is not so good for regular slow hard drives.

Let me state the following: If you have a lot of memory (and this is not rare nowadays, for example

We’ve published a new white paper that explains how to stop sharding and start scaling vertically with PCI-E flash drives, specifically the Virident tachIOn drive, which offers consistent, low-latency IO performance. I’ve been beating this drum for a while, so it’s a great feeling to have an explicitly recommended reference architecture: buy flash storage first, shard as a last resort. From the summary: The sharding approach that has been advocated for the last five years or so is becoming increasingly questionable advice in some environments. Today’s solid-state PCIe hardware offers extremely high-bandwidth, low-latency I/O performance, exemplified by the Virident tachIOn drive. “Scaling up” is once again a viable and economical strategy for MySQL, and “scaling out” need no longer be the default database architecture.

Is it a good idea to deploy your database into the cloud? It depends. I have seen it work well many times, and cause trouble at other times. In this blog post I want to examine cloud-based I/O. I/O matters a lot when a) the database’s working set is bigger than the server’s memory, or b) the workload is write-heavy. If this is the case, how expensive is it to get good performance, relative to what you get with physical hardware? Specifically, how does it compare to commodity solid-state drives? Let’s put them in the ring and let them duke it out.

I could do benchmarks, but that would not be interesting — we already know that benchmarks are unrealistic, and we know that SSDs would win. I’d rather look at real systems and see how they behave. Are the theoretical advantages of SSDs really a big advantage

Mat Keep’s blog post on InnoDB-vs-MyISAM benchmarks that Oracle recently published prompted me to do some mathematical modeling of InnoDB’s scalability as the number of cores in the server increases. Vadim runs lots of benchmarks that measure what happens under increasing concurrency while holding the hardware constant, but not as many with varying numbers of cores, so I decided to use Mat Keep’s data for this. The modeling I performed is Universal Scalability Law modeling, which can predict both software and hardware scalability, depending on how it is used.

In brief, the benchmarks are sysbench’s read-only