注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

使用 pgbench 进行数据库压力测试  

2011-08-20 10:39:09|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

          
       pgbench 是对 PostgreSQL 进行压力测试的一款简单程序, SQL 命令可以在一个连接中顺序地
执行,通常会开多个数据库 Session, 并且在测试最后形成测试报告,得出每秒平均事务数,pgbench
可以测试 select,update,insert,delete 命令,用户可以编写自己的脚本进行测试,下面是 pgbench
的简单使用过程。


--1 初始化
语法: pgbench -i [ other-options ] dbname
[postgres@pg1 bin]$ pgbench -i  -s 10 skytf  -U skytf
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...


--2 数据情况
table                   # of rows
---------------------------------
pgbench_branches        10
pgbench_tellers         100
pgbench_accounts        1000000
pgbench_history         0


--3 测试的事务脚本
\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom delta -5000 5000
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers 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;

    备注:上面是 pgbench 自带的测试脚本,用户也可以自己编写测试脚本。
 

--4 压力测试
  
       测试服务器配置: 8核 16G
       数据量:   pgbench_accounts  120 M , 另外两张表比较小。

4.1 测试一 ( 开一个session )
nohup pgbench -c 1 -T 30 -d skytf -U skytf -f script_1.sql   >  script_1.out &

pghost:  pgport: 1921 nclients: 1 duration: 30 dbName: skytf
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 30966
tps = 1032.164322 (including connections establishing)
tps = 1032.259389 (excluding connections establishing)


4.2 测试二  ( 开 10 个session )
nohup pgbench -c 10 -T 30 -d skytf -U skytf -f script_1.sql   >  script_1.out &

pghost:  pgport: 1921 nclients: 10 duration: 30 dbName: skytf
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 30 s
number of transactions actually processed: 78146
tps = 2604.131434 (including connections establishing)
tps = 2606.302653 (excluding connections establishing)


4.3 测试三 ( 增加 -j 参数 )
nohup pgbench -c 10 -T 30 -j 5 -d skytf -U skytf -f script_1.sql   >  script_1.out &

pghost:  pgport: 1921 nclients: 10 duration: 30 dbName: skytf
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 5
duration: 30 s
number of transactions actually processed: 67551
tps = 2251.026043 (including connections establishing)
tps = 2251.931874 (excluding connections establishing)


4.4 测试四 ( 开 50 个session )
nohup pgbench -c 50 -T 30 -d skytf -U skytf -f script_1.sql   >  script_1.out &

pghost:  pgport: 1921 nclients: 50 duration: 30 dbName: skytf
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 30 s
number of transactions actually processed: 49133
tps = 1635.045134 (including connections establishing)
tps = 1641.632876 (excluding connections establishing)

  备注:测试二的 tps 为 2604, 为测试的最大事务值, 其中每个事务中
有一个 select,一个 insert, 3 个 update 语句。
 
tps =  2604 /s   平均处理时间 0.38  毫秒 (包含网络开销) 
QPS =  13020/s   平均处理时间 0.07  毫秒 (包含网络开销)

 

--5 总结

    pgbench 的自带脚本在测试过程中,发现了有 updae waiting 的情况,说明对测试结果是有影响的;
在生产的实际测试过程需要根据实际情况进行测试。

 

附一: pgbench 初始化参数
pgbench accepts the following command-line initialization arguments:

-F fillfactor
Create the pgbench_accounts, pgbench_tellers and pgbench_branches tables with the given fillfactor. Default is 100.

-i
Required to invoke initialization mode.

-s scale_factor
Multiply the number of rows generated by the scale factor. For example, -s 100 will create 10,000,000 rows in the pgbench_accounts table. Default is 1.


附二: pgbench 压力测试参数
pgbench accepts the following command-line benchmarking arguments:

-c clients
Number of clients simulated, that is, number of concurrent database sessions. Default is 1.

-C
Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead.

-d
Print debugging output.

-D varname=value
Define a variable for use by a custom script (see below). Multiple -D options are allowed.

-f filename
Read transaction script from filename. See below for details. -N, -S, and -f are mutually exclusive.

-j threads
Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. The number of clients must be a multiple of the number of threads, since each thread is given the same number of client sessions to manage. Default is 1.

-l
Write the time taken by each transaction to a log file. See below for details.

-M querymode
Protocol to use for submitting queries to the server:

simple: use simple query protocol.

extended: use extended query protocol.

prepared: use extended query protocol with prepared statements.

The default is simple query protocol. (See Chapter 46 for more information.)

-n
Perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include the standard tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers.

-N
Do not update pgbench_tellers and pgbench_branches. This will avoid update contention on these tables, but it makes the test case even less like TPC-B.

-s scale_factor
Report the specified scale factor in pgbench is output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of rows in the pgbench_branches table. However, when testing custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used.

-S
Perform select-only transactions instead of TPC-B-like test.

-t transactions
Number of transactions each client runs. Default is 10.

-T seconds
Run the test for this many seconds, rather than a fixed number of transactions per client. -t and -T are mutually exclusive.

-v
Vacuum all four standard tables before running the test. With neither -n nor -v, pgbench will vacuum the pgbench_tellers and pgbench_branches tables, and will truncate pgbench_history.

  评论这张
 
阅读(30756)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016