Thread: Some minor changes to pgbench
2006/08/22
* New schema contributed by Joshua Drake
* The schema now uses foreign keys to more accurately reflect a finacial DDL
* The history table now has a primary key that uses a serial
* The respective balance columns have been increased to int8 to deal
with larger values
* Initalization will be done in a new schema/namespace, pgbench will
exit if this schema/namespace exists
* The new DDL should allow both Mammoth Replicator and Slony to be
tested using pgbench (at least basic replication)
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
*** pgbench.c 2006-08-22 20:14:54.525176500 -0700
--- pgbench_new.c 2006-08-22 21:50:40.072250750 -0700
***************
*** 136,146 ****
static char *tpc_b = {
"\\set nbranches :tps\n"
"\\set ntellers 10 * :tps\n"
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
"\\setrandom bid 1 :nbranches\n"
"\\setrandom tid 1 :ntellers\n"
"\\setrandom delta -5000 5000\n"
"BEGIN;\n"
"UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
--- 136,147 ----
static char *tpc_b = {
"\\set nbranches :tps\n"
"\\set ntellers 10 * :tps\n"
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
"\\setrandom bid 1 :nbranches\n"
"\\setrandom tid 1 :ntellers\n"
"\\setrandom delta -5000 5000\n"
+ "SET SEARCH_PATH = pgbench;\n"
"BEGIN;\n"
"UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
***************
*** 154,164 ****
static char *simple_update = {
"\\set nbranches :tps\n"
"\\set ntellers 10 * :tps\n"
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
"\\setrandom bid 1 :nbranches\n"
"\\setrandom tid 1 :ntellers\n"
"\\setrandom delta -5000 5000\n"
"BEGIN;\n"
"UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
--- 155,166 ----
static char *simple_update = {
"\\set nbranches :tps\n"
"\\set ntellers 10 * :tps\n"
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
"\\setrandom bid 1 :nbranches\n"
"\\setrandom tid 1 :ntellers\n"
"\\setrandom delta -5000 5000\n"
+ "SET SEARCH_PATH = pgbench;\n"
"BEGIN;\n"
"UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
***************
*** 168,175 ****
/* -S case */
static char *select_only = {
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
};
--- 170,178 ----
/* -S case */
static char *select_only = {
! "\\set naccounts 100000 * :tps\n"
"\\setrandom aid 1 :naccounts\n"
+ "SET SEARCH_PATH = pgbench;\n"
"SELECT abalance FROM accounts WHERE aid = :aid;\n"
};
***************
*** 215,221 ****
return (NULL);
}
! res = PQexec(con, "SET search_path = public");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "%s", PQerrorMessage(con));
--- 218,224 ----
return (NULL);
}
! res = PQexec(con, "SET search_path = pgbench");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "%s", PQerrorMessage(con));
***************
*** 715,743 ****
PGconn *con;
PGresult *res;
static char *DDLs[] = {
"drop table branches",
! "create table branches(bid int not null,bbalance int,filler char(88))",
"drop table tellers",
! "create table tellers(tid int not null,bid int,tbalance int,filler char(84))",
"drop table accounts",
! "create table accounts(aid int not null,bid int,abalance int,filler char(84))",
"drop table history",
! "create table history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))"};
! static char *DDLAFTERs[] = {
! "alter table branches add primary key (bid)",
! "alter table tellers add primary key (tid)",
! "alter table accounts add primary key (aid)"};
!
char sql[256];
int i;
!
if ((con = doConnect()) == NULL)
exit(1);
for (i = 0; i < (sizeof(DDLs) / sizeof(char *)); i++)
! {
res = PQexec(con, DDLs[i]);
if (strncmp(DDLs[i], "drop", 4) && PQresultStatus(res) != PGRES_COMMAND_OK)
{
--- 718,752 ----
PGconn *con;
PGresult *res;
static char *DDLs[] = {
+ "drop schema pgbench cascade",
+ "create schema pgbench",
+ "set search_path = pgbench",
"drop table branches",
! "create table branches(bid int not null primary key,bbalance int8,filler char(88))",
"drop table tellers",
! "create table tellers(tid int not null primary key,bid int references branches(bid),tbalance int8,filler
char(84))",
"drop table accounts",
! "create table accounts(aid int not null primary key,bid int references branches(bid),abalance int8,filler
char(84))",
"drop table history",
! "create table history(hid serial primary key, tid int references tellers(tid),bid int references branches
(bid),aidint references accounts (aid),delta int,mtime timestamp,filler char(22))"};
char sql[256];
int i;
!
if ((con = doConnect()) == NULL)
exit(1);
+ /* Let's make sure we are not going to blow anything away */
+
+ res = PQexec(con, "select nspname from pg_namespace where nspname = 'pgbench'");
+ if (PQntuples(res) != 0)
+ {
+ fprintf(stderr, "pgbench schema already exists, exiting...\n");
+ exit(1);
+ }
for (i = 0; i < (sizeof(DDLs) / sizeof(char *)); i++)
! {
res = PQexec(con, DDLs[i]);
if (strncmp(DDLs[i], "drop", 4) && PQresultStatus(res) != PGRES_COMMAND_OK)
{
***************
*** 848,867 ****
#endif /* NOT_USED */
}
}
- fprintf(stderr, "set primary key...\n");
- for (i = 0; i < (sizeof(DDLAFTERs) / sizeof(char *)); i++)
- {
- res = PQexec(con, DDLAFTERs[i]);
- if (PQresultStatus(res) != PGRES_COMMAND_OK)
- {
- fprintf(stderr, "%s", PQerrorMessage(con));
- exit(1);
- }
- PQclear(res);
- }
-
/* vacuum */
! fprintf(stderr, "vacuum...");
res = PQexec(con, "vacuum analyze");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
--- 857,864 ----
#endif /* NOT_USED */
}
}
/* vacuum */
! fprintf(stderr, "vacuum analyze...");
res = PQexec(con, "vacuum analyze");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
*** README.pgbench 2006-08-22 22:06:04.810043250 -0700
--- README.pgbench.new 2006-08-22 22:02:32.844796250 -0700
***************
*** 43,52 ****
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 careful if you have tables having same
! names. Default test data contains:
table # of tuples
-------------------------
--- 43,53 ----
pgbench -i <dbname>
! where <dbname> is the name of database. pgbench will create a new
! schema named pgbench within your database to create its tables. If
! pgbench detects that the schema alreday exists it will exit.
! pgbench uses four tables accounts, branches, history and tellers.
! Default test data contains:
table # of tuples
-------------------------
***************
*** 235,240 ****
--- 236,253 ----
o History
+ 2006/08/22
+ * New schema contributed by Joshua Drake
+
+ * The schema now uses foreign keys to more accurately reflect a finacial DDL
+ * The history table now has a primary key that uses a serial
+ * The respective balance columns have been increased to int8 to deal with
+ larger values
+ * Initalization will be done in a new schema/namespace, pgbench will exit
+ if this schema/namespace exists
+ * The new DDL should allow both Mammoth Replicator and Slony to be tested using
+ pgbench (at least basic replication)
+
2006/07/26
* New features contributed by Tomoaki Sato.
"Joshua D. Drake" <jd@commandprompt.com> writes:
> * The schema now uses foreign keys to more accurately reflect a finacial DDL
Addition of foreign key checking will certainly impact performance
significantly.
> * The history table now has a primary key that uses a serial
Ditto.
> * The respective balance columns have been increased to int8 to deal
> with larger values
Ditto.
> * Initalization will be done in a new schema/namespace, pgbench will
> exit if this schema/namespace exists
OK, maybe that doesn't matter.
> * The new DDL should allow both Mammoth Replicator and Slony to be
> tested using pgbench (at least basic replication)
Erm ... exactly why couldn't you do that before?
pgbench doesn't have all that many things to recommend it, but what
it does have is that it's been a stable testbed across quite a few
PG releases. Arbitrarily whacking around the tested functionality
will destroy that continuity. I fell into this trap before myself
... I have a local copy of pgbench that produces TPS numbers quite
a lot better than the standard pgbench, against exactly the same
server. What's wrong with that picture?
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> * The schema now uses foreign keys to more accurately reflect a finacial DDL
>
> Addition of foreign key checking will certainly impact performance
> significantly.
That is kind of the point. Without foreign keys it is a flawed test
because you wouldn't be running in production without them and thus you
can't bench without them.
>
>> * The history table now has a primary key that uses a serial
>
> Ditto.
Again, part of the point :)
>
>> * The respective balance columns have been increased to int8 to deal
>> with larger values
>
> Ditto.
This was done because you can easily break pgbench without the increase
in data type.
pgbench -c 850 -t 1000 pgbench
gave a stream of errors like this before ending:
Client 18 aborted in state 8: ERROR: integer out of range
Client 429 aborted in state 8: ERROR: integer out of range
Client 168 aborted in state 8: ERROR: integer out of range
PG error log showed:
2006-08-22 15:45:19 PDT-[local]STATEMENT: UPDATE branches SET bbalance
= bbalance + 4209228 WHERE bid = 679;
2006-08-22 15:45:19 PDT-[local]ERROR: integer out of range
>> * Initalization will be done in a new schema/namespace, pgbench will
>> exit if this schema/namespace exists
>
> OK, maybe that doesn't matter.
Yeah I did it just so we wouldn't stomp on somebody on accident.
>
>> * The new DDL should allow both Mammoth Replicator and Slony to be
>> tested using pgbench (at least basic replication)
>
> Erm ... exactly why couldn't you do that before?
history was missing a primary key. It could be done before. I just
removed a step in getting it to work.
> pgbench doesn't have all that many things to recommend it, but what
> it does have is that it's been a stable testbed across quite a few
> PG releases. Arbitrarily whacking around the tested functionality
> will destroy that continuity.
Well to be fair, I wasn't doing it arbitrarily. I had a specific purpose
which was to have it use a schema that would be closer to a production
schema, without breaking existing behavior.
This patch does that :)
> I fell into this trap before myself
> ... I have a local copy of pgbench that produces TPS numbers quite
> a lot better than the standard pgbench, against exactly the same
> server. What's wrong with that picture?
Well I think we all agree that some of the behavior of pgbench has been
weird.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> Addition of foreign key checking will certainly impact performance
>> significantly.
> That is kind of the point. Without foreign keys it is a flawed test
> because you wouldn't be running in production without them and thus you
> can't bench without them.
pgbench is not about reality, though. If we can't rely on it to give
consistent results across versions then I don't think it's useful at all.
There are many other benchmarks you can run that do speak to reality
(eg OSDL's work).
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Tom Lane wrote:
>>> Addition of foreign key checking will certainly impact performance
>>> significantly.
>
>> That is kind of the point. Without foreign keys it is a flawed test
>> because you wouldn't be running in production without them and thus you
>> can't bench without them.
>
> pgbench is not about reality, though. If we can't rely on it to give
> consistent results across versions then I don't think it's useful at all.
> There are many other benchmarks you can run that do speak to reality
> (eg OSDL's work).
Would it be worthwhile to add a switch so that the foreign key test is
only used "if" they use the switch in conjunction with a -i?
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Would it be worthwhile to add a switch so that the foreign key test is
> only used "if" they use the switch in conjunction with a -i?
I wouldn't object to providing that as a (non default) option.
The int8 change should be unnecessary in view of Tatsuo's recent fix
to make the random deltas symmetrical about zero. I would counsel
against making the other changes either, as they seem to accomplish
little except make the comparability of results more doubtful.
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Would it be worthwhile to add a switch so that the foreign key test is
>> only used "if" they use the switch in conjunction with a -i?
>
> I wouldn't object to providing that as a (non default) option.
O.k. I will take a look at what that would take..
>
> The int8 change should be unnecessary in view of Tatsuo's recent fix
> to make the random deltas symmetrical about zero.
O.k. that may be the case, my testing was with the 8.1 version pgbench.
I will verify with -HEAD before I change the int8.
> I would counsel
> against making the other changes either, as they seem to accomplish
> little except make the comparability of results more doubtful.
I am not interested in really changing the overall internals. I just
wanted the foreign key option.
Sincerely,
Joshua D. Drake
>
> 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
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/