Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently (like every other hour), or when there was no such thing as off-peak hours, in such cases you need consistent performance. But dropping tables is known to cause stalls that exhibit themselves as reduced QPS during the table drops or stalls. Percona Server since version 5.1 has a feature know as “Lazy Drop Table” that alleviates the problem to a great extent but does not get rid of it completely. In the new releases of MySQL (versions >= 5.5.23) work has been done on reducing …

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb team has chosen to use very small (just 1MB) and hard coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process.
Mark Callaghan and Facebook Team has fixed this in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date.

Here is example of gains you can expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table.

Buffer Length   |  alter table sbtest add key(c)
1MB                      34 sec
8MB …

I have a customer who is considering Percona XtraDB Cluster (PXC) in a two colo WAN environment.  They wanted me to do a test comparing PXC against semi-synchronous replication to see how they stack up against each other.

Test Environment

The test environment included AWS EC2 nodes in US-East and US-West (Oregon).  The ping RTT latency between these nodes was right around 100ms.

All environments used Percona Server or Percona XtraDB Cluster server 5.5.24.  Innodb durability was disabled for all tests (innodb_flush_log_at_trx_commit=2).  All other settings were the same kept the same unless otherwise noted.

I tested against the following setups:

Control

The control …

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 …