This is to follow up my previous post with kernel_mutex problem.

First, I may have an explanation why the performance degrades to significantly and why innodb_sync_spin_loops may fix it.
Second, if that is correct ( or not, but we can try anyway), than playing with innodb_thread_concurrency also may help. So I ran some benchmarks with innodb_thread_concurrency.

My explanation on the performance degradation is following:
InnoDB still uses some strange mutex implementation, based on sync_arrays (hello 1990ies), I do not have a good reason why it is not yet replaced.
Sync_array internally uses pthread_cond_wait / pthread_cond_broadcast construction, and on pthread_cond_broadcast call, all threads, competing on mutex, wake up and start …

Problem with kernel_mutex in MySQL 5.1 and MySQL 5.5 is known: Bug report. In fact in MySQL 5.6 there are some fixes that suppose to provide a solution, but MySQL 5.6 yet has long way ahead before production, and it is also not clear if the problem is really fixed.

Meantime the problem with kernel_mutex is raising, I had three customer problems related to performance drops during the last month.

So what can be done there ? Let’s run some benchmarks.

But some theory before benchmarks. InnoDB uses kernel_mutex when it starts/stop transactions, and when InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is inside kernel_mutex. That is to see kernel_mutex in action, we need many concurrent but short transactions.

For this we will …

As I mentioned in previous post on Virident FlashMAX MLC, beside sysbench benchmark, I also run tpcc-mysql (to compare performance Virident FlashMAX vs Fusion-io ioDrive Duo)

The report with results is there: http://www.percona.com/files/white-papers/virident-mlc-tpcc.pdf

The graphical result for tpcc-mysql 5000W:

My conclusions from this benchmark:

• Virident FlashMAX provides stability of performance and reveals a denser throughput.
• In addition to stability, in many cases there is also a better throughput in MySQL (up to 40\%) using the Virident …

I tend to speak highly of the random query generator as a testing tool and thought I would share a story that shows how it can really shine. At our recent dev team meeting, we spent approximately 30 minutes of hack time to produce test cases for 3 rather hard to duplicate bugs. Of course, I would also like to think that the way we have packaged our randgen tests into unittest format for dbqp played some small part, but I might be mildly biased.

The best description of the randgen’s power comes courtesy of Andrew Hutchings – “fishing with dynamite“. This is a very apt metaphor for how the tool works – it can be quite effective for stressing a server and finding bugs, but it can also be …

When we’re looking at benchmarks we typically run some stable workload and we run it in isolation – nothing else is happening on the system. This is not however how things happen in real world when we have significant variance in the load and many things can be happening concurrently.

It is very typical to hear complains about MySQL interactive performance – serving simple standard web traffic is drastically impacted when some heavy queries are ran in background or backup is done with mysqldump – a lot more than you would expect from simple resource competition. I finally found some time to look further in this problem and see what can be done to remedy it.

We designed the benchmark the following way – there is a small table (200MB) which completely fits in the Innodb Buffer Pool (512MB). We also have larger table 4GB which does not fit in the buffer pool. We’re running uniform sysbench OLTP on the …

I have been following Virident for a long time (e.g. http://www.mysqlperformanceblog.com/2010/06/15/virident-tachion-new-player-on-flash-pci-e-cards-market/). They have great PCIe Flash cards based on SLC NAND.
I always thought that Virident needed to come up with an MLC card, and I am happy to see they have finally done so.

At Virident’s request, I performed an evaluation of their MLC card to assess how it handles MySQL workload. As I am very satisfied with the results, I wish to share my findings in this post.

But first, I wish to offer an overview of the card.

Virident FlashMax Cards are …

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 of all systems ( results in throughput per 10 sec, more is …

When I mention Galera replication as in my previous post on this topic, the most popular question is how does it affect performance.

Of course you may expect performance overhead, as in case with Galera replication we add some network roundtrip and certification process. How big is it ? In this post I am trying to present some data from my benchmarks.

For tests I use tpcc-mysql, datasize 600W (~60GB) with buffer pool 52GB. Workload is run under 48 user connections.
Hardware:

• 1st node: HP ProLiant DL380 G6
• 2nd node: Dell PowerEdge R815
• Both nodes use …

As part of work on “High Performance MySQL, 3rd edition”, Baron asked me to compare different MySQL version in some simple benchmark, but on decent hardware.

So why not.

I took our Cisco UCS C250 and ran simple sysbench oltp read-write all data fits into memory workload.

Versions in question:

• MySQL 4.1
• MySQL 5.0
• MySQL 5.1 (with built-in InnoDB)
• MySQL 5.1 with InnoDB-plugin
• MySQL 5.5
• MySQL 5.6

All versions are vanilla MySQL, not Percona Server.

The results are there:

There is …

It is known that MySQL due internal limitations is not able to utilize
all CPU and IO resources available on modern hardware.
Idea is to run multiple instances of MySQL to gain better performance on Fusion-io ioDrive card.

Full report is available in PDF

For tests we used tpcc-mysql package, which generates TPCC-like workload on MySQL systems.

• Server hardware: Dell PowerEdge R815
• Storage: Fusion-io ioDrive Duo 640GB MLC. Fusion-io driver version: 2.3.1 build 123; Firmware v5.0.7, rev 101971
• Software: Percona Server 5.5.15
• Client hardware: IBM x3650

Fusion-io ioDrive Duo 640GB MLC card was provided by Fusion-io.

More details on hardware and software …