Settingup DBT-2

DBT-2 is a TPC-C like OLTP benchmark, and very popular amongst many MySQL users. It is used by MySQL QA team to test the stability and performance before release. However, steps to setup DBT-2 is a little bit messy, and its README files include some dummy information. So I introduce you these steps below:

1. Download it!

You can download the source code from here: http://osdldbt.sourceforge.net/

2. Required packages

The following perl packages are required to build DBT-2. Unfortunately, configure script doesn't complain even if they are missing. Install them using, e.g. CPAN.

shell> sudo cpan Statistics::Descriptive
shell> sudo cpan Test::Parser
shell> sudo cpan Test::Reporter

If you want to make a graph from the output, you have to install gnuplot in advance. e.g. Ubuntu users can install it like below:

shell> sudo apt-get install gnuplot

3. Compile it

You can build the software in very standard way, configure and make. For MySQL users, --with-mysql option is mandatory. If you want to specify install path, then use --prefix option.

shell> ./configure --with-mysql=/usr/local/mysql [options] && make
shell> sudo make install

Ubuntu users may need to install build-essentials package in advance.

shell> sudo apt-get install build-essentials

4. Generate and load data

shell> datagen -w 10 -d /var/tmp/dbt2 --mysql

datagen is a command to generate data under the given directory. -w option specifies the number of warehouse, more is larger data. If you want to test CPU bound test, use small value.

shell> scripts/mysql/build_db.sh \
-w 3 -d dbt2 -f /var/tmp/dbt2 -s /tmp/mysql.sock \
-h localhost -u user -p password -e INNODB

Then, you can load the data generated by datagen program using build_db.sh, located under scripts/mysql directory under the source code directory. The following indicates the size of data with 10 warehouses for example.

mysql> select table_name,sum(data_length) from tables where table_schema='dbt2' group by table_name with rollup;
+------------+------------------+
| table_name | sum(data_length) |
+------------+------------------+
| customer   |        200081408 |
| district   |            16384 |
| history    |         25755648 |
| item       |         11026432 |
| new_order  |          4702208 |
| orders     |         22626304 |
| order_line |        314572800 |
| stock      |        381648896 |
| warehouse  |            16384 |
| NULL       |        960446464 |
+------------+------------------+
10 rows in set (0.54 sec)

5. Run benchmark

shell> export USE_PGPOOL=0
shell> export LD_LIBRARY_PATH=/usr/local/mysql/lib
shell> scripts/run_workload.sh -c 16 -d 600 -n -w 10 -s 10 -u user -x password

The benchmark is done using run_workload.sh script. This script accepts the following option.

-c ... thread concurrency
-d ... duration
-n ... no thinking time. do not miss this option if you want to see the database performance ;)
-w ... number of warehouse
-s ... delay before starting each new thread in millisecond
- u ... MySQL user name
-x ... MySQL password
-l ... MySQL TCP/IP port number
-H ... MySQL server hostname

Then, the benchmark runs during the period specified by -d option, and generates data under ./output directory. You may see the error like following. This error indicates that no data is written in the ./output directory. It is not a Test::Parser::Dbt2.pm problem at all!

Can't use an undefined value as an ARRAY reference at /usr/local/share/perl/5.10.0/Test/Parser/Dbt2.pm line 521.

One reason why this error appears is that libmysqlclient.so wasn't loaded correctly. Do not forget specifying the right PATH to the library before the benchmark.

shell> export LD_LIBRARY_PATH=/usr/local/mysql/lib


Enjoy!!