One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the original developers) did also. But I didn’t know the …

MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks for different queries and explain the results so that you have a better understanding of when using the Hash Join will be best and when not. Although Hash Joins are available since MariaDB 5.3, but I will be running my benchmarks on the newer MariaDB 5.5.

Overview

Hash Join is a new algorithm introduced in MariaDB 5.3/5.5 that can be used for joining tables that have a equijoin conditions of the form tbl1.col1 = tbl2.col1, etc. As I mentioned above that what is actually implemented is the Classic Hash Join. But its known as Block Nested Loop Hash (BNLH) Join in MariaDB.
The Classic Hash Join Algorithm …

Every time I have a conversation on SSD, someone mentions btrfs filesystem. And usually it is colored as a solution that will solve all our problems, improve overall performance and SSD in particular, and it is a saviour. Of course it caught my curiosity and I decided to perform a benchmark similar to what I did on ext4 filesystem over Intel 520 SSD.
I was prepared for surprises, as even on formatting stage, mkfs.btrfs says that filesystem is EXPERIMENTAL. In case with filesystems I kind of agree with Stewart, so question #1, what you should ask deciding on what filesystem to use, is “Was this filesystem used in a production more than 5 years?”, so from this point, btrfs has a long way ahead.

How you can get btrfs? …

In my raw IO benchmark of Intel 520 SSD we saw that the drive does not provide uniform throughput and response time, but it is interesting how does it affect workload if it comes from MySQL.
I prepared benchmarks results for Sysbench OLTP workload with MySQL running on Intel 520.
You can download it there.

There I want to publish graphs to compare Intel 520 vs regular RAID10.

Throughput:

Response time:

So despite big variation in raw IO, it seems it …

I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

OK, let’s start off with the configuration details.

Configuration

First of all let me describe the EC2 instance type that I used.

EC2 Configuration

I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

High-Memory Quadruple Extra Large Instance
68.4 GB of memory
26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute …

I was lucky enough to get my hands on new Fusion-io ioDrive2 Duo card. So I decided to run the same series of tests I did for other Flash devices. This is ioDrive2 Duo 2.4TB card and it is visible to OS as two devices (1.2TB each), which can be connected together via software RAID. So I tested in two modes: single drive, and software RAID-0 over two drives.

I should note that to run this card you need to have an external power, by the same reason I mentioned in the previous post: PCIe slot can provide only 25W power, which is not enough for ioDrive2 Duo to provide full performance. I mention this, as it may be challenge for some servers: some models may not have connector for external power, and for some …

Sysbench has three distribution for random numbers: uniform, special and gaussian. I mostly use uniform and special, and I feel that both do not fully reflect my needs when I run benchmarks. Uniform is stupidly simple: for a table with 1 mln rows, each row gets equal amount of hits. This barely reflects real system, it also does not allow effectively test caching solution, each row can be equally put into cache or removed. That’s why there is special distribution, which is better, but to other extreme – it is skewed to very small percentage of rows, which makes this distribution good to test cache, but it is hard to emulate high IO load.

That’s why I was looking for alternatives, and Zipfian distribution seems decent one. This distribution has a parameter θ (theta), which defines how skewed the distribution is. A physical sense of this parameter, if to apply …

In my previous post with results for Fusion-io ioDrive we saw some instability in results, I was pointed that it may be fixed in new drivers VSL 3.1.1. I am not sure if this driver is available for everyone – if you are interested, please contact your Fusion-io support representative. I installed new drivers and firmware, and in fact, the result improved.

Information about driver and firmware: Firmware v6.0.0, rev 107006. Fusion-io driver version: 3.1.1 build 172.

Actually an upgrade was not flawless, after a firmware upgrade I had to perform low-level formatting, which erase all data. So if you want to do the same – make sure you copy your data.

So there are results for driver 3.1 (with comparison to previous driver 2.3)

Random writes:
…

I still continue to run benchmarks of different SSD cards. This time I show numbers for Virident FlashMAX 1400. This is a MLC PCIe SSD device. There are couple notes on these results.
First, this time I use a different server. For this benchmark it is Cisco UCS C250, while for previous results I used HP ProLiant DL380 G6.

Second note is, that I use a mode “turbo=1″ for Virident card. What does that mean? Apparently PCIe specification has a limitation on available power. If I am not mistaken it is 25W, however Virident to provide full write performance requires 28W. And while many servers can handle 28W on PCIe, this is a non-standard mode, and Virident by default uses 25W (turbo=0). To force full power, I load a driver with turbo=1. I also use “maxperformance” formatting for Virident, …

Following my series of posts on testing different SSD, in my last post I mentioned that SATA SSD performance is getting closer to PCIe cards. It really makes sense to test it under MySQL workload, but before getting to that, let me review the same workload on Fusion-io ioDrive PCIe card. This is yet previous generation of Fusion-io cards, but this is the one that has biggest installation base.

Driver information: Fusion-io driver version: 2.3.10 build 110; Firmware v5.0.7, rev 107053

Following the format of previous benchmarks, first is random write async 16KB case.

We can see some wave-like pattern with throughput 350-400 MiB/sec.

Random reads, async:

…