I have been benchmarking Waffle Grid using the new Innodb Plugin 1.03 the past couple of days. Let me say the plugin is fast. Which got me thinking, generally when you fix a bottleneck another area becomes a bottleneck… its a vicious cycle really. I figured why not benchmarks several different settings just to see what sort of improvement or detriment we get in Inno. This hopefully will lead to the next place to look for potential performance improvements. For the test I chose a somewhat IO bound setup and a CPU bound setup.

The IO bound setup was a 20W test, 768M buffer pool.
The CPU boud setup was a 20W test, 5GB buffer pool.

I decided to start with the Double Write Buffer. For those who are not familiar with the double write buffer check out the docs or …

Some months ago, Google released a patch for InnoDB that boosts performance on multi-core servers. We decided to incorporate the change into the InnoDB Plugin to make everybody happy: users of InnoDB don’t have to apply the patch, and Google no longer has to maintain the patch for new versions of InnoDB. And it makes us at Innobase happy because it improves our product (as you can in this post about InnoDB Plugin release 1.0.3).

However, there are always technical and business issues to address. Given the low-level changes in the patch, was it technically sound? Was the patch stable and as rock solid as is the rest of InnoDB? Although it was written for the built-in InnoDB in MySQL 5.0.37, we needed to adapt it to the InnoDB Plugin. Could we …

I don’t know if it is because of my science background, I am a physicist, I do like graphs, especially when I do performance tuning. With UNIX like operating systems, the vmstat command give you an easy way to grab many essential performance counters but, generating graphs from vmstat output with tools like OpenOffice Calc is time consuming and not very efficient. In order to solve this, I wrote a few scripts using gnuplot but they are not very easy to work with. Then, doing some benchmarks with DBT2, I found the vmplot.sh script and… I like that one. I just hacked it little bit to make it keeps the graph on screen, adding the “-persist” parameters to the gnuplot invocations. The script will produce 7 graphs that will be displayed on screen and save in png format in /tmp. The graphs it produces are the following:

• CPU: graphs idle, user, sys and wait time
• …

So we have already looked at sysbench & dbt2 tests… now we have to look at the new Juice DB benchmark. Juice runs a series of queries generate its load, these queries are combined into a workload. I tested the v1010 with a mixed workload ( mix of short & long updates and selects ), a mixed simple workload ( mix of short running updates and selects ) , and a read only ( selects which are designed to hit the disk ) . Because this is still an evolving benchmark I am including results from an Intel MLC drive (note these boxes are vastly different).  Keep in mind this is not a completely fair comparison. The Intel drive is not the enterprise class drive, but even with the SLC drive I don’t think its a fair comparison. The price difference between these two solutions is ~$50/GB -vs- ~$12.5GB.

The setup for this test created about a 20GB database, with each of the 3 large tables coming in around 6 GB each. I tested primarily with a 768M …

Continuing my series on the Violin Memory 1010 I am turning my attention to the DBT2 benchmark which simulates an OLTP workload. I started with my typical “waffle” workload which is a 20 warehouse setup ( about 2.5 GB ) with a 768M buffer pool and I compared it to a 5G buffer pool with the same setup.  The ultimate goal or the nirvana state of any system is to have the performance of the storage system be as fast as having everything all in memory. The closer we can get the better off we are. The sad thing is even with the fastest of flash solutions we see times in the 70-300 microsecond response time range,  which is very  far off the nano second response time delivered by memory. That being said lets see how close we can get to a fully cached database:

I am including the Intel #’s for perspective here and to show just how close we can get full in memory speeds. The fact is I am comparing a potentially …

One little bit of wisdom I would like to pass on:

If a program runs fast with 20 threads, that does not mean it will run fast with 50. And if it runs fast with 50, it does not mean that it will run fast with 100, and if it runs fast with 100 ... don't bet on it running fast with 200 :)

In my last blog I discussed some improvement to the performance of PBXT running the DBT2 benchmark. Despite the overall significant increase in performance I noted a drop off at 32 threads that indicated a scaling problem. For the last couple of weeks I have been working on this problem and I have managed to fix it:

As before, this test was done using MySQL 5.1.30 on an 8 core, 64-bit, Linux machine with an SSD drive and a 5 warehouse DBT2 …

There has been much talking about MySQL performance related to logging. Since MySQL 5.1.21, when Bug #30414 was reported (Slowdown (related to logging) in 5.1.21 vs. 5.1.20) I have been monitoring the performance of the server, both on 5.0 and 5.1.
Recently, I got a very powerful server, which makes these measurements meaningful.
Thus, I measured the performance of the server, using all publicly available sources, because I want this benchmark to be repeatable by everyone.
I will first describe the method used for the benchmarks, and then I report the results.The serverThe server is a Linux Red Hat Enterprise 5.2, running on a 8core processor, with 32 GB RAM and 1.5 TB storage.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

$ cat /proc/cpuinfo |grep "processor\|model name" | sort …

I was asked today about what is the cost of adding an index on a frequently updated column ( like a timestamp, count, or weight )… typically my answer is it depends. But for this question it was narrowed down to a specific case. An update on a secondary index based on a PK lookup. I decided to try and give an exact answer. I hacked the Juice DB Benchmark to attack my medium sized table ( which magically already had a count column in it ). I then cranked up the test. A few more details Query 23 updated a column without an index, queries 21,23,24 updated the d_count column. query 21 adds 5 to the count, query 22 adds 150, query 24 subtracts 1…. here are the results:

With a solo index on d_count:

Run Number:  86  threads:  8 Length :  340 LoadType: upd
Total Test Runtime = 375.245010137558 seconds, limiting results to 300 seconds however
QNum:     21 ... QCount:  78448 ... QTime:   0.003985 ... Max:   0.095937 ... FlatTime:   0.003673 …

Ooops! Mark Callaghan of Google is one of world’s experts in InnoDB, and a frequent blogger on its performance characteristics. The InnoDB Plugin 1.0.3 is much more scalable on multi-core systems because of the contributions he has made (along with Ben Handy).

Mark will deliver a keynote the on Google’s use of MySQL and InnoDB on Tuesday morning at the MySQL Conference, and another talk on Wednesday. As Mark says, “Although Innodb is not in the title, it is prominent in both of the talks I will do”:

• This is not a web app – the evolution of a MySQL deployment at Google Tues, 9am
• …

That should read “Talks, Talks, Talks” … There will be several presentations by InnoDB experts at the upcoming 2009 MySQL Conference and Expo. Whether you’re a newbie or an experienced DBA deeply familiar with InnoDB, you won’t want to miss these important talks about InnoDB:

• Innovative Technologies for Performance and Data Protection by Ken and Heikki, Tues, 11:55am
• Crash Recovery and Media Recovery in InnoDB by Heikki, Wed, 2pm
• InnoDB Internals: InnoDB File Formats & Source Code Structure by Heikki and Calvin, Wed, 5:15pm
• …