PostgreSQL數據庫壓力測試工具pgbench簡單應用

jopen 12年前發布 | 37K 次閱讀 PostgreSQL 數據庫服務器

PG數據庫提供了一款輕量級的壓力測試工具叫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]$ ll
total 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 --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
  pgbench [OPTIONS]... [DBNAME]
Initialization options:
  -i           invokes initialization mode
  -F NUM       fill factor
  -s NUM       scaling factor
Benchmarking 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 tests
Common 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
Report bugs to .</pre>3.初始化測試數據 
[postgres@localhost  ~]$ pgbench -i pgbench
creating 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 pgbench
psql (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: no
Options: fillfactor=100
pgbench=# \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: no
Options: fillfactor=100
pgbench=# \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: no
pgbench=# \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: no
Options: fillfactor=100</pre>說明： 

a.這里使用的是默認的參數值,帶-s 參數時可指定測試數據的數據量,-f可以指定測試的腳本,這里用的是默認腳本 

b.不要在生產的庫上做，新建一個測試庫，當生產上有同名的測試表時將被重置 



4.測試過程 

4.1 1個session

[postgres@localhost  ~]$ nohup pgbench -c 1 -T 20 -r pgbench > file.out  2>&1
[postgres@localhost  ~]$ more file.out 
nohup: ignoring input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 12496                                                     tps = 624.747958 (including connections establishing)                                                tps = 625.375564 (excluding connections establishing)
statement latencies in milliseconds:
        0.005299        \set nbranches 1 * :scale
        0.000619        \set ntellers 10 * :scale
        0.000492        \set naccounts 100000 * :scale
        0.000700        \setrandom aid 1 :naccounts
        0.000400        \setrandom bid 1 :nbranches
        0.000453        \setrandom tid 1 :ntellers
        0.000430        \setrandom delta -5000 5000
        0.050707        BEGIN;
        0.200909        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        0.098718        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        0.111621        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        0.107297        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.095156        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        0.919101        END;
4.2 30個session [postgres@localhost  ~]$nohup pgbench -c 30 -T 20 -r pgbench > file.out  2>&1
[postgres@localhost  ~]$ more file.out 
nohup: ignoring input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
duration: 20 s
number of transactions actually processed: 8056                                                      tps = 399.847446 (including connections establishing)                                                tps = 404.089024 (excluding connections establishing)
statement latencies in milliseconds:
    0.004195        \set nbranches 1 * :scale
    0.000685        \set ntellers 10 * :scale
    0.000887        \set naccounts 100000 * :scale
    0.000805        \setrandom aid 1 :naccounts
    0.000656        \setrandom bid 1 :nbranches
    0.000523        \setrandom tid 1 :ntellers
    0.000499        \setrandom delta -5000 5000
    0.515565        BEGIN;
    0.865217        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    0.307207        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    50.543371       UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    19.210089       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    0.384190        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    2.116383        END;
4.3 50個session [postgres@localhost  ~]$nohup pgbench -c 50 -T 20 -r pgbench > file.out  2>&1
[postgres@localhost  ~]$ more file.out 
nohup: ignoring input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 20 s
number of transactions actually processed: 7504                                                      tps = 370.510431 (including connections establishing)                                                tps = 377.964565 (excluding connections establishing)
statement latencies in milliseconds:
        0.004291        \set nbranches 1 * :scale
        0.000769        \set ntellers 10 * :scale
        0.000955        \set naccounts 100000 * :scale
        0.000865        \setrandom aid 1 :naccounts
        0.000513        \setrandom bid 1 :nbranches
        0.000580        \setrandom tid 1 :ntellers
        0.000522        \setrandom delta -5000 5000
        0.604671        BEGIN;
        1.480723        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        0.401148        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        104.713566      UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        21.562787       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.412209        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        2.243497        END;
4.4 100個session 

超過100個會報錯，因為數據庫當前設置最大session是100 [postgres@localhost  ~]$ nohup pgbench -c 100 -T 20 -r pgbench> file.out  2>&1
[postgres@localhost  ~]$ more file.out 
nohup: ignoring input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 20 s
number of transactions actually processed: 6032                                                      tps = 292.556692 (including connections establishing)                                                tps = 305.595090 (excluding connections establishing)
statement latencies in milliseconds:
        0.004508        \set nbranches 1 * :scale
        0.000787        \set ntellers 10 * :scale
        0.000879        \set naccounts 100000 * :scale
        0.001620        \setrandom aid 1 :naccounts
        0.000485        \setrandom bid 1 :nbranches
        0.000561        \setrandom tid 1 :ntellers
        0.000656        \setrandom delta -5000 5000
        3.660809        BEGIN;
        4.198062        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        1.727076        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        281.955832      UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        27.054125       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.524155        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        2.710619        END;
5.說明 

我們主要關心的是最后的輸出報告中的TPS值，里面有兩個,一個是包含網絡開銷(including),另一個是不包含網絡開銷的 (excluding),這個值是反映的每秒處理的事務數，反過來也可以查出每個事務數所消耗的平均時間，一般認為能將硬件用到極致，速度越快越好。 



參考：http://www.postgresql.org/docs/9.1/static/pgbench.html

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1341845783994.html

PostgreSQL 數據庫服務器

PostgreSQL數據庫壓力測試工具pgbench簡單應用

count

count

count

count

相關經驗

相關資訊

相關文檔

目錄