Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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 10
will set a random number into variable aid between 1 and 1.
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 10
\setrandom bid 1 1
\setrandom tid 1 10
\setrandom delta 1 1
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
*** pgbench/pgbench.c 2004-11-09 15:09:31.0 +0900
--- pgbench-new/pgbench.c 2005-09-27 14:31:34.0 +0900
***
*** 41,46
--- 41,49
#include sys/resource.h
#endif /* ! WIN32 */
+ #include ctype.h
+ #include search.h
+
extern char *optarg;
extern intoptind;
***
*** 72,77
--- 75,83
#define ntellers 10
#define naccounts 10
+ #define SQL_COMMAND 1
+ #define META_COMMAND 2
+
FILE *LOGFILE = NULL;
bool use_log;/* log transaction latencies to
a file */
***
*** 91,96
--- 97,108
typedef struct
{
+ char *name;
+ char *value;
+ } Variable;
+
+ typedef struct
+ {
PGconn *con;/* connection handle to DB */
int id; /* client No. */
int state; /* state No. */
***
*** 103,115
int tid;/* teller id for this
transaction */
int delta;
int abalance;
struct timeval txn_begin; /* used for measuring latencies */
} CState;
static void
usage(void)
{
! fprintf(stderr, usage: pgbench [-h hostname][-p port][-c nclients][-t
ntransactions][-s scaling_factor][-n][-C][-v][-S][-N][-l][-U login][-P
password][-d][dbname]\n);
fprintf(stderr, (initialize mode): pgbench -i [-h hostname][-p
port][-s scaling_factor][-U login][-P password][-d][dbname]\n);
}
--- 115,137
int tid;/* teller id for this
transaction */
int delta;
int abalance;
+ void *variables;
struct timeval txn_begin; /* used for measuring latencies */
} CState;
+ typedef struct
+ {
+ int type;
+ int argc;
+ char **argv;
+ } Command;
+
+ Command **commands = NULL;
+
static void
usage(void)
{
! fprintf(stderr, usage: pgbench [-h hostname][-p port][-c nclients][-t
ntransactions][-s scaling_factor][-n][-C][-v][-S][-N][-f filename][-l][-U
login][-P password][-d][dbname]\n);
fprintf(stderr, (initialize mode): pgbench -i [-h hostname][-p
port][-s scaling_factor][-U login][-P password][-d][dbname]\n);
}
***
*** 190,195
--- 212,326
return (0); /* OK */
}
+ static int
+ compareVariables(const void *v1, const void *v2)
+ {
+ return strcmp(((Variable *)v1)-name, ((Variable *)v2)-name);
+ }
+
+ static char *
+ getVariable(CState * st, char *name)
+ {
+ Variablekey = { name }, *var;
+
+ var = tfind(key, st-variables, compareVariables);
+ if (var != NULL)
+ return (*(Variable **)var)-value;
+ else
+ return NULL;
+ }
+
+ static int
+ putVariable(CState * st, char *name, char *value)
+ {
+ Variablekey = { name }, *var;
+
+ var = tfind(key, st-variables, compareVariables);
+ if (var == NULL)
+ {
+ if ((var = malloc(sizeof(Variable))) == NULL)
+ return false;
+
+ var-name = NULL;
+ var-value = NULL;
+
+ if ((var-name = strdup(name)) == NULL
+ || (var-value = strdup(value)) == NULL
+ || tsearch(var, st-variables, compareVariables) ==
NULL)
+
