数据库压测 - 分布式数据库

#资料
	https://help.aliyun.com/zh/polardb/polardb-for-xscale/tpc-c-test?spm=5176.21213303.J_v8LsmxMG6alneH-O7TCPa.1.72cf2f3dRed3ST&scm=20140722.S_help@@%E6%96%87%E6%A1%A3@@405018._.ID_help@@%E6%96%87%E6%A1%A3@@405018-RL_tpc~DAS~c%E6%B5%8B%E8%AF%95-LOC_2024SPHelpResult-OR_ser-PAR1_215044bd17417471198153204efa9d-V_4-RE_new6-P0_0-P1_0
	
---
#SysBench
##1、安装 
	yum install sysbench
---
	sysbench --version

![](/media//202502/2025-02-21105239675561.png)

---
##2、压前准备
####1、创建压测数据库
	CREATE DATABASE yatest;
	
---
####2、导入压测数据
	sysbench oltp_write_only \
	  --tables='10' \
	  --table-size='10000000' \
	  --threads='8' \
	  --histogram='on' \
	  --report-interval='1' \
	  --mysql-host=172.16.81.206 \
	  --mysql-port=3306 \
	  --mysql-user=root \
	  --mysql-password=test9cloud@ \
	  --mysql-db=yatest \
	  prepare
	  
---
	#点查
	oltp_point_select

#只读
	oltp_read_only

#写入
	oltp_write_only

#读写
	oltp_read_write

#按索引更新
	oltp_update_index

#非索引更新
	oltp_update_non_index

![](/media//202502/2025-02-21111426054325.png)

---
####3、清理
	sysbench oltp_read_write \
	  --tables='10' \
	  --mysql-host=172.16.81.206 \
	  --mysql-port=3306 \
	  --mysql-user=root \
	  --mysql-password=test9cloud@ \
	  --mysql-db=yatest \
	  cleanup

---
####4、压测
	sysbench oltp_read_write \
	  --db-ps-mode='disable'\
	  --mysql-ignore-errors='all'\
	  --tables='5'\
	  --table-size='10000000'\
	  --range-size=5\
	  --threads='4'\
	  --histogram='on'\
	  --report-interval='1' \
	  --mysql-host=172.16.81.206 \
	  --mysql-port=3306 \
	  --mysql-user=root \
	  --mysql-password=test9cloud@ \
	  --mysql-db=yatest \
	  --time='60'\
	  run
---
####响应时间分布图（--histogram='on'）

![](/media//202502/2025-02-21113724121991.png)

---
####压测结果
	SQL statistics:
	    queries performed:
	        read:                            254688						#总select数量
	        write:                           72768						#总update、insert、delete语句数量
	        other:                           36384						#commit，unlock tables数量
	        total:                           363840
	    transactions:                        18192  (302.34 per sec.)	#通常需要关注的数字(TPS)
	    queries:                             363840 (6046.74 per sec.)	#QPS
	    ignored errors:                      0      (0.00 per sec.)		#忽略的错误数
	    reconnects:                          0      (0.00 per sec.)
	
	General statistics:
	    total time:                          60.1694s					#压测时间
	    total number of events:              18192						#总的事件数，一般与transactions相同
	
	Latency (ms):
	         min:                                    3.05				#最低响应时间
	         avg:                                   13.23				#平均响应时间
	         max:                                  623.67				#最大响应时间
	         95th percentile:                       59.99				#95%的平均响应时间
	         sum:                               240605.34
	
	Threads fairness:
	    events (avg/stddev):           4548.0000/262.31
	    execution time (avg/stddev):   60.1513/0.00