tpcc-mysqlのインストール備忘録。
個人環境にクエリをかけながら検証をしたかったので元隣の隣の人に相談した所、tpcc-mysqlというベンチマークツールを教えてもらったのでインストールしてみた。
mysqlをyumでいれてないためところどころ詰まったので一緒にメモっておく
GitHub - Percona-Lab/tpcc-mysql
$ git clone https://github.com/Percona-Lab/tpcc-mysql.git $ cd src $ vim Makefile ### mysql_configのパスをフルパスに書き直した。 LIBS= `/usr/local/mysql5718/bin/mysql_config --libs_r` -lrt INC= -I. `/usr/local/mysql5718/bin/mysql_config --include` ### $ cd src ; make
## databaseの作成。例に習って`tpcc1000`とする
MYSQL_PWD="mysql_pass" /usr/local/mysql5718/bin/mysqladmin --defaults-file=<my.cnfのパス> -uroot create tpcc1000
## CREATE TABLE文の流し込み。
$ mysql -u root -p tpcc1000 < /data/tpcc-mysql.git/create_table.sql
## データ挿入！とおもったらコケた。。。
$ ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "mysql_pass" -w 1000
./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
## シンボリックリンク貼り直し
$ ln -s /usr/local/mysql5718/lib/libmysqlclient.so /usr/lib64/libmysqlclient.so.20
$ ./tpcc_load -hlocalhost -d tpcc1000 -u root -p "mysql_pass" -w 1000
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value 'localhost'
option d with value 'tpcc1000'
option u with value 'root'
option p with value 'mysql_pass'
option w with value '1000'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc1000
[user]: root
[pass]: mysql_pass
[warehouse]: 1000
2002, HY000, Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
## `/tmp/mysql.sock`を見に行ってしまうらしい。シンボリックリンクで凌ぐ
$ ln -s <my.cnfに設定しているパス>/mysql.sock /tmp/mysql.sock
## 通った。
$ ./tpcc_load -hlocalhost -d tpcc1000 -u root -p "mysql_pass" -w 1000
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value 'localhost'
option d with value 'tpcc1000'
option u with value 'root'
option p with value 'mysql_pass'
option w with value '1000'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc1000
[user]: root
[pass]: mysql_pass
[warehouse]: 1000
TPCC Data Load Started...
Loading Item
.................................................. 5000
.................................................. 10000
.................................................. 15000
.................................................. 20000
.................................................. 25000
.................................................. 30000
.................................................. 35000
.................................................. 40000
...
実行。
./tpcc_start -h 127.0.0.1 -P 3306 -d tpcc1000 -u root -p mysql_pass -w 1000 -c 32 -r 10 -l 1000
実行中にInnoTopを見たところ下記みたいな感じ。ちゃんと32接続来てる。
When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.00 34 3.14k 0 59/15/20/ 1 0.00% 100.00% 129.85k 942.15k Total 0.00 151 10.14 0 59/15/20/ 1 0.00% 77.27% 424.62 3.14k Cmd ID State User Host DB Time Query When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.00 34 3.14k 0 59/15/20/ 1 0.00% 100.00% 129.85k 942.15k Total 0.00 151 10.14 0 59/15/20/ 1 0.00% 77.27% 424.62 3.14k Cmd ID State User Host DB Time Query Execute 270 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 271 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 272 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 273 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 276 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 277 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 278 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 279 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 280 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 281 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 284 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 285 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 286 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y1 Execute 289 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 291 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 293 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 294 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 295 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 296 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 297 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 298 Creating sort inde root 127.0.0.1 tpcc1000 00:01 SELECT c_id FROM custo Execute 300 updating root 127.0.0.1 tpcc1000 00:01 UPDATE warehouse SET w_y Execute 269 updating root 127.0.0.1 tpcc1000 00:00 UPDATE warehouse SET w_y Execute 274 Sending to client root 127.0.0.1 tpcc1000 00:00 SELECT s_quantity, s_data Execute 275 updating root 127.0.0.1 tpcc1000 00:00 UPDATE warehouse SET w_y Execute 282 updating root 127.0.0.1 tpcc1000 00:00 UPDATE warehouse SET w_y Execute 283 Creating sort inde root 127.0.0.1 tpcc1000 00:00 SELECT c_id FROM custo Execute 287 updating root 127.0.0.1 tpcc1000 00:00 UPDATE district SET d_ytd = Execute 288 updating root 127.0.0.1 tpcc1000 00:00 UPDATE order_line SET ol_d Execute 290 closing tables root 127.0.0.1 tpcc1000 00:00 UPDATE stock SET s_quant Execute 292 closing tables root 127.0.0.1 tpcc1000 00:00 SELECT count(*) FROM stoc Execute 299 updating root 127.0.0.1 tpcc1000 00:00 UPDATE warehouse SET w_y
ただ、途中でサーバーの容量がいっぱいいっぱいになってしまったので最後まで実行完了できなかった。
