A few days ago MariaDB, MySQL and Percona all three released new versions of the 5.5 server. So I decided it’s time to run sysbench once more and compare the OLTP performance. The test candidates are:

• MariaDB-5.5.24, using either XtraDB (default) or InnoDB
• MySQL-5.5.25
• Percona Server 5.5.24-26.0

For the benchmarks I used our trusty old pitbull machine which has 24 cpu cores, 24G of RAM and a nice RAID-0 composed of 3 SAS SSD.

The benchmark was sysbench-0.5 multi-table OLTP, using 8 tables with total 10G of data. InnoDB buffer pool was 16G, InnoDB log group capacity 4G (the maximum for MySQL). The mysqld process was constrained to 16 cores, sysbench to the other 8.

The only nonstandard tuning was for I/O scalability: innodb_io_capacity = 20000 and innodb_flush_neighbor_pages = none (for Percona Server

This article describes how I tuned MariaDB to give the best write throughput with SSD based storage.

When you have a write-heavy application writing into InnoDB, you will probably experience the InnoDB Checkpoint Blues. The effect manifests as stalls – short periods of time where the troughput falls to zero and I/O activity goes crazy. The phenomenon is well known and described i.e. here. More background about checkpointing can be found

Often application needs to know where a user is physically located. The easiest way to figure that out is by looking up their IP address in a special database. It can all be implemented in MySQL, but I often see it done inefficiently. In my post I will show how to implement a complete solution that offers great performance.

First you will require a database mapping network addresses to real locations. There are various resources available, but I chose the one nginx web server uses with its geoip module. GeoLite City comes in CSV format and is available for download with no charge from MaxMind.

The archive contains two files. GeoLiteCity-Blocks.csv lists all IP

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

I had to benchmark an EC2 instance to see whether a database could be safely moved to it. It is a good practice, which helps avoiding surprises when an instance or its storage are allocated in a noisy neighborhood, where the neighbors use so much resources that it affects the performance of our MySQL database. It is understandable that one can never get very reliable results on EC2, this is a shared environment after all, and that some fluctuations should be expected, however it is still good to know the numbers. I started my benchmarks and everything seemed fine at first, but then sometimes statistics I was getting started looking quite odd.

I was running the benchmarks on a High-CPU Extra Large Instance and couldn’t see any reliability in the results at all. I mean, in one moment I was getting poor throughput and horrible response

Security-Enhanced Linux or SELinux is a Linux kernel feature that provides a mechanism for supporting access control security policies. It enables a system administrator to create an extra set of rules that define allowed operations for programs even after the standard controls are checked. In other words, SELinux can help improving system security by restricting access of an application to only a few resources it actually needs, which makes it more difficult for an attacker to gain access to the entire system through exploiting any possible vulnerabilities in the application.

However as rarely anything in life is free, is there any price we have to pay to use SELinux on a MySQL server?

I ran a simple MySQL benchmark first with database working in a system with SELinux enabled (SELINUX=enforcing), and then also with

There are probably thousands of articles on the Internet about disk statistics in Linux, what various columns mean, how accurate the information is, and so on. I decided to attack the problem from a little bit more practical side. Hopefully this will be just the first of many future posts on identifying various I/O related performance problems on a MySQL server.

Linux exposes disk statistics through /proc/diskstats. However the contents of this file isn’t something anyone can understand quickly. It needs a tool to transform the information into something human readable. A tool that is available for any Linux distribution is called iostat and comes with sysstat package.

Usually you want to call iostat one way:

iostat -xkd <interval> <block device>

In one of my previous posts, “How to resize InnoDB logs?”, I gave the advice on how to safely change the size of transaction logs. This time, I will explain why doing it may become necessary.

A brief introduction to InnoDB transaction logs

The transaction logs handle REDO logging, which means they keep the record of all recent modifications performed by queries in any InnoDB table. But they are a lot more than just an archive of transactions. The logs play important part in the process of handling writes. When a transaction commits, InnoDB synchronously makes a note of any changes into the log, while updating the actual table files happens asynchronously and may take place much later. Each log entry is assigned a Log Sequence Number

This week we talk with Martin Farach-Colton of Tokutek about what's new with TokuDB.

News/Events/Feedback
Oracle announced the MySQL Connect Conference to be held in San Francisco on Saturday, September 29 and Sunday, September 30th. Call for papers and registration will open on April 16th.

There is a free webcast on Efficiently deploying new MySQL applications on Windows .

read more

MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn’t enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible.

The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.

Let’s assume there is an application, which collects user activity in various places. The application uses a poorly indexed database, so there are plenty of examples to choose from. Our example query performs a full table scan, which means it reads all rows from the table it uses.

How important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.

It all begins with the specific way InnoDB organizes data internally. There are two major pieces of information that anyone should know: