mysql系列–压测工具
一、tpcc
准备
1、wget http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz
2、gunzip tpcc-mysql-src.tgz
3、tar xf tpcc-mysql-src.tar
4、cd /opt/tpcc-mysql/src
5、make
生成 tpcc 二进制命令行工具 tpcc_load 、 tpcc_start
mysql -uroot -p -S /tmp/mysql.sock -e ‘create database lei’;
mysql -uroot -p -S /tmp/mysql.sock lei <./create_table.sql;
mysql -uroot -p -S /tmp/mysql.sock lei < add_fkey_idx.sql
mysql -uroot -p -S /tmp/mysql.sock -e ‘show tables from lei’;
±--------------+
| Tables_in_lei |
±--------------+
| customer | 客户表
| district |地区表
| history | Payment:支付,主要对应 orders、history 表
| item |商品
| new_orders | New-Order:新订单,一次完整的订单事务,几乎涉及到全部表
| order_line |Order-Status:订单状态;Delivery:发货,主要对应 order_line 表
| orders |Order-Status:订单状态;主要对应 orders、order_line 表
| stock |Stock-Level:库存,主要对应 stock 表
| warehouse |仓库
±--------------+
压测数据写入:
./tpcc_load 127.0.0.1:3306 lei root 123456 10
./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
解决方案:
ln -s /usr/local/mysql-5.7.28/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20
echo “/usr/local/lib” >>/etc/ld.so.conf
/sbin/ldconfig -v —更新下配置
usage: tpcc_load [server] [DB] [user] [pass] [warehouse]
OR
tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]* [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
./tpcc_load 127.0.0.1:3306 lei root 123456 10
[server]: 127.0.0.1 [port]: 3306 [DBname]: lei [user]: root [pass]: 123456 [warehouse]: 10 TPCC Data Load Started... Loading Item…DATA LOADING COMPLETED SUCCESSFULLY.
mysql> SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = ‘lei’ ORDER BY table_rows DESC limit 10;
±-----------±-----------+
| table_name | table_rows |
±-----------±-----------+
| order_line | 2967045 |
| stock | 954213 |
| history | 307762 |
| orders | 298836 |
| customer | 288047 |
| item | 99297 |
| new_orders | 90171 |
| district | 100 |
| warehouse | 10 |
±-----------±-----------+
9 rows in set (0.01 sec)
压测:
./tpcc_start --help
Usage: tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file
选项说明:
-w 指定仓库数量
-c 指定并发连接数
-r 指定开始测试前进行warmup的时间,进行预热后,测试效果更好
-l 指定测试持续时间
-i 指定生成报告间隔时长
-f 指定生成的报告文件名
./tpcc_start -h127.0.0.1 -P3306 -d lei -u root -p 123456 -w 10 -c 10 -r 120 -l 300 -i 5
[server]: 127.0.0.1 [port]: 3306 [DBname]: lei [user]: root [pass]: 123456 [warehouse]