Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about the tpcc-mysql-specific MySQL benchmarking tutorial. Have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the tpcc-mysql benchmarking tutorial dedicated to MySQL.

Installation

Download and install epel:

Wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Rpm-ivh epel-release-6-8.noarch.rpm

Install the bzr client:

Yum install-y bzr

Download the tpcc-mysql-master.zip software:

Https://github.com/Percona-Lab/tpcc-mysql

Unzip tpcc-mysql-master.zip

Cd tpcc-mysql-master/src/

Make

If the make compilation does not report an error, the tpcc binary command line tools tpcc_load and tpcc_start will be generated under / tmp/tpcc-mysql

Introduction to the structure of Tpcc tool

Create_table.sql: create the required database tables with a total of 9 tables

Add_fkey_idx.sql: creating indexes and foreign keys

Drop_cons.sql: deleting constraint

The relationship between these 9 tables:

Customer district history item new_orders order_line orders stock warehouse

The business logic of tpcc-mysql and the functions of several related tables are as follows:

New-Order: new order, a complete order transaction, involving almost all tables

Payment: payment, which mainly corresponds to orders and history tables

Order-Status: order status, which mainly corresponds to orders and order_line tables

Delivery: delivery, mainly corresponding to order_line table

Stock-Level: inventory, mainly corresponding to stock table

Other related tables:

Customer: mainly corresponding to customer table

Region: mainly corresponding to district table

Goods: mainly corresponding to item table

Warehouse: mainly corresponding to warehouse table

Test procedure

(1) Building database and table

Mysqladmin-uroot-p147258 create tpccdb

Mysql-uroot-p147258 tpccdb

< /tmp/tpcc-mysql-master/create_table.sql mysql -uroot -p147258 tpccdb < /tmp/tpcc-mysql-master/add_fkey_idx.sql --这一步也可以在加载数据之后做； （2）填充数据 如：向tpccdb库压测100个仓库数量： ./tpcc_load -hlocalhost -dtpccdb -uroot -p147258 -w100 【NOTE】选项 warehouse为测试库下的仓库数量，真实测试场景中，仓库数一般不建议少于100个，视云服务器硬件配置而定，如果是配备了SSD或者PCie SSD这种高IOPS设备的话，建议最少不低于1000个。 补充tpcc_load的参数： tpcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh -h 指定主机名 -P 指定mysql端口 -d 指定库名 -u 指定用户 -p 指定密码 -w 指定仓库数 -l 指定* [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS -m 指定最小warehouse -n 指定最大warehouse （3）开始测试 如：指定仓库1000，并发数32，预热120s，测试持续1h； tpcc_start -hlocalhost -d tpccdb -u root -p "147258" -w 1000 -c 32 -r 120 -l 3600 -f tpcc_mysql_20140921.log >

> tpcc_caseX_20140921.log 2 > & 1

[NOTE] in real test scenarios, it is recommended that the preheating time is not less than 5 minutes and the continuous stress test time is not less than 30 minutes, otherwise the test data may not be of reference significance.

The use of supplementary tpcc_start:

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 specify the number of warehouses

-c specifies the number of concurrent connections. The default is 1.

-r specifies the time (in s) for warmup before the test starts

-l specifies the test duration in s

-I specify the interval between report generation

-f specifies the file name of the generated report

Interpretation of TPCC results

Output comments:

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

-10-the seconds from the start of the benchmark

(statistics every 10s)

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

(the number of new order transactions within 10 seconds. The higher the value, the better the performance.)

-- 95%: 9.483:-The 95% Response time of New Order transactions per given interval. In this case it is 9.483 sec

(95% response time for new order transactions in each given interval)

-- 99%: 18.738:-The 99% Response time of New Order transactions per given interval. In this case it is 18.738 sec

(99% response time for new order transactions in each given range)

-max_rt: 213.169:-The Max Response time of New Order transactions per given interval. In this case it is 213.169 sec

(maximum response time for new orders)

-- 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

(number of other things and maximum response time)

Start stress testing of 10 designated warehouses, 32 concurrent, preheat 10s for 120s, generate reports every 10s, and generate log files tpccdb20170315.log: (the test here is our own virtual machine, and the hardware configuration is not very good, so the test parameters are not too large, just to have a result for us to learn and analyze. Not meaningful) [root@Darren1 tpcc-mysql-master] #. / tpcc_start-S / tmp/mysql.sock-hlocalhost-dtpccdb-uroot-p147258-w10-c32-R10-1120-ftpccdb20170315.log** # easy### TPC-C Load Generator * * * option S (socket) with value'/ tmp/mysql.sock'option h with value 'localhost'option d with value' tpccdb'option u with value 'root'option p with value' 147258'option w with value '10'option c with value' 32'option r with value '10'option l with value' 120'option f with value 'tpccdb20170315.log' [server]: localhost [port]: 3306 [DBname]: tpccdb [user]: root [pass]: 147258 [warehouse]: 10 [connection]: 32 [rampup]: 10 (sec.)-preheating duration [measure]: 120 (sec.)-Test duration RAMP-UP TIME. (10 sec.)-preheating ends MEASURING START. -- Test start 10, trx: 13,95%: 15137.766, 99%: 18323.255, max_rt: 18325.580, 14 | 13748.201 | 1574.186, 0 | 0.000, 0 | 0.000 20, trx: 13,95%: 21680.204, 99%: 22399.251, max_rt: 22399.842, 10 | 23612.802, 0 | 0.000,0 | 0.00030, trx: 14,95%: 21550.797,99%: 24863.416,8; 19415.285,0 | 0.00030 | | 34375.755, 0 | 0.000 40, trx: 6,95%: 30071.479, 99%: 30071.479, max_rt: 30070.638, 13 | 21700.073, 2 | 1556.698, 0 | 0.000, 0 | 0.000 50, trx: 16,95%: 16555.115, 99%: 18088.917, max_rt: 18089.462,15 | 10727.610,1 | 985.788,2 | 44921.254,0 | 0.00060, trx: 6,95%: 20426.460,99%: 20426.460, max_rt: 20427.254,7 | 16342.486,0 | 0.000 | | 0 | 0.000, 0 | 0.000 70, trx: 7,95%: 20840.263, 99%: 20840.263, max_rt: 20842.583, 6 | 17685.274 | 0.000, 2 | 71371.553, 1 | 73601.007 80, trx: 12,95%: 19331.825, 99%: 21961.066, max_rt: 21963.506,12 | 16032.869,2 | 6187.316,0 | 0.000,0 | 0.00090, trx: 10,95%: 14917.352,99%: 14917.352, max_rt: 14915.321, 8 | 11906.947,1 | 3237.487 | 1 | 56860.851, 1 | 73551.862 100, trx: 8,95%: 19061.751, 99%: 19061.751, max_rt: 19061.873, 9 | 14089.579, 1 | 433.691, 2 | 54415.280, 3 | 106539.602 110, trx: 16,76.521,99%: 16270.171, max_rt: 16268.213,3 | 11618.824,1 | 92.480,1 | 8790.656, 0 | 0.000120, trx: 10,95%: 13437.587, 99%: 13437.587, max_rt: 13437.183,22 | 31044.348,1 | 5419.699 2 | 53726.988, 0 | 0.000STOPPING THREADS.. -- end stress test-- first statistical result [0] sc:0 lt:131 rt:0 fl:0 avg_rt: 13862.8 (5)-- new-order, number of new order business successes (success), number of delays (late), number of retries (retry), number of failures (failure) Average number of retries [1] sc:0 lt:127 rt:0 fl:0 avg_rt: 8024.2 (5)-payment Payment Business Statistics [2] sc:0 lt:11 rt:0 fl:0 avg_rt: 3254.7 (5)-order-status Order status business statistics [3] sc:0 lt:11 rt:0 fl:0 avg_rt: 56329.9 (80)-- delivery shipping business statistics [4] sc:0 lt:5 rt:0 fl:0 avg_rt: 208602.8 (20)-- stock-level inventory business statistics in 120 sec. -- second test results [0] sc:0 lt:131 rt:0 fl:0 [1] sc:0 lt:127 rt:0 fl:0 [2] sc:0 lt:11 rt:0 fl:0 [3] sc:0 lt:11 rt:0 fl:0 [4] sc:0 lt:5 rt:0 fl:0 (all must be [OK])-- all must be OK [transaction percentage] Payment: 44.56% (> = 43.0%) [OK]-number of successful payments The result is more than 43%, which is OK, and that is less than 43%, which is NG. Order-Status: 3.86% (> = 4.0%) [NG] *-- order status Delivery: 3.86% (> = 4.0%) [NG] *-- Shipping status Stock-Level: 1.75% (> = 4.0%) [NG] *-inventory status [response time (at least 90% passed)]-response time indicator must exceed 90% pass New-Order: 0.000% [NG] *-- indicates not good Payment: 0.000% [NG] * Order-Status: 0.000% [NG] * Delivery: 0.00% [NG] * Stock-Level: 0.00% [NG] * 65.500 TpmC-- TpmC result value (transactions per minute This value is the number of new order transactions in the result of the first count divided by the total number of minutes spent, for example, in this case: 131comp2x65.5), TpmC/60=tps

FAQ

Error report during installation

[root@Darren1 tpcc-mysql-master] #. / tpcc_load help

. / tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

Solution:

[root@Darren1 tpcc-mysql-master] # ln-s / usr/local/mysql/lib/libmysqlclient.so.20 / usr/lib64/libmysqlclient.so.20

What do you think of the article that tpcc-mysql is dedicated to the MySQL benchmarking tutorial and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.