This is a continuation of my series of benchmark posts comparing Amazon RDS to a server running on Amazon EC2. Upcoming posts (probably 6 or 8 in total) will extend the scope of the benchmark to include data on our Dell r900 with traditional hard drives in RAID10, and a server in the Joyent cloud. As a reminder, my goal was to run a long-term benchmark and see how the instance performed over time. Can it sustain performance over a several-day period of intense workload? The first step was to determine the number of threads that should be used for the benchmark.

To gauge this, I ran a series of 60-second benchmarks on the RDS server, and extracted the transactions per second from them, then used the peak throughput as my target configuration. The benchmark was sysbench oltp complex, with 400 million rows (88GB of data and indexes, which is larger than memory in all of the servers I benchmarked). Here are the results:

#Threads …

The duration of a benchmark is an important factor that helps determine how meaningful it is. Most systems have some “burstable capacity,” and this can influence the results a lot. You can see this in all areas of life — you can sprint much faster than you can run a 10k race. Your stereo system components are usually advertised in both peak and sustained output. Transducers can generally hit peaks that would melt them due to heat dispersion challenges if run at that level long-term. Database servers are no different. Many components in the system have the capacity to absorb peaks. But buffers eventually fill if pressured for a long time.

When designing a benchmark, you should think about what type of performance characteristics you are looking for in your production system. If you want a system that can handle peak loads that don’t last very long, then measuring burstable capacity with a short benchmark might be okay. But if you want …

Before I begin, a disclaimer. VoltDB is not a customer, and did not pay Percona or me to investigate VoltDB’s scalability or publish this blog post. More disclaimers at the end. Short version: VoltDB is very scalable; it should scale to 120 partitions, 39 servers, and 1.6 million complex transactions per second at over 300 CPU cores, on the benchmarked configuration, with the recommended level of redundancy for HA.

First, if you’re new to VoltDB, I’ll summarize: it’s an open-source OLTP database that is designed to run on a cluster, not just a single machine, and doesn’t sacrifice consistency during a network partition. It is an in-memory shared-nothing system, and tables are partitioned across multiple servers in the cluster; high availability is ensured by keeping more copies of each partition. You query VoltDB with stored procedures, not with arbitrary SQL …

In my recent benchmarks, such as this one about the Virident TachIon card, I used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql database with size 100G. This was needed in order to test different memory/dataset size ratios. But why is it important, and how does it affect how InnoDB works internally? Let me show some details.

Internally, InnoDB uses two lists for flushing (writing pages from the Innodb buffer pool memory to disk): the LRU list and the flush list. You can see a tracking of these lists in SHOW ENGINE INNODB STATUS:

...
Pending writes: LRU 0, flush list 129, single page 0
...

It is important to understand which list is being used for flushing, because that defines what MySQL InnoDB tuning …

I again work with the system which needs high insertion rate for data which generally fits in memory. Last time I worked with similar system it used MyISAM and the system was built using multiple tables. Using multiple key caches was the good solution at that time and we could get over 200K of inserts/sec.

This time I worked with Innodb tables... it was a different system with different table structure, not to mention different hardware so It can't be compared directly, still it is nice to see you can get the numbers as high with Innodb too.

I will spare you all experiments we went through and just share final numbers. On 8 core Opteron Box we were able to achieve 275K inserts/sec at which time we started to see load to get IO bound because of log writes and flushing dirty buffers. I'm confident you can get to 400K+ inserts/sec on faster hardware and disks (say better RAID or …

In my post MySQL 5.5.8 and Percona Server: being adaptive I mentioned that I used innodb-log-block-size=4096 in Percona Server to get better throughput, but later Dimitri in his article MySQL Performance: Analyzing Percona's TPCC-like Workload on MySQL 5.5 sounded doubt that it really makes sense. Here us quote from his article:

"Question: what is a potential impact on buffered 7MB/sec writes if we'll use 4K or 512 bytes block size to write to the buffer?.. )
There will be near no or no impact at all as all writes are managed by the filesystem, and filesystem will use its own block size.. - Of course the things may change if …

A couple of days ago, Dimitri published a blog post, Analyzing Percona's TPCC-like Workload on MySQL 5.5, which was  a response to my post, MySQL 5.5.8 and Percona Server: being adaptive. I will refer to Dimitri's article as article [1]. As always, Dimitri has provided a very detailed and thoughtful article, and I strongly recommend reading if you want to understand how InnoDB works. In his post, Dimitri questioned some of my conclusions, so I decided to take a more detailed look at my findings. Let me show you my results.

Article [1] recommends using the innodb_max_dirty_pages_pct and innodb_io_capacity parameters to …

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 …