1 of 1

Segmented Key Cache Performance

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}

Segmented Key Cache Performance

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.

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

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}