In developing MySQL Server and MySQL Cluster we use four types of
testing.
We use unit testing, we use functional testing, we use system
testing and
we use benchmark testing.
For the unit tests and functional tests we use the MTR test
framework. This
is dealing with most issues of how to use SQL to query the MySQL
database
engine.
For system testing the InnoDB team has a test framework that is
used to ensure
that InnoDB will survive crashes of many sorts. In a similar
fashion MySQL
Cluster uses the autotest framework that I described in an
earlier blog:
Link to earlier blog.
For benchmark testing we have a few frameworks, I am going to
describe the one
that I use on a regular basis. This framework can be downloaded
from:
Link to download.
I started developing this framework in 2006. So it has been
extended for quite
some time and it is a lot simpler to run it now than it ever was
before.
In this blog I am going to describe a very minimal setup
needed to run a
sysbench benchmark against MySQL Cluster 7.4.
These benchmarks can be used to run the following types of
tests:
1) Sysbench on a single server with InnoDB as storage
engine
2) Sysbench on a single server with NDB as storage engine
3) Sysbench against InnoDB with InnoDB and sysbench on different
servers
4) Sysbench against NDB with different programs on different
servers
5) flexAsynch for MySQL Cluster testing on a set of servers
6) DBT2 on a single server with InnoDB as storage engine
7) DBT2 on a single server with NDB as storage engine
8) DBT2 using NDB as a storage engine against a lot of
servers
Running against NDB as a storage engine means to use MySQL
Cluster.
I mostly focus on 1) above for InnoDB testing. For MySQL Cluster
I mostly
focus on 2) and 5). But occasionally I also test 8). 3), 4), 6)
and
7) are perfectly possible setups and have been tested as well,
but I don't
personally use those so much.
I will write a few blogs about how to setup a benchmark using
these benchmark
scripts. I will first describe the basics of setting up a
sysbench benchmark
against MySQL Cluster in this blog. Then a basic flexAsynch
benchmark. Finally
a basic DBT2 benchmark on a single server.
After describing the basic configuration setup, I will describe
in detail the
various parts to configure. There are numerous things to
configure:
Mandatory items:
1) Which benchmark to run
2) Pointer to file locations for tarballs, data directory and
install directory
3) Pointer to tarballs for MySQL, Sysbench and DBT2 benchmarks,
also the
benchmark scripts which are located in the DBT2
tarball.
4) Definition on which servers the various programs under test
will be
executing.
Optional parts of configuring SUT (System Under
Test)
5) Configuration of MySQL Server. Started also in flexAsynch, but
not really
used in flexAsynch benchmark.
6) Configuration of InnoDB if running test against InnoDB.
7) Configuration of NDB storage engine and NDB nodes if running
against NDB
storage engine.
Optional parts of configuring the actual benchmark
8) Sysbench setup. Setting up how to run the actual sysbench
benchmark, for how
long to run, what sysbench variant to use, how many tables and a
lot of other
things can be configured about how sysbench is to run. This part
is obviously
only interesting when running a sysbench test.
9) DBT2 setup. There are numerous ways to adapt the basic DBT2
benchmark
although the benchmark transactions are always the
same.
10) flexAsynch benchmark setup. Also in flexAsynch there are
multiple ways to
adapt the benchmark.
11) In connection with the flexAsynch benchmark one can also
benchmark restarts
in MySQL Cluster. First the scripts use flexAsynch
to load the database and
then various forms of restarts can be tested.
The full capabilities of those benchmark scripts are quite
extensive, they are
the result of 9 years of incremental design where we have tried
to extend the
use cases of these benchmarks to support as many environments as
possible, but
also to automate as much as is possible.
Obviously to understand all of this in one go is a tad difficult.
So we will
try to start by describing how to setup a very simple sysbench
benchmark using
one machine running sysbench against the NDB storage engine using
the latest
development version of MySQL Cluster 7.4.
This is a good first step to get acquainted with the benchmark
scripts. I will
add more details in later blogs about running more advanced
setups.
The scripts have been used mostly on Linux, also a fair deal of
usage on
Solaris. When preparing this blog I ran it on Mac OS X. This
requires some
changes that will soon arrive in the dbt2-0.37.50.7 updated
version of the
benchmark scripts. So preferably use Linux when trying this out
for the
first time.
The first step in running this test is to create a directory from
where the
tests are located on disk. I usually use some home directory and
create a
directory called bench there. So on Mac OS X that would
/Users/mikael/bench.
On a Linux machine I would normally use /home/mikael/bench.
In this directory I create four directories. I create one
directory called
tarballs, this is where I will place the DBT2, sysbench and MySQL
tarballs.
I create one directory called ndb, this will be used as the data
directory
and will be automatically filled by the scripts. Next I will
create a
directory mysql, this directory is where I will place the binary
installation.
Finally I create a directory called sysbench.
Next step is to download the DBT2 tarball and the sysbench
tarball from the
website:
http://dev.mysql.com/downloads/benchmarks.html
Copy those tarballs to the tarballs directory created
above.
Next step is to get the MySQL tarball that you want to test. It's
ok to use
both source tarballs and binary tarballs. Personally I obviously
mostly work
with source tarballs since I want to have full control over
everything, but
it's ok to use a binary tarball as well.
Place this tarball also in the tarballs directory.
Now in order to be able to run the benchmark scripts it is
necessary to
get the top-level script copied from the DBT2 tarball. To do this
perform
the following commands:
cd /Users/mikael/bench #use your bench directory
here
cd tarballs
tar xfz dbt2-0.37.50.6.tar.gz
cp dbt2-037.50.6/scripts/bench_prepare.sh ..
rm -rf dbt2-0.37.50.6
Now ls from /Users/mikael/bench should look like this:
Mikaels-MacBook-Pro:bench mikael$ ls
bench_prepare.sh ndb tarballs
mysql sysbench
Mikaels-MacBook-Pro:bench mikael$ pwd
/Users/mikael/bench
and ls from tarballs should look something like this:
Mikaels-MacBook-Pro:bench mikael$ cd tarballs
Mikaels-MacBook-Pro:tarballs mikael$ ls
dbt2-0.37.50.6.tar.gz mysql-cluster-gpl-7.4.5.tar.gz
sysbench-0.4.12.7.tar.gz
Now it's time to create the config file.
This file is always located in an almost empty directory under
the bench directory with only
one file in it. This file is always called autobench.conf.
Here is the content of the file in my simplistic test
run.
#Define benchmark to run
BENCHMARK_TO_RUN="sysbench"
#Define file locations of datadir, installdir and
tarballs
TARBALL_DIR="/Users/mikael/bench/tarballs"
REMOTE_BIN_INSTALL_DIR="/Users/mikael/bench/mysql"
DATA_DIR_BASE="/Users/mikael/bench/ndb"
#Define tarball versions
MYSQL_VERSION="mysql-cluster-gpl-7.4.5"
MYSQL_BASE="5.6"
SYSBENCH_VERSION="sysbench-0.4.12.7"
DBT2_VERSION="dbt2-0.37.50.7"
#Define engine to use
ENGINE="ndb"
#Define way to build
USE_BINARY_MYSQL_TARBALL="no"
#Define servers to use
SERVER_HOST="127.0.0.1"
NDB_MGMD_NODES="127.0.0.1"
NDBD_NODES="127.0.0.1"
#Configure MySQL Server
SERVER_PORT="3316"
USE_MALLOC_LIB="no"
#Configure NDB part
NDB_REPLICAS="1"
NDB_DATA_MEMORY="2G"
NDB_INDEX_MEMORY="200M"
NDB_MAX_NO_OF_EXECUTION_THREADS="8"
#Configure sysbench
RUN_RW="yes"
SYSBENCH_ROWS="100000"
THREAD_COUNTS_TO_RUN="16;32"
MAX_TIME="60"
NUM_TEST_RUNS="1"
At first we need to define the benchmark to run, this is sysbench
in our case.
We need to point out the tarball directory, we need to point out
the directory
where to place the installation (REMOTE_BIN_INSTALL_DIR), we also
need to point
out the data directory.
Next step is to provide the names of the tarballs, this is done
in
MYSQL_VERSION, SYSBENCH_VERSION and DBT2_VERSION. Since the
scripts
have to adapt to different MySQL versions we also need to specify
the base version
of MySQL which in the case of MySQL Cluster 7.4 is 5.6. The
scripts currently
supports 5.1, 5.5 and 5.6. There is also some adaption needed for
a new MySQL
version and so 5.7 is currently not supported but work is on the
way for this.
Next we define if we want to use innodb or if we want to use ndb
as the
storage engine in this test run.
We also define in the variable USE_BINARY_MYSQL_TARBALL whether
we use a
source tarball or a binary tarball. In this case I use a source
tarball.
We need to define the servers, there is in the case of sysbench
always
one and only one MySQL Server, there can be one or many NDB
management
servers. There can also be one or many NDB data nodes. But in
this case
we wanted a simple setup on one machine so here there is one of
each
sort and they are all located on 127.0.0.1 which is the local
host.
We always configure the MySQL server port to ensure we don't have
a
problem running the benchmark if another MySQL Server is running
on
the host. By default we use libtcmalloc instead of libc malloc,
we avoid
this default since there are better malloc libraries and this
variable should
be set purposely and not by default in most cases. But setting it
to not use
a special malloc library will definitely work, so we do that here
as a first
step.
Since we run with NDB storage engine we need to set up a few
configuration
variables. The NDB configuration can be quite extensive, but here
we strive
for a simplistic one. We want only one replica which we set using
NDB_REPLICAS.
We need to set the size of the NDB data memory and the NDB index
memory.
Finally we set the number of execution threads to 8 which is
pretty standard.
This should be sufficient for a simple benchmark run of
sysbench.
The final section sets up a minimalistic set of parameters to run
sysbench.
We run OLTP complex RW case. We insert 100.000 rows into the
table and we
run two tests, one with 16 MySQL connections and one with 32
connections.
Each test will run for 1 minute and we will only run each test
once.
Now that is a minimal configuration, it is possible to make it
even smaller
but then it's hard to understand what the test actually does
since one
needs to be an expert in the default settings which even I don't
remember
for all variables.
Now this file autobench.conf is placed in
/Users/mikael/bench/sysbench/autobench.conf
in my case.
Now it is time to run the test case.
The test is executed by the following commands:
cd /Users/mikael/bench
./bench_prepare.sh --default-directory
/Users/mikael/bench/sysbench
The test will run everything until completed. Actually I more or
less always use
one more parameter --skip-cleanup. This means that I don't have
to repeat the
building of the source code if I want to rerun the test. If I run
it then a
second time I should run with both --skip-cleanup and
--skip-build to ensure
that I skip rebuilding the source and skip cleaning away the
built source code
at the end of the test run.
The test result is located in the file:
/Users/mikael/bench/sysbench/final_results.txt
The output from the sysbench program in this particular case can
be found
in the file:
/Users/mikael/bench/sysbench/sysbench_results/oltp_complex_rw_1.res
Good luck in benchmarking.
My favourite tool in following the benchmarks is top. I usually
lock the various
threads onto different CPUs to ensure that I can understand how
the different
threads behaves. I haven't done this in this set up since this is
just a basic
setup. But top is still a good tool to follow what is going on in
the server.
One final note is that running this basic setup will require
20-30 GByte of
disk space and also during the test run there will be a fairly
high load on
the disk. As many developers are not suitable for benchmarks
of
database engines one way to adapt the benchmark for a
developer
environment is to set the config parameter
NDB_DISKLESS="yes".
This essentially makes the file system behave as if it was
located on
/dev/null.
Mar
23
2015