MySQL测试工具之-TPCC（业界通用的压测工具）
生活随笔
收集整理的這篇文章主要介紹了
MySQL测试工具之-TPCC（业界通用的压测工具）
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
TPCC業(yè)界通用的壓測(cè)工具，主要是壓數(shù)據(jù)庫性能。
首先安裝tpcc
?
官網(wǎng)地址：https://github.com/Percona-Lab/tpcc-mysql [root@test3 src]# unzip tpcc-mysql-master.zip [root@test3 src]# mv tpcc-mysql-master /usr/local/src/ [root@test3 local]# cd tpcc-mysql-master/ [root@test3 tpcc-mysql-master]# ls add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src [root@test3 tpcc-mysql-master]# cd src/ [root@test3 src]# make #沒有make install [root@test3 src]# cd .. [root@test3 tpcc-mysql-master]# ls add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src tpcc_load tpcc_start#編譯之后生成了tpcc_load和tpcc_start命令?
在進(jìn)行測(cè)試之前，需要導(dǎo)入tpcc的數(shù)據(jù)。
創(chuàng)建數(shù)據(jù)庫，然后導(dǎo)入tpcc的建表語句：
?
mysql> create database tpcc_test; Query OK, 1 row affected (0.06 sec) mysql> use tpcc_test; Database changed mysql> source /usr/local/tpcc-mysql-master/create_table.sql; Query OK, 0 rows affected (0.00 sec) ...... mysql> show tables; +---------------------+ | Tables_in_tpcc_test | +---------------------+ | customer | | district | | history | | item | | new_orders | | order_line | | orders | | stock | | warehouse | +---------------------+ 9 rows in set (0.00 sec)?
tpcc創(chuàng)建了九張表：
?
tpcc-mysql的業(yè)務(wù)邏輯及其相關(guān)的幾個(gè)表作用如下： New-Order：新訂單，一次完整的訂單事務(wù)，幾乎涉及到全部表 Payment：支付，主要對(duì)應(yīng) orders、history 表 Order-Status：訂單狀態(tài)，主要對(duì)應(yīng) orders、order_line 表 Delivery：發(fā)貨，主要對(duì)應(yīng) order_line 表 Stock-Level：庫存，主要對(duì)應(yīng) stock 表其他說明： 客戶：主要對(duì)應(yīng) customer 表 地區(qū)：主要對(duì)應(yīng) district 表 商品：主要對(duì)應(yīng) item 表 倉庫：主要對(duì)應(yīng) warehouse 表?
首先加載數(shù)據(jù)：
?
./tpcc_load -h 10.0.102.214 -P 3306 -d tpcc_test -u root -p 123456 -w 10 #這個(gè)過程有點(diǎn)慢 ，1個(gè)warehouse對(duì)應(yīng)10個(gè)地區(qū)，1地區(qū)對(duì)應(yīng)3000的用戶 mysql> select count(*) from warehouse; +----------+ | count(*) | +----------+ |?????? 10 | +----------+ 1 row in set (0.00 sec)mysql> select count(*) from district; +----------+ | count(*) | +----------+ |????? 100 | +----------+ 1 row in set (0.00 sec)mysql> select count(*) from customer; +----------+ | count(*) | +----------+ |?? 300000 | +----------+ 1 row in set (0.11 sec)?
數(shù)據(jù)插入成功之后，然后添加索引【注意一定要先插入數(shù)據(jù)再添加索引，若先創(chuàng)建索引，則數(shù)據(jù)插入的會(huì)更慢】
?
mysql> source /usr/local/tpcc-mysql-master/add_fkey_idx.sql; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (2.63 sec) Records: 0 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.98 sec) Records: 0 Duplicates: 0 Warnings: 0 ...... Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)?
各個(gè)表的結(jié)構(gòu)關(guān)系如圖（使用MySQL workbench自動(dòng)生成的er圖）
數(shù)據(jù)插入，索引創(chuàng)建之后，開始測(cè)試數(shù)據(jù)。
?
[root@test3 tpcc-mysql-master]# ./tpcc_start --help *************************************** *** ###easy### TPC-C Load Generator *** *************************************** ./tpcc_start: invalid option -- '-' 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 指定倉庫數(shù)量 -c 指定并發(fā)連接數(shù) -r 指定開始測(cè)試前進(jìn)行warmup的時(shí)間，進(jìn)行預(yù)熱后，測(cè)試效果更好 -l 指定測(cè)試持續(xù)時(shí)間 -i 指定生成報(bào)告間隔時(shí)長(zhǎng) -f 指定生成的報(bào)告文件名?
真實(shí)測(cè)試場(chǎng)景中，建議預(yù)熱時(shí)間不小于5分鐘，持續(xù)壓測(cè)時(shí)長(zhǎng)不小于30分鐘，否則測(cè)試數(shù)據(jù)可能不具參考意義。
?
[root@test3 tpcc-mysql-master]# ./tpcc_start -h 10.0.102.214 -P 3306 -d tpcc_test -u root -p 123456 -w 10 -c 128 -r 120 -l 200 *************************************** *** ###easy### TPC-C Load Generator *** *************************************** option h with value '10.0.102.214' option P with value '3306' option d with value 'tpcc_test' option u with value 'root' option p with value '123456' option w with value '10' option c with value '128' option r with value '120' option l with value '200' <Parameters>[server]: 10.0.102.214[port]: 3306[DBname]: tpcc_test[user]: root[pass]: 123456[warehouse]: 10[connection]: 128[rampup]: 120 (sec.)[measure]: 200 (sec.)RAMP-UP TIME.(120 sec.) #預(yù)熱結(jié)束MEASURING START.10, trx: 2175, 95%: 201.680, 99%: 381.106, max_rt: 1619.119, 2156|3724.232, 217|229.157, 216|3278.305, 221|1335.47120, trx: 2299, 95%: 157.736, 99%: 235.577, max_rt: 1499.332, 2303|3527.383, 229|44.633, 232|2130.182, 224|343.88630, trx: 2484, 95%: 166.968, 99%: 221.357, max_rt: 398.790, 2505|1928.303, 250|66.903, 248|586.503, 246|374.86040, trx: 2429, 95%: 172.350, 99%: 256.941, max_rt: 392.431, 2416|2335.130, 242|75.140, 239|779.635, 246|403.24850, trx: 2510, 95%: 184.524, 99%: 251.689, max_rt: 336.677, 2498|1205.047, 251|35.061, 255|678.351, 252|413.73160, trx: 2170, 95%: 184.358, 99%: 828.455, max_rt: 1187.092, 2175|2113.244, 217|62.069, 218|1613.712, 219|337.85670, trx: 2539, 95%: 175.632, 99%: 233.821, max_rt: 372.486, 2539|1720.610, 253|69.966, 252|624.549, 253|397.74380, trx: 2388, 95%: 177.588, 99%: 275.997, max_rt: 717.325, 2393|1750.563, 240|50.437, 240|1046.998, 239|332.51690, trx: 2391, 95%: 190.074, 99%: 244.413, max_rt: 404.727, 2394|1602.446, 238|68.237, 238|812.026, 241|393.183100, trx: 2458, 95%: 190.017, 99%: 291.625, max_rt: 483.963, 2450|1657.739, 247|59.142, 244|771.332, 244|404.857110, trx: 2507, 95%: 166.070, 99%: 222.753, max_rt: 321.543, 2506|1610.591, 251|80.395, 252|650.804, 253|394.323120, trx: 2130, 95%: 187.812, 99%: 552.392, max_rt: 817.886, 2145|2443.558, 213|37.962, 213|1586.296, 206|406.044130, trx: 2488, 95%: 170.299, 99%: 212.399, max_rt: 290.578, 2471|1787.442, 249|58.585, 254|601.749, 256|379.250140, trx: 2575, 95%: 178.600, 99%: 236.850, max_rt: 383.657, 2587|1550.100, 257|75.059, 253|633.108, 250|471.181150, trx: 2441, 95%: 185.133, 99%: 308.322, max_rt: 479.655, 2447|1691.942, 244|38.844, 246|802.569, 248|449.930160, trx: 2521, 95%: 172.660, 99%: 216.249, max_rt: 302.257, 2519|1521.757, 253|62.614, 252|675.931, 249|435.944170, trx: 2512, 95%: 164.290, 99%: 225.504, max_rt: 1306.804, 2505|1745.858, 251|57.538, 248|610.941, 258|530.579180, trx: 2181, 95%: 180.914, 99%: 251.463, max_rt: 1415.735, 2183|2489.273, 217|30.965, 221|1915.949, 214|428.293190, trx: 2398, 95%: 197.262, 99%: 399.086, max_rt: 556.640, 2409|1319.926, 241|57.555, 238|977.416, 240|491.435200, trx: 2470, 95%: 177.428, 99%: 217.612, max_rt: 341.156, 2448|1357.371, 245|76.254, 248|655.950, 246|457.847STOPPING THREADS................................................................................................................................<Raw Results>[0] sc:0 lt:48066 rt:0 fl:0 avg_rt: 148.6 (5)[1] sc:1 lt:48049 rt:0 fl:0 avg_rt: 604.9 (5)[2] sc:3986 lt:819 rt:0 fl:0 avg_rt: 6.6 (5)[3] sc:0 lt:4807 rt:0 fl:0 avg_rt: 683.6 (80)[4] sc:0 lt:4805 rt:0 fl:0 avg_rt: 272.2 (20)in 200 sec.<Raw Results2(sum ver.)>[0] sc:0 lt:48066 rt:0 fl:0 [1] sc:1 lt:48054 rt:0 fl:0 [2] sc:3986 lt:819 rt:0 fl:0 [3] sc:0 lt:4807 rt:0 fl:0 [4] sc:0 lt:4805 rt:0 fl:0 <Constraint Check> (all must be [OK])[transaction percentage]Payment: 43.47% (>=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.00% [NG] *Payment: 0.00% [NG] *Order-Status: 82.96% [NG] *Delivery: 0.00% [NG] *Stock-Level: 0.00% [NG] *<TpmC>14419.800 TpmC [root@test3 tpcc-mysql-master]#?
轉(zhuǎn)載至https://www.cnblogs.com/wxzhe/p/10027474.html
總結(jié)
以上是生活随笔為你收集整理的MySQL测试工具之-TPCC（业界通用的压测工具）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 知名IT网站
- 下一篇： 广西田园及20家子公司引入契约锁电子签章