POSIX Asynchronous I/O benchmark with Sysbench and MySQL (InnoDB)

Sysbench in OLTP mode allows comparing different POSIX AIO implementations by exercising MySQL with an InnoDB database type (currently InnoDB being the only database type using AIO). See http://sysbench.sourceforge.net for a detailed explanation of the SQL queries run by the test.

This benchmark was run against 3 MySQL servers:

  • one using libposix-aio (using kernel support)
  • one using librt aio implementation (using user threads)
  • the last one using InnoDB native simulated AIO

For the first test, sysbench was run against the three servers with a large number of requests (2 000 000) using 16 then 64 threads.
The second test was meant to show how the implementations scaled with the number of concurrent threads using a lower number of requests (20 000).

MySQL was modified to use POSIX AIO. Patches are available here as a quilt patchset. The first patch in the series adds generic POSIX AIO support to the InnoDB engine. The second patch adds detection of libposix-aio for use in place of librt.

CPU: 2 Intel(R) Xeon(TM) CPU 2.80GHz (hyperthreaded)
Kernel: 2.6.10 with POSIX AIO patches
glibc: 2.3.3 NPTL or 2.3.5 NPTL (see below)
MySQL 4.1.12: Compiled against glibc 2.3.3 NPTL and libposix-aio
Compiled against glibc 2.3.5 NPTL and librt
Compiled against glibc 2.3.3 NPTL
InnoDB data partition: 60 GB dedicated ext2
Test 1
The first test was run using the following sysbench commands for each of the 3 MySQL servers:

sysbench --test=oltp --oltp-table-size=200000 --mysql-user=root prepare
sysbench --test=oltp 16 --max-requests=2000000 --mysql-user=root run
sysbench --test=oltp --mysql-user=root cleanup

It was then repeated with --num-threads=64.

Results are available here.

As can be seen from the results, the Linux POSIX AIO library compares well with the other implementations.

One thing to note is that the native InnoDB simulated AIO performs better than the librt implementation although the approach seems to be the same (user level threads). This might be due to the fact that the InnoDB implementation is highly optimized for its task whereas librt is more generic.

Test 2
The second test was run using the following sysbench_threads script for each of the 3 MySQL servers:



threads="1 2 4 8 16 32 64 128 256"

if [ $# -eq 0 ]; then
    echo "Usage: `basename $0` test_name"
    exit 1

# Create table
$SYSBENCH --test=oltp --oltp-table-size=200000 --mysql-user=root prepare

for num_threads in $threads

    echo "Starting mysql-$1 bench with $num_threads threads"
    $SYSBENCH --num-threads=$num_threads --max-requests=20000 --test=oltp --mysql-user=root run > $LOGFILE

# drop table
$SYSBENCH --test=oltp --mysql-user=root cleanup

The script was run three times as

sysbench_threads paio
sysbench_threads rt
sysbench_threads noaio

Results are available here.

Page maintained by: Sébastien Dugué. Valid XHTML 1.0! Valid CSS!