From 6917ae1b217c15bca291face0da4eb2b57a2e919 Mon Sep 17 00:00:00 2001 From: tgl Date: Thu, 7 May 2009 22:01:18 +0000 Subject: [PATCH] Change pgbench to use the table names pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers, rather than just accounts, branches, history, and tellers. This is to prevent accidental conflicts with real application tables, as has been reported to happen at least once. Also remove the automatic "SET search_path = public" that it did at startup, as this seems to restrict testing flexibility without actually buying much. Per proposal by Joshua Drake and ensuing discussion. Joshua Drake and Tom Lane --- contrib/pgbench/pgbench.c | 102 +++++++++++++++++++++++----------------------- doc/src/sgml/pgbench.sgml | 79 ++++++++++++++++++----------------- 2 files changed, 92 insertions(+), 89 deletions(-) diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c index ad20cacd2d..5fb561b916 100644 --- a/contrib/pgbench/pgbench.c +++ b/contrib/pgbench/pgbench.c @@ -76,8 +76,8 @@ int nxacts = 0; /* number of transactions per client */ int duration = 0; /* duration in seconds */ /* - * scaling factor. for example, scale = 10 will make 1000000 tuples of - * accounts table. + * scaling factor. for example, scale = 10 will make 1000000 tuples in + * pgbench_accounts table. */ int scale = 1; @@ -181,11 +181,11 @@ static char *tpc_b = { "\\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" - "UPDATE tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n" - "UPDATE branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n" - "INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n" + "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n" + "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n" + "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n" + "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n" + "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n" "END;\n" }; @@ -199,9 +199,9 @@ static char *simple_update = { "\\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" - "INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n" + "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n" + "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n" + "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n" "END;\n" }; @@ -209,7 +209,7 @@ static char *simple_update = { static char *select_only = { "\\set naccounts 100000 * :scale\n" "\\setrandom aid 1 :naccounts\n" - "SELECT abalance FROM accounts WHERE aid = :aid;\n" + "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n" }; /* Connection overhead time */ @@ -269,8 +269,8 @@ usage(const char *progname) " -M {simple|extended|prepared}\n" " protocol for submitting queries to server (default: simple)\n" " -n do not run VACUUM before tests\n" - " -N do not update tables \"tellers\" and \"branches\"\n" - " -s NUM report scale factor in output\n" + " -N do not update tables \"pgbench_tellers\" and \"pgbench_branches\"\n" + " -s NUM report this scale factor in output\n" " -S perform SELECT-only transactions\n" " -t NUM number of transactions each client runs (default: 10)\n" " -T NUM duration of benchmark test in seconds\n" @@ -357,8 +357,6 @@ doConnect(void) return NULL; } - executeStatement(conn, "SET search_path = public"); - return conn; } @@ -1001,8 +999,6 @@ disconnect_all(CState * state) static void init(void) { - PGconn *con; - PGresult *res; /* * Note: TPC-B requires at least 100 bytes per row, and the "filler" * fields in these table declarations were intended to comply with that. @@ -1014,22 +1010,24 @@ init(void) * behavior. */ static char *DDLs[] = { - "drop table if exists branches", - "create table branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=%d)", - "drop table if exists tellers", - "create table tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=%d)", - "drop table if exists accounts", - "create table accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=%d)", - "drop table if exists history", - "create table history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))"}; + "drop table if exists pgbench_branches", + "create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=%d)", + "drop table if exists pgbench_tellers", + "create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=%d)", + "drop table if exists pgbench_accounts", + "create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=%d)", + "drop table if exists pgbench_history", + "create table pgbench_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)"}; - + "alter table pgbench_branches add primary key (bid)", + "alter table pgbench_tellers add primary key (tid)", + "alter table pgbench_accounts add primary key (aid)" + }; + PGconn *con; + PGresult *res; char sql[256]; - int i; if ((con = doConnect()) == NULL) @@ -1040,9 +1038,9 @@ init(void) /* * set fillfactor for branches, tellers and accounts tables */ - if ((strstr(DDLs[i], "create table branches") == DDLs[i]) || - (strstr(DDLs[i], "create table tellers") == DDLs[i]) || - (strstr(DDLs[i], "create table accounts") == DDLs[i])) + if ((strstr(DDLs[i], "create table pgbench_branches") == DDLs[i]) || + (strstr(DDLs[i], "create table pgbench_tellers") == DDLs[i]) || + (strstr(DDLs[i], "create table pgbench_accounts") == DDLs[i])) { char ddl_stmt[128]; @@ -1058,13 +1056,13 @@ init(void) for (i = 0; i < nbranches * scale; i++) { - snprintf(sql, 256, "insert into branches(bid,bbalance) values(%d,0)", i + 1); + snprintf(sql, 256, "insert into pgbench_branches(bid,bbalance) values(%d,0)", i + 1); executeStatement(con, sql); } for (i = 0; i < ntellers * scale; i++) { - snprintf(sql, 256, "insert into tellers(tid,bid,tbalance) values (%d,%d,0)" + snprintf(sql, 256, "insert into pgbench_tellers(tid,bid,tbalance) values (%d,%d,0)" ,i + 1, i / ntellers + 1); executeStatement(con, sql); } @@ -1072,14 +1070,14 @@ init(void) executeStatement(con, "commit"); /* - * fill the accounts table with some data + * fill the pgbench_accounts table with some data */ fprintf(stderr, "creating tables...\n"); executeStatement(con, "begin"); - executeStatement(con, "truncate accounts"); + executeStatement(con, "truncate pgbench_accounts"); - res = PQexec(con, "copy accounts from stdin"); + res = PQexec(con, "copy pgbench_accounts from stdin"); if (PQresultStatus(res) != PGRES_COPY_IN) { fprintf(stderr, "%s", PQerrorMessage(con)); @@ -1122,10 +1120,10 @@ init(void) /* vacuum */ fprintf(stderr, "vacuum..."); - executeStatement(con, "vacuum analyze branches"); - executeStatement(con, "vacuum analyze tellers"); - executeStatement(con, "vacuum analyze accounts"); - executeStatement(con, "vacuum analyze history"); + executeStatement(con, "vacuum analyze pgbench_branches"); + executeStatement(con, "vacuum analyze pgbench_tellers"); + executeStatement(con, "vacuum analyze pgbench_accounts"); + executeStatement(con, "vacuum analyze pgbench_history"); fprintf(stderr, "done.\n"); PQfinish(con); @@ -1466,7 +1464,7 @@ printResults( if (ttype == 0) s = "TPC-B (sort of)"; else if (ttype == 2) - s = "Update only accounts"; + s = "Update only pgbench_accounts"; else if (ttype == 1) s = "SELECT only"; else @@ -1811,9 +1809,9 @@ main(int argc, char **argv) { /* * get the scaling factor that should be same as count(*) from - * branches if this is not a custom query + * pgbench_branches if this is not a custom query */ - res = PQexec(con, "select count(*) from branches"); + res = PQexec(con, "select count(*) from pgbench_branches"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "%s", PQerrorMessage(con)); @@ -1822,7 +1820,7 @@ main(int argc, char **argv) scale = atoi(PQgetvalue(res, 0, 0)); if (scale < 0) { - fprintf(stderr, "count(*) from branches invalid (%d)\n", scale); + fprintf(stderr, "count(*) from pgbench_branches invalid (%d)\n", scale); exit(1); } PQclear(res); @@ -1830,7 +1828,7 @@ main(int argc, char **argv) /* warn if we override user-given -s switch */ if (scale_given) fprintf(stderr, - "Scale option ignored, using branches table count = %d\n", + "Scale option ignored, using pgbench_branches table count = %d\n", scale); } @@ -1854,15 +1852,15 @@ main(int argc, char **argv) if (!is_no_vacuum) { fprintf(stderr, "starting vacuum..."); - executeStatement(con, "vacuum branches"); - executeStatement(con, "vacuum tellers"); - executeStatement(con, "truncate history"); + executeStatement(con, "vacuum pgbench_branches"); + executeStatement(con, "vacuum pgbench_tellers"); + executeStatement(con, "truncate pgbench_history"); fprintf(stderr, "end.\n"); if (do_vacuum_accounts) { - fprintf(stderr, "starting vacuum accounts..."); - executeStatement(con, "vacuum analyze accounts"); + fprintf(stderr, "starting vacuum pgbench_accounts..."); + executeStatement(con, "vacuum analyze pgbench_accounts"); fprintf(stderr, "end.\n"); } } diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml index a3bed1dab7..a105452e92 100644 --- a/doc/src/sgml/pgbench.sgml +++ b/doc/src/sgml/pgbench.sgml @@ -25,6 +25,7 @@ transaction type: TPC-B (sort of) scaling factor: 10 +query mode: simple number of clients: 10 number of transactions per client: 1000 number of transactions actually processed: 10000/10000 @@ -32,7 +33,7 @@ tps = 85.184871 (including connections establishing) tps = 85.296346 (excluding connections establishing) - The first four lines just report some of the most important parameter + The first five lines report some of the most important parameter settings. The next line reports the number of transactions completed and intended (the latter being just the product of number of clients and number of transactions per client); these will be equal unless the run @@ -63,9 +64,10 @@ pgbench -i other-options dbn - pgbench -i creates four tables accounts, - branches, history, and - tellers, destroying any existing tables of these names. + pgbench -i creates four tables pgbench_accounts, + pgbench_branches, pgbench_history, and + pgbench_tellers, + destroying any existing tables of these names. Be very careful to use another database if you have tables having these names! @@ -76,12 +78,12 @@ pgbench -i other-options dbn contain this many rows: -table # of rows -------------------------- -branches 1 -tellers 10 -accounts 100000 -history 0 +table # of rows +--------------------------------- +pgbench_branches 1 +pgbench_tellers 10 +pgbench_accounts 100000 +pgbench_history 0 You can (and, for most purposes, probably should) increase the number @@ -134,15 +136,16 @@ pgbench options dbname -s scale_factor Multiply the number of rows generated by the scale factor. - For example, -s 100 will imply 10,000,000 rows - in the accounts table. Default is 1. + For example, -s 100 will create 10,000,000 rows + in the pgbench_accounts table. Default is 1. -F fillfactor - Create the accounts, tellers and - branches tables with the given fillfactor. + Create the pgbench_accounts, + pgbench_tellers and + pgbench_branches tables with the given fillfactor. Default is 100. @@ -177,14 +180,15 @@ pgbench options dbname -T seconds - Duration of benchmark test in seconds. -t and + Run the test for this many seconds, rather than a fixed number of + transactions per client. -t and -T are mutually exclusive. -M querymode - Protocol to use for submitting queries for the server: + Protocol to use for submitting queries to the server: simple: use simple query protocol. @@ -203,7 +207,8 @@ pgbench options dbname -N - Do not update tellers and branches. + Do not update pgbench_tellers and + pgbench_branches. This will avoid update contention on these tables, but it makes the test case even less like TPC-B. @@ -226,21 +231,21 @@ pgbench options dbname -n - No vacuuming is performed before running the test. + Perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include - the standard tables accounts, - branches, history, and - tellers. + the standard tables pgbench_accounts, + pgbench_branches, pgbench_history, and + pgbench_tellers. -v Vacuum all four standard tables before running the test. - With neither -n nor -v, pgbench will vacuum - tellers and branches tables, and - will remove all entries in history. + With neither -n nor -v, pgbench will vacuum the + pgbench_tellers and pgbench_branches + tables, and will truncate pgbench_history. @@ -271,7 +276,7 @@ pgbench options dbname Report the specified scale factor in pgbench's output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of - rows in the branches table. However, when testing + rows in the pgbench_branches table. However, when testing custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used. @@ -323,11 +328,11 @@ pgbench options dbname 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, CURRENT_TIMESTAMP); + UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; + SELECT abalance FROM pgbench_accounts WHERE aid = :aid; + UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; + UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; + INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END; @@ -410,7 +415,7 @@ pgbench options dbname Sets variable varname to a random integer value - between the limits min and max. + between the limits min and max inclusive. Each limit can be either an integer constant or a :variablename reference to a variable having an integer value. @@ -463,11 +468,11 @@ pgbench options dbname \setrandom tid 1 :ntellers \setrandom delta -5000 5000 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, CURRENT_TIMESTAMP); +UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; +SELECT abalance FROM pgbench_accounts WHERE aid = :aid; +UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; +UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; +INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END; @@ -537,7 +542,7 @@ END; (-s) should be at least as large as the largest number of clients you intend to test (-c); else you'll mostly be measuring update contention. There are only -s rows in - the branches table, and every transaction wants to + the pgbench_branches table, and every transaction wants to update one of them, so -c values in excess of -s will undoubtedly result in lots of transactions blocked waiting for other transactions. -- 2.11.4.GIT