Postgresql pgbench Tool

Postgresql pgbench tool pgbench is a benchmarking tool for PostgreSQL. pgbench is a performance benchmark tool provided by postgresql. You can use pgbench -- help to check the parameter Usage: pgbench [OPTIONS]... [DBNAME] Initialization options:-I invokes initialization mode-F NUM fill factor-s NUM scaling factorBenchmarking options:-c NUM number of concurrent database clients (default: 1) -C establish new connection for each transaction-d varname = VALUE define variable for use by custom script-f FILENAME read transaction script from FILENAME-j NU M number of threads (default: 1)-l write transaction times to log file-M {simple | extended | prepared} protocol for submitting queries to server (default: simple) -n do not run VACUUM before tests-N do not update tables "pgbench_tellers" and "pgbench_branches"-r report average latency per command-s NUM report this scale factor in output-S perform SELECT-only transactions-t NUM number of transa Ctions each client runs (default: 10)-t num duration of benchmark test in seconds-v vacuum all four standard tables before testsCommon options: -d print debugging output-h HOSTNAME database server host or socket directory-p PORT database server port number-u username connect as specified database user -- help show this help, then exit -- version output version information, then exit initialization: scale is 10 Pgbench_accounts records 100 W, pgbench-I-s 10 pgbench-h 127.0.0.1-p 1931-U testuser. Four tables will be created during initialization here, schema | Name | Type | Owner | Size | Description -------- + ------------------ + ------- + ---------- + -------- + --------------- public | pgbench_accounts | table | testuser | 130 MB | public | pgbench_branches | table testuser | 168 kB | public | pgbench_history | table | testuser | 960 kB | public | pgbench _ Tellers | table | testuser | 456 kB | test: 100 client concurrency, each with 100 transactions pgbench-c 100-t 100 pgbenchstarting vacuum... end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simplenumber of clients: 100 number of threads: 1 number of transactions per client: 100 number of transactions actually processed: 10000/10000 tps = 114.339158 (including connections establishing) -- number of transactions containing network overhead tps = 114.643324 (Excluding connections establishing) -- number of transactions that do not contain network overhead pgbench-c 100-t 100-j 100 pgbenchstarting vacuum... end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simplenumber of clients: 100 number of threads: 100 number of transactions per client: 100 number of transactions actually processed: 10000/10000 tps = 134.473603 (including connections establishing) tps = 134.616838 (excluding connec Tions establishing) Note that-c must be a multiple of-j, that is, the client is a multiple of threads. Pgbench-c 100-j 100-T 10 pgbenchstarting vacuum... end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simplenumber of clients: 100 number of threads: 100 duration: 10 snumber of transactions actually processed: 2144tps = 209.617862 (including connections establishing) tps = 211.282453 (excluding connections establishing) Note:-t and-T cannot be used at the same time. Script content: \ set nbranches: scale \ set ntellers 10 *: scale \ set naccounts 100000 *: scale \ setrandom aid 1: naccounts \ setrandom bid 1: nbranches \ setrandom tid 1: ntellers \ setrandom delta-5000 5000 BEGIN; UPDATE pgbench_accounts SET abalance = abalance +: delta WHERE aid =: aid; SELECT abalance FROM pgbench_accounts WHERE aid =: aid; update into 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; an example is provided to test the speed of machine insert. SQL: create table data (filler text); insert-size. SQL content: insert into data (filler) values (repeat ('x',: scale )); $ psql-d pgbench-f insertsize. SQL $ pgbench-s 100-c 10-t 10000 pgbench-f insert-size. SQL starting vacuum... end. transaction type: Custom queryscaling factor: 100 query mode: simplenumber of clients: 10 number of threads: 1 number of transactions per client: 10000 number of transactions actually processed: 100000/100000 tps = 608.144907 (including connections establishing) tps = 608.234303 (excluding connections establishing)