PostgreSQL資料庫壓力測試工具pgbench簡單應用

PostgreSQL資料庫提供了一款輕量級的壓力測試工具叫pgbench,其實就是一個編譯好後的擴充性的可執行檔。介紹如下。
環境：
CentOS 5.7(final)
PG:9.1.2
Vmware 8.0
資料庫參數： max_connection=100 ,其他略，預設

1.安裝
進入源碼安裝包,編譯,安裝

[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

安裝完畢以後可以在bin檔案夾下看到新產生的pgbench檔案

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

2.參數介紹

[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.初始化測試資料

[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

說明：
a.這裡使用的是預設的參數值,帶-s 參數時可指定測試資料的資料量,-f可以指定測試的指令碼,這裡用的是預設指令碼
b.不要在生產的庫上做，建立一個測試庫，當生產上有同名的測試表時將被重設

1 2 下一頁