Dbt2 Benchmark 설치

학부 논문 때 Mysql에서 트레이스 추출시 신뢰성을 위해 TPC-C 벤치마크를 사용했다.
벤치마크 설치 과정이 까다롭고 벤치마크 설치 전에 기본 패키지들이 깔려있어야 나중에 에러가 나지 않는다.
참고 사이트 : http://blog.dbadojo.com/2007/08/mysql-dbt2-benchmark-on-ec2-part-1_31.html

< TPC-C 설치과정 >
dbt2-0.40.tar.gz 다운로드
*압축풀기 : tar xzvf dbt2-0.40.tar.gz
cd dbt2-0.40
* 환경설정 : ./configure --wirh-mysql=/usr/local/mysql
make
mkdir -p /mnt/data
* 20개의 warehouse dataset 생성
dbt2-0.40/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...

* 인덱스 추가
/dbt2-0.40/scripts/mysql# vi build_db.sh <--이 파일 수정

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)
)"

* Dataset 로드
scripts/mysql# ./build_db.sh -d dbt2 -f /mnt/data -s /tmp/mysql.sock -u root
(Mysql에 비밀번호 설정되어 있으면 -p 옵션사용)

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

* Stored procedure의 delimiter 편집
dbt2-0.40/storedproc/mysql# sed -i -e 's/|\;/|/' *.sql

 /usr/local/mysql/bin/mysql -u root -D dbt2 < new_order.sql
/usr/local/mysql/bin/mysql -u root -D dbt2 < new_order_2.sql
/usr/local/mysql/bin/mysql -u root -D dbt2 < order_status.sql
/usr/local/mysql/bin/mysql -u root -D dbt2 < payment.sql
/usr/local/mysql/bin/mysql -u root -D dbt2 < stock_level.sql

* Mysql 실행

/usr/local/mysql/bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.0.45-log Source distribution

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

dbt2 데이터베이스 구성 확인

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.03 sec)

Mysql이 사용하는 포트번호와 socket경로, 그리고 각 buffer의 크기 등을 확인하고 싶을 때 my.cnf 파일을 확인
 grep -v "#" /etc/my.cnf|sed -e '/^$/d'

[client]
port  = 3306
socket  = /tmp/mysql.sock
[mysqld]
port  = 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

* 벤치마크수행
dbt2-0.40/scripts# ./run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root

root@ubuntu-desktop:~/dbt2/dbt2-0.40/scripts# ./run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root
MySQL pid file '/usr/local/mysql/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/home/hychoe/dbt2/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 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 498: 10005 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} > ${OUTPUT_DIR}/driver.out 2>&1
./run_workload.sh: line 461:  9955 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} > ${OUTPUT_DIR}/client.out 2>&1
MySQL pid file '/usr/local/mysql/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 `/home/hychoe/dbt2/dbt2-0.40/scripts/output/1/db/log': No such file or directory
Test completed.
Results are in: /home/hychoe/dbt2/dbt2-0.40/scripts/output/1

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   4.17     16.924 :    19.729           49                0   0.00
   New Order  44.73     16.844 :    19.804          526                8   1.54
Order Status   4.42     15.297 :    17.998           52                0   0.00
     Payment  40.65     16.772 :    19.956          478                0   0.00
 Stock Level   6.04     17.401 :    20.177           71               26  57.78
------------  -----  ---------------------  -----------  ---------------  -----

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

결과가 나오지 않을 경우 /usr/bin 디렉토리에 cpan을 다시 실행해서 
다음 기본 패키지를 깔아준다.

install Chart::Graph::Gnuplot
install Test::Parser
install Test::Reporter