Test PostgreSQL with Pgbench

Pgbench–help

Initialization options:

-I call initialization mode

-F NUM Fill factor

-S NUM scale factor (related to the amount of data produced)

Benchmarking Options:

-C NUM Database client concurrency (default: 1)

-C (Make a new connection for each transaction)

-D varname=value defining variables for users through client script

-f filename reads the transaction script from file filename

Number of-j NUM threads (default: 1)

-I write transaction time to log file

-m{simple|extended|prepared} The protocol that submits the query to the server

-N do not run vacuum before testing

-N Do not update table "Pgbench_tellers" "Pgbench_branches"

-R report average delay per command

-S NUM reports the scale factor in the output

-S performing select-only transactions

-T NUM number of transactions per client (default: 10)

-T NUM Benchmark test time (units: seconds)

-V clears all four standard tables before testing

Common options:

-D Output Print debugging information

-H HOSTNAME database server host or socket directory

-U USERNAME The connection of the specified database user

--HELP Display Help information, and then exit

--version output version information, and then exit

Generate Data

postgres=# Create user benchmarksql with superuser password ' 123456 ';

CREATE ROLE

postgres=# CREATE DATABASE benchmarksql owner Benchmarksql;

CREATE DATABASE

[Email protected] bin]$/pgbench-i-F 100-s 1428-h 127.0.0.1-p 6432-u benchmarksql benchmarksql

Connected data

[Email protected] bin]$/psql-h 127.0.0.1-p 6432-u benchmarksql benchmarksql

View database size

Select Pg_database_size (' benchmarksql ')/1024/1024/1024| | ' GB ';

Start testing

[Email protected] bin]$/pgbench-c 10–j–t-r-t 1800-h 127.0.0.1-p 6432-u Benchmarksql

Transaction type:tpc-b (sort of)

Scaling factor:1428

Query mode:prepared

Number of Clients:10

Number of Threads:10

duration:1800 s

Number of transactions actually processed:1294479

Latency average:13.905 ms

TPS = 715.507310 (including connections establishing)

TPS = 715.510043 (excluding connections establishing)

Statement latencies in milliseconds:

0.002768 \set nbranches 1 *: Scale

0.000647 \set Ntellers *: Scale

0.000562 \set naccounts 100000 *: Scale

0.000823 \setrandom Aid 1:naccounts

0.000640 \setrandom Bid 1:nbranches

0.000684 \setrandom Tid 1:ntellers

0.000615 \setrandom delta-5000 5000

0.054656 BEGIN;

6.063291 UPDATE pgbench_accounts SET abalance = abalance +:d elta WHERE aid =: aid;

0.092079 SELECT abalance from pgbench_accounts WHERE aid =: aid;

2.897447 UPDATE pgbench_tellers SET tbalance = tbalance +:d elta WHERE tid =: tid;

2.026997 UPDATE pgbench_branches SET bbalance = bbalance +:d elta WHERE bid =: bid;

1.640868 INSERT into Pgbench_history (TID, bid, aid, Delta, Mtime) VALUES (: Tid,: Bid,: aid,:d Elta, Current_timestamp);

1.184043 END;

[Email protected] bin]$/pgbench-c 20-j 20-m prepared-n-S 1428-t 1800-r-H 127.0.0.1-p 6432-u Benchmarksql Benc Hmarksql

Transaction type:tpc-b (sort of)

Scaling factor:1428

Query mode:prepared

Number of CLIENTS:20

Number of THREADS:20

duration:1800 s

Number of transactions actually processed:1503569

Latency average:23.943 ms

TPS = 829.034591 (including connections establishing)

TPS = 829.038294 (excluding connections establishing)

Statement latencies in milliseconds:

0.003509 \set nbranches 1 *: Scale

0.001146 \set Ntellers *: Scale

0.000805 \set naccounts 100000 *: Scale

0.001075 \setrandom Aid 1:naccounts

0.000881 \setrandom Bid 1:nbranches

0.000874 \setrandom Tid 1:ntellers

0.000881 \setrandom delta-5000 5000

0.077150 BEGIN;

9.083130 UPDATE pgbench_accounts SET abalance = abalance +:d elta WHERE aid =: aid;

0.123980 SELECT abalance from pgbench_accounts WHERE aid =: aid;

6.025891 UPDATE pgbench_tellers SET tbalance = tbalance +:d elta WHERE tid =: tid;

3.595181 UPDATE pgbench_branches SET bbalance = bbalance +:d elta WHERE bid =: bid;

3.098900 INSERT into Pgbench_history (TID, bid, aid, Delta, Mtime) VALUES (: Tid,: Bid,: aid,:d Elta, Current_timestamp);

2.098509 END;

Transaction type:tpc-b (sort of)

Scaling factor:1428

Query mode:prepared

Number of clients:30

Number of Threads:10

duration:1800 s

Number of transactions actually processed:1468864

Latency average:36.763 ms

TPS = 815.178087 (including connections establishing)

TPS = 815.184313 (excluding connections establishing)

Statement latencies in milliseconds:

0.003654 \set nbranches 1 *: Scale

0.001164 \set Ntellers *: Scale

0.001123 \set naccounts 100000 *: Scale

0.001288 \setrandom Aid 1:naccounts

0.001202 \setrandom Bid 1:nbranches

0.001106 \setrandom Tid 1:ntellers

0.001129 \setrandom delta-5000 5000

0.154459 BEGIN;

13.228743 UPDATE pgbench_accounts SET abalance = abalance +:d elta WHERE aid =: aid;

0.212691 SELECT abalance from pgbench_accounts WHERE aid =: aid;

9.070507 UPDATE pgbench_tellers SET tbalance = tbalance +:d elta WHERE tid =: tid;

4.904218 UPDATE pgbench_branches SET bbalance = bbalance +:d elta WHERE bid =: bid;

5.595383 INSERT into Pgbench_history (TID, bid, aid, Delta, Mtime) VALUES (: Tid,: Bid,: aid,:d Elta, Current_timestamp);

3.612066 END;

[Email protected] bin]$/pgbench-c 50-j 50-m prepared-n-S 1428-t 1800-r-H 127.0.0.1-p 6432-u Benchmarksql Benc Hmarksql

Transaction type:tpc-b (sort of)

Scaling factor:1428

Query mode:prepared

Number of CLIENTS:50

Number of THREADS:50

duration:1800 s

Number of transactions actually processed:1641175

Latency average:54.839 ms

TPS = 901.460449 (including connections establishing)

TPS = 901.475075 (excluding connections establishing)

Statement latencies in milliseconds:

0.005079 \set nbranches 1 *: Scale

0.001588 \set Ntellers *: Scale

0.001257 \set naccounts 100000 *: Scale

0.001768 \setrandom Aid 1:naccounts

0.001328 \setrandom Bid 1:nbranches

0.001424 \setrandom Tid 1:ntellers

0.001316 \setrandom delta-5000 5000

0.256724 BEGIN;

19.791906 UPDATE pgbench_accounts SET abalance = abalance +:d elta WHERE aid =: aid;

0.284488 SELECT abalance from pgbench_accounts WHERE aid =: aid;

13.638615 UPDATE pgbench_tellers SET tbalance = tbalance +:d elta WHERE tid =: tid;

8.232851 UPDATE pgbench_branches SET bbalance = bbalance +:d elta WHERE bid =: bid;

7.664059 INSERT into Pgbench_history (TID, bid, aid, Delta, Mtime) VALUES (: Tid,: Bid,: aid,:d Elta, Current_timestamp);

5.561957 END;

Test PostgreSQL with Pgbench