Greenplum数据库使用总结-PGBENCH使用

7.3 PGBENCH测试案例

7.3.1创建表信息

以下命令在postgres数据库中执行

$ psql -h 192.168.31.200 -d postgres -U postgres -p 5432

创建tb表

postgres=# 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);

创建序列

postgres=# alter sequence tbl_id_seq cache 10000;

创建函数

postgres=# create or replace function f() returns void as $$

insert into tbl (crt_time,sensorid,info) values ( clock_timestamp(),trunc(random()*500000),substring(md5(random()::text),1,8) );

$$ language sql strict;

7.3.2修改以下参数

7.3.2.1修改配置文件

vi $PGDATA/postgresql.conf

autovacuum_naptime=1s

maintenance_work_mem=1GB

autovacuum_work_mem=1GB

autovacuum = on

autovacuum_max_workers = 3

log_autovacuum_min_duration = 0

autovacuum_vacuum_cost_delay=0

7.3.2.2重启服务

$ pg_ctl reload

7.3.3创建SQL测试文件

# cd /home/postgres/test-dome

#cat test.sql

select f();

7.3.4查看测试效果

以下命令在postgres用户下执行

$ pgbench -M prepared -n -r -P 1 -f /home/postgres/test-dome/test.sql -c 48 -j 48 -T 20

progress: 1.0 s, 4219.9 tps, lat 9.921 ms stddev 7.691

progress: 2.0 s, 5166.3 tps, lat 9.281 ms stddev 5.148

progress: 3.0 s, 5252.5 tps, lat 9.147 ms stddev 5.743

progress: 4.0 s, 5251.0 tps, lat 9.162 ms stddev 5.189

progress: 5.0 s, 5304.0 tps, lat 9.048 ms stddev 5.568

progress: 6.0 s, 5303.7 tps, lat 9.056 ms stddev 4.770

progress: 7.0 s, 5121.3 tps, lat 9.381 ms stddev 5.015

progress: 8.0 s, 5067.9 tps, lat 9.461 ms stddev 5.068

progress: 9.0 s, 5408.2 tps, lat 8.837 ms stddev 5.149

progress: 10.0 s, 5225.5 tps, lat 9.208 ms stddev 5.052

progress: 11.0 s, 4857.7 tps, lat 9.904 ms stddev 7.952

progress: 12.0 s, 4840.6 tps, lat 9.897 ms stddev 6.897

progress: 13.0 s, 4877.0 tps, lat 9.849 ms stddev 7.397

progress: 14.0 s, 4891.0 tps, lat 9.827 ms stddev 5.587

progress: 15.0 s, 5113.0 tps, lat 9.352 ms stddev 5.706

progress: 16.0 s, 4957.6 tps, lat 9.679 ms stddev 6.322

progress: 17.0 s, 4887.6 tps, lat 9.852 ms stddev 5.719

progress: 18.0 s, 4192.2 tps, lat 11.386 ms stddev 8.290

progress: 19.0 s, 4382.5 tps, lat 11.028 ms stddev 9.783

progress: 20.0 s, 2466.5 tps, lat 19.138 ms stddev 17.423

transaction type: /home/postgres/test-dome/test.sql

scaling factor: 1

query mode: prepared

number of clients: 48

number of threads: 48

duration: 20 s

number of transactions actually processed: 96836

latency average = 9.868 ms

latency stddev = 7.167 ms

tps = 4825.008646 (including connections establishing)

tps = 4853.968567 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

9.868 select f();

在以上可以看出测试线程是48个，在不建立连接的情况下TPS是4825.008646,不在连接的情况下TPS是4853.968567，测试的语句是select f();