tpcc测试数据库性能

TPCC的安装 安装包地址 ://github.com/Percona-Lab/tpcc-mysql 安装文档 Build binaries cd src ; make ( you should have mysql_config available in $PATH) Load data create database mysqladmin create tpcc1000 create tables mysql tpcc1000 < create_table.sql create indexes and FK ( this step can be done after loading data) mysql tpcc1000 < add_fkey_idx.sql populate data simple step tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "" -w 1000 |hostname:port| |dbname| |user| |password| |WAREHOUSES| ref. tpcc_load --help for all options load data in parallel check load.sh script Start benchmark ./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800 |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME| ref. tpcc_start --help for all options Output With the defined interval (-i option), the tool will produce the following output:   10, trx: 12920, 95%: 9.483, 99%: 18.738, max_rt: 213.169, 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842   20, trx: 12666, 95%: 7.074, 99%: 15.578, max_rt: 53.733, 12668|50.420, 1267|35.846, 1266|58.292, 1267|37.421   30, trx: 13269, 95%: 6.806, 99%: 13.126, max_rt: 41.425, 13267|27.968, 1327|32.242, 1327|40.529, 1327|29.580   40, trx: 12721, 95%: 7.265, 99%: 15.223, max_rt: 60.368, 12721|42.837, 1271|34.567, 1272|64.284, 1272|22.947   50, trx: 12573, 95%: 7.185, 99%: 14.624, max_rt: 48.607, 12573|45.345, 1258|41.104, 1258|54.022, 1257|26.626 Where: 10 - the seconds from the start of the benchmark trx: 12920 - New Order transactions executed during the gived interval (in this case, for the previous 10 sec). Basically this is the throughput per interval. The more the better 95%: 9.483: - The 95% Response time of New Order transactions per given interval. In this case it is 9.483 sec 99%: 18.738: - The 99% Response time of New Order transactions per given interval. In this case it is 18.738 sec max_rt: 213.169: - The Max Response time of New Order transactions per given interval. In this case it is 213.169 sec the rest: 12919|98.778, 1292|101.096, 1293|443.955, 1293|670.842 is throughput and max response time for the other kind of transactions and can be ignored ###安装 1。解压 [root@msyqlzhu ]# unzip tpcc-mysql-master.zip [root@msyqlzhu ]# cd tpcc-mysql-master [root@msyqlzhu tpcc-mysql-master]# ls -rlt total 44 drwxr-xr-x. 2 root root 4096 Jan 20  2017 src drwxr-xr-x. 5 root root 4096 Jan 20  2017 scripts drwxr-xr-x. 2 root root 4096 Jan 20  2017 schema2 -rw-r--r--. 1 root root 2302 Jan 20  2017 README.md -rw-r--r--. 1 root root  573 Jan 20  2017 load.sh -rw-r--r--. 1 root root 1079 Jan 20  2017 load_multi_schema.sh -rw-r--r--. 1 root root  763 Jan 20  2017 drop_cons.sql -rw-r--r--. 1 root root  194 Jan 20  2017 Dockerfile -rw-r--r--. 1 root root 3105 Jan 20  2017 create_table.sql -rw-r--r--. 1 root root  317 Jan 20  2017 count.sql -rw-r--r--. 1 root root 1621 Jan 20  2017 add_fkey_idx.sql 2.make编译 [root@msyqlzhu src]# make cc -w -O3 -g -I. `mysql_config --include`  -c load.c cc -w -O3 -g -I. `mysql_config --include`  -c support.c cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load cc -w -O3 -g -I. `mysql_config --include`  -c main.c cc -w -O3 -g -I. `mysql_config --include`  -c spt_proc.c cc -w -O3 -g -I. `mysql_config --include`  -c driver.c cc -w -O3 -g -I. `mysql_config --include`  -c sequence.c cc -w -O3 -g -I. `mysql_config --include`  -c rthist.c cc -w -O3 -g -I. `mysql_config --include`  -c sb_percentile.c cc -w -O3 -g -I. `mysql_config --include`  -c neword.c cc -w -O3 -g -I. `mysql_config --include`  -c payment.c cc -w -O3 -g -I. `mysql_config --include`  -c ordstat.c cc -w -O3 -g -I. `mysql_config --include`  -c delivery.c cc -w -O3 -g -I. `mysql_config --include`  -c slev.c cc main.o spt_proc.o driver.o support.o sequence.o rthist.o sb_percentile.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start 3.创建测试库 [root@msyqlzhu src]# mysqladmin -uroot -pORACLE create database tpcc1000 4.导入表结构 [root@msyqlzhu src]# cd .. [root@msyqlzhu tpcc-mysql-master]# pwd /root/tpcc-mysql-master [root@msyqlzhu tpcc-mysql-master]# mysql -uroot -pORACLE  tpcc1000 < create_table.sql mysql: [Warning] Using a password on the command line interface can be insecure. 5.创建外键 [root@msyqlzhu tpcc-mysql-master]# mysql -uroot -pORACLE  tpcc1000 < add_fkey_idx.sql 6.加载数据 -w 表示加载几个WAREHOUSES，每个WAREHOUSES里面的数据很大 simple step tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "" -w 1000 |hostname:port| |dbname| |user| |password| |WAREHOUSES| ref. tpcc_load --help for all options load data in parallel check load.sh script [root@msyqlzhu tpcc-mysql-master]#  ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "ORACLE" -w 2 ************************************ *** TPCC-mysql Data Loader        *** ************************************* option h with value '127.0.0.1' option d with value 'tpcc1000' option u with value 'root' option p with value 'ORACLE' option w with value '2' <Parameters>      [server]: 127.0.0.1      [port]: 3306      [DBname]: tpcc1000        [user]: root        [pass]: ORACLE   [warehouse]: 2 TPCC Data Load Started... Loading Item  .................................................. 5000 .................................................. 10000 省略部分 Loading Orders for D=10, W= 2 .......... 1000 .......... 2000 .......... 3000 Orders Done. ...DATA LOADING COMPLETED SUCCESSFULLY. 7.开始运行 ./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800 |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME| ref. tpcc_start --help for all options -c 表示用多少个线程 -l 表示运行300秒 -i 打印报告的时间间隔 [root@msyqlzhu tpcc-mysql-master]#  ./tpcc_start -h127.0.0.1 -d tpcc1000 -u root -p "ORACLE" -w 2  -c 2 -l 300 -i 10  160, trx: 451, 95%: 102.348, 99%: 200.716, max_rt: 383.960, 453|196.867, 45|2.165, 45|338.903, 45|50.930  170, trx: 573, 95%: 51.460, 99%: 100.979, max_rt: 288.610, 572|280.383, 57|2.292, 57|210.452, 58|59.415  180, trx: 474, 95%: 123.109, 99%: 166.269, max_rt: 209.113, 475|266.656, 48|3.217, 48|309.402, 47|60.546  190, trx: 538, 95%: 65.599, 99%: 134.797, max_rt: 195.894, 538|204.119, 54|24.384, 53|367.505, 53|63.732  200, trx: 448, 95%: 158.115, 99%: 193.983, max_rt: 280.373, 446|284.824, 45|9.769, 46|297.318, 45|67.677  210, trx: 565, 95%: 50.802, 99%: 113.823, max_rt: 198.511, 567|259.840, 56|8.313, 56|332.234, 57|51.848  220, trx: 462, 95%: 132.994, 99%: 162.968, max_rt: 216.271, 461|162.846, 46|28.508, 46|399.657, 46|60.813  230, trx: 342, 95%: 165.129, 99%: 259.881, max_rt: 469.534, 342|340.310, 35|5.365, 34|751.851, 34|38.368  240, trx: 556, 95%: 63.493, 99%: 117.212, max_rt: 285.464, 554|276.425, 55|2.333, 56|314.255, 56|55.452  250, trx: 455, 95%: 90.690, 99%: 142.089, max_rt: 321.924, 455|484.051, 46|1.941, 45|269.097, 46|48.171  260, trx: 428, 95%: 71.527, 99%: 117.951, max_rt: 210.271, 432|418.011, 42|5.737, 44|306.409, 43|77.536  270, trx: 474, 95%: 95.139, 99%: 184.634, max_rt: 289.261, 469|142.192, 47|5.389, 46|240.919, 46|54.208  280, trx: 499, 95%: 76.464, 99%: 125.491, max_rt: 209.247, 501|256.123, 51|4.486, 50|388.811, 51|64.832  290, trx: 502, 95%: 75.306, 99%: 161.656, max_rt: 224.623, 505|219.696, 50|2.502, 51|273.799, 50|52.631  300, trx: 442, 95%: 105.080, 99%: 185.299, max_rt: 367.650, 440|251.798, 44|3.571, 44|404.255, 44|47.842    <Raw Results>   [0] sc:121 lt:14423  rt:0  fl:0 avg_rt: 22.2 (5)   [1] sc:10457 lt:4089  rt:0  fl:0 avg_rt: 9.4 (5)   [2] sc:1440 lt:14  rt:0  fl:0 avg_rt: 1.2 (5)   [3] sc:1130 lt:325  rt:0  fl:0 avg_rt: 76.0 (80)   [4] sc:60 lt:1395  rt:0  fl:0 avg_rt: 31.9 (20)  in 300 sec. <Raw Results2(sum ver.)>   [0] sc:121  lt:14423  rt:0  fl:0    [1] sc:10457  lt:4089  rt:0  fl:0    [2] sc:1440  lt:14  rt:0  fl:0    [3] sc:1130  lt:325  rt:0  fl:0    [4] sc:60  lt:1395  rt:0  fl:0  <Constraint Check> (all must be [OK])  [transaction percentage]         Payment: 43.48% (>=43.0%) [OK]    Order-Status: 4.35% (>= 4.0%) [OK]        Delivery: 4.35% (>= 4.0%) [OK]     Stock-Level: 4.35% (>= 4.0%) [OK]  [response time (at least 90% passed)]       New-Order: 0.83%  [NG] *         Payment: 71.89%  [NG] *    Order-Status: 99.04%  [OK]        Delivery: 77.66%  [NG] *     Stock-Level: 4.12%  [NG] * <TpmC>                  2908.800 TpmC   -----这里表示每分钟能做的业务的总量这里每分钟能做2908个事务。     trx 442 表示TPS每10秒的事务数，这个约大约好 95%: 105.080, 95%的订单响应时间是105秒 99%: 185.299    -每个给定间隔的新订单事务的99%响应时间 max_rt: 367.650  -每个给定间隔的新订单事务的最大响应时间