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();