A Japanese Geek's MySQL Blog.

2009-03-20

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!!

14 comments:

  1. Hi Mikiya
    Nice and informative writeup.
    can you please elaborate on the differences between sizing and benchmark. Forgive me for the triviality of the question, I am a newbie.

    ReplyDelete
  2. This article has been a great help. It would be helpful, if there is also an example to read the results.
    Thanks for this article.

    ReplyDelete
  3. This article helps me quicly starting the MySQL benchmark. Thanks a lot!

    ReplyDelete
  4. Its a good news for the internet radio listeners, now you can listen your favourite internet radio through this web site, we provide unlimited music 24/7, so go a head click here to rock.....
    Enjoy your music,
    Thank you.

    ReplyDelete
  5. IT’S A GOOD NEWS FOR THE INTERNET RADIO LISTENERS, NOW YOU CAN LISTEN YOUR FAVOURITE INTERNET RADIO THROUGH THIS WEBSITE, WE PROVIDE UNLIMITED MUSIC 24/7, SO GO AHEAD CLICK HERE TO ROCK…..

    ENJOY YOUR MUSIC
    THANQ

    ReplyDelete
  6. Great information, I thought that it would take more steps or a more difficult procedure for it to work.

    ReplyDelete
  7. Very useful. Here are the small changes for Ubuntu 11.04:
    1. Need to install libmysqlclient-dev - using `sudo apt-get install libmysqlclient-dev'
    2. mysql.sock is located at /var/run/mysqld/mysqld.sock

    ReplyDelete
    Replies
    1. when I type
      "./configure --with-mysql=/usr/local/mysql [options] && make"
      it says
      "configure: error: invalid MySQL root directory: /usr/local/mysql"
      what's wrong? and do we have to chage [options] into some options?
      i'm using ubuntu 11.04 too,

      Delete
    2. LOL!

      Just use:

      ./configure --with-mysql && make

      Delete
  8. These facts are really interesting. Few of them were well known for me but many of them were brand new for me too!
    I will print this one out and show to my friends because they will be definitely interested in that. Thanks!
    MySQL

    ReplyDelete
  9. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, MySql online trainingamong the MySql in Hyderabad. Classroom Training in Hyderabad India

    ReplyDelete
  10. I have the next error while compiling. This is my line
    ./configure --with-mysql --with-mysql-includes=-I/usr/include/mysql --with-mysql-libs=-L/usr/lib/x86_64-linux-gnu && make

    And too mnay errors like
    ./configure --with-mysql --with-mysql-includes=-I/usr/include/mysql --with-mysql-libs=-L/usr/lib/x86_64-linux-gnu && make
    and finally say me collect2: ld returned 1 exit status
    make[2]: *** [client] Error 1
    make[2]: Leaving directory `/home/alumno/Downloads/dbt2-0.40/src'
    make[1]: *** [all-recursive] Error 1
    make[1]: Leaving directory `/home/alumno/Downloads/dbt2-0.40'
    make: *** [all] Error 2

    Anybody can help me?? Thanks!

    ReplyDelete