MySQL/Benchmark

From Segfault
Jump to: navigation, search

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:[1]

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.[2]. Let's try to get this working:

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

Build & install:

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

Just to be sure (?), create a my.cnf file:

$ cat ~/.my.cnf
[client]
user = osdb
password = osdb
database = osdb

Run the oltp test for MySQL:

PATH=/opt/sysbench/bin:$PATH
opt_mysql="--db-driver=mysql --mysql-user=osdb --mysql-password=osdb --mysql-db=osdb"
sysbench --test=tests/db/oltp.lua $opt_mysql cleanup
sysbench --test=tests/db/oltp.lua $opt_mysql prepare
sysbench --test=tests/db/oltp.lua $opt_mysql --mysql-dry-run=off --num-threads=8 --max-requests=10000 run

For PostgreSQL:

opt_pgsql="--db-driver=pgsql --pgsql-user=osdb --pgsql-password=osdb --pgsql-db=osdb"
sysbench --test=tests/db/oltp.lua $opt_pgsql cleanup
sysbench --test=tests/db/oltp.lua $opt_pgsql prepare
sysbench --test=tests/db/oltp.lua $opt_pgsql --num-threads=8 --max-requests=10000 run

Interesting options to tinker with:

 --num-threads=N             number of threads to use [1]
 --max-requests=N            limit for total number of requests [10000]
 --validate=[on|off]         perform validation checks where possible [off]
 
 --oltp-test-mode=STRING                  {simple,complex,nontrx,sp} [complex]
 --oltp-reconnect-mode=STRING             {session,transaction,query,random} [session]
 --oltp-sp-name=STRING                    name of store procedure to call in SP test mode []
 --oltp-read-only=[on|off]                generate only 'read' queries (do not modify database) [off]
 --oltp-skip-trx=[on|off]                 skip BEGIN/COMMIT statements [off]
 --oltp-range-size=N                      range size for range queries [100]
 --oltp-point-selects=N                   number of point selects [10]
 --oltp-simple-ranges=N                   number of simple ranges [1]
 --oltp-sum-ranges=N                      number of sum ranges [1]
 --oltp-order-ranges=N                    number of ordered ranges [1]
 --oltp-distinct-ranges=N                 number of distinct ranges [1]
 --oltp-index-updates=N                   number of index update [1]
 --oltp-non-index-updates=N               number of non-index updates [1]
 --oltp-nontrx-mode=STRING                {select, update_key, update_nokey, insert, delete} [select]
 --oltp-auto-inc=[on|off]                 whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
 --oltp-connect-delay=N                   time in microseconds to sleep after connection to database [10000]
 --oltp-user-delay-min=N                  minimum time in microseconds to sleep after each request [0]
 --oltp-user-delay-max=N                  maximum time in microseconds to sleep after each request [0]
 --oltp-table-name=STRING                 name of test table [sbtest]
 --oltp-table-size=N                      number of records in test table [10000]
 --oltp-dist-type=STRING                  random numbers distribution {uniform,gaussian,special} [special]
 --oltp-dist-iter=N                       number of iterations used for numbers generation [12]
 --oltp-dist-pct=N                        percentage of values to be treated as 'special' (for special distribution) [1]
 --oltp-dist-res=N                        percentage of 'special' values to use (for special distribution) [75]
 --oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]
 --oltp-point-select-all-cols=[on|off]    select all columns for the point-select query [off]
 --oltp-secondary=[on|off]                Use a secondary index in place of the PRIMARY index [off]
 
 --mysql-table-engine=STRING   test table engine {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
 --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]
 --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]

TODO

Links

References