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@localhostpgbench]$ lltotal 164-rw-r--r--. 1 postgres postgres 538 Dec12011 Makefile-rwxrwxr-x. 1 postgres postgres 50203 Apr 26 23:50 pgbench-rw-r--r--. 1 postgres postgres 61154 Dec12011 pgbench.c-rw-rw-r--. 1 postgres postgres 47920 Apr 26 23:50 pgbench.o[postgres@localhostpgbench]$make all[postgres@localhostpgbench]$make install安装完毕以后可以在bin文件夹下看到新生成的pgbench文件 [postgres@localhostbin]$ ll $PGHOME/bin pgbench-rwxr-xr-x. 1 postgres postgres 50203 Jul8 20:28 pgbench2.参数介绍 [postgres@localhostbin]$ 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 FILENAMEread 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 HOSTNAMEdatabase server host or socket directory-p PORTdatabase server port number-U USERNAMEconnect as specified database user--help show this help, then exit--versionoutput 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_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.不要在生产的库上做，新建一个测试库，当生产上有同名的测试表时将被重置

轻量级数据库比较：SQLite、H2 和 MySQL EmbeddedPL/SQL访问64位Oracle的解决办法相关资讯 PostgreSQL PostgreSQL教程

Ubuntu 16.04 下安装 PostgreSQL （08月14日）
PostgreSQL 发布全系安全更新（02月12日）
使用pg_basebackup搭建PostgreSQL （12/30/2015 09:00:29）

Linux下RPM包方式安装PostgreSQL （03月04日）
PostgreSQL9.5新特性之行级安全性（01月19日）
利用pgpool实现PostgreSQL的高可用（12/30/2015 08:54:36）

本文评论查看全部评论（0）