dbt2 mysql_mysql dbt2 benchmark Centos6下安装配置

环境说明

mysql 5.1

Centos 6.4 x86_64

dbt2-2.0.40

安装步骤

安装依赖包

#yum install gnuplot gcc sysstat perl cpan

#cpan Statistics::Descriptive

#cpan Test::Parser

#cpan Test::Reporter

安装dbt2

获取dbt2安装包

安装dbt2

#tar -xzvf dbt2-0.40.tar.gz

#cd dbt2-0.40

#./configure --with-mysql

#make

使用dbt2

生成测试数据

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

-w 指定了数据仓库的个数

-d  指定了生成的数据所在的目录

修改build.sh文件

#cd /mnt/dbt2-0.40/scripts/mysql

#vi build_db.sh

将NEW_ORDER部分修改为以下内容

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)

)"

将

command_exec "$MYSQL $DB_NAME -e \"LOAD DATA $LOCAL INFILE \\\"$DB_PATH/$FN.data\\\" \

INTO TABLE $TABLE FIELDS TERMINATED BY '\t'\""

修改为

command_exec "$MYSQL $DB_NAME -e \"LOAD DATA $LOCAL INFILE \\\"$DB_PATH/$FN.data\\\" IGNORE\

INTO TABLE $TABLE FIELDS TERMINATED BY '\t'\""

修改/etc/my.cnf文件如下：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#[mysqld_safe]

#log-error=/var/log/mysqld.log

#pid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]

log-error=/var/lib/mysql/mysql.err

pid-file=/var/lib/mysql/mysql.pid

[client]

socket=/var/lib/mysql/mysql.sock

在/tmp下创建mysql.sock连接

#ln -s /var/lib/mysql/mysql.sock /tmp/

运行build.sh

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

导入sql

#cd /mnt/dbt2-0.40/storedproc/mysql

#sed -i -e 's/|\;/|/' *.sql

#mysql -u root -p$MYSQLPASS -D dbt2 < new_order.sql

#mysql -u root -p$MYSQLPASS -D dbt2 < new_order_2.sql

#mysql -u root -p$MYSQLPASS -D dbt2 < order_status.sql

#mysql -u root -p$MYSQLPASS -D dbt2 < payment.sql

#mysql -u root -p$MYSQLPASS -D dbt2 < stock_level.sql

运行dbt2测试

#./run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root -x hgctech

MySQL pid file 'yes/var/localhost.pid' does not exist.

MySQL was not stopped, if it was running.

/root/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p

************************************************************************

* DBT-2 test for mysql started

* *

* Results can be found in output/1 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...

./run_workload.sh: line 461: 13144 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

./run_workload.sh: line 498: 13190 Terminated ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} > ${OUTPUT_DIR}/driver.out 2>&1

chmod: cannot access `/root/dbt2-0.40/scripts/output/1/db/log': No such file or directory

Test completed.

Results are in: /root/dbt2-0.40/scripts/output/1

Response Time (s)

Transaction % Average : 90th % Total Rollbacks %

------------ ----- --------------------- ----------- --------------- -----

Delivery 3.76 0.162 : 0.285 81 0 0.00

New Order 43.55 0.054 : 0.125 938 7 0.75

Order Status 4.27 0.038 : 0.096 92 0 0.00

Payment 41.69 0.021 : 0.057 898 0 0.00

Stock Level 6.73 0.107 : 0.200 145 50 52.63

------------ ----- --------------------- ----------- --------------- -----

504.00 new-order transactions per minute (NOTPM)

1.8 minute duration

0 total unknown errors

397 second(s) ramping up

-c并发的线程数   -d 执行的时间(秒)   -n 无思考时间，作者说如果想看数据库性能，请不要漏掉这个选项   -w 数据仓库个数   -s 每个新线程开始前的延迟时间(微秒)   -u MySQL用户名   -x MySQL相应用户的密码   -l MySQL TCP/IP端口号   -H MySQL主机名