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

49 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
    Replies
    1. Hmm , very informative article! Thanks to author for it! Don't you think it's worth make a video on this article and post it on TikTok? There a large audience of users will be able to see it and give you a solution to the problem. You can also always buy tiktok followers in order to wind up their number.

      Delete
    2. Nice
      https://thepixelphotography.in/
      #thepixelphotography

      Delete
  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. Great information, I thought that it would take more steps or a more difficult procedure for it to work.

    ReplyDelete
  6. 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
    3. https://thepixelphotography.in/
      very Informative Site

      Delete
  7. 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
  8. 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
  9. Thank you very much. Post was lengthy, but I loved to read it till the last word. Thanks for all the information, it was very helpful I really like that you are providing information on android app development.
    Oracle Fusion HCM Technical Training

    ReplyDelete
  10. Hi,
    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 note this one out and show to my friends because they will be definitely interested in that.
    thank you.
    oracle EBS training

    ReplyDelete
  11. You’ve done a great job with this.Thanks to share this informative article.

    Oracle Fusion Technical Training In Hyderabad

    ReplyDelete
  12. Thanks for posting this blog. This was very interesting blog, I felt comfortable while reading this post, thank you..
    Please click here for more information
    Oracle DBA Training in Al Karama, Dubai

    ReplyDelete
  13. This article has been a great help,Thanks for this article.For more details about Oracle Fusion PPM Training in Hyderabad

    ReplyDelete
  14. Nice Info. Thanks for Sharing.
    Ensures Best Online Job Support.
    Best Online Job Support

    ReplyDelete
  15. This post was very well written, and it also contains a lot of useful facts. I enjoyed your distinguished way of writing the post. Thanks, you have made it easy for me to understand.

    Accounting Firms Melbourne
    Bookkeeping Services Melbourne
    Tax Agent Melbourne
    Small Business Accountant Melbourne

    ReplyDelete
  16. instagram takipçi satın al
    instagram takipçi satın al
    takipçi satın al
    takipçi satın al
    instagram takipçi satın al
    takipçi satın al
    instagram takipçi satın al
    aşk kitapları
    tiktok takipçi satın al
    instagram beğeni satın al
    youtube abone satın al
    twitter takipçi satın al
    tiktok beğeni satın al
    tiktok izlenme satın al
    twitter takipçi satın al
    tiktok takipçi satın al
    youtube abone satın al
    tiktok beğeni satın al
    instagram beğeni satın al
    trend topic satın al
    trend topic satın al
    youtube abone satın al
    beğeni satın al
    tiktok izlenme satın al
    sms onay
    youtube izlenme satın al
    tiktok beğeni satın al
    sms onay
    sms onay
    perde modelleri
    instagram takipçi satın al
    takipçi satın al
    tiktok jeton hilesi
    pubg uc satın al

    ReplyDelete
  17. On your place I would make a video how to setup dbt2 and share such video on youtube. Nowadays is very easy to get many likes and views for such video. You can use this site https://soclikes.com to buy it

    ReplyDelete
  18. yes very Informative Site
    https://thepixelphotography.in/

    ReplyDelete
  19. https://thepixelphotography.in/
    Best Wedding photography in Hyderabad, Telangana, Andhrapradesh, Godhavarikhani,Peddhapalli,
    Ramagundham, Sulthanabad,Vijayawada,Vizag.

    ReplyDelete
  20. At Yoga Bali we offer unique Yoga Teacher Training in Bali and online and have graduated over 50 successful yoga teacher trainees from all corners of the globe. Our 80 video Yoga Teacher Training Course is internationally accredited. Our 80 Hour Yoga Online Video Training is designed to connect existing teachers with a robust body of further knowledge and professional up-skilling with our dedicated teaching team.

    ReplyDelete
  21. It s a very useful page. Thank you. 16561006735286b1207ec5b1b4afa55b
    keles
    imranli
    cifteler
    alacakaya
    karasu
    seydiler
    havsa
    bahce
    tortum

    ReplyDelete
  22. Congratulations on your article, it was very helpful and successful. e21b98640a66cf7d9d65e22f33690891
    website kurma
    sms onay
    numara onay

    ReplyDelete
  23. Thank you for your explanation, very good content. b3c67bb649f32b1a82119cfa940b45ba
    define dedektörü

    ReplyDelete
  24. Thanks for your article. 3553c9dfba870b6e20a3d7655ec953c9
    evde iş imkanı

    ReplyDelete