1. 环境描述
1.1 软件版本
| 软件 | 版本 |
|---|---|
| 操作系统 | centos7.5 |
| greenplum | 6.2.1 |
1.2 部署规划
| 主机名 | cpu | 内存 | 磁盘 | gp角色 |
|---|---|---|---|---|
| node1 | 8c | 16g | 2*100GB | master |
| node2 | 8c | 16g | 2*100GB | standby+2segment |
| node3 | 8c | 16g | 2*100GB | 2segment |
1.3 greenplum资源配置
连接数最大数：256
资源队列数：200
单个查询最大可用：2G
2. pgbench部署
注意： 直接通过编译postgresql安装包方式失效，原因是postgresql源码包中缺失相关编译需要的文件。
yum install -y postgresql-contrib
3. 测试前提条件准备
创建测试表
create table tbl(id serial8, crt_time timestamp, sensorid int, sensorloc point, info text) with (autovacuum_enabled=on, autovacuum_vacuum_threshold=1,autovacuum_vacuum_cost_delay=0);
insert.sql为向表中插入一条随机数据
insert into tbl (crt_time,sensorid,info) values ( clock_timestamp(),trunc(random()*500000),substring(md5(random()::text),1,8) );
read.sql 为从表中读取一条随机数据
select * from tbl where sensorid = trunc(random()*500000);
4. 测试用例和执行结果
4.1 测试1：连接数测试
经过多次测试最大连接数可用为224
测试过程如下：
模拟不通数量客户端模拟并发
先后测试了64,128,136,144,152,160,168，176………224，用例太多，只展示临界最大值测试结果。如下：
pgbench -f /home/gpadmin/insert.sql -c 224 -C -j 8 -t 8
模拟224个客户端连接，8个线程，每个客户端8个事物,当连接数提升到232时报错。
测试前
测试后
4.2 测试2：读写速度测试
写入测试
客户端64 10w写入
pgbench -f /home/gpadmin/insert.sql -c 64 -C -j 8 -t 1563
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 8
number of transactions per client: 1563
number of transactions actually processed: 100032/100032
tps = 180.591353 (including connections establishing)
tps = 318.071965 (excluding connections establishing)
starting vacuum...end.
real 9m14.089s
客户端128 10w写入
pgbench -f /home/gpadmin/insert.sql -c 128 -C -j 8 -t 782
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 128
number of threads: 8
number of transactions per client: 782
number of transactions actually processed: 100096/100096
tps = 206.868931 (including connections establishing)
tps = 426.641587 (excluding connections establishing)
starting vacuum...end.
real 8m4.026s
客户端164 100w写入
pgbench -f /home/gpadmin/insert.sql -c 64 -C -j 8 -t 15625
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 8
number of transactions per client: 15625
number of transactions actually processed: 1000000/1000000
tps = 182.547673 (including connections establishing)
tps = 319.338652 (excluding connections establishing)
starting vacuum...end.
real 91m18.215s
客户端128 100w写入
pgbench -f /home/gpadmin/insert.sql -c 128 -C -j 8 -t 7813
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 128
number of threads: 8
number of transactions per client: 7813
number of transactions actually processed: 1000064/1000064
tps = 209.777655 (including connections establishing)
tps = 428.593284 (excluding connections establishing)
starting vacuum...end.
real 79m27.436s
读取测试
客户端64 10w查询
pgbench -f /home/gpadmin/read.sql -c 64 -C -j 8 -t 1563
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 8
number of transactions per client: 1563
number of transactions actually processed: 100032/100032
tps = 20.063108 (including connections establishing)
tps = 20.855078 (excluding connections establishing)
starting vacuum...end.
real 83m6.096s
客户端128 10w查询
pgbench -f /home/gpadmin/read.sql -c 128 -C -j 8 -t 782
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 128
number of threads: 8
number of transactions per client: 782
number of transactions actually processed: 100096/100096
tps = 20.964251 (including connections establishing)
tps = 21.801045 (excluding connections establishing)
starting vacuum...end.
real 79m34.813s
客户端64 100w查询
pgbench -f /home/gpadmin/read.sql -c 64 -C -j 8 -t 15625
执行速度太慢没有继续测试，感兴趣可以提升服务器配置，进行测试