Here you will find details about our automated benchmark runs and long running tests. Feel free to suggest other benchmarks and tests. You can also send us your findings about benchmarks and tests, which you have run.

3x Five Minute Runs on work with 5.2-wl86 key cache partitions on and off

MariDB 5.2-wl86 sysbench benchmark comparison with key_cache_partitions off and 7 in %

Each test was run three times for 5 minutes.

Number of threads
                     1       4       8       16      32      64      128
 sysbench test
  delete            -18.36  -20.66  -11.32    5.42   -2.91  -14.62   -3.47
  insert             -2.38  -30.11   -1.64   -0.98   -1.19    0.12   -2.37
  oltp_complex_ro     0.16    2.61    4.03    2.99    3.10    5.73   20.95
  oltp_complex_rw   Dup key errors (due to sysbench)
  oltp_simple        -1.24    1.86   11.14   10.69   16.11   17.16   14.31
  select             -0.22    2.00   11.42   10.31   15.58   17.10   14.31
  update_index       -9.34   15.75   -0.36  -10.33    1.94    2.44   41.44
  update_non_index    0.73    1.04   11.12   17.32    5.30   -0.24   -9.55

 (MariaDB 5.2-wl86 key_cache_partitions off q/s /
  MariaDB 5.2-wl86 key_cache_partitions=7 q/s * 100)

key_buffer_size = 32M

Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.

MariaDB and MySQL were compiled with

BUILD/compile-amd64-max

MariaDB revision was:

lp:~maria-captains/maria/maria-5.2-wl86

revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
  WL#86: Partitioned key cache for MyISAM.
  This is the base patch for the task.

sysbench was run with the following parameters:

and this variable part of parameters

Configuration used for MariDB:

_{This page is licensed: CC BY-SA / Gnu FDL}

MariDB/MySQL sysbench benchmark comparison in %

Number of threads
                     1       4       8       16      32      64      128
 sysbench test
  delete             92.42   82.60   88.05   89.85   94.68   98.75   97.77
  insert            100.70   99.90  103.21   89.51   85.16  105.39  108.51
  oltp_complex_ro   101.09  101.83  100.32  103.78  102.10  101.29  102.92
  oltp_complex_rw    95.04   90.22   91.84   88.78  100.98  101.96  101.60
  oltp_simple       100.29   99.90  101.69  102.22  102.61  102.18  101.49
  select            101.57  101.73  100.26  102.15  101.99  102.39  102.09
  update_index       96.01  103.06  105.89  108.35  108.13  104.36  101.61
  update_non_index   99.85  102.05  110.76  119.51  119.69  118.25  122.77

(MariaDB q/s / MySQL q/s * 100)

Run on Lenovo ThinkPad T500 with dual core 2.80GHz and 4GB RAM

MariaDB and MySQL were compiled with

BUILD/compile-amd64-max

MariaDB revision was:

revno: 2818 timestamp: Wed 2010-02-17 21:10:02 +0100

MySQL revision was:

revno: 3360 [merge] timestamp: Wed 2010-02-17 18:48:40 +0100

sysbench was run with these parameters:

and this variable part of parameters

Configuration used for MariDB and MySQL:

_{This page is licensed: CC BY-SA / Gnu FDL}

sysbench v0.5 - 3x 15 Minute Runs on perro with 5.2-wl86 key cache partitions off, 8, and 32 and key buffer size 400

MariDB sysbench benchmark comparison for key_cache_partitions in % with key_buffer_size = 400MB

Each test was run 3 times for 15 minutes with 3 minutes warmup.

Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0

MariaDB and MySQL were compiled with

MariaDB revision was:

sysbench was run with the following parameters:

and these variable parameters:

Configuration used for MariaDB:

_{This page is licensed: CC BY-SA / Gnu FDL}

3x Five Minute Runs on work with 5.1 vs. 5.2-wl86 key cache partitions off

vs. 5.2-wl86 sysbench benchmark comparison in %

Each test was run three times for 5 minutes.

Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.

MariaDB and MySQL were compiled with

revision was:

-wl86 revision was:

sysbench was run with the following parameters:

and this variable part of the parameters

Configuration used for MariaDB:

_{This page is licensed: CC BY-SA / Gnu FDL}

MariDB/MySQL sysbench benchmark comparison in %

Each test was run for 5 minutes 3 times

Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.

MariaDB and MySQL were compiled with

MariaDB revision was:

MySQL revision was:

sysbench was run with these parameters:

and this variable part of parameters

Configuration used for MariaDB and MySQL:

_{This page is licensed: CC BY-SA / Gnu FDL}

3x 15 Minute Runs on perro with 5.2-wl86 key cache partitions off, 8, and 32 and key buffer size 75

MariDB sysbench benchmark comparison for key_cache_partitions in % with key_buffer_size = 75MB

Each test was run 3 times for 15 minutes with 3 minutes warmup.

Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0

MariaDB and MySQL were compiled with

MariaDB revision was:

sysbench was run with the following parameters:

and the following variable parameters

Configuration used for MariDB:

_{This page is licensed: CC BY-SA / Gnu FDL}

All of the helper and wrapper scripts we use for benchmarks and tests are available in the mariadb.org-tools project on GitHub.

_{This page is licensed: CC BY-SA / Gnu FDL}

run-sql-bench.pl is a perl script for automating runs of sql-bench (You can find sql-bench in the .)

run-sql-bench.pl can be found in the project on Launchpad. Once you have a copy of mariadb-tools, you'll find the script, and its configuration directories, in the sql-bench directory. For the purposes of this article, wherever you located your local branch of mariadb-tools will be called ${BASE_DIR}.

The run-sql-bench.pl script is located at '${BASE_DIR}/sql-bench/run-sql-bench.pl'.

Example configuration scripts used for different runs can be found in the various subdirectories of ${BASE_DIR}/sql-bench/

MariDB/MySQL sysbench benchmark comparison in % Each test was run for 5 minutes.

Number of threads
                     1       4       8       16      32      64      128
 sysbench test
  delete            103.72  101.84  106.56  102.80   94.19   86.23   65.13
  insert            102.01   95.04   97.44   89.00   82.42   81.82   85.63
  oltp_complex_ro   104.21  104.98  105.30  102.67  102.69  102.95  101.10
  oltp_complex_rw   105.08  104.34  103.60  102.90  100.76   98.41   89.94
  oltp_simple       100.66  100.44  102.82  104.23  103.08  100.55   95.90
  select            102.93  101.56  103.70  104.18  102.25  100.65   97.33
  update_index      101.74   92.33  101.69   93.09   76.45   73.67   72.88
  update_non_index  101.58   98.13   98.91   92.32   84.00   76.75   74.19

(MariaDB q/s / MySQL q/s * 100)

Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0

MariaDB and MySQL were compiled with

BUILD/compile-amd64-max

MariaDB revision was:

revno: 2821
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: maria-5.1
timestamp: Tue 2010-02-23 13:04:58 +0100
message:
fix for a possible DoS in the my_net_skip_rest()

MySQL revision was:

sysbench was run with these parameters:

and this variable part of parameters

Configuration used for MariDB and MySQL:

_{This page is licensed: CC BY-SA / Gnu FDL}

MariDB/MySQL sysbench benchmark comparison in %

Each test was run for 5 minutes.

Number of threads
                     1       4       8       16      32      64      128
 sysbench test
  delete            121.52  144.77  117.70  115.15  100.48   75.39   66.56
  insert            114.89  181.50  118.06  136.00  125.53  141.83  113.88
  oltp_complex_ro   103.13  100.99   94.65  104.14   97.87   90.18   79.93
  oltp_complex_rw   131.65  149.90  120.88  128.58  116.71   89.92   80.63
  oltp_simple       102.32  102.57   97.33   96.34   93.99   78.81   59.71
  select            102.12  102.05   96.64   97.28   93.55   81.53   59.83
  update_index      114.08  103.98  115.59  124.90  123.51  104.38   99.11
  update_non_index  134.04  147.94  150.91  150.04  152.12  108.34   89.24

insert/4 is a glitch

(MariaDB q/s / MySQL q/s * 100)

Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.

MariaDB and MySQL were compiled with

BUILD/compile-amd64-max

MariaDB revision was:

revno: 2821
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: maria-5.1
timestamp: Tue 2010-02-23 13:04:58 +0100
message:
fix for a possible DoS in the my_net_skip_rest()

MySQL revision was:

sysbench was run with these parameters:

and this variable part of parameters

Configuration used for MariaDB and MySQL:

_{This page is licensed: CC BY-SA / Gnu FDL}

• select_random_ranges (select 10 ranges with a delta as parameter)

• select_random_points (select 100 random points)

Findings:

select_random_ranges

• A delta of 100 for the ranges gives 3 - 6% performance gain

• A delta of 50 for the ranges gives 3 - 15% performance gain

• A delta of 5 for the ranges gives up to 70% performance gain

• A delta of 1 million shows no difference at all.

select_random_points

• We see up to 150% performance gain fetching index only

• We see up to 50% performance gain fetching index and data

The absolute numbers are highly RAM depended

• We see an up to 250% performance difference on a 2GB system compared to a 4GB system.

MariaDB and MySQL were compiled with

MariaDB revision was:

sysbench was run with the following parameters:

and the following variable parameters

Configuration used for MariDB:

_{This page is licensed: CC BY-SA / Gnu FDL}

We have not yet had time to benchmark properly. Here follows some things that have been discussed on the maria-discuss email list.

Aria used for internal temporary tables

By default Aria (instead of MyISAM) is used for the internal temporary tables when MEMORY tables overflows to disk or MEMORY tables can't be used (for example when you are using temporary results with BLOB's). In most cases Aria should give you better performance than using MyISAM, but this is not always the case.

CREATE TABLE `t1` (`id` INT(11) DEFAULT NULL, `tea` TEXT) 
  ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT t1 SELECT rand()*2e8, repeat(rand(), rand()*64) FROM t1;

Repeat the last row until you get 2097152 rows.

The queries tested

Results (times in seconds, lower is better):

The good news is that for common group by queries that is using summary functions there is a close to 50 % speedup of using Aria for internal temporary tables.

Note that queries Q1,Q3 and Q5 are not typical queries as there is no sum functions involved. In this case rows are just written to the tmp tables and there is no updates. As soon as there are summary functions and updates the new row format in Aria gives a close to 50 % speedup.

The above table also shows how the page size (determined by the system variable) affects the performance. The reason for the difference is that there is more data to move back/from the page cache for inserting of keys. (When reading data we are normally not copying pages). The bigger page size however allows longer keys and fewer index levels so for bigger data sets the different should be smaller. It's possible to in the future optimize Aria to not copy pages from the page cache also for index writes and then this difference should disappear.

The default page size for Aria is 8K.

If you want to run MariaDB with MyISAM for temporary tables, don't use the configure option '--with-aria-tmp-tables' when building MariaDB.

_{This page is licensed: CC BY-SA / Gnu FDL}

Running benchmarks requires a lot of different settings. In this article we collect our best known settings and recommendations.

Hardware and BIOS Settings

We have had good experiences with Intel's hyperthreading on newer Xeon CPUs. Please turn on hyperthreading in your BIOS.

NUMA

The NUMA architecture attaches resources (most important: memory) to individual NUMA nodes (typically: NUMA node = cpu socket). This results in a performance penalty when a cpu core from one NUMA node accesses memory from another NUMA node.

The NUMA topology can be checked with the numactl command:

The Linux kernel uses ACPI tables from BIOS to detect if the hardware is NUMA. On NUMA hardware extra optimizations kick in:

• if a task has been scheduled on a certain NUMA node, the scheduler tries to put it on the same node again in the future

• if a task running on a certain NUMA node allocates memory, the kernel tries hard to map physical memory from the same NUMA node

This results in all kinds of weird behavior when you run one big process (mysqld) that consumes most of the memory. In such cases it is recommended to either turn off NUMA (BIOS or kernel command line) or prefix such problem processes with numactl --interleave all. You can enable this by running with the --numa-interleave option.

.

Linux Kernel Settings

See .

InnoDB Settings

to about 80% of RAM or leaving <5G RAM free (on large RAM systems). Less if lots of connections are used.

to be larger than the amount of writes in the test run or sufficient to cover several minutes of the test run at least.

MyISAM Settings

General Settings

should be the same as (unless using thread pools).

_{This page is licensed: CC BY-SA / Gnu FDL}

When you build for benchmarks, it's important to use consistent compile time settings across different versions and even products (i.e. when comparing MySQL and MariaDB).

MariaDB and MySQL are now built using cmake. This makes it hard to fine tune settings because when you chose a predefined build configuration (recommended: RelWithDebInfo) then other settings like CFLAGS are overwritten by those from the CMakefile.

There are more pain points with cmake:

• cmake uses a different install layout than autotools builds

• the OQGraph engine is included by default, but fails often due to mismatching boost libraries

• make install tries to create directories in system locations (/etc/my.cnf.d etc.) which fails as ordinary user

• CMakefiles for different products sometimes use different CFLAGS

So here is my build script that fixes all those things.

The script shall be run from a subdir of a source tree. i.e.

_{This page is licensed: CC BY-SA / Gnu FDL}

For our automated MariaDB/MySQL sysbench benchmark tests, we use sysbench fromlp:sysbench. This page describes the basic parameters and configuration we use.

You can find the automation wrapper scripts we use for running sysbench in

Current general parameters

I just backported Windows performance patches I've done for 5.5 back to . There will be a bit more in Maria that in MySQL 5.5, but more on this later.

First, I feel Windows performance improvements in 5.5 were never adequately described, so here is the redux. For those familiar with Windows systems programming, MySQL code used to offer of low-hanging performance fruits. I picked some of them those back in my days in MySQL/Sun. The result benchmark curve became really nice: look at Calvin's blog entry.

If graphs in this blog looks familiar to you, it is because it was often used by Oracle marketing as proof of big-O's positive influence on MySQL code :)

There were 3 Windows performance related patches. I comment on the bugs history a little bit, too.

• Bug#24509. The fix removed the limit of 2048 open MyISAM files, and as a nice side-effect allowed for much bigger table cache. When mysqld starts, it checks the maximum open files value, and corrects the value of table cache, if max_open_files is low or max_connection is high. This is what also happened during benchmarks. If you look at the read-only benchmark graph in the Calvin's blog above, you'll notice a drop around 64 concurrent users. No wonder, mysql server recalculated table cache size, setting it to the absolute minimum, that is to 64.

The fix was to create an own sort-of C runtime library on top of pure Win32, which is capable of handling more than 2048 open files (16K default). Some other things are also done nicer than in Microsofts C runtime, e.g there are no locks, and there is an acceptable pread()/pwrite() implementation. The main advantage as I said is being able to have a large table cache - for this, rewriting C runtime is likely an overkill, but I did not come up with anything better.

• . This bug was fixing a lot of questionable places in InnoDB that were written probably back in the NT3.1 days .

First it is importantto understand how innodb "mutex" structure is acquired. Details on it are hairy, mutex is a composite structure which has a real os mutex( known under Windows as CRITICAL_SECTION) plus innodb event (known under Windows as event). There are a couple of variations on the implementation - mutex can be an interlocked (aka atomic for my Unix friends) variable, under Unix event is represented as condition variable.

Acquisition is done in 2 steps - first, trylock on os mutex is performed , possibly several times with in a loop, if unsuccessfull, event is reserved in a global table of events known as "sync array", event is entering a waiting state. mutex unlock would wakeup the waiters if there are any. Do not ask me why the implementation is so complicated, it is so :) Maybe, this design helps to find deadlocks.

Variation of this implementation - instead of trylock on mutex, there might be a compare_exchange instruction on interlocked(atomic) variable.

Back to Windows, the implementation of the above exposed a couple of interesting self-compensating bugs.

1. First, I fixed os_mutex_trylock() to be what it really means . The implementation was EnterCriticalSection, which is "try very hard", and actually acquire the lock. A more conscious trylock would be TryEnterCriticalSection. When I fixed that, contrary to my expectation, this made mysqld really slow. When trylock() failed, innodb started to enter code paths it has never seen before. for example, reserving space in the mentioned "sync array". Access to sync array is protected by so-called "slow lock" and this showed up very often in the profiler. The next step was fixing the "slow lock"

2. "slow Innodb mutex" was implemented as kernel object aka Windows mutex (for my Unix friends this is sort of SysV semaphore). It can be used to synchronize processes but is an absolute overkill for synchronization of threads inside the same process. It was a "really slow mutex". Changing this to CRITICAL_SECTION made it faster however...

3. When all of the above was fixed, found out that Windows events (mentioned events) did not really scale well in many-threads scenarios. On newer Windows (Vista+), there is a CONDITION_VARIABLE that is documented to scale better, and measuring also showed that it scaled really well. So I used condition variables when possible, which is ironic, because InnoDB events were really modeled after Windows events.

So, the story about "atomics did not work well on Windows" was a cumulative effect of different things.

Prior to that patch . Once atomics were enabled, implementation of fast mutexes did not use CRITICAL_SECTION, but compare_exchange instruction. Ingenious "trylock_veryhard" as we have seen at the step 1. above is not used anymore, instead it is a quite correct "try" lock . Once try_lock() began to fail with many concurrent threads, overhead of sync array guard implemented as Windows kernel object that we have seen in 2. became apparent, and less-then inefficient Windows events mentioned in 3. finished that picture.

This patch was merely to compensate for negative effects of the 5.5 metadata lock on MyISAM bechmarks, and fix was using native Vista performance primitives. The patch per se is not interesting, and repeats a lot of what was done for Innodb. What was great, was a discussion prior to the patch between myself, Davi, Dmitry on different implementations of reader writer locks, including 2 homebacked ones, and one by .

Without doubt, the discussions around that was a highlight in my very short stint at Oracle. Also, if you want to get a MySQL-classic-style code review with 17 things to fix, of which at least 10 would be marked with "Coding Style" (yes, both words capitalized) , try to get Dmitry Lenev as a reviewer, he's great - this is the proof Anyway, the patch improves MyISAM throughput by 10-20% , which I think is quite ok. Somehow those percents were subsequently eaten by MDL though :)

Notes

Taken from a note on Facebook: by Vladislav Vaintroub.

_{This page is licensed: CC BY-SA / Gnu FDL}

Results from various Sysbench runs. The data is in OpenDocument Spreadsheet format (.ods).

For reference, the "perro" and "work" systems were configured as follows:

sysbench v0.5 results

• Single Five Minutes Runs on T500 Laptop, OO.org spreadsheet:

• Single Five Minutes Runs on perro, OO.org spreadsheet:

• Single Five Minutes Runs on work, OO.org spreadsheet:

• Three Times Five Minutes Runs on work with 5.1.42, OO.org spreadsheet:

_{This page is licensed: CC BY-SA / Gnu FDL}

Consider 2 pseudo-code implementation of event handling loop handling IO completion on Windows.

• Using Windows events

• I/O Completion port based

Which one is more efficient ? The right answer is - I/O completion port based. This is because:

1. the number of outstanding events a thread can handle is not restricted by a constant like in the WaitForMultipleObject() case.

2. if there several io_handler() threads running, they load-balance since every I/O can be "dequeued" by GetQueuedCompletionStatus in any io handler thread. With WaitForMultipleObjects(), the thread that will dequeue the I/O result is predetermined for each I/O.

InnoDB has used asynchronous file I/O on Windows since the dawn of time, probably since NT3.1 . On some reason unknown to me (I can only speculate that Microsoft documentation was not good enough back then), InnoDB has always used method with events, and this lead to relatively complicated designs - if you're seeing "segment" mentioning in os0file.c or fil0fil.c , this is mostly due to the fact that number of events WaitForMultipleObjects() can handle is fixed.

I changed async IO handling for XtraDB in MariaDB5.3 to use completion ports, rather than wait_multiple technique. The results of a synthetic benchmark are good.

The test that I run was sysbench 0.4 "update_no_key"

I do understand, sysbench it does not resemble anything that real-life load, and I'm ready to admit cheating with durability for this specific benchmark, but this is an equal-opportunity cheating, all 3 versions ran with the same parameters.

What do I refer to as durability cheating:

1. using , which, for me , is ok for many scenarios

2. "Switch off Windows disk flushing" setting, which has the effect of not flushing data in the disk controller (file system caching is not used here anyway). This setting is only recommended for battery backed disks, my own desktop does not have it, of course.

However, if I have not done the above, then I would be measuring the latency of a FlushFileBuffers() in my benchmark, which was not what I wanted. I wanted to stress the asynchronous IO.

And here is the obligatory graph:

Notes

This is taken from an original Facebook note from Vladislav Vaintroub, and it can be found:

It is also worth noting a note from Vlad about the graph: "The graph is given for 5.2, because I developed that patch for 5.2. I pushed it into 5.3 though :)"

_{This page is licensed: CC BY-SA / Gnu FDL}

This page contains database preparation and creation times that were discovered while working on the .

Database creation times

Performance testing

The performance/perfrun.pl executes each query against a set of tw servers and reports the outcome.

• --input-directory contains the queries to be run, one query per file. Alternative sources for queries will be made available in the future;

Testing method for segmented key cache performance

We used SysBench v0.5 from Launchpad to test the performance for the MyISAM storage engine of -gamma.

As wrapper scripts for automated running of SysBench we used the sysbench/ directory from MariaDB Tools.

To test that splitting the key cache's global mutex into several mutex helps under multi user load, we wrote a new SysBench test called select_random_points.lua. We used one big table and selected random points with increasing number of concurrent users.

Main testing outcomes

We see up to 250% performance gain depending on the amount of concurrent users.

Detailed testing outcomes

On our machine pitbull

On pitbull with --random-points=10

In relative numbers:

On pitbull with --random-points=50

In relative numbers:

On pitbull with --random-points=100

In relative numbers:

Detailed numbers of all runs on pitbull

You can find the absolute and relative numbers in our OpenOffice.org spread sheet here:

On our machine perro

On perro with --random-points=10

In relative numbers:

On perro with --random-points=50

In relative numbers:

On perro with --random-points=100

In relative numbers:

Detailed numbers of all runs on perro

You can find the absolute and relative numbers in our OpenOffice.org spread sheet here:

Table and query used

Table definition:

Query used:

The ? parameters were replaced by random numbers when running the SysBench test. We used 10, 50, and 100 random points in our tests.

We inserted 20 million rows using random data, which gave us a data and index file size of:

We chose our key buffer size to be big enough to hold the index file.

Testing environment

MariaDB sources

We used -gamma with following revision from our launchpad repository

Compiling MariaDB

We compiled MariaDB using this line:

MariaDB runtime options

We used following configuration for running MariaDB

SysBench v0.5 select_random_points.lua options

We run the SysBench v0.5 select_random_points.lua test with following options:

We tested with increasing number of concurrent users with a warm up time of 8 minutes and a run time of 20 minutes:

We also tested an increasing number of random points:

Kernel parameters

IO scheduler

For optimal IO performance running a database we are using the noop scheduler. You can check your scheduler setting with:

For instance, it should look like this output:

You can find detailed notes about Linux schedulers here: .

Open file limits

Having a lot of concurrent connections can hit the open file limit on your system. On most Linux systems the open file limit is at 1024, which can be not enough. Please set your open file limit higher by editing

and adding a line like

Your ulimit -a output should look like this afterwards:

Machines used for testing

perro

pitbull

_{This page is licensed: CC BY-SA / Gnu FDL}

Introduction

This page shows the results for benchmarking the following configuration:

• Beta + XtraDB with all optimizations (optimizater_switch) set to ON

Here are some benchmarks of some development threadpool code (the ).

The benchmarks were run on three machines:

• facebook-maria1 (Linux, 16 cores)

• pitbull (Linux, 24 cores)

• windows (Windows, 8 cores)

Sysbench 0.4 was used to run some "unit" OLTP tests (point-select and update-nokey), as well as the "classic" OLTP-readonly and OLTP-readwrite. All tests were run with the number of concurrent clients ranging from 16 to 4096, with warm cache, with the sysbench table having 1M records.

The results are quite different on all of the machines tested (the machines are very different, in terms of cores, IO etc), yet threadpool has a positive effect on all 3 machines, and the positive effect seems to grow with the number of cores.

Some notes on how the benchmarks were run:

1. The benchmark client and server used different CPUs - ('taskset' (Linux), or 'start /affinity' (Windows) were used to run the benchmark client on #CPUs/4, the rest of CPUs were used by the server). On the Linux boxes, --thread_pool_size=<N> (where N is number of cores dedicated to the server) was used.

2. innodb_flush_log_at_trx_commit=0 and innodb_flush_method=ALL_O_DIRECT was used to avoid extensive fsyncing, which is ok for the purposes of the testing for this.

OLTP_RO

OLTP_RO facebook-maria1

OLTP_RO pitbull

OLTP_RO Windows

OLTP_RW

OLTP_RW facebook-maria1

OLTP_RW pitbull

OLTP_RW Windows

POINT_SELECT

POINT_SELECT facebook-maria1

POINT_SELECT pitbull

POINT_SELECT Windows

UPDATE_NOKEY

UPDATE_NOKEY facebook-maria1

UPDATE_NOKEY pitbull

UPDATE_NOKEY Windows

_{This page is licensed: CC BY-SA / Gnu FDL}

DBT-3 (OSDL Database Test 3) is a workload tool for the Linux kernel that OSDL (Open Source Development Labs, inc) developed based on TPC-H which is provided by the Transaction Performance Processing Council (TPC).

DBT-3, like TPC-H, simulates an actual decision-making support system and models complex business analysis applications that perform data processing jobs for making better business decisions. By running the workload that DBT-3 simulates, it is possible to verify and measure the performances of the Linux kernel in an actual decision-making support system.

DBT-3 uses the "scale factor (SF)" as a stress indicator of the system. By varying the SF, it becomes possible to make the size of a database the SF times its size.

The tests performed by DBT-3 comprise the three tests listed below. DBT-3 obtains the execution times of these three tests as well as the system status information and database statistics information.

1. Load test

• Enters the data to be used for the Power and Throughput tests into the database. Makes a bulk insert of the huge CSV data corresponding to the scale factor into the database.

1. Power test

• Performs 22 complex queries.

1. Throughput test

• Performs the same 22 queries as in the Power test simultaneously in more than one process.

For the purpose of this task, only the Power test is performed over preliminary prepared database with various Scale factors. The time for each query execution will be measured and stored into a database. Later the results of one whole test with all 22 queries will be rendered into a histogram graphics comparing it to different configurations.

Benchmark environment preparation

sudo rights

The user that will run the benchmark must have sudo rights on the machine.

For clearing the system caches between query runs, the automation script uses the following command:

This command must be run with superuser rights. Even if a user supplies a password to sudo, this password expires after some timeout. In order for this command to be run without requiring password, the following line should be added to the sudoers file (edit it with the "sudo visudo" command):

...where 'your_username' is the user that will run the benchmark.

Required software

The automated DBT3 benchmark requires the following software:

• • Project home:

• • Project home:

• — a Perl module to connect to MariaDB/MySQL and PostgreSQL. To install it use the following command:

NOTE: You may receive an error saying that CPAN could not findmysql_config. In this case you have to install the mysql client development library. In OpenSuse the command is:

Alternatively this module can be installed manually by following these steps:

1. Download DBD-mysql-4.020.tar.gz from and unpack it

2. Run the perl script PerlMake.pl under the unzipped dir:

1. Run make to compile DBD::mysql:

1. Add the necessary paths in order to run DBD::mysql:

Tested DBMS

• • Download location: → Generally Available (GA) Releases → Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive - downloads mysql-5.5.x-linux2.6-x86_64.tar.gz - gzipped tar file for Linux x86

• • Download location: , downloaded with Bazaar:

• • Download location:

NOTE: The DBT3 benchmark requires a lot of disk space (for example MySQL 5.5.x + MyISAM database with scale factor 30 takes about 50 GB). Also some queries require the utilization of temp tables under the directory set by the--tmpdir startup parameter passed to mysqld. In the prepared configuration files the temp directory is pointed to the mysql system directory of the binary distribution, but one should reassure that there is enough free space available for the temp directory.

Installation instructions

NOTE: The directory where all the files will be downloaded or installed will be referred as $PROJECT_HOME. This could be for example ~/benchmark/dbt3.

Download

1. Go to your project folder

1. Get the latest branch from LaunchPad with Bazaar:

Now the project for the dbt3 benchmark test will be in the following dir:

The project dbt3_benchmark has the following directories and files:

• config — a folder where the configuration files for MariaDB, MySQL and PostgreSQL are stored. They are divided into subfolders named 'sXX', where XX is the scale factor.

• dbt3_mysql — a folder with all the necessary files for preparing DBT3 databases and queries for the tests with MySQL and MariaDB

• tests — a folder where the different test configurations are stored. It contains the following directories:

Prepare benchmark workload and queries

For the purpose of the benchmark from we will only need DBGEN and QGEN. DBGEN is a tool that generates a workload for the test and QGEN is a tool that generates the queries used for the test.

1. Go to

2. Download the archive for DBT3 1.9 into your project folder $PROJECT_HOME

3. Unzip the archive into your project folder

1. Copy the file tpcd.h into the dbt3 folder. This step includes the necessary labels for MySQL/MariaDB when building queries.

1. Copy the file Makefile under $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/ into the dbt3 folder

• NOTE: This step is executed only if you want to overwrite the default behavior of PostgreSQL settings. After copying this Makefile and building the project, QGEN will be set to generate queries for MariaDB/MySQL. If you skip this step, QGEN will generate queries for PostgreSQL by default.

1. Go to $PROJECT_HOME/dbt3-1.9/src/dbgen and build the project

1. Set the variable DSS_QUERY to the folder with template queries for MariaDB/MySQL or for PostgreSQL

2. If you want to build the queries that fit MariaDB/MySQL dialect execute the following command:

1. If you want to use the default PostgreSQL templates, execute the following command:

1. Create a directory to store the generated queries in

1. Generate the queries

NOTE: The examples use scale factor 30. If you want different scale, change the value of -s parameter

cd $PROJECT_HOME/dbt3-1.9/src/dbgen ./qgen -s 30 1 > $PROJECT_HOME/gen_query/s30-m/1.sql ./qgen -s 30 2 > $PROJECT_HOME/gen_query/s30-m/2.sql ./qgen -s 30 3 > $PROJECT_HOME/gen_query/s30-m/3.sql ./qgen -s 30 4 > $PROJECT_HOME/gen_query/s30-m/4.sql ./qgen -s 30 5 > $PROJECT_HOME/gen_query/s30-m/5.sql ./qgen -s 30 6 > $PROJECT_HOME/gen_query/s30-m/6.sql ./qgen -s 30 7 > $PROJECT_HOME/gen_query/s30-m/7.sql ./qgen -s 30 8 > $PROJECT_HOME/gen_query/s30-m/8.sql ./qgen -s 30 9 > $PROJECT_HOME/gen_query/s30-m/9.sql ./qgen -s 30 10 > $PROJECT_HOME/gen_query/s30-m/10.sql ./qgen -s 30 11 > $PROJECT_HOME/gen_query/s30-m/11.sql ./qgen -s 30 12 > $PROJECT_HOME/gen_query/s30-m/12.sql ./qgen -s 30 13 > $PROJECT_HOME/gen_query/s30-m/13.sql ./qgen -s 30 14 > $PROJECT_HOME/gen_query/s30-m/14.sql ./qgen -s 30 15 > $PROJECT_HOME/gen_query/s30-m/15.sql ./qgen -s 30 16 > $PROJECT_HOME/gen_query/s30-m/16.sql ./qgen -s 30 17 > $PROJECT_HOME/gen_query/s30-m/17.sql ./qgen -s 30 18 > $PROJECT_HOME/gen_query/s30-m/18.sql ./qgen -s 30 19 > $PROJECT_HOME/gen_query/s30-m/19.sql ./qgen -s 30 20 > $PROJECT_HOME/gen_query/s30-m/20.sql ./qgen -s 30 21 > $PROJECT_HOME/gen_query/s30-m/21.sql ./qgen -s 30 22 > $PROJECT_HOME/gen_query/s30-m/22.sql

./qgen -s 30 -x 1 > $PROJECT_HOME/gen_query/s30-m/1_explain.sql ./qgen -s 30 -x 2 > $PROJECT_HOME/gen_query/s30-m/2_explain.sql ./qgen -s 30 -x 3 > $PROJECT_HOME/gen_query/s30-m/3_explain.sql ./qgen -s 30 -x 4 > $PROJECT_HOME/gen_query/s30-m/4_explain.sql ./qgen -s 30 -x 5 > $PROJECT_HOME/gen_query/s30-m/5_explain.sql ./qgen -s 30 -x 6 > $PROJECT_HOME/gen_query/s30-m/6_explain.sql ./qgen -s 30 -x 7 > $PROJECT_HOME/gen_query/s30-m/7_explain.sql ./qgen -s 30 -x 8 > $PROJECT_HOME/gen_query/s30-m/8_explain.sql ./qgen -s 30 -x 9 > $PROJECT_HOME/gen_query/s30-m/9_explain.sql ./qgen -s 30 -x 10 > $PROJECT_HOME/gen_query/s30-m/10_explain.sql ./qgen -s 30 -x 11 > $PROJECT_HOME/gen_query/s30-m/11_explain.sql ./qgen -s 30 -x 12 > $PROJECT_HOME/gen_query/s30-m/12_explain.sql ./qgen -s 30 -x 13 > $PROJECT_HOME/gen_query/s30-m/13_explain.sql ./qgen -s 30 -x 14 > $PROJECT_HOME/gen_query/s30-m/14_explain.sql ./qgen -s 30 -x 15 > $PROJECT_HOME/gen_query/s30-m/15_explain.sql ./qgen -s 30 -x 16 > $PROJECT_HOME/gen_query/s30-m/16_explain.sql ./qgen -s 30 -x 17 > $PROJECT_HOME/gen_query/s30-m/17_explain.sql ./qgen -s 30 -x 18 > $PROJECT_HOME/gen_query/s30-m/18_explain.sql ./qgen -s 30 -x 19 > $PROJECT_HOME/gen_query/s30-m/19_explain.sql ./qgen -s 30 -x 20 > $PROJECT_HOME/gen_query/s30-m/20_explain.sql ./qgen -s 30 -x 21 > $PROJECT_HOME/gen_query/s30-m/21_explain.sql ./qgen -s 30 -x 22 > $PROJECT_HOME/gen_query/s30-m/22_explain.sql

mkdir $PROJECT_HOME/gen_data/s30

export DSS_PATH=$PROJECT_HOME/gen_data/s30/

./dbgen -vfF -s 30

gunzip < mysql-5.5.x-linux2.6-x86_64.tar.gz |tar xf -

$PROJECT_HOME/bin/mysql-5.5.x-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &

gunzip < mysql-5.6.x-m5-linux2.6-x86_64.tar.gz |tar xf -

$PROJECT_HOME/bin/mysql-5.6.x-m5-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &

bzr branch lp:maria/5.3 mv 5.3/ mariadb-5.3

cd mariadb-5.3/ ./BUILD/compile-amd64-max

./scripts/make_binary_distribution

mv mariadb-5.3.x-beta-linux-x86_64.tar.gz $PROJECT_HOME/bin/ cd $PROJECT_HOME/bin/ tar -xf mariadb-5.3.x-beta-linux-x86_64.tar.gz

$PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64/bin/mysqld_safe --datadir=some/data/dir &

LOAD DATA INFILE '/some/path/to/gen_data/nation.tbl' into table nation fields terminated by '|';

LOAD DATA INFILE '~/benchmark/dbt3/gen_data/s30/nation.tbl' into table nation fields terminated by '|';

cd $DB_HOME ./scripts/mysql_install_db --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s30/load_mysql_myisam_my.cnf --basedir=$DB_HOME --datadir=$PROJECT_HOME/db_data/myisam-s30/

./bin/mysqladmin --user=root --socket=$PROJECT_HOME/temp/mysql.sock shutdown 0

gunzip < postgresql-9.1rc1.tar.gz |tar xf -

mkdir $PROJECT_HOME/PostgreSQL_bin cd $PROJECT_HOME/postgresql-9.1rc1 ./configure --prefix=$PROJECT_HOME/bin/PostgreSQL_bin make make install

mkdir $PROJECT_HOME/db_data/postgre_s30 cd $PROJECT_HOME/bin/PostgreSQL_bin ./bin/initdb -D $PROJECT_HOME/db_data/postgre_s30

./bin/postgres -D $PROJECT_HOME/db_data/postgre_s30 -p 54322 &

./bin/createdb -O {YOUR_USERNAME} dbt3 -p 54322 ./bin/psql -p 54322 -d dbt3 -f $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_pg.sql

./bin/pg_ctl -D $PROJECT_HOME/db_data/postgre_s30/ -p 54322 stop

cd $PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64

./scripts/mysql_install_db --datadir=$PROJECT_HOME/db_data/dbt3_results_db

./bin/mysqld_safe --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/results_mariadb_my.cnf --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock --datadir=$PROJECT_HOME/db_data/dbt3_results_db/ &

./bin/mysql -u root -P 12340 -S $PROJECT_HOME/temp/mysql_results.sock < $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-results-db.sql

./bin/mysqladmin --user=root --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock shutdown 0

[common] RESULTS_DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/results_db_conf/results_db.conf TEST_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/test_conf/test_myisam.conf

[mariadb_5_3] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam.conf

[mysql_5_5] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries_mysql.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mysql_5_5_myisam.conf ...

unlink /path/to/datadir/mysql ln -s /path/to/value/in/MYSQL_SYSTEM_DIR/mysql_mariadb_5_3 /path/to/datadir/mysql

[db_settings] DBMS_HOME = $PROJECT_HOME/bin/mariadb-5.3.2-beta-linux-x86_64 DBMS_USER = root ...

QUERIES_AT_ONCE = 0 CLEAR_CACHES = 1 WARMUP = 0 ...

[queries_settings] QUERIES_HOME = /path/to/queries

[query1] QUERY=1.sql EXPLAIN_QUERY=1_explain.sql STARTUP_PARAMS=

[query2] QUERY=2.sql EXPLAIN_QUERY=2_explain.sql STARTUP_PARAMS=--optimizer_switch='mrr=on' --mrr_buffer_size=8M --some_startup_parmas ...

DBMS_HOME = $PROJECT_HOME/mariadb-5.3.x-beta-linux-x86_64 DBMS_USER = root ...

sudo /sbin/sysctl vm.drop_caches=3

{your_username} ALL=NOPASSWD:/sbin/sysctl

sar -u 0 2>null

sar -b 0 2>null

sar -r 0 2>null

perl launcher.pl --project-home=/path/to/project/home/ --results-output-dir=/path/to/project/home/results/myisam_test --datadir=/path/to/project/home/db_data/ --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf --queries-home=/path/to/project/home/gen_query/ --scale-factor=30 --TIMEOUT=600

perl launcher.pl --project-home=/path/to/project/home/ --results-output-dir=/path/to/project/home/results/innodb_test --datadir=/path/to/project/home/db_data/ --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/innodb_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf --queries-home=/path/to/project/home/gen_query/ --scale-factor=30 --TIMEOUT=7200 --NUM_TESTS=3

[mariadb_5_3_new_configuration] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries-mariadb.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam_new_configuration.conf