enhanced pgbench
Hi all,
We have enhanced pgbench so that it accepts a series of SQL commands
in a file(see attached patches against 8.0.3). This would make it
possible to test various sets of SQL commands. In the file it is
allowed to use a "meta command". Currently only "\setrandom" meta
command is allowed, which sets specified random number into a
variable. For example,
\setrandom aid 1 100000
will set a random number into variable "aid" between 1 and 10000.
A variable can be reffered to in an SQL command by adding ":" in front
of the the command name.
Here is an example SQL command file.
\setrandom aid 1 100000
\setrandom bid 1 1
\setrandom tid 1 10
\setrandom delta 1 10000
BEGIN
UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid
SELECT abalance FROM accounts WHERE aid = :aid
UPDATE tellers SET tbalance = tbalance + :delta WHERE tid = :tid
UPDATE branches SET bbalance = bbalance + :delta WHERE bid = :bid
INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, 'now')
END
This will execute virtually same SQL commands builtin pgbench.
To use the SQL command file, you can use "-f" option:
pgbench -f /foo/bar/sqlfile
I think the enhanced pgbench is quite usefull and I would like to
include in 8.1. Or should I keep it for 8.2?
--
SRA OSS, Inc. Japan
Tatsuo Ishii
Attachments:
pgbench.patchtext/plain; charset=us-asciiDownload+605-133
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
I think the enhanced pgbench is quite usefull and I would like to
include in 8.1. Or should I keep it for 8.2?
Well, we've traditionally been laxer about contrib than the core
software, so the fact that we're past feature freeze isn't sufficient
reason to say "no". I'm inclined to say "yes" because I think this
might make a handy debugging tool --- for instance, Janning Vygen's
current problem with temp tables might be reproducible using a pgbench
script, without having to write any new software.
Have you thought about ripping out all the original pgbench code and
instead having a "default script" that duplicates the original behavior?
Also it seems like it'd be useful if there could be more than one
script, so as to test situations where the clients aren't all doing
exactly the same thing.
regards, tom lane
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
I think the enhanced pgbench is quite usefull and I would like to
include in 8.1. Or should I keep it for 8.2?Well, we've traditionally been laxer about contrib than the core
software, so the fact that we're past feature freeze isn't sufficient
reason to say "no". I'm inclined to say "yes" because I think this
might make a handy debugging tool --- for instance, Janning Vygen's
current problem with temp tables might be reproducible using a pgbench
script, without having to write any new software.
Ok. I will commit patches and will continue to work on below.
Have you thought about ripping out all the original pgbench code and
instead having a "default script" that duplicates the original behavior?
No, but seems an idea. There are few things I need to think about:
1) There are three pgbench's default behaviors: TPC-B like(default),
-N and -S. So we need three scripts.
2) it requires a location where those scripts reside. $prefix/etc
seems appropriate?
Also it seems like it'd be useful if there could be more than one
script, so as to test situations where the clients aren't all doing
exactly the same thing.
How do you assign different scripts to different clients?
--
SRA OSS, Inc. Japan
Tatsuo Ishii
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
2) it requires a location where those scripts reside. $prefix/etc
seems appropriate?
I was thinking of just embedding the default scripts as constants in
the program text. A little bit ugly but saves a lot of headache with
needing to find installed files.
Also it seems like it'd be useful if there could be more than one
script, so as to test situations where the clients aren't all doing
exactly the same thing.
How do you assign different scripts to different clients?
I'd be happy with either round-robin or random selection of a new script
each time a thread finishes a script.
regards, tom lane
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
2) it requires a location where those scripts reside. $prefix/etc
seems appropriate?I was thinking of just embedding the default scripts as constants in
the program text. A little bit ugly but saves a lot of headache with
needing to find installed files.
I see.
Also it seems like it'd be useful if there could be more than one
script, so as to test situations where the clients aren't all doing
exactly the same thing.How do you assign different scripts to different clients?
I'd be happy with either round-robin or random selection of a new script
each time a thread finishes a script.
Ok.
--
SRA OSS, Inc. Japan
Tatsuo Ishii