Partitioning as performance booster

When I developed partitioning for MySQL the main goal was
to make it easier for MySQL users to manage large tables
by enabling them to easily add and drop partitions.

It turns out that partitioning can also be used as a manner
to make MySQL more scalable. The reason is that in some
cases the storage engine have internal locks per table or
per index (one such example is the btr_search_latch in InnoDB).

So in this case adding a
PARTITION BY KEY (key_part)
PARTITIONS 4
to the table definition makes a very hot table into 4 tables
from the storage engine point of view.

This would mostly be beneficial in cases where the main
operation is primary key lookups on the table. Dividing the
indexes in cases of scans can be both positive and negative.
So this solution is definitely not a winner for all situations.

I haven't tried this out yet myself in my benchmark suites,
but I plan to make some experiments in this area. It is usable
in sysbench, it's possible to use for DBT2 (have used partitioning
for DBT2 in MySQL Cluster benchmarks a lot already) and it's
possible to use in Dimitri's dbStress benchmark.

Full automation of DBT2 test runs in benchmark scripts

My benchmark scripts was upgraded once more today.
I fixed all issues pertaining to full automation
of DBT2 runs. So now it is as easy to start a
DBT2 test run as it previously was to start a
sysbench run. Obviously DBT2 was already earlier
supported in the benchmark scripts, so what I did
now was add the final steps to make it fully
automated. This includes also generating the DBT2
load files needed to load the DBT2 database.

See the download section on www.iclaustron.com
for the tarball including some description of
how to configure sysbench and DBT2 test runs.

New version of benchmark scripts also supporting Drizzle

I updated my benchmark scripts this week. These scripts can now
run:

- Sysbench benchmarks for MySQL and Drizzle
- DBT2 benchmarks for MySQL and MySQL Cluster
- TPC-W benchmark for MySQL Cluster

There is also a number of scripts to start and stop
MySQL, Drizzle and MySQL Cluster nodes.

In this version I added Drizzle support for sysbench and also
added a README-AUTOMATED file that describes the steps needed
to set-up a completely automated sysbench run for MySQL
and Drizzle.

To run a MySQL sysbench benchmark one needs the DBT2 tarball,
the sysbench tarball and a MySQL tarball (gzipped tarballs).

The tarball is found on www.iclaustron.com in the downloads
section and this version is named dbt2-0.37.47.tar.gz.

Improvements by LOCK_* patches

I have done a long series of tests to verify that
the impact of the LOCK_alarm removal, removing two
variables from LOCK_threadcount protection to being
atomic increments instead and decreasing hold time
of LOCK_open is positive in most if not all cases.

There are a number of test cases needed:
1) With and Without cpuspeed activated
2) With sysbench local and with sysbench on another
server
3) With MySQL Server limited to 2,4,6,8,12,16 cores.
4) With number of threads going from 1 to 256 threads
in fair sized steps.

I've done most of those tests for Sysbench Readonly
and Sysbench Readwrite. The results are positive in
almost all cases towards the baseline which is based
off the MySQL 5.4.3 tree (not exactly 5.4.3 but close
enough).

In the case of networked benchmark with cpuspeed
activated the gain is biggest, top performance goes up
about 10% and also top performance moves from 64 to 128
threads, for 256 threads the performance increases
by about 50%.

When cpuspeed is deactivated and we use networked
benchmarks the network handling becomes a bottleneck, so
the numbers here are less interesting since we need to
resolve the network bottleneck first.

With cpuspeed activated and local communication the top
performance increase by about 8% and there is gain
for all number of threads. The gain is a bit higher
on sysbench readonly than on sysbench readwrite.

With cpuspeed deactivated and local communication we
naturally get the best numbers but also the
smallest gains. Top performance of Sysbench
Readonly increased by 2.5% and for Readwrite
it increased 4%, the top performance for
sysbench readwrite also moved from 16 threads
to 32 threads. The improvement is slightly
better on more threads.