PostgreSQL 使用sysbench进行数据库压测
悠扬的幻想天空 - 博客
April 2, 2021 技术 • 作者：悠扬
PostgreSQL 使用sysbench进行数据库压测
环境准备
连接实例
安装 pgsql 客户端
yum install -y postgresql执行如下命令连接到 TDSQL PostgreSQL版
psql -h 实例地址 -p 端口 -U tbaseadmin -d postgres基准测试
RHEL/CentOS 安装 sysbench:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench客户端配置
8核；16G；300G
tLinux 2.2-集成版
TBase 配置
GTM 节点配置 1 主 1 从，节点配置为 1核 CPU / 4GB 内存 / 100GB 硬盘
CN 节点配置 2 组：每组 1 主 1 从；节点配置为 1核 CPU / 4GB 内存 / 100GB 硬盘
DN 节点配置 2 组：每组 1 主 1 从；节点配置为 1核 CPU / 6GB 内存 / 100GB 硬盘
TBase 版本
postgres=> SELECT version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 TBase V2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)Sysbench 版本
[root@VM-234-97-centos ~]# sysbench --version
sysbench 1.0.17 进行压测
[root@VM-234-97-centos ~]# sysbench --db-driver=pgsql --pgsql-host=127.0.0.83 --pgsql-user=tbaseadmin --pgsql-password=xxx --pgsql-db=6521 --oltp-table-size=10000 --rand-init=on --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
[root@VM-234-97-centos ~]# sysbench --db-driver=pgsql --pgsql-host=127.0.0.83 --pgsql-user=tbaseadmin --pgsql-password=xxx --pgsql-db=6521 --oltp-table-size=10000 --rand-init=on --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 10 tps: 212.48 qps: 4279.55 (r/w/o: 2998.89/850.31/430.35) lat (ms,99%): 99.33 err/s: 1.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 150.80 qps: 3022.35 (r/w/o: 2115.94/601.61/304.81) lat (ms,99%): 170.48 err/s: 0.60 reconn/s: 0.00
[ 30s ] thds: 10 tps: 116.90 qps: 2354.10 (r/w/o: 1649.10/468.00/237.00) lat (ms,99%): 196.89 err/s: 0.80 reconn/s: 0.00
[ 40s ] thds: 10 tps: 109.80 qps: 2211.09 (r/w/o: 1549.69/439.10/222.30) lat (ms,99%): 186.54 err/s: 0.70 reconn/s: 0.00
[ 50s ] thds: 10 tps: 87.50 qps: 1760.41 (r/w/o: 1232.81/350.00/177.60) lat (ms,99%): 200.47 err/s: 0.60 reconn/s: 0.00
[ 60s ] thds: 10 tps: 82.00 qps: 1646.60 (r/w/o: 1153.00/327.40/166.20) lat (ms,99%): 204.11 err/s: 0.50 reconn/s: 0.00
[ 70s ] thds: 10 tps: 79.30 qps: 1595.70 (r/w/o: 1117.80/318.00/159.90) lat (ms,99%): 204.11 err/s: 0.30 reconn/s: 0.00
[ 80s ] thds: 10 tps: 72.30 qps: 1444.80 (r/w/o: 1011.10/287.90/145.80) lat (ms,99%): 287.38 err/s: 0.20 reconn/s: 0.00
[ 90s ] thds: 10 tps: 64.90 qps: 1310.50 (r/w/o: 918.50/261.00/131.00) lat (ms,99%): 297.92 err/s: 0.50 reconn/s: 0.00
[ 100s ] thds: 10 tps: 66.70 qps: 1339.29 (r/w/o: 938.60/266.10/134.60) lat (ms,99%): 287.38 err/s: 0.20 reconn/s: 0.00
[ 110s ] thds: 10 tps: 60.10 qps: 1210.00 (r/w/o: 847.00/241.50/121.50) lat (ms,99%): 297.92 err/s: 0.50 reconn/s: 0.00
[ 120s ] thds: 10 tps: 55.80 qps: 1121.90 (r/w/o: 785.30/223.70/112.90) lat (ms,99%): 303.33 err/s: 0.50 reconn/s: 0.00
SQL statistics:
queries performed:
read: 163226
write: 46377
other: 23449
total: 233052
transactions: 11595 (96.46 per sec.)
queries: 233052 (1938.84 per sec.)
ignored errors: 64 (0.53 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.2011s
total number of events: 11595
Latency (ms):
min: 13.86
avg: 103.62
max: 503.44
99th percentile: 282.25
sum: 1201421.99
Threads fairness:
events (avg/stddev): 1159.5000/43.35
execution time (avg/stddev): 120.1422/0.07
[root@VM-234-97-centos ~]# sysbench --db-driver=pgsql --pgsql-host=127.0.0.83 --pgsql-user=tbaseadmin --pgsql-password=xxx --pgsql-db=6521 --oltp-table-size=10000 --rand-init=on --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Dropping table 'sbtest1'...解释一下其中的含义，首先[100s] 表示这是在第100秒的时候输出的一段压测统计结果，其他字段如下：
thds:10: 表示当前有 10 个线程正在压测tps:66.7: 表示当前每秒执行了 66.7 个事务qps:1339.29: 表示当前每秒可以执行 1339.29 个请求(r/w/o: 938.60/266.10/134.60): 表示在每秒 1339.29 个请求中，有 938.6 个读请求，266.1 个写请求，134.6 个其他请求，其实就是对qps的细化lat (ms,99%): 360.89：表示 99% 的请求的延迟都在 287.38 毫秒以下err/s: 0.20 reconn/s: 0.00：表示每秒平均有 0.2 个请求失败，发生了 0 次的网络重连
在压测结束后会输出一个总的压测结果，其中的参数的解释：
SQL statistics:
queries performed:
read: 163226 //这就是说在120s的压测期间执行了16万多次的读请求
write: 46377 //这是说在压测期间执行了4万多次的写请求
other: 23449 //这是说在压测期间执行了2万多次的其他请求
total: 233052 //这是说一共执行了23万多次的请求
transactions: 11595 (96.46 per sec.) //这是说一共执行了1万多个事务，每秒执行96多个事务
queries: 233052 (1938.84 per sec.) //这是说一共执行了23万多次的请求，每秒执行接近2k请求
ignored errors: 64 (0.53 per sec.)
reconnects: 0 (0.00 per sec.)
// 一共执行了120s压测，执行了1万+的事务
General statistics:
total time: 120.2011s
total number of events: 11595
Latency (ms):
min: 13.86 // 延迟最小为13ms
avg: 103.62 // 平均延迟为103ms
max: 503.44 // 延迟最大为504ms
99th percentile: 282.25 // 99%的延迟小于282ms
sum: 1201421.99 // 合计耗时
Threads fairness:
events (avg/stddev): 1159.5000/43.35
execution time (avg/stddev): 120.1422/0.07引用：https://blog.csdn.net/weixin_43424368/article/details/107314052