MySQL/Benchmark

Preparation

Create a test user and a test database, for MySQL:

mysql> drop database osdb;
mysql> drop user osdb@localhost;
mysql> create user osdb@localhost identified by 'osdb';
mysql> create database osdb;
mysql> grant all privileges on osdb.* to osdb@localhost;

For PostgreSQL:

sudo useradd -M -U -s /usr/sbin/nologin osdb                   # --no-create-home, --user-group
sudo -u postgres psql

postgres=# drop database osdb;
postgres=# drop role osdb;
postgres=# create role osdb with password 'osdb' createdb login;
postgres=# create database osdb owner osdb;

OSDB

The Open Source Database Benchmark may not be maintained any more. Building seems more or less straightforward:

sudo apt-get install subversion g++ libmysqlclient-dev libpq-dev
svn co svn://svn.code.sf.net/p/osdb/code/trunk osdb-svn
cd osdb-svn/osdb
./configure --prefix=/opt/osdb-svn
make && sudo make install

Get and extract the test data:

wget http://downloads.sourceforge.net/project/osdb/data-40mb/noCR/data-40mb.tgz
tar -xzf data-40mb.tgz
mv data-40mb data

Create a configuration file:

$ cat osdb.conf 
<?xml version="1.0" ?>
<osdb>
    <main>
        <logFilePath>osdb.log</logFilePath>
        <useStdOut>true</useStdOut>
    </main>
    <mysql>
        <server>localhost</server>
        <user>osdb</user>
        <password>osdb</password>
        <database>osdb</database>
    </mysql>
    <pgsql>
        <server>localhost</server>
        <user>osdb</user>
        <password>osdb</password>
        <dbname>osdb</dbname>
    </pgsql>
</osdb>

With all that in place, osdb seems to run - but fails early on:

$ /opt/osdb-svn/bin/osdb 
Open Source Database Benchmark
Reading configuration file: osdb.conf
Table creation took 0.04339 seconds
mysql_query() failed The used command is not allowed with this MySQL version
mysql_query() failed The used command is not allowed with this MySQL version
mysql_query() failed The used command is not allowed with this MySQL version
mysql_query() failed The used command is not allowed with this MySQL version
mysql_query() failed The used command is not allowed with this MySQL version
Dataset load took 0.00235105 seconds
agg_create_view completed in 0.00788403 seconds
agg_func completed in 0.00066185 seconds
agg_info_retrieval completed in 0.000458002 seconds
[...]

Sysbench

Unfortunately, the Debian version of sysbench only has the mysql driver compiled in - that's why we have to build this from source:

git clone https://github.com/akopytov/sysbench.git sysbench-git
cd sysbench-git

For some reason, the default branch of the upstream project doesn't build the oltp testsuite at all.. Let's try to get this working:

NOTE: The OLTP tests have been converted to LUA scripts.

Build & install:

sudo apt-get install libmysqlclient-dev libpq-dev
./autogen.sh && ./configure --prefix=/opt/sysbench --with-mysql --with-pgsql
make
sudo make install

Run the oltp test for MySQL:

PATH=/opt/sysbench/bin:${PATH}
export opt_mysql="--db-driver=mysql --mysql-host=localhost6 --mysql-user=osdb --mysql-password=osdb --mysql-db=osdb"

sysbench --threads=8 --time=60 ${opt_mysql} oltp_insert cleanup
sysbench --threads=8 --time=60 ${opt_mysql} oltp_insert prepare
sysbench --threads=8 --time=60 ${opt_mysql} oltp_insert run

For PostgreSQL:

export opt_pgsql="--db-driver=pgsql --pgsql-host=localhost6 --pgsql-user=osdb --pgsql-password=osdb --pgsql-db=osdb"

sysbench --threads=8 --time=60 ${opt_pgsql} oltp_insert cleanup
sysbench --threads=8 --time=60 ${opt_pgsql} oltp_insert prepare
sysbench --threads=8 --time=60 ${opt_pgsql} oltp_insert run

TODO

• What about Firebird? Other databases?

Links

• Percona Software Documentation » Benchmark Pages » sysbench » oltp.lua
• lp:~vadim-tk/sysbench/sysbench-db-scripts
• lp:~ahel/maria/mariadb-benchmarks