MySQL DBT2 Benchmark on EC2 part 1

Posted by

10

In the last couple of articles I have been using the load simulator/generator tool provided with the MySQL 5.1 install called mysqlslap.

I read around on some other blogs and thought it might be also useful to use a benchmarking tool. DBT2 is a TPC-C like benchmark tool provided by OSDL. You can download the software from the DBT sourceforge site.

The TPC-C is a online transaction test benchmark and the overall measure is in Transactions per Minute on TPM. The higher the better.

I chose to use the DBT2 benchmark as this is the tool that MySQL AB themselves have used to benchmark their MySQL Cluster. I also found plenty of useful information of Peter Zaitsev’s MySQLPerformance blog, specifically this presentation (PDF).

On with the show, once again, we need to test a plain standalone MySQL database. Once that is done we can build on that to test MySQL replication (Master-Slave) and MySQL NDB Clusters.

The documentation for the tool is essentially the README and README-MYSQL files in the tarball, the README mentioned a user manual which unfortunately I couldn’t find anywhere.
There is scant mention of prerequisites either, hopefully this article will help fill the void for a while.
I will probably need to create a proper HOWTO document for this as there were a bunch of conflicting information in the README files which was different to the actually files available.

Prerequisites:

  1. Perl 5.8
  2. Perl Modules: Chart::Graph::Gnuplot, Test::Parser, Test::Reporter, XML::Simple, XML::Twig
  3. Linux packages: gcc gnuplot sysstat
  4. MySQL 5.0 for stored procedures.

Comments:

  • I used CentOS 4.4 again as the base linux distro and added the required packages.
  • use CPAN to install the perl modules as it will handle any dependencies.
  • Some of the names are different from the README files.
  • The TPM results for 20 warehouses and 20 concurrent sessions scaled ok based on the number of threads (Terminal Threads). The lower the number of threads the lower the TPM.
  • For the most part the benchmark was constrained by IO waits. I was using the /mnt mountpoint on EC2, and whilst a test write of a 100M file using dd if=/dev/zero of=/mnt/data/test1 count=1 bs=100M was quick, the random nature of the IO was a killer.
  • Innodb logfiles where separated from the ibdata file.

Results:

  • Using an optimized my.cnf based on my-heavy-innodb-4G.cnf file (see listing below)
  • Data generated 20 warehouses which equated to a 3 Gigabyte database
  • Benchmark settings of 100 terminal threads, 20 concurrent sessions, 20 warehouses
  • Duration of 30 minutes.
  • 2016.02 TPM (transactions per minute)

As I have time I will try and replicate the size (200 warehouses) of Peter Zaitsev’s article, however the data generation step took a fair amount of time.

Have Fun

Paul



Get and install the prerequisites

cd /mnt
wget http://optusnet.dl.sourceforge.net/sourceforge/osdldbt/dbt2-0.40.tar.gz
tar -xzvf dbt2-0.40.tar.gz
yum install gnuplot gcc sysstat
cd dbt2-0.40
./configure --with-mysql --with-mysql-libs=/usr/local/mysql/lib/ \
--with-mysql-includes=/usr/local/mysql/includes
make

Generate the 20 warehouse dataset

mkdir -p /mnt/data
src/datagen -w 20 -d /mnt/data --mysql

warehouses = 20
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900

Output directory of data files: /mnt/data

Generating data files for 20 warehouse(s)...
Generating item table data...
Finished item table data...
Generating warehouse table data...
Finished warehouse table data...
Generating stock table data...
Finished stock table data...
Generating district table data...
Finished district table data...
Generating customer table data...
Finished customer table data...
Generating history table data...
Finished history table data...
Generating order and order-line table data...

Finished order and order-line table data...
Generating new-order table data...
Finished new-order table data...

Load the dataset after adding an additional index as suggested by Zaitsev presentation

cd /mnt/dbt2-0.40/scripts/mysql

vi build_db.sh

Modified the CREATE TABLE add index to NEW_ORDER table

NEW_ORDER="CREATE TABLE new_order (
no_o_id int(11) NOT NULL default '0',
no_d_id int(11) NOT NULL default '0',
no_w_id int(11) NOT NULL default '0',
PRIMARY KEY  (no_d_id,no_w_id,no_o_id),
KEY ix_no_wid_did (no_w_id,no_d_id)
)"

Load the dataset

sh build_db.sh -d dbt2 -f /mnt/data -s /tmp/mysql.sock -u root -p $MYSQLPASS

Loading of DBT2 dataset located in /mnt/data to database dbt2.

DB_ENGINE:      INNODB
DB_SCHEME:      OPTIMIZED
DB_HOST:        localhost
DB_USER:        root
DB_SOCKET:      /tmp/mysql.sock

Creating table STOCK
Creating table ITEM
Creating table ORDER_LINE
Creating table ORDERS
Creating table NEW_ORDER
Creating table HISTORY
Creating table CUSTOMER
Creating table DISTRICT
Creating table WAREHOUSE

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
Loading table orders
Loading table stock
Loading table warehouse

Edit the MySQL stored procedures to fix delimiter (replacing |; with |)

cd /mnt/dbt2-0.40/storedproc/mysql
sed -i -e 's/|\;/|/' *.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order_2.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt order_status.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt payment.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt stock_level.sql

Check that MySQL is ready to go

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dbt2
Database changed
mysql> show tables;
+----------------+
| Tables_in_dbt2 |
+----------------+
| customer       |
| district       |
| history        |
| item           |
| new_order      |
| order_line     |
| orders         |
| stock          |
| warehouse      |
+----------------+
9 rows in set (0.00 sec)

mysql> show table status
 -> ;
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| customer   | InnoDB |      10 | Compact    |  603562 |            665 |   401604608 |               0 |     46907392 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| district   | InnoDB |      10 | Compact    |      43 |           1524 |       65536 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| history    | InnoDB |      10 | Compact    |  600452 |             83 |    49889280 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| item       | InnoDB |      10 | Compact    |  100160 |            110 |    11026432 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| new_order  | InnoDB |      10 | Compact    |  152246 |             51 |     7880704 |               0 |      3686400 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| order_line | InnoDB |      10 | Compact    | 5697509 |             95 |   545259520 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| orders     | InnoDB |      10 | Compact    |  600343 |             60 |    36257792 |               0 |     27885568 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| stock      | InnoDB |      10 | Compact    | 2000025 |            382 |   764411904 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| warehouse  | InnoDB |      10 | Compact    |      20 |            819 |       16384 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
9 rows in set (0.77 sec)

mysql> SHOW PROCEDURE STATUS
 -> ;
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db   | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| dbt2 | delivery     | PROCEDURE | root@localhost | 2007-08-30 21:21:11 | 2007-08-30 21:21:11 | DEFINER       |         |
| dbt2 | new_order    | PROCEDURE | root@localhost | 2007-08-30 21:22:33 | 2007-08-30 21:22:33 | DEFINER       |         |
| dbt2 | new_order_2  | PROCEDURE | root@localhost | 2007-08-30 21:22:38 | 2007-08-30 21:22:38 | DEFINER       |         |
| dbt2 | order_status | PROCEDURE | root@localhost | 2007-08-30 21:22:44 | 2007-08-30 21:22:44 | DEFINER       |         |
| dbt2 | payment      | PROCEDURE | root@localhost | 2007-08-30 21:22:51 | 2007-08-30 21:22:51 | DEFINER       |         |
| dbt2 | stock_level  | PROCEDURE | root@localhost | 2007-08-30 21:22:56 | 2007-08-30 21:22:56 | DEFINER       |         |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
6 rows in set (0.00 sec)

Listing of my.cnf

grep -v "#" /etc/my.cnf|sed -e '/^$/d'

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type =myISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir =/mnt/mysql/data/
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

Benchmark runs:

sh run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/16 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 400
TERMINALS PER WAREHOUSE: 20
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 300
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 300 seconds

Stage 3. Processing of results...
Killing client...
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 498: 10227 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
run_workload.sh: line 461: 10185 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/16/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/16

                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.53      0.263 :     0.533           71                0   0.00
New Order  45.58      0.028 :     0.076          918               10   1.10
Order Status   3.67      0.071 :     0.173           74                0   0.00
  Payment  41.46      0.035 :     0.102          835                0   0.00
Stock Level   5.76      0.003 :     0.004          116               37  46.84
------------  -----  ---------------------  -----------  ---------------  -----

503.55 new-order transactions per minute (NOTPM)
1.8 minute duration
0 total unknown errors
400 second(s) ramping up

sh run_workload.sh -c 20 -t 10 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/18 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 200
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 13344 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 13382 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/18/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/18

1188549933
8597
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.99      0.117 :     0.227          344                0   0.00
   New Order  45.19      0.041 :     0.086         3900               33   0.85
Order Status   3.90      0.042 :     0.106          337                0   0.00
     Payment  42.79      0.013 :     0.023         3693                0   0.00
 Stock Level   4.13      0.003 :     0.003          356                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

254.19 new-order transactions per minute (NOTPM)
15.1 minute duration
0 total unknown errors
199 second(s) ramping up


sh run_workload.sh -c 20 -t 5 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/19 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 100
TERMINALS PER WAREHOUSE: 5
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14070 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/19/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/19

1188551055
4260
                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.81      0.085 :     0.174          163                0   0.00
New Order  47.02      0.030 :     0.080         2011               17   0.85
Order Status   3.95      0.038 :     0.113          169                0   0.00
  Payment  41.55      0.010 :     0.021         1777                0   0.00
Stock Level   3.67      0.002 :     0.003          157                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

130.21 new-order transactions per minute (NOTPM)
15.2 minute duration
0 total unknown errors
98 second(s) ramping up

Adjust ulimit and filesize

sh run_workload.sh -c 20 -t 200 -d 300 -w 20 -u root -x $MYSQLPASS

error: you're open files ulimit is too small, must be at least 8020

ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) 10000
pending signals                 (-i) 13664
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 13664
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

ulimit -n 9000
ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) 10000
pending signals                 (-i) 13664
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 9000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 13664
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

sh run_workload.sh -c 20 -t 100 -d 3600 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/20 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 2000
TERMINALS PER WAREHOUSE: 100
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 3600
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 3600 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14572 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 14610 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 554: 18714 File size limit exceeded${abs_top_srcdir}/scripts/post-process --dir ${OUTPUT_DIR} --xml >${DRIVER_OUTPUT_DIR}/results.out
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/20/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/20

Run the post-process step manually

./post-process --dir /mnt/dbt2-0.40/scripts/output/20 --xml

Use of uninitialized value at /usr/lib/perl5/site_perl/5.8.5/Test/Parser/Dbt2.pm line
                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.79      5.578 :     5.167         5309                0   0.00
New Order  43.84      6.023 :     5.492        61347              618   1.02
Order Status   3.74      5.270 :     5.021         5240                0   0.00
  Payment  41.30      5.276 :     5.005        57799                1   0.00
Stock Level   7.33      5.004 :     5.513        10252             4883  90.95
------------  -----  ---------------------  -----------  ---------------  -----

2016.02 new-order transactions per minute (NOTPM)
29.8 minute duration
0 total unknown errors
2015 second(s) ramping up

10 thoughts on “MySQL DBT2 Benchmark on EC2 part 1”

  1. Hi, I am trying to change my.cnf file so that I can increase the max_connections variable. But, this doesn’t seem to change as I can still see the default connection. Can you please let me know how do I change some parameters and get that effective.Thanks,Harita

  3. Hi,I got an error in ‘make’ stage,like this[root@search-ksdb1-1 /data3/dbt2-0.40] makemake all-recursivemake[1]: Entering directory `/data3/dbt2-0.40′Making all in .make[2]: Entering directory `/data3/dbt2-0.40′make[2]: Leaving directory `/data3/dbt2-0.40′Making all in srcmake[2]: Entering directory `/data3/dbt2-0.40/src’gcc -I/data1/mysql/include -mcpu=i686 -Wall -g -O2 -L/data1/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm -o client client-client.o client-transaction_queue.o client-listener.o client-db_threadpool.o client-db.o client-common.o client-logging.o client-_socket.o client-client_interface.o client-transaction_data.o client-_semaphore.o client-dbc_common.o client-dbc_new_order.o client-dbc_payment.o client-dbc_order_status.o client-dbc_delivery.o client-dbc_stock_level.o client-dbc_integrity.o client-dbc_nonsp.o -lz -lm -lpthreadclient-dbc_common.o(.text+0x27): In function `commit_transaction’:mysql/dbc_common.c:26: undefined reference to `mysql_real_query’client-dbc_common.o(.text+0x46):mysql/dbc_common.c:28: undefined reference to `mysql_error’client-dbc_common.o(.text+0x52):mysql/dbc_common.c:28: undefined reference to `mysql_errno’client-dbc_common.o(.text+0xa5): In function `_connect_to_db’:mysql/dbc_common.c:38: undefined reference to `mysql_init’...I dont know what the problem is. any idea??Thanks,yw Kim.

  6. It would need to set delay=0 to run the dbt2, by using option:./run_workload.sh -n …This option can increase the CPU utlization from 3%->99%, improving the TPM from arround 250TPM->7850TPM

  7. Thanks Jenny for the comment.I will have to try that out.The no delay option determines the maximum possible TPM, which would indicate a ceiling of sorts for an EC2 node…. now I am even more keen to see what it is for each instance πŸ™‚

  10. Thanks for the useful dbt2 post.Just to add that in the stored procedure section the delivery.sql execution is missing.Don't forget to add it as a delivery prosedure.

Comments are closed.