Mysql에서 TPC-C 벤치마크 수행

으하하 드디어 해냈다 TPC-C 벤치마크 수행,,
프로젝트 할 때 날 그렇게 괴롭히더니 텀 프로젝트하면서 해결됐다 ㅠㅠ
매우 슬프군,,

그래서 흔적을 남긴다
담에도 또 써먹을수 있도록!!

*먼저 설치할 것
-gnuplot
gnuplot-4.2.3.tar.gz

cd gnuplot-4.2.0
./configure
make
make check
make install

-sysstat
sysstat-7.0.0.tar.gz(설치법: http://blog.naver.com/blueruby?Redirect=Log&logNo=60029332160)

1.       #mkdir /usr/local/sar1

2.       #mkdir –p /usr/local/sar1/log/sa

3.       #cd /usr/local/sar1

4.       #wget http://perso.orange.fr/sebastien.godard/sysstat-7.0.0.tar.gz

5.       #tar xvzf sysstat-7.0.0.tar.gz

6.       #cd sysstat-7.0.0

7.       #make config

….

Isstallation directory /usr/local/sar1

…     sadc directory

system activity directory /usr/local/sar1/log/sa

clean system activity directory n

Enable National Language Support y

8.       #make

9.       #make install

10.   #cd /usr/local/sar1

11.   #ls

bin doc lib log man share

sysstat-7.0.0      sysstat-7.0.0.tar.gz

12.   #cd /etc/cron.d

13.   #ls

14.   sysstat

#vi sysstat

#run system activity accownting tool every to minutes

*/10 * * * * root /usr/lib/sa/sa1 1 1

*/10 * * * * root /usr/local/sar1/lib/sa/sa1 1 1

#generate a daily summary of process accounting at 23:53

53 23 * * * root /usr/lib/sa/sa2 -A

53 23 * * * root /sar1/lib/sa/sa2 –A

-iostat 7.0
#cd /usr/local/sar1
#ls
#cd bin
#ls (iostat mpstat sadf sar)
#iostat
#./iostat

http://blog.dbadojo.com/2007/08/mysql-dbt2-benchmark-on-ec2-part-1_31.html
1. tpc-c 설치
/mnt에 dbt2-0.40.tar.gz 다운로드
cd /mnt
tar -xzvf dbt2-0.40.tar.gz
cd dbt2-0.40
./configure --with-mysql=/usr/local/mysql
make

2. 20개 warehouse dataset 생성
mkdir -p /mnt/data
src/datagen -w 20 -d /mnt/data --mysql

3. 인덱스 추가
cd /mnt/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)
)"

4. dataset 로드

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

sh 설치 안되어있으면
./build_db.sh -d dbt2 -f /mnt/data -s /tmp/mysql.sock -u root -p $MYSQLPASS

mysql 패스워드가 있으면 위에처럼 하고, 아니면 -p 옵션 안주면 됨

5. stored procedure의 delimiter 편집
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

6. mysql 설치된 디렉토리에 가서
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
mysql> SHOW PROCEDURE STATUS;

10.
Listing of my.cnf

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

11. 벤치마크 수행
sh run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root -x $MYSQLPASS
sh run_workload.sh -c 20 -t 10 -d 900 -w 20 -u root -x $MYSQLPASS
sh run_workload.sh -c 20 -t 5 -d 900 -w 20 -u root -x $MYSQLPASS
sh run_workload.sh -c 20 -t 200 -d 300 -w 20 -u root -x $MYSQLPASS
sh run_workload.sh -c 20 -t 100 -d 3600 -w 20 -u root -x $MYSQLPASS

./post-process --dir /mnt/dbt2-0.40/scripts/output/20 --xml
post-process 해서 아래 화면 처럼 안나오면 perl 설치
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
------------  -----  ---------------------  -----------  ---------------  -----


perl 설치 방법(http://blog.naver.com/hotfeel7?Redirect=Log&logNo=150002056924)
$ tar xvfz ./perl-5.8.5.tar.gz
$ cd perl-5.8.5
$ ./Configure -Dprefix=$HOME/perl -des
$ make
$ make test
$ make install

$ cd perl/bin
$ ./perl -V

cpan 초기화
$ cd ~/perl/bin
$ ./perl -MCPAN -e shell

$ cd ~/perl

$ ./bin/cpan

cpan>install Chart::Graph::Gnuplot
cpan>install Test::Parser
cpan>install Test::Reporter
cpan>install XML::Simple
cpan>install XML::Twig