TPCC-mysql压测示例

请先查看上一篇文章的TPCC-mysql安装和测试数据库和数据表的导入操作

https://www.cnbugs.com/post-3454.html

压测步骤：

1. 确定硬件资源及数据库大小
2. 压测时，收集系统信息及Mysql服务信息
3. 准备压测工具：tpc_start
4. 解读压测结果
5. 进行多轮测试
6. 确定系统能力

1、确定硬件资源及数据库大小

查看CPU资源（执行top后然后按1）

8个

查看内存资源（执行free -h）

16G

确定数据库大小

[root@cnbugs1 ~]# ls -lh /var/lib/mysql/tpcc
total 2.6G
-rw-rw---- 1 mysql mysql 9.2K Dec 19 15:36 customer.frm
-rw-rw---- 1 mysql mysql 928M Dec 19 15:37 customer.ibd
-rw-rw---- 1 mysql mysql   65 Dec 19 15:30 db.opt
-rw-rw---- 1 mysql mysql 8.8K Dec 19 15:36 district.frm
-rw-rw---- 1 mysql mysql 128K Dec 19 15:36 district.ibd
-rw-rw---- 1 mysql mysql 8.7K Dec 19 15:37 history.frm
-rw-rw---- 1 mysql mysql 160M Dec 19 15:37 history.ibd
-rw-rw---- 1 mysql mysql 8.5K Dec 19 15:30 item.frm
-rw-rw---- 1 mysql mysql  17M Dec 19 15:33 item.ibd
-rw-rw---- 1 mysql mysql 8.5K Dec 19 15:37 new_orders.frm
-rw-rw---- 1 mysql mysql  11M Dec 19 15:37 new_orders.ibd
-rw-rw---- 1 mysql mysql 8.8K Dec 19 15:38 order_line.frm
-rw-rw---- 1 mysql mysql 380M Dec 19 15:38 order_line.ibd
-rw-rw---- 1 mysql mysql 8.7K Dec 19 15:37 orders.frm
-rw-rw---- 1 mysql mysql  36M Dec 19 15:37 orders.ibd
-rw-rw---- 1 mysql mysql 9.0K Dec 19 15:39 stock.frm
-rw-rw---- 1 mysql mysql 1.1G Dec 19 15:40 stock.ibd
-rw-rw---- 1 mysql mysql 8.7K Dec 19 15:30 warehouse.frm
-rw-rw---- 1 mysql mysql  96K Dec 19 15:35 warehouse.ibd

2、收集系统信息及Mysql服务信息

mysql的服务信息

MariaDB [(none)]> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
201219 15:53:49 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 730 1_second, 729 sleeps, 72 10_second, 13 background, 13 flush
srv_master_thread log flush and writes: 976
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 303072, signal count 567905
Mutex spin waits 1446169, rounds 4419240, OS waits 94297
RW-shared spins 343805, rounds 5691686, OS waits 90872
RW-excl spins 509679, rounds 6226617, OS waits 105885
Spin rounds per wait: 3.06 mutex, 16.55 RW-shared, 12.22 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
377006 OS file reads, 809375 OS file writes, 59914 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 149 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 9312140436
Log flushed up to   9312140436
Last checkpoint at  9312140436
Max checkpoint age    7782360
Checkpoint age target 7539162
Modified age          0
Checkpoint age        0
0 pending log writes, 0 pending chkp writes
26279 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137756672; in additional pool allocated 0
Total memory allocated by read views 360
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 4658800         (2213368 + 2445432)
    Page hash           139112 (buffer pool 0 only)
    Dictionary cache    702899  (554768 + 148131)
    File system         91312   (82672 + 8640)
    Lock system         333624  (332872 + 752)
    Recovery system     0       (0 + 0)
Dictionary memory allocated 148131
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            0
Database pages          8042
Old database pages      2948
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 458498, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 376133, created 434695, written 758527
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8042, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 86427
Read view up limit trx id 86427
Read view low limit trx id 86427
Read view individually stored trx ids:
-----------------
Main thread process no. 12549, id 140305727158016, state: flushing log
Number of rows inserted 37534774, updated 0, deleted 0, read 17730514
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 86427
Purge done for trx's n:o < 86427 undo n:o < 0
History list length 247
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 38, OS thread handle 0x7f9b94325700, query id 11515772 localhost root
show engine innodb status
---TRANSACTION 86420, not started
MySQL thread id 4, OS thread handle 0x7f9b943b9700, query id 11514973 localhost root
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

ERROR: No query specified

3、压测工具

建议预热时间不少于5分钟，持续压测时间不少于30分钟，否则测试数据不具备实际参考意义

本次为了演示只测试100秒

• -l 时间秒
• -c 并发数
• -w 使用的数据仓库数
• -r 预测时间

执行

[root@cnbugs1 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p 123456 -w 10 -c 1 -r 300 -l 100

等待10秒中

.................................................................................................................................

<Raw Results>
成功的    延迟的   重复操作数  失败操作数 某一操作平均时间
  [0] sc:3 lt:10  rt:74220  fl:37 avg_rt: 17.3 (5)  #新订单
  [1] sc:18 lt:0  rt:64403  fl:33 avg_rt: 7.3 (5)  #支付
  [2] sc:0 lt:0  rt:9995  fl:5 avg_rt: inf (5)   #查询订单
  [3] sc:5 lt:0  rt:0  fl:0 avg_rt: 18.5 (80)    #发货
  [4] sc:1 lt:1  rt:5997  fl:3 avg_rt: 46.6 (20)  #查询库存
 in 100 sec.

<Raw Results2(sum ver.)>
  [0] sc:3  lt:10  rt:74220  fl:37   
  [1] sc:18  lt:0  rt:64403  fl:33   
  [2] sc:0  lt:0  rt:9995  fl:5    
  [3] sc:5  lt:0  rt:0  fl:0 
  [4] sc:1  lt:1  rt:5997  fl:3 

<Constraint Check> (all must be [OK]) # 约束条件
 [transaction percentage]                       #事物占比
        Payment: 47.37% (>=43.0%) [OK]          # 支付
   Order-Status: 0.00% (>= 4.0%) [NG] *        #查询订单
       Delivery: 13.16% (>= 4.0%) [OK]       #发货
    Stock-Level: 5.26% (>= 4.0%) [OK]    #查询库存
 [response time (at least 90% passed)]   # 在响应时间内，至少完成90%事物才算合格
      New-Order: 23.08%  [NG] *   # 新订单业务
        Payment: 100.00%  [OK]    #支付
   Order-Status: -nan%  [NG] *   #查询订单
       Delivery: 100.00%  [OK]   #发货
    Stock-Level: 50.00%  [NG] *  #查询库存

<TpmC>
                 7.800 TpmC
TPMC：每分钟处理事物数（PS：没有做优化是真特么的慢啊，建议优化后在执行压测可以大大节省时间）

查看VMSTAT的so 分页和id CPU空闲 b是IO等待

执行第二轮测试（此处不在演示）

./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p 123456 -w 10 -c 5 -r 300 -l 100

执行第三轮测试（此处不在演示）

./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p 123456 -w 10 -c 6 -r 300 -l 100

确定系统能力

取没有出现NG的事物处理能力的中间值

Mr丶点心永久会员

打赏 收藏 海报 链接