Simple Application of PostgreSQL database stress testing tool pgbench

PostgreSQL database provides a lightweight stress testing tool called pgbench, which is actually an executable file with compiled scalability. It is described as follows.
Environment:
CentOS 5.7 (final)
PG: 9.1.2
Vmware 1, 8.0
Database parameter: max_connection = 100, other omitted, default

1. Install
Go to the source code installation package, compile, and install

[postgres@localhost  ~]$ cd postgresql-9.1.2/contrib/pgbench/[postgres@localhost  pgbench]$ lltotal 164-rw-r--r--. 1 postgres postgres   538 Dec  1  2011 Makefile-rwxrwxr-x. 1 postgres postgres 50203 Apr 26 23:50 pgbench-rw-r--r--. 1 postgres postgres 61154 Dec  1  2011 pgbench.c-rw-rw-r--. 1 postgres postgres 47920 Apr 26 23:50 pgbench.o[postgres@localhost  pgbench]$make all[postgres@localhost  pgbench]$make install

After installation, you can view the new pgbench file in the bin folder.

[postgres@localhost  bin]$ ll $PGHOME/bin pgbench-rwxr-xr-x. 1 postgres postgres 50203 Jul  8 20:28 pgbench

2. Parameter Introduction

[postgres@localhost  bin]$ pgbench --helppgbench is a benchmarking tool for PostgreSQL.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 NUM       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 transactions 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 exitReport bugs to .

3. initialize Test Data

[postgres@localhost  ~]$ pgbench -i pgbenchcreating tables...10000 tuples done.20000 tuples done.30000 tuples done.40000 tuples done.50000 tuples done.60000 tuples done.70000 tuples done.80000 tuples done.90000 tuples done.100000 tuples done.set primary key...NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"vacuum...done.[postgres@localhost  ~]$ psql -d pgbenchpsql (9.1.2)Type "help" for help.pgbench=# select count(1) from pgbench_accounts; count  -------- 100000(1 row)pgbench=# select count(1) from pgbench_branches; count -------     1(1 row)pgbench=# select count(1) from pgbench_history; count -------     0(1 row)pgbench=# select count(1) from pgbench_tellers; count -------    10(1 row)pgbench=# \d+ pgbench_accounts                Table "public.pgbench_accounts"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- aid      | integer       | not null  | plain    |  bid      | integer       |           | plain    |  abalance | integer       |           | plain    |  filler   | character(84) |           | extended | Indexes:    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)Has OIDs: noOptions: fillfactor=100pgbench=# \d+ pgbench_branches                Table "public.pgbench_branches"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- bid      | integer       | not null  | plain    |  bbalance | integer       |           | plain    |  filler   | character(88) |           | extended | Indexes:    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)Has OIDs: noOptions: fillfactor=100pgbench=# \d+ pgbench_history                       Table "public.pgbench_history" Column |            Type             | Modifiers | Storage  | Description --------+-----------------------------+-----------+----------+------------- tid    | integer                     |           | plain    |  bid    | integer                     |           | plain    |  aid    | integer                     |           | plain    |  delta  | integer                     |           | plain    |  mtime  | timestamp without time zone |           | plain    |  filler | character(22)               |           | extended | Has OIDs: nopgbench=# \d+ pgbench_tellers                 Table "public.pgbench_tellers"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- tid      | integer       | not null  | plain    |  bid      | integer       |           | plain    |  tbalance | integer       |           | plain    |  filler   | character(84) |           | extended | Indexes:    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)Has OIDs: noOptions: fillfactor=100

Note:
A. The default parameter value is used here. The data size of the test data can be specified when the-s parameter is included.-f can specify the test script. The default script is used here.
B. Do not create a test database on the production database. When a test table with the same name exists in the production database, it is reset.

1 2 Next Page