TPCC安装和压测数据库数据表创建生成

下载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的环境变量

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

编译

[root@cnbugs1 src]# make

修改mysql数据库为独立表空间模式

修改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)

创建数据库，导入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)

创建索引

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

单进程加载数据

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

-w：加两个仓库，当前测试环境，如果是生产机，最少1000个

完成之后进入数据库数据文件中查看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

创建测试数据库并导入表

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)

执行脚本文件

[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

查看进程

查看数据文件大小

[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

创建索引

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

Mr丶点心永久会员

打赏 收藏 海报 链接