Practice of pgbench

pgbench is a simple program to run benchmarks on PostgreSQL. It may run the same sequence of SQL commands over and over in concurrent database sessions and calculate the average transaction rate (the number of transactions per second). pgbench supports two ways of testing:
1. By default, pgbench will test a TPC-B-based but more relaxed scenario involving five SELECT, UPDATE, and INSERT commands per transaction.
2. Users can easily test other situations by writing their own transaction script files.
Let's first look at the default test method for pgbench. The default TPC-B transaction-like test requires that specific tables be pre-set. You can use the - I (initialization) option to call pgbench to create and populate these tables (you don't need this step when testing a custom script, but you need to do some setup work according to your own test needs). Initialize something like this:

pgbench -i [ other-options ] dbname

Here we will scale up 100 times to test:

pg12@isdtest-> pgbench -i postgres -s 100
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...

postgres=# /d
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)


postgres=# select count(*) from pgbench_accounts ;
  count   
----------
 10000000
(1 row)

You can see that pgbench_accounts has 10 million pieces of data (default 100,000).
The scripts for the default test of pgbench are as follows:

 BEGIN; 

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 

SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 

UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 

UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;        

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 

END;

After initializing the default environment, we begin to do the pressure measurement:

pg12@isdtest-> pgbench -n -r -T 60 -P 1 -c 64 -j 64
     transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 63
number of threads: 63
duration: 60 s
number of transactions actually processed: 1659519
latency average = 2.281 ms
latency stddev = 64.946 ms
tps = 27586.607969 (including connections establishing)
tps = 27593.617598 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  /set aid random(1, 100000 * :scale)
         0.001  /set bid random(1, 1 * :scale)
         0.001  /set tid random(1, 10 * :scale)
         0.001  /set delta random(-5000, 5000)
         0.053  BEGIN;
         1.116  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.166  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.352  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.299  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.160  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.133  END;

This default tpcb test is not very useful in practical use. Generally, pgbench is used to run custom benchmark scenarios by reading transaction scripts from a file (-f option) instead of default transaction scripts (as mentioned above). In this case, a "transaction" is an execution of a script file.
Here's a simple test of a checking script.

Tectonic setting:

postgres=# create table test(  
postgres(#   id int8 primary key,   
postgres(#   info text default 'tessssssssssssssssssssssssssssssssssssst',   
postgres(#   state int default 0,   
postgres(#   crt_time timestamp default now(),   
postgres(#   mod_time timestamp default now()  
postgres(# );  
CREATE TABLE
postgres=# insert into test select generate_series(1,10000000);  

INSERT 0 10000000

Build scripts:
vi test.sql

/set id random(1,100000000)  
select * from test where id=:id;

Test:

pg12@isdtest-> pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 60  
...
...
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 60 s
number of transactions actually processed: 4931686
latency average = 0.388 ms
latency stddev = 1.248 ms
tps = 82187.250560 (including connections establishing)
tps = 82200.894111 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  /set id random(1,10000000)  
         0.385  select * from test where id=:id;
TPS: 82187

Average response time: 0.388 milliseconds

Tags: SQL PostgreSQL Database

Posted on Fri, 06 Sep 2019 03:10:30 -0700 by Cyberspace