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安装包

wget http://optusnet.dl.sourceforge.net/sourceforge/osdldbt/dbt2-0.40.tar.gz

 

安装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主机名