PostgreSQL数据库提供了一款轻量级的压力测试工具叫pgbench,其实就是一个编译好后的扩展性的可执行文件。介绍如下。 环境： Cen
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 pgbench2.参数介绍 [postgres@localhost bin]$ pgbench --helppgbench is a benchmarking tool for PostgreSQL.Usage: pgbench [OPTIONS]... [DBNAME]Initialization options: -iinvokes initialization mode -F NUMfill factor -s NUMscaling factorBenchmarking options: -c NUMnumber of concurrent database clients (default: 1) -Cestablish new connection for each transaction -D VARNAME=VALUEdefine variable for use by custom script -f FILENAME read transaction script from FILENAME -j NUMnumber of threads (default: 1) -lwrite transaction times to log file -M {simple|extended|prepared}protocol for submitting queries to server (default: simple) -ndo not run VACUUM before tests -Ndo not update tables "pgbench_tellers" and "pgbench_branches" -rreport average latency per command -s NUMreport this scale factor in output -Sperform SELECT-only transactions -t NUMnumber of transactions each client runs (default: 10) -T NUMduration of benchmark test in seconds -vvacuum all four standard tables before testsCommon options: -dprint debugging output -h HOSTNAME database server host or socket directory -p PORTdatabase server port number -U USERNAME connect as specified database user --helpshow 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_accountsTable "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_branchesTable "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_historyTable "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_tellersTable "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.不要在生产的库上做，新建一个测试库，当生产上有同名的测试表时将被重置