mysql dbt2 benchmark Centos6下安装配置

电玩女神 2022-09-17 11:26 131阅读 0赞

# 环境说明 #

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