tpcc mysql下载_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;