Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the role of pgbench in PostgreSQL". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the role of pgbench in PostgreSQL"?

Pgbench is a benchmark tool for PostgreSQL. By default (the tool is provided by default), the pgbench test is based on the TPC-B scenario, and each transaction includes five SELECT, UPDATE, and INSERT commands. You can customize testing on demand by writing your own transaction script files.

I. data initialization

Initialize the base table and data through the-I parameter. Create a test base table pgbench_accounts/pgbench_branches/pgbench_history/pgbench_tellers

[atlasdb@localhost tmp] $createdb pgbench [atlasdb@localhost tmp] $pgbench-I-U atlasdb-p 5432-d pgbenchdropping old tables...NOTICE: table "pgbench_accounts" does not exist, skippingNOTICE: table "pgbench_branches" does not exist, skippingNOTICE: table "pgbench_history" does not exist, skippingNOTICE: table "pgbench_tellers" does not exist, skippingcreating tables...generating data...100000 of 100000 tuples (0.45s, remaining 0.00s) vacuuming...creating primary keys...done. Second, parameter description

You can view the complete list of parameters through pgbench-- help.

Where:

-c,-- client=NUM

Number of database clients, which can be understood as the number of database sessions (number of postgres processes). The default is 1.

-C,-- connect

Each transaction creates a connection, and because PG uses the process model, you can test the performance of frequent Kill/Create processes

-J,-- jobs=NUM

Number of worker threads for pgbench

-T,-- time=NUM

The duration of pressure measurement in seconds

-v,-- vacuum-all

Execute the vacuum command before each test to avoid the impact of "junk" space

-M,-- protocol=simple | extended | prepared

Submit the query command to the protocol used by the server. Simple is the default option, and prepared is similar binding.

-r,-- report-latencies

Report the average delay per command (SQL statement)

-S,-- select-only

Execute only query statements

[atlasdb@localhost tmp] $pgbench-- helppgbench is a benchmarking tool for PostgreSQL.Usage: pgbench [OPTION]. [DBNAME] Initialization options:-I,-initialize invokes initialization mode-I,-- init-steps= [dtgvpf] + (default "dtgvp") III. Basic use

Perform benchmark tests

Start 4 worker threads and create 8 client connection PG by executing the following command:

Pgbench-h 192.168.26.103-U atlasdb-p 5432-d pgbench-r-M prepared-v-T 120-c 8-j 4-- aggregate-interval=5-l

The following is some of the log output during execution:

Client 5 receivingclient 2 receivingclient 2 sending INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 39145,-3294, CURRENT_TIMESTAMP); client 2 receivingclient 4 receivingclient 4 executing script "" client 4 executing\ set aidclient 4 executing\ set bidclient 4 executing\ set tidclient 4 executing\ set deltaclient 4 sending BEGIN;client 4 receivingclient 2 receivingclient 2 sending END;client 2 receivingclient 4 receivingclient 4 sending UPDATE pgbench_accounts SET abalance = abalance + 4608 WHERE aid = 80792 receivingclient client 4 receivingclient 4 receivingclient 4 sending SELECT abalance FROM pgbench_accounts WHERE aid = 80792witclient 4 sending SELECT abalance FROM pgbench_accounts WHERE aid 2 sending SELECT abalance FROM pgbench_accounts WHERE aid

Here is the output of the result:

Transaction type: scaling factor: 1query mode: preparednumber of clients: 8number of threads: 4duration: 34.537 snumber of transactions actually processed: 27809latency average = 34.537 mstps = 231.635472 (including connections establishing) tps = 231.685954 (excluding connections establishing) statement latencies in milliseconds: 0.035\ set aid random (1, 100000 *: scale) 0.008\ set bid random (1, 1 *: scale) 0.007\ set tid random (1 10 *: scale) 0.006\ set delta random (- 5000, 5000) 1.588 BEGIN 1.987 UPDATE pgbench_accounts SET abalance = abalance +: delta WHERE aid =: aid; 1.829 SELECT abalance FROM pgbench_accounts WHERE aid =: aid; 9.305 UPDATE pgbench_tellers SET tbalance = tbalance +: delta WHERE tid =: tid; 15.904 UPDATE pgbench_branches SET bbalance = bbalance +: delta WHERE bid =: bid 1.741 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (: tid,: bid,: aid,: delta, CURRENT_TIMESTAMP); 2.101 END

Perform query benchmark

Pgbench-h 192.168.26.103-U atlasdb-p 5432-d pgbench-S-r-M prepared-v-T 120-c 8-j 4-- aggregate-interval=5-l

The final results are as follows:

Transaction type: scaling factor: 1query mode: preparednumber of clients: 8number of threads: 4duration: 120snumber of transactions actually processed: 418458latency average = 2.294 mstps = 3486.766688 (including connections establishing) tps = 3487.047954 (excluding connections establishing) statement latencies in milliseconds: 0.018\ set aid random (1, 100000 *: scale) 2.266 SELECT abalance FROM pgbench_accounts WHERE aid =: aid At this point, I believe you have a deeper understanding of "what is the role of pgbench in PostgreSQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.