vacuumlo -

Remove orphaned large objects

by Peter T Mount <peter@retep.org.uk>

pgbench -

TPC-B like benchmarking tool

by Tatsuo Ishii <t-ishii@sra.co.jp>

SRCDIR= ../../src

include$(SRCDIR)/Makefile.global

CFLAGS:= -I$(LIBPQDIR)$(CFLAGS)

TARGET = pgbench

OBJS = pgbench.o

all::$(TARGET)

$(TARGET):$(OBJS)

$(CC) -o$(TARGET)$(OBJS) -L$(LIBPQDIR) -lpq$(LDFLAGS)

install:$(TARGET)

$(INSTALL)$(INSTL_EXE_OPTS)$(TARGET)$(X)$(BINDIR)/$(TARGET)$(X)

clean:

distclean: clean

pgbench 1.2 README2000/1/15 Tatsuo Ishii (t-ishii@sra.co.jp)

o What is pgbench?

pgbench is a simple program to run a benchmark test sort of

"TPC-B". pgbench is a client application of PostgreSQL and runs

with PostgreSQL only. It performs lots of small and simple

transactions including select/update/insert operations then

calculates number of transactions successfully completed within a

second (transactions per second, tps). Targeting data includes a

table with at least 100k tuples.

Example outputs from pgbench look like:

number of clients: 4

number of transactions per client: 100

number of processed transactions: 400/400

tps = 19.875015(including connections establishing)

tps = 20.098827(excluding connections establishing)

Similar program called "JDBCBench" already exists, but it requires

Java that may not be available on every platform. Moreover some

people concerned about the overhead of Java that might lead

inaccurate results. So I decided to write in pure C, and named

it "pgbench."

o features of pgbench

- pgbench is written in C using libpq only. So it is very portable

and easy to install.

- pgbench can simulate concurrent connections using asynchronous

capability of libpq. No threading is required.

o How to install pgbench

(1) Edit the first line in Makefile

POSTGRESHOME = /usr/local/pgsql

so that it points to the directory where PostgreSQL installed.

(2) Run configure

(3) Run make. You will see an executable file "pgbench" there.

o How to use pgbench?

(1) Initialize database by:

pgbench -i <dbname>

where <dbname> is the name of database. pgbench uses four tables

accounts, branches, history and tellers. These tables will be

destroyed. Be very carefully if you have tables having same

names. Default test data contains:

table# of tuples

-------------------------

branches1

tellers10

accounts100000

history0

You can increase the number of tuples by using -s option. See

below.

(2) Run the benchmark test

pgbench <dbname>

The default configuration is:

number of clients: 1

number of transactions per client: 10

o options

pgbench has number of options.

-h hostname

hostname where the backend is running. If this option

is omitted, pgbench will connect to the localhost via

Unix domain socket.

-p port

the port number that the backend is accepting. default is

5432.

-c number_of_clients

Number of clients simulated. default is 1.

-t number_of_transactions

Number of transactions each client runs. default is 10.

-s scaling_factor

this should be used with -i (initialize) option.

number of tuples generated will be multiple of the

scaling factor. For example, -s 100 will imply 10M

(10,000,000) tuples in the accounts table.

default is 1.

-n

No vacuuming and cleaning the history table prior the

test is performed.

-v

Do vacuuming before testing. This will take some time.

Without both -n and -v pgbench will vacuum tellers and

branches tables only.

-S

Perform select only transactions instead of TPC-B.

-d

debug option.

o What is the "transaction" actually performed in pgbench?

(1) begin;

(2) update accounts set abalance = abalance + :delta where aid = :aid;

(3) select abalance from accounts where aid = :aid;

(4) update tellers set tbalance = tbalance + :delta where tid = :tid;

(5) update branches set bbalance = bbalance + :delta where bid = :bid;

(6) insert into history(tid,bid,aid,delta) values(:tid,:bid,:aid,:delta);

(7) end;

o License?

Basically it is same as BSD license. See pgbench.c for more details.

o History

2000/1/15 pgbench-1.2 contributed to PostgreSQL

* Add -v option

1999/09/29 pgbench-1.1 released

* Apply cygwin patches contributed by Yutaka Tanida

* More robust when backends die

* Add -S option (select only)

1999/09/04 pgbench-1.0 released

pgbench 1.2 README2000/1/15 Tatsuo Ishii (t-ishii@sra.co.jp)

$B"#(Bpgbench $B$H$O!)(B

pgbench $B$O(B TPC-B$B$K;w$?%Y%s%A%^!<%/%F%9%H$r9T$J$&%W%m%0%i%`$G$9!#:#$N$H(B

$B$3$m(B PostgreSQL $B@lMQ$G$9!#(B

pgbench $B$O(B select/update/insert $B$r4^$`%H%i%s%6%/%7%g%s$r<B9T$7!"A4BN$N(B

$B<B9T;~4V$H<B:]$K40N;$7$?%H%i%s%6%/%7%g%s$N?t$+$i(B 1 $BIC4V$K<B9T$G$-$?%H(B

$B%i%s%6%/%7%g%s?t(B (tps) $B$rI=<($7$^$9!#=hM}$NBP>]$H$J$k%F!<%V%k$O%G%U%)(B

$B%k%H$G$O(B 10$BK|%?%W%k$N%G!<%?$r4^$_$^$9!#(B

$B<B:]$NI=<($O0J2<$N$h$&$J46$8$G$9!#(B

number of clients: 4

number of transactions per client: 100

number of processed transactions: 400/400

tps = 19.875015(including connections establishing)

tps = 20.098827(excluding connections establishing)

pgbench $B$O(B JDBCBench $B$H$$$&!"$b$H$b$H$O(B MySQL $BMQ$K=q$+$l$?(B JDBC $BMQ$N%Y(B

$B%s%A%^!<%/%W%m%0%i%`$r;29M$K:n@.$5$l$^$7$?!#(B

$B"#(Bpgbench $B$NFCD'(B

o C $B8@8l$H(B libpq $B$@$1$G=q$+$l$F$$$k$N$G0\?"@-$,9b$/!"4JC1$K%$%s%9%H!<(B

$B%k$G$-$^$9!#(B

o pgbench $B$O(B libpq $B$NHsF14|=hM}5!G=$r;H$C$F%^%k%A%f!<%64D6-$r%7%_%e%l!<(B

$B%H$7$^$9!#MF0W$KF1;~@\B34D6-$r%F%9%H$G$-$^$9!#(B

$B"#(Bpgbench $B$N%$%s%9%H!<%k(B

Makefile$B$N0lHV>e$K$"$k(B

POSTGRESHOME = /usr/local/pgsql

$B$rI,MW$K1~$8$F=$@5$7!"(Bconfigure;make $B$9$k$@$1$G$9!#(B

$B"#(Bpgbench $B$N;H$$J}(B

$B4pK\E*$J;H$$J}$O!"(B

$ pgbench [$B%G!<%?%Y!<%9L>(B]

$B$G$9!#%G!<%?%Y!<%9L>$r>JN,$9$k$H!"%f!<%6L>$HF1$8%G!<%?%Y!<%9$r;XDj$7$?(B

$B$b$N$H$_$J$7$^$9!#%G!<%?%Y!<%9$O8e=R$N(B -i $B%*%W%7%g%s$r;H$C$F$"$i$+$8$a(B

$B=i4|2=$7$F$*$/I,MW$,$"$j$^$9!#(B

pgbench $B$K$O$$$m$$$m$J%*%W%7%g%s$,$"$j$^$9!#(B

-h $B%[%9%HL>(BPostgreSQL$B$N%G!<%?%Y!<%9%G!<%b%s(B postmaster $B$NF0(B

$B$$$F$$$k%[%9%HL>$r;XDj$7$^$9!#>JN,$9$k$H<+%[%9%H$K(B Unix domain

socket $B$G@\B3$7$^$9!#(B

-p $B%]!<%HHV9f(Bpostmaster $B$N;HMQ$9$k%]!<%HHV9f$r;XDj$7$^$9!#>JN,$9$k$H(B 5432

$B$,;XDj$5$l$?$b$N$H$_$J$7$^$9!#(B

-c $B%/%i%$%"%s%H?t(B$BF1;~<B9T%/%i%$%"%s%H?t$r;XDj$7$^$9!#>JN,;~$O(B

1 $B$H$J$j$^$9!#(Bpgbench $B$OF1;~<B9T%/%i%$%"%s%HKh$K(B

$B%U%!%$%k%G%#%9%/%j%W%?$r;HMQ$9$k$N$G!";HMQ2DG=(B

$B%U%!%$%k%G%#%9%/%j%W%??t$r1[$($k%/%i%$%"%s%H?t$O(B

$B;XDj$G$-$^$;$s!#;HMQ2DG=%U%!%$%k%G%#%9%/%j%W%??t(B

$B$O(B limit $B$d(B ulimit $B%3%^%s%I$GCN$k$3$H$,$G$-$^$9!#(B

-t $B%H%i%s%6%/%7%g%s?t(B$B3F%/%i%$%"%s%H$,<B9T$9$k%H%i%s%6%/%7%g%s?t$r(B

$B;XDj$7$^$9!#>JN,;~$O(B 10 $B$H$J$j$^$9!#(B

-s $B%9%1!<%j%s%0%U%!%/%?!<(B

-i $B%*%W%7%g%s$H0l=o$K;HMQ$7$^$9!#(B

$B%9%1!<%j%s%0%U%!%/%?!<$O(B1$B0J>e$N@0?t!#%9%1!<%j%s%0%U%!(B

$B%/%?!<$rJQ$($k$3$H$K$h$j!"%F%9%H$NBP>]$H$J$k%F!<%V%k$N(B

$BBg$-$5$,(B 10$BK|(B x [$B%9%1!<%j%s%0%U%!%/%?!<(B]$B$K$J$j$^$9!#(B

$B%G%U%)%k%H$N%9%1!<%j%s%0%U%!%/%?!<$O(B 1 $B$G$9!#(B

-v$B$3$N%*%W%7%g%s$r;XDj$9$k$H!"%Y%s%A%^!<%/3+;OA0$K(B vacuum $B$H(B

history $B$N%/%j%"$r9T$J$$$^$9!#(B-v $B$H(B -n $B$r>JN,$9$k$H!"(B

$B:G>.8B$N(B vacuum $B$J$I$r9T$$$^$9!#$9$J$o$A!"(Bhistory $B$N:o=|!"(B

$B$H(B history, branches, history $B$N(B vacuum $B$r9T$$$^$9!#(B

$B$3$l$O!"(Bvacuum $B$N;~4V$r:G>.8B$K$7$J$,$i!"%Q%U%)!<%^%s%9$K(B

$B1F6A$9$k%4%_A]=|$r8z2LE*$K9T$$$^$9!#DL>o$O(B -v $B$H(B -n $B$r(B

$B>JN,$9$k$3$H$r$*$9$9$a$7$^$9!#(B

-n$B$3$N%*%W%7%g%s$r;XDj$9$k$H!"%Y%s%A%^!<%/3+;OA0$K(B vacuum $B$H(B

history $B$N%/%j%"$r9T$J$$$^$;$s!#(B

-STPC-B$B$N%H%i%s%6%/%7%g%s$G$O$J$/!"8!:w$N$_$N%H%i%s%6%/%7%g%s$r(B

$B<B9T$7$^$9!#8!:w%9%T!<%I$rB,Dj$7$?$$$H$-$K;H$$$^$9!#(B

-d$B%G%P%C%0%*%W%7%g%s!#MM!9$J>pJs$,I=<($5$l$^$9!#(B

$B"#%G!<%?%Y!<%9$N=i4|2=(B

pgbench $B$G%Y%s%A%^!<%/%F%9%H$r<B;\$9$k$?$a$K$O!"$"$i$+$8$a%G!<%?%Y!<%9(B

$B$r=i4|2=$7!"%F%9%H%G!<%?$r:n$kI,MW$,$"$j$^$9!#(B

$ pgbench -i [$B%G!<%?%Y!<%9L>(B]

$B$3$l$K$h$j0J2<$N%F!<%V%k$,:n$i$l$^$9(B($B%9%1!<%j%s%0%U%!%/%?!<(B == 1 $B$N>l9g(B)$B!#(B

$B!vCm0U!v(B

$BF1$8L>A0$N%F!<%V%k$,$"$k$H:o=|$5$l$F$7$^$&$N$G$4Cm0U2<$5$$!*!*(B

$B%F!<%V%kL>(B$B%?%W%k?t(B

-------------------------

branches1

tellers10

accounts100000

history0

$B%9%1!<%j%s%0%U%!%/%?!<$r(B 10,100,1000 $B$J$I$KJQ99$9$k$H!">e5-%?%W%k?t$O(B

$B$=$l$K1~$8$F(B10$BG\!"(B100$BG\!"(B1000$BG\$K$J$j$^$9!#$?$H$($P!"%9%1!<%j%s%0%U%!(B

$B%/%?!<$r(B 10 $B$H$9$k$H!"(B

$B%F!<%V%kL>(B$B%?%W%k?t(B

-------------------------

branches10

tellers100

accounts1000000

history0

$B$K$J$j$^$9!#(B

$B"#!V%H%i%s%6%/%7%g%s!W$NDj5A(B

pgbench $B$G$O!"0J2<$N%7!<%1%s%9$rA4It40N;$7$F(B1$B%H%i%s%6%/%7%g%s$H?t$($F(B

$B$$$^$9!#(B

(1) begin;

(2) update accounts set abalance = abalance + :delta where aid = :aid;

$B$3$3$G!"(B:delta$B$O(B1$B$+$i(B1000$B$^$G$NCM$r<h$kMp?t!"(B:aid $B$O(B 1$B$+$i(B100000$B$^$G(B

$B$NCM$r<h$kMp?t$G$9!#0J2<!"Mp?t$NCM$O$=$l$>$l$3$N%H%i%s%6%/%7%g%s$N(B

$BCf$G$OF1$8CM$r;H$$$^$9!#(B

(3) select abalance from accounts where aid = :aid;

$B$3$3$G$O(B1$B7o$@$18!:w$5$l$^$9!#(B

(4) update tellers set tbalance = tbalance + :delta where tid = :tid;

$B$3$3$G(B :tid $B$O(B 1$B$+$i(B10$B$N4V$NCM$r$H$kMp?t$G$9!#(B

(5) update branches set bbalance = bbalance + :delta where bid = :bid;

$B$3$3$G(B :bid $B$O(B 1 $B$+$i(B[$B%9%1%j%s%0%U%!%/%?!<(B]$B$N4V$NCM$r<h$kMp?t$G$9!#(B

(6) insert into history(tid,bid,aid,delta) values(:tid,:bid,:aid,:delta);

(7) end;

$B"#:n<T$H%i%$%;%s%9>r7o(B

pgbench $B$O@P0f(B $BC#IW$K$h$C$F=q$+$l$^$7$?!#%i%$%;%s%9>r7o$O(B pgbench.c $B$N(B

$BKAF,$K=q$$$F$"$j$^$9!#$3$N>r7o$r<i$k8B$jL5=~$GMxMQ$7!"$^$?<+M3$K:FG[IU(B

$B$G$-$^$9!#(B

$B"#2~DjMzNr(B

2000/1/15 pgbench-1.2 $B$O(B PostgreSQL $B$K(B contribute $B$5$l$^$7$?!#(B

* -v $B%*%W%7%g%sDI2C(B

1999/09/29 pgbench-1.1 $B%j%j!<%9(B

* $BC+ED$5$s$K$h$k(Bcygwin$BBP1~%Q%C%A<h$j9~$_(B

* $B%P%C%/%(%s%I%/%i%C%7%e;~$NBP1~(B

* -S $B%*%W%7%g%sDI2C(B

1999/09/04 pgbench-1.0 $B%j%j!<%9(B