pgbench并发查询测试

• 复制
  复制成功

[tbase@VM_0_37_centos pgbench]$ cat select_t_trgm.sql
\set id random(1, 1000000)
select * from t_trgm where trgm ilike '%'||md5(:id)||'%';
[tbase@VM_0_37_centos pgbench]$ pgbench -h 127.0.0.1 -p 11008 -d postgres -U tbase -c 4 -j 1 -n -M prepared -T 60 -r -f select_t_trgm.sql > select_t_trgm.txt 2>&1
[tbase@VM_0_37_centos pgbench]$ tail -20 select_t_trgm.txt  
client 0 receiving
client 1 receiving
client 2 receiving
client 0 receiving
client 3 receiving
pghost: 127.0.0.1 pgport: 11008 nclients: 4 duration: 60 dbName: postgres
transaction type: select_t_trgm.sql
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 1
duration: 60 s
number of transactions actually processed: 14150
latency average = 16.965 ms
tps = 235.773228 (including connections establishing)
tps = 235.794290 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.013  \set id random(1, 1000000)
        16.944  select * from t_trgm where trgm ilike '%'||md5(:id)||'%';
[tbase@VM_0_37_centos pgbench]$ cat select_t_trgm.sql
\set id random(1, 1000000)
select * from t_trgm where trgm ilike '%'||substring(md5(:id) from 3 for 6)||'%';
[tbase@VM_0_37_centos pgbench]$ pgbench -h 127.0.0.1 -p 11008 -d postgres -U tbase -c 4 -j 1 -n -M prepared -T 60 -r -f select_t_trgm.sql > select_t_trgm.txt 2>&1
[tbase@VM_0_37_centos pgbench]$ tail -20  select_t_trgm.txt
client 3 receiving
client 2 receiving
client 1 receiving
client 0 receiving
client 3 receiving
pghost: 127.0.0.1 pgport: 11008 nclients: 4 duration: 60 dbName: postgres
transaction type: select_t_trgm.sql
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 1
duration: 60 s
number of transactions actually processed: 2365
latency average = 101.642 ms
tps = 39.353883 (including connections establishing)
tps = 39.357547 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.015  \set id random(1, 1000000)
       101.528  select * from t_trgm where trgm ilike '%'||substring(md5(:id) from 3 for 6)||'%';