As ext4 is a standard de facto filesystem for many modern Linux system, I am getting a lot of question if this is good for SSD, or something else (i.e. xfs) should be used.
Traditionally our recommendation is xfs, and it comes to known problem in ext3, where IO gets serialized per i_node in O_DIRECT mode (check for example Domas’s post)

However from the results of my recent benchmarks I felt that this should be revisited.
While I am still running experiments, I would like to share earlier results what I have.

I use STEC SSD drive 200GB SLC SATA (my thanks to STEC for providing drives).

What I see, that ext4 still has problem with O_DIRECT. There are results for “single file” with O_DIRECT case (sysbench fileio 16 KiB blocksize random write workload):

…

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at.

Index Condition Pushdown

Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as:

KEY `i_l_partkey` …

Traditionally the most benchmarks are focusing on throughput. We all get used to that, and in fact in our benchmarks, sysbench and tpcc-mysql, the final result is also represents the throughput (transactions per second in sysbench; NewOrder transactions Per Minute in tpcc-mysql). However, like Mark Callaghan mentioned in comments, response time is way more important metric to compare.

I want to pretend that we pioneered (not invented, but started to use widely) a benchmark methodology when we measure not the final throughput, but rather periodic probes (i.e. every 10 sec).
It allows us to draw “stability” graphs, like this one

where we can see not only a final result, but how the system behaves in dynamic.

What’s wrong with existing …

If you are terrified by the stability of the results in MySQL in my previous post, I am going to show what we can get with Percona Server. This is also to address the results presented there Benchmarking MariaDB-5.3.4

The initial benchmark is described in Benchmarks of Intel 320 SSD 600GB, and the result for MySQL 5.5.20 in case with 4 (46GB of data) and 16 tables (184GB of data) you can see in my experiments with R graphics.

How do we solve it in Percona Server ? There is whole set of improvement we made, like: …

My previous post I finished with the graph with unstable results.

There I won’t analyze causes, but rather I want to show some different ways to present results.

I enjoy working with R, and though I am not even close to be proficient in it, I want to share some graphs you can build with R + ggplot2.

The conditions of the benchmark are the same as in the previous post, with difference there are results for 4 and 16 tables cases running MySQL 5.5.20.

Let me remind how I do measurements. I run benchmark for 1 hours, with measurements every 10 seconds.
So we have 360 points – metrics.

If we draw them all, it will look like:

I will also show my R code how to …

I have a chance to test a system with Intel 320 SSD drives (NewRelic provided me with an access to the server), and compare performance with SAS hard drives.

System specification

• Dell PowerEdge R610
• Memory: 48GB
• CPU: Intel(R) Xeon(R) CPU X5650
• RAID controller: Perc H800
• RAID configuration: RAID 5 over 11 disks + 1 hot spare. RAID 5 is chosen for space purposes. In this configuration using 600GB disk, we can get 5.5T of useful space
• Intel drives: Intel 320 SSD 600GB
• HDD drives: Seagate Cheetah 15K 600GB 16MB Cache SAS
• Filesystem: XFS, mkfs.xfs -s size=4096, mount -o nobarrier

Benchmark:
For the benchmark I took a sysbench uniform oltp rw workload. 256 tables, 50mil rows each, which gives in total 3T of data.
To vary a ratio memory/data I will vary an …

MariaDB 5.3 has reached the release candidate milestone, and the 5.3 version promises a lot of new features and optimization (i.e in optimizer http://kb.askmonty.org/en/what-is-mariadb-53#query-optimizer). No surprise I wanted to check how all improvements affect general performance.

So I why don’t we run old good sysbench benchmark.

For the benchmark I took:

• HP ProLiant DL380 G6 box
• sysbench multitables oltp rw workload, 16 tables, 500mil rows each, total datasize about 30GB
• working threads from 1 to 256
• Versions: MariaDB 5.3.4, MySQL 5.5.20
• Data is stored on RAID10 HDD partition
• Like in all my recent benchmarks, I make throughput measurements each …

In our recent release of Percona Server 5.5.19 we introduced new value for innodb_flush_neighbor_pages=cont.
This way we are trying to deal with the problem of InnoDB flushing.

Actually there is also the second fix to what we think is bug in InnoDB, where it blocks queries while it is not needed (I will refer to it as “sync fix”). In this post I however will focus on innodb_flush_neighbor_pages.

By default InnoDB flushes so named neighbor pages, which really are not neighbors.
Say we want to flush page P. InnoDB is looking in an area of 128 pages around page P, and flushes all the pages in that area that are dirty. To illustrate, say we have an area of memory like …

The announcement of Percona XtraDB Cluster seems to have generated a fair bit of interest : )

Although the documentation contains more formal instructions for setting up a test cluster, I wanted to share a quick way to set up an ad-hoc cluster on a single machine to help people play with this (imho) rather amazing bit of software.

To do this, you will need kewpie (PXC will have kewpie in-tree soon)
cd basedir;
bzr branch lp:kewpie

edit the file kewpie.py like so:

=== modified file …

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

• Dell R610
• 24 core Intel Xeon X5670 @ 2.93ghz
• 72GB ECC RAM
• Brocade 825 HBA
• Local disk: RAID-10 15K SAS Ext3 (ugh)
• SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(MLC-SSD), 36GB write cache (SLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped …