下載TPCC-mysql

[root@cnbugs1 ~]# git clone https://github.com/Percona-Lab/tpcc-mysql.git

配置TPCC-mysql

[root@cnbugs1 ~]# mv tpcc-mysql/ /usr/local/

[root@cnbugs1 ~]# cd /usr/local/tpcc-mysql/

[root@cnbugs1 tpcc-mysql]# ls

add_fkey_idx.sql create_table.sql drop_cons.sql load.sh schema2 src

count.sql Dockerfile load_multi_schema.sh README.md scripts

[root@cnbugs1 tpcc-mysql]# cd src/

指定mysql的環(huán)境變量

[root@cnbugs1 src]# export PATH=$PATH:/usr/local/mysql/bin

編譯

[root@cnbugs1 src]# make

修改mysql數(shù)據(jù)庫為獨立表空間模式

修改my.cnf配置文件增加

innodb_file_per_table=1

MariaDB [tpcc_test]> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

創(chuàng)建數(shù)據(jù)庫，導入tpcc的建表語句

MariaDB [(none)]> create database tpcc_test;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use tpcc_test;

Database changed

MariaDB [tpcc_test]> source /usr/local/tpcc-mysql/create_table.sql;

MariaDB [tpcc_test]> show tables;

+---------------------+

| Tables_in_tpcc_test |

+---------------------+

| customer |

| district |

| history |

| item |

| new_orders |

| order_line |

| orders |

| stock |

| warehouse |

+---------------------+

9 rows in set (0.00 sec)

創(chuàng)建索引

MariaDB [tpcc_test]> source /usr/local/tpcc-mysql/add_fkey_idx.sql;

單進程加載數(shù)據(jù)

[root@cnbugs1 tpcc-mysql]# ./tpcc_load -h localhost -d tpcc_test -u root -p 123456 -w 2

-w：加兩個倉庫，當前測試環(huán)境，如果是生產機，最少1000個

完成之后進入數(shù)據(jù)庫數(shù)據(jù)文件中查看ibd文件大小

并行加載測試

修改load.sh腳本

export LD_LIBRARY_PATH=/var/lib/mysql/

DBNAME=$1

WH=$2

HOST=127.0.0.1

STEP=100

./tpcc_load -h $HOST -d $DBNAME -u root -p "123456" -w $WH -l 1 -m 1 -n $WH >> 1.out &

x=1

while [ $x -le $WH ]

do

echo $x $(( $x + $STEP - 1 ))

./tpcc_load -h $HOST -d $DBNAME -u root -p "123456" -w $WH -l 2 -m $x -n $(( $x + $STEP - 1 )) >> 2_$x.out &

./tpcc_load -h $HOST -d $DBNAME -u root -p "123456" -w $WH -l 3 -m $x -n $(( $x + $STEP - 1 )) >> 3_$x.out &

./tpcc_load -h $HOST -d $DBNAME -u root -p "123456" -w $WH -l 4 -m $x -n $(( $x + $STEP - 1 )) >> 4_$x.out &

x=$(( $x + $STEP ))

done

創(chuàng)建測試數(shù)據(jù)庫并導入表

MariaDB [mysql]> create database tpcc;

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> use tpcc;

Database changed

MariaDB [tpcc]> source /usr/local/tpcc-mysql/create_table.sql;

Query OK, 0 rows affected (0.00 sec)

執(zhí)行腳本文件

[root@cnbugs1 tpcc-mysql]# ./load.sh tpcc 1000

1 100

101 200

201 300

301 400

401 500

501 600

601 700

701 800

801 900

901 1000

查看進程

查看數(shù)據(jù)文件大小

[root@cnbugs1 tpcc]# ls -lh *.ibd

-rw-rw---- 1 mysql mysql 512M Dec 19 15:34 customer.ibd

-rw-rw---- 1 mysql mysql 96K Dec 19 15:33 district.ibd

-rw-rw---- 1 mysql mysql 84M Dec 19 15:34 history.ibd

-rw-rw---- 1 mysql mysql 17M Dec 19 15:33 item.ibd

-rw-rw---- 1 mysql mysql 10M Dec 19 15:34 new_orders.ibd

-rw-rw---- 1 mysql mysql 160M Dec 19 15:34 order_line.ibd

-rw-rw---- 1 mysql mysql 18M Dec 19 15:34 orders.ibd

-rw-rw---- 1 mysql mysql 676M Dec 19 15:34 stock.ibd

-rw-rw---- 1 mysql mysql 96K Dec 19 15:33 warehouse.ibd

[root@cnbugs1 tpcc]# ls -lh *.ibd

-rw-rw---- 1 mysql mysql 584M Dec 19 15:34 customer.ibd

-rw-rw---- 1 mysql mysql 96K Dec 19 15:34 district.ibd

-rw-rw---- 1 mysql mysql 96M Dec 19 15:34 history.ibd

-rw-rw---- 1 mysql mysql 17M Dec 19 15:33 item.ibd

-rw-rw---- 1 mysql mysql 10M Dec 19 15:34 new_orders.ibd

-rw-rw---- 1 mysql mysql 184M Dec 19 15:34 order_line.ibd

-rw-rw---- 1 mysql mysql 20M Dec 19 15:34 orders.ibd

-rw-rw---- 1 mysql mysql 768M Dec 19 15:34 stock.ibd

-rw-rw---- 1 mysql mysql 96K Dec 19 15:34 warehouse.ibd

只是測試，先kill掉進程

[root@cnbugs1 tpcc]# ps -e|grep tpcc|awk '{print $1}'|xargs kill -9

創(chuàng)建索引

MariaDB [tpcc]> source /usr/local/tpcc-mysql/add_fkey_idx.sql;

總結

以上是生活随笔為你收集整理的tpcc mysql下载_TPCC安装和压测数据库数据表创建生成的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。