X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=contrib%2Fpgbench%2Fpgbench.c;h=41312e88c64bb10ccc652992f797bac4d2395d05;hb=49639a7b2c52263f2580463394e08b29059d5882;hp=1f2e0bf4167e5d27cc5917d8c217ff492f06d9c1;hpb=b082ef7f12000c9b1948eb92fa6a25a05f30cd02;p=postgresql diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c index 1f2e0bf416..41312e88c6 100644 --- a/contrib/pgbench/pgbench.c +++ b/contrib/pgbench/pgbench.c @@ -1,21 +1,30 @@ /* - * $PostgreSQL: pgsql/contrib/pgbench/pgbench.c,v 1.62 2007/03/13 09:06:35 mha Exp $ + * pgbench.c * - * pgbench: a simple benchmark program for PostgreSQL - * written by Tatsuo Ishii + * A simple benchmark program for PostgreSQL + * Originally written by Tatsuo Ishii and enhanced by many contributors. * - * Copyright (c) 2000-2007 Tatsuo Ishii + * $PostgreSQL: pgsql/contrib/pgbench/pgbench.c,v 1.79 2008/03/19 03:33:21 ishii Exp $ + * Copyright (c) 2000-2008, PostgreSQL Global Development Group + * ALL RIGHTS RESERVED; + * + * Permission to use, copy, modify, and distribute this software and its + * documentation for any purpose, without fee, and without a written agreement + * is hereby granted, provided that the above copyright notice and this + * paragraph and the following two paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE + * POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES, + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. * - * Permission to use, copy, modify, and distribute this software and - * its documentation for any purpose and without fee is hereby - * granted, provided that the above copyright notice appear in all - * copies and that both that copyright notice and this permission - * notice appear in supporting documentation, and that the name of the - * author not be used in advertising or publicity pertaining to - * distribution of the software without specific, written prior - * permission. The author makes no representations about the - * suitability of this software for any purpose. It is provided "as - * is" without express or implied warranty. */ #include "postgres_fe.h" @@ -24,10 +33,13 @@ #include #ifdef WIN32 -#include "win32.h" +#undef FD_SETSIZE +#define FD_SETSIZE 1024 +#include #else #include #include +#endif /* ! WIN32 */ #ifdef HAVE_GETOPT_H #include @@ -40,20 +52,20 @@ #ifdef HAVE_SYS_RESOURCE_H #include /* for getrlimit */ #endif -#endif /* ! WIN32 */ extern char *optarg; extern int optind; -#ifdef WIN32 -#undef select -#endif - /******************************************************************** * some configurable parameters */ -#define MAXCLIENTS 1024 /* max number of clients allowed */ +/* max number of clients allowed */ +#ifdef FD_SETSIZE +#define MAXCLIENTS (FD_SETSIZE - 10) +#else +#define MAXCLIENTS 1024 +#endif int nclients = 1; /* default number of simulated clients */ int nxacts = 10; /* default number of transactions per clients */ @@ -64,6 +76,12 @@ int nxacts = 10; /* default number of transactions per clients */ */ int scale = 1; +/* + * fillfactor. for example, fillfactor = 90 will use only 90 percent + * space during inserts and leave 10 percent free. + */ +int fillfactor = 100; + /* * end of configurable parameters *********************************************************************/ @@ -81,11 +99,10 @@ int remains; /* number of remaining clients */ int is_connect; /* establish connection for each transaction */ char *pghost = ""; -char *pgport = NULL; +char *pgport = ""; char *pgoptions = NULL; char *pgtty = NULL; char *login = NULL; -char *pwd = NULL; char *dbName; /* variable definitions */ @@ -95,6 +112,8 @@ typedef struct char *value; /* its value */ } Variable; +#define MAX_FILES 128 /* max number of SQL script files allowed */ + /* * structures used in custom query mode */ @@ -108,10 +127,13 @@ typedef struct int ecnt; /* error count */ int listen; /* 0 indicates that an async query has been * sent */ + int sleeping; /* 1 indicates that the client is napping */ + struct timeval until; /* napping until */ Variable *variables; /* array of variable definitions */ int nvariables; struct timeval txn_begin; /* used for measuring latencies */ int use_file; /* index in sql_files for this client */ + bool prepared[MAX_FILES]; } CState; /* @@ -121,6 +143,17 @@ typedef struct #define META_COMMAND 2 #define MAX_ARGS 10 +typedef enum QueryMode +{ + QUERY_SIMPLE, /* simple query */ + QUERY_EXTENDED, /* extended query */ + QUERY_PREPARED, /* extended query with prepared statements */ + NUM_QUERYMODE +} QueryMode; + +static QueryMode querymode = QUERY_SIMPLE; +static const char *QUERYMODE[] = { "simple", "extended", "prepared" }; + typedef struct { int type; /* command type (SQL_COMMAND or META_COMMAND) */ @@ -128,8 +161,6 @@ typedef struct char *argv[MAX_ARGS]; /* command list */ } Command; -#define MAX_FILES 128 /* max number of SQL script files allowed */ - Command **sql_files[MAX_FILES]; /* SQL script files */ int num_files; /* its number */ @@ -174,57 +205,120 @@ static char *select_only = { "SELECT abalance FROM accounts WHERE aid = :aid;\n" }; +/* Connection overhead time */ +static struct timeval conn_total_time = {0, 0}; + +/* Calculate total time */ +static void +addTime(struct timeval *t1, struct timeval *t2, struct timeval *result) +{ + int sec = t1->tv_sec + t2->tv_sec; + int usec = t1->tv_usec + t2->tv_usec; + if (usec >= 1000000) + { + usec -= 1000000; + sec++; + } + result->tv_sec = sec; + result->tv_usec = usec; +} + +/* Calculate time difference */ +static void +diffTime(struct timeval *t1, struct timeval *t2, struct timeval *result) +{ + int sec = t1->tv_sec - t2->tv_sec; + int usec = t1->tv_usec - t2->tv_usec; + if (usec < 0) + { + usec += 1000000; + sec--; + } + result->tv_sec = sec; + result->tv_usec = usec; +} + static void usage(void) { - fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s scaling_factor][-D varname=value][-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"); + fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s scaling_factor][-D varname=value][-n][-C][-v][-S][-N][-M querymode][-f filename][-l][-U login][-d][dbname]\n"); + fprintf(stderr, "(initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor] [-F fillfactor] [-U login][-d][dbname]\n"); } -/* random number generator */ +/* random number generator: uniform distribution from min to max inclusive */ static int getrand(int min, int max) { - return min + (int) (((max - min) * (double) random()) / MAX_RANDOM_VALUE + 0.5); + /* + * Odd coding is so that min and max have approximately the same chance of + * being selected as do numbers between them. + */ + return min + (int) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE + 1.0)); } -/* set up a connection to the backend */ -static PGconn * -doConnect(void) +/* call PQexec() and exit() on failure */ +static void +executeStatement(PGconn *con, const char *sql) { - PGconn *con; PGresult *res; - con = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, - login, pwd); - if (con == NULL) + res = PQexec(con, sql); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { - fprintf(stderr, "Connection to database '%s' failed.\n", dbName); - fprintf(stderr, "Memory allocatin problem?\n"); - return (NULL); + fprintf(stderr, "%s", PQerrorMessage(con)); + exit(1); } + PQclear(res); +} - if (PQstatus(con) == CONNECTION_BAD) +/* set up a connection to the backend */ +static PGconn * +doConnect(void) +{ + PGconn *conn; + static char *password = NULL; + bool new_pass; + + /* + * Start the connection. Loop until we have a password if requested by + * backend. + */ + do { - fprintf(stderr, "Connection to database '%s' failed.\n", dbName); + new_pass = false; - if (PQerrorMessage(con)) - fprintf(stderr, "%s", PQerrorMessage(con)); - else - fprintf(stderr, "No explanation from the backend\n"); + conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, + login, password); + if (!conn) + { + fprintf(stderr, "Connection to database \"%s\" failed\n", + dbName); + return NULL; + } - return (NULL); - } + if (PQstatus(conn) == CONNECTION_BAD && + PQconnectionNeedsPassword(conn) && + password == NULL && + !feof(stdin)) + { + PQfinish(conn); + password = simple_prompt("Password: ", 100, false); + new_pass = true; + } + } while (new_pass); - res = PQexec(con, "SET search_path = public"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); + fprintf(stderr, "Connection to database \"%s\" failed:\n%s", + dbName, PQerrorMessage(conn)); + PQfinish(conn); + return NULL; } - PQclear(res); - return (con); + executeStatement(conn, "SET search_path = public"); + + return conn; } /* throw away response from backend */ @@ -243,7 +337,7 @@ discard_response(CState * state) /* check to see if the SQL result was good */ static int -check(CState *state, PGresult *res, int n) +check(CState * state, PGresult *res, int n) { CState *st = &state[n]; @@ -256,7 +350,7 @@ check(CState *state, PGresult *res, int n) default: fprintf(stderr, "Client %d aborted in state %d: %s", n, st->state, PQerrorMessage(st->con)); - remains--; /* I've aborted */ + remains--; /* I've aborted */ PQfinish(st->con); st->con = NULL; return (-1); @@ -357,68 +451,104 @@ putVariable(CState * st, char *name, char *value) return true; } +static char * +parseVariable(const char *sql, int *eaten) +{ + int i = 0; + char *name; + + do + { + i++; + } while (isalnum((unsigned char) sql[i]) || sql[i] == '_'); + if (i == 1) + return NULL; + + name = malloc(i); + if (name == NULL) + return NULL; + memcpy(name, &sql[1], i - 1); + name[i - 1] = '\0'; + + *eaten = i; + return name; +} + +static char * +replaceVariable(char **sql, char *param, int len, char *value) +{ + int valueln = strlen(value); + + if (valueln > len) + { + char *tmp; + size_t offset = param - *sql; + + tmp = realloc(*sql, strlen(*sql) - len + valueln + 1); + if (tmp == NULL) + { + free(*sql); + return NULL; + } + *sql = tmp; + param = *sql + offset; + } + + if (valueln != len) + memmove(param + valueln, param + len, strlen(param + len) + 1); + strncpy(param, value, valueln); + + return param + valueln; +} + static char * assignVariables(CState * st, char *sql) { - int i, - j; char *p, *name, *val; - void *tmp; - i = 0; - while ((p = strchr(&sql[i], ':')) != NULL) + p = sql; + while ((p = strchr(p, ':')) != NULL) { - i = j = p - sql; - do + int eaten; + + name = parseVariable(p, &eaten); + if (name == NULL) { - i++; - } while (isalnum((unsigned char) sql[i]) || sql[i] == '_'); - if (i == j + 1) + while (*p == ':') { p++; } continue; + } - name = malloc(i - j); - if (name == NULL) - return NULL; - memcpy(name, &sql[j + 1], i - (j + 1)); - name[i - (j + 1)] = '\0'; val = getVariable(st, name); free(name); if (val == NULL) - continue; - - if (strlen(val) > i - j) { - tmp = realloc(sql, strlen(sql) - (i - j) + strlen(val) + 1); - if (tmp == NULL) - { - free(sql); - return NULL; - } - sql = tmp; + p++; + continue; } - if (strlen(val) != i - j) - memmove(&sql[j + strlen(val)], &sql[i], strlen(&sql[i]) + 1); + if ((p = replaceVariable(&sql, p, eaten, val)) == NULL) + return NULL; + } - strncpy(&sql[j], val, strlen(val)); + return sql; +} - if (strlen(val) < i - j) - { - tmp = realloc(sql, strlen(sql) + 1); - if (tmp == NULL) - { - free(sql); - return NULL; - } - sql = tmp; - } +static void +getQueryParams(CState *st, const Command *command, const char **params) +{ + int i; - i = j + strlen(val); - } + for (i = 0; i < command->argc - 1; i++) + params[i] = getVariable(st, command->argv[i+1]); +} - return sql; +#define MAX_PREPARE_NAME 32 +static void +preparedStatementName(char *buffer, int file, int state) +{ + sprintf(buffer, "P%d_%d", file, state); } static void @@ -431,6 +561,20 @@ doCustom(CState * state, int n, int debug) top: commands = sql_files[st->use_file]; + if (st->sleeping) + { /* are we sleeping? */ + int usec; + struct timeval now; + + gettimeofday(&now, NULL); + usec = (st->until.tv_sec - now.tv_sec) * 1000000 + + st->until.tv_usec - now.tv_usec; + if (usec <= 0) + st->sleeping = 0; /* Done sleeping, go ahead with next command */ + else + return; /* Still sleeping, nothing to do here */ + } + if (st->listen) { /* are we receiver? */ if (commands[st->state]->type == SQL_COMMAND) @@ -461,7 +605,9 @@ top: diff = (int) (now.tv_sec - st->txn_begin.tv_sec) * 1000000.0 + (int) (now.tv_usec - st->txn_begin.tv_usec); - fprintf(LOGFILE, "%d %d %.0f\n", st->id, st->cnt, diff); + fprintf(LOGFILE, "%d %d %.0f %d %ld %ld\n", + st->id, st->cnt, diff, st->use_file, + (long) now.tv_sec, (long) now.tv_usec); } if (commands[st->state]->type == SQL_COMMAND) @@ -508,6 +654,9 @@ top: if (st->con == NULL) { + struct timeval t1, t2, t3; + + gettimeofday(&t1, NULL); if ((st->con = doConnect()) == NULL) { fprintf(stderr, "Client %d aborted in establishing connection.\n", @@ -517,6 +666,9 @@ top: st->con = NULL; return; } + gettimeofday(&t2, NULL); + diffTime(&t2, &t1, &t3); + addTime(&conn_total_time, &t3, &conn_total_time); } if (use_log && st->state == 0) @@ -524,29 +676,83 @@ top: if (commands[st->state]->type == SQL_COMMAND) { - char *sql; + const Command *command = commands[st->state]; + int r; - if ((sql = strdup(commands[st->state]->argv[0])) == NULL - || (sql = assignVariables(st, sql)) == NULL) + if (querymode == QUERY_SIMPLE) { - fprintf(stderr, "out of memory\n"); - st->ecnt++; - return; + char *sql; + + if ((sql = strdup(command->argv[0])) == NULL + || (sql = assignVariables(st, sql)) == NULL) + { + fprintf(stderr, "out of memory\n"); + st->ecnt++; + return; + } + + if (debug) + fprintf(stderr, "client %d sending %s\n", n, sql); + r = PQsendQuery(st->con, sql); + free(sql); } + else if (querymode == QUERY_EXTENDED) + { + const char *sql = command->argv[0]; + const char *params[MAX_ARGS]; - if (debug) - fprintf(stderr, "client %d sending %s\n", n, sql); - if (PQsendQuery(st->con, sql) == 0) + getQueryParams(st, command, params); + + if (debug) + fprintf(stderr, "client %d sending %s\n", n, sql); + r = PQsendQueryParams(st->con, sql, command->argc - 1, + NULL, params, NULL, NULL, 0); + } + else if (querymode == QUERY_PREPARED) { + char name[MAX_PREPARE_NAME]; + const char *params[MAX_ARGS]; + + if (!st->prepared[st->use_file]) + { + int j; + + for (j = 0; commands[j] != NULL; j++) + { + PGresult *res; + char name[MAX_PREPARE_NAME]; + + if (commands[j]->type != SQL_COMMAND) + continue; + preparedStatementName(name, st->use_file, j); + res = PQprepare(st->con, name, + commands[j]->argv[0], commands[j]->argc - 1, NULL); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + fprintf(stderr, "%s", PQerrorMessage(st->con)); + PQclear(res); + } + st->prepared[st->use_file] = true; + } + + getQueryParams(st, command, params); + preparedStatementName(name, st->use_file, st->state); + if (debug) - fprintf(stderr, "PQsendQuery(%s)failed\n", sql); + fprintf(stderr, "client %d sending %s\n", n, name); + r = PQsendQueryPrepared(st->con, name, command->argc - 1, + params, NULL, NULL, 0); + } + else /* unknown sql mode */ + r = 0; + + if (r == 0) + { + if (debug) + fprintf(stderr, "client %d cannot send %s\n", n, command->argv[0]); st->ecnt++; } else - { st->listen = 1; /* flags that should be listened */ - } - free(sql); } else if (commands[st->state]->type == META_COMMAND) { @@ -695,6 +901,42 @@ top: st->listen = 1; } + else if (pg_strcasecmp(argv[0], "sleep") == 0) + { + char *var; + int usec; + struct timeval now; + + if (*argv[1] == ':') + { + if ((var = getVariable(st, argv[1] + 1)) == NULL) + { + fprintf(stderr, "%s: undefined variable %s\n", argv[0], argv[1]); + st->ecnt++; + return; + } + usec = atoi(var); + } + else + usec = atoi(argv[1]); + + if (argc > 2) + { + if (pg_strcasecmp(argv[2], "ms") == 0) + usec *= 1000; + else if (pg_strcasecmp(argv[2], "s") == 0) + usec *= 1000000; + } + else + usec *= 1000000; + + gettimeofday(&now, NULL); + st->until.tv_sec = now.tv_sec + (now.tv_usec + usec) / 1000000; + st->until.tv_usec = (now.tv_usec + usec) % 1000000; + st->sleeping = 1; + + st->listen = 1; + } goto top; } @@ -719,14 +961,24 @@ 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. + * But because they default to NULLs, they don't actually take any + * space. We could fix that by giving them non-null default values. + * However, that would completely break comparability of pgbench + * results with prior versions. Since pgbench has never pretended + * to be fully TPC-B compliant anyway, we stick with the historical + * behavior. + */ 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", + "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))"}; static char *DDLAFTERs[] = { "alter table branches add primary key (bid)", @@ -741,77 +993,62 @@ init(void) if ((con = doConnect()) == NULL) exit(1); - for (i = 0; i < (sizeof(DDLs) / sizeof(char *)); i++) + for (i = 0; i < lengthof(DDLs); i++) { - res = PQexec(con, DDLs[i]); - if (strncmp(DDLs[i], "drop", 4) && PQresultStatus(res) != PGRES_COMMAND_OK) + /* + * 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])) { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); + char ddl_stmt[128]; + + snprintf(ddl_stmt, 128, DDLs[i], fillfactor); + executeStatement(con, ddl_stmt); + continue; } - PQclear(res); + else + executeStatement(con, DDLs[i]); } - res = PQexec(con, "begin"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); + executeStatement(con, "begin"); for (i = 0; i < nbranches * scale; i++) { snprintf(sql, 256, "insert into branches(bid,bbalance) values(%d,0)", i + 1); - res = PQexec(con, sql); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); + executeStatement(con, sql); } for (i = 0; i < ntellers * scale; i++) { snprintf(sql, 256, "insert into tellers(tid,bid,tbalance) values (%d,%d,0)" ,i + 1, i / ntellers + 1); - res = PQexec(con, sql); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); + executeStatement(con, sql); } - res = PQexec(con, "end"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) + executeStatement(con, "commit"); + + /* + * fill the accounts table with some data + */ + fprintf(stderr, "creating tables...\n"); + + executeStatement(con, "begin"); + executeStatement(con, "truncate accounts"); + + res = PQexec(con, "copy accounts from stdin"); + if (PQresultStatus(res) != PGRES_COPY_IN) { fprintf(stderr, "%s", PQerrorMessage(con)); exit(1); } PQclear(res); - /* - * occupy accounts table with some data - */ - fprintf(stderr, "creating tables...\n"); for (i = 0; i < naccounts * scale; i++) { int j = i + 1; - if (j % 10000 == 1) - { - res = PQexec(con, "copy accounts from stdin"); - if (PQresultStatus(res) != PGRES_COPY_IN) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); - } - snprintf(sql, 256, "%d\t%d\t%d\t\n", j, i / naccounts + 1, 0); if (PQputline(con, sql)) { @@ -820,63 +1057,79 @@ init(void) } if (j % 10000 == 0) - { - /* - * every 10000 tuples, we commit the copy command. this should - * avoid generating too much WAL logs - */ fprintf(stderr, "%d tuples done.\n", j); - if (PQputline(con, "\\.\n")) - { - fprintf(stderr, "very last PQputline failed\n"); - exit(1); - } - - if (PQendcopy(con)) - { - fprintf(stderr, "PQendcopy failed\n"); - exit(1); - } - -#ifdef NOT_USED - - /* - * do a checkpoint to purge the old WAL logs - */ - res = PQexec(con, "checkpoint"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); -#endif /* NOT_USED */ - } } - fprintf(stderr, "set primary key...\n"); - for (i = 0; i < (sizeof(DDLAFTERs) / sizeof(char *)); i++) + if (PQputline(con, "\\.\n")) { - res = PQexec(con, DDLAFTERs[i]); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); + fprintf(stderr, "very last PQputline failed\n"); + exit(1); } + if (PQendcopy(con)) + { + fprintf(stderr, "PQendcopy failed\n"); + exit(1); + } + executeStatement(con, "commit"); + + /* + * create indexes + */ + fprintf(stderr, "set primary key...\n"); + for (i = 0; i < lengthof(DDLAFTERs); i++) + executeStatement(con, DDLAFTERs[i]); /* vacuum */ fprintf(stderr, "vacuum..."); - res = PQexec(con, "vacuum analyze"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) + executeStatement(con, "vacuum analyze"); + + fprintf(stderr, "done.\n"); + PQfinish(con); +} + +/* + * Parse the raw sql and replace :param to $n. + */ +static bool +parseQuery(Command *cmd, const char *raw_sql) +{ + char *sql, + *p; + + sql = strdup(raw_sql); + if (sql == NULL) + return false; + cmd->argc = 1; + + p = sql; + while ((p = strchr(p, ':')) != NULL) { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); + char var[12]; + char *name; + int eaten; + + name = parseVariable(p, &eaten); + if (name == NULL) + { + while (*p == ':') { p++; } + continue; + } + + if (cmd->argc >= MAX_ARGS) + { + fprintf(stderr, "statement has too many arguments (maximum is %d): %s\n", MAX_ARGS - 1, raw_sql); + return false; + } + + sprintf(var, "$%d", cmd->argc); + if ((p = replaceVariable(&sql, p, eaten, var)) == NULL) + return false; + + cmd->argv[cmd->argc] = name; + cmd->argc++; } - PQclear(res); - fprintf(stderr, "done.\n"); - PQfinish(con); + cmd->argv[0] = sql; + return true; } static Command * @@ -951,9 +1204,33 @@ process_commands(char *buf) fprintf(stderr, "%s: extra argument \"%s\" ignored\n", my_commands->argv[0], my_commands->argv[j]); } + else if (pg_strcasecmp(my_commands->argv[0], "sleep") == 0) + { + if (my_commands->argc < 2) + { + fprintf(stderr, "%s: missing argument\n", my_commands->argv[0]); + return NULL; + } + + if (my_commands->argc >= 3) + { + if (pg_strcasecmp(my_commands->argv[2], "us") != 0 && + pg_strcasecmp(my_commands->argv[2], "ms") != 0 && + pg_strcasecmp(my_commands->argv[2], "s")) + { + fprintf(stderr, "%s: unknown time unit '%s' - must be us, ms or s\n", + my_commands->argv[0], my_commands->argv[2]); + return NULL; + } + } + + for (j = 3; j < my_commands->argc; j++) + fprintf(stderr, "%s: extra argument \"%s\" ignored\n", + my_commands->argv[0], my_commands->argv[j]); + } else { - fprintf(stderr, "invalid command %s\n", my_commands->argv[0]); + fprintf(stderr, "Invalid command %s\n", my_commands->argv[0]); return NULL; } } @@ -961,10 +1238,21 @@ process_commands(char *buf) { my_commands->type = SQL_COMMAND; - if ((my_commands->argv[0] = strdup(p)) == NULL) - return NULL; - - my_commands->argc++; + switch (querymode) + { + case QUERY_SIMPLE: + if ((my_commands->argv[0] = strdup(p)) == NULL) + return NULL; + my_commands->argc++; + break; + case QUERY_EXTENDED: + case QUERY_PREPARED: + if (!parseQuery(my_commands, p)) + return NULL; + break; + default: + return NULL; + } } return my_commands; @@ -1112,8 +1400,7 @@ process_builtin(char *tb) static void printResults( int ttype, CState * state, - struct timeval * tv1, struct timeval * tv2, - struct timeval * tv3) + struct timeval * start_time, struct timeval * end_time) { double t1, t2; @@ -1124,10 +1411,11 @@ printResults( for (i = 0; i < nclients; i++) normal_xacts += state[i].cnt; - t1 = (tv3->tv_sec - tv1->tv_sec) * 1000000.0 + (tv3->tv_usec - tv1->tv_usec); + t1 = (end_time->tv_sec - start_time->tv_sec) * 1000000.0 + (end_time->tv_usec - start_time->tv_usec); t1 = normal_xacts * 1000000.0 / t1; - t2 = (tv3->tv_sec - tv2->tv_sec) * 1000000.0 + (tv3->tv_usec - tv2->tv_usec); + t2 = (end_time->tv_sec - start_time->tv_sec - conn_total_time.tv_sec) * 1000000.0 + + (end_time->tv_usec - start_time->tv_usec - conn_total_time.tv_usec); t2 = normal_xacts * 1000000.0 / t2; if (ttype == 0) @@ -1141,6 +1429,7 @@ printResults( printf("transaction type: %s\n", s); printf("scaling factor: %d\n", scale); + printf("query mode: %s\n", QUERYMODE[querymode]); printf("number of clients: %d\n", nclients); printf("number of transactions per client: %d\n", nxacts); printf("number of transactions actually processed: %d/%d\n", normal_xacts, nxacts * nclients); @@ -1155,7 +1444,7 @@ main(int argc, char **argv) int c; int is_init_mode = 0; /* initialize mode? */ int is_no_vacuum = 0; /* no vacuum at all before testing? */ - int is_full_vacuum = 0; /* do full vacuum before testing? */ + int do_vacuum_accounts = 0; /* do vacuum accounts before testing? */ int debug = 0; /* debug flag */ int ttype = 0; /* transaction type. 0: TPC-B, 1: SELECT only, * 2: skip update of branches and tellers */ @@ -1163,16 +1452,17 @@ main(int argc, char **argv) CState *state; /* status of clients */ - struct timeval tv1; /* start up time */ - struct timeval tv2; /* after establishing all connections to the - * backend */ - struct timeval tv3; /* end time */ + struct timeval start_time; /* start up time */ + struct timeval end_time; /* end time */ int i; fd_set input_mask; int nsocks; /* return from select(2) */ int maxsock; /* max socket number to be waited */ + struct timeval now; + struct timeval timeout; + int min_usec; #ifdef HAVE_GETRLIMIT struct rlimit rlim; @@ -1205,7 +1495,7 @@ main(int argc, char **argv) memset(state, 0, sizeof(*state)); - while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:U:P:CNSlf:D:")) != -1) + while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:U:CNSlf:D:F:M:")) != -1) { switch (c) { @@ -1219,7 +1509,7 @@ main(int argc, char **argv) is_no_vacuum++; break; case 'v': - is_full_vacuum++; + do_vacuum_accounts++; break; case 'p': pgport = optarg; @@ -1256,7 +1546,7 @@ main(int argc, char **argv) fprintf(stderr, "Use limit/ulimit to increase the limit before using pgbench.\n"); exit(1); } -#endif /* HAVE_GETRLIMIT */ +#endif /* HAVE_GETRLIMIT */ break; case 'C': is_connect = 1; @@ -1280,9 +1570,6 @@ main(int argc, char **argv) case 'U': login = optarg; break; - case 'P': - pwd = optarg; - break; case 'l': use_log = true; break; @@ -1310,6 +1597,29 @@ main(int argc, char **argv) } } break; + case 'F': + fillfactor = atoi(optarg); + if ((fillfactor < 10) || (fillfactor > 100)) + { + fprintf(stderr, "invalid fillfactor: %d\n", fillfactor); + exit(1); + } + break; + case 'M': + if (num_files > 0) + { + fprintf(stderr, "querymode(-M) should be specifiled before transaction scripts(-f)\n"); + exit(1); + } + for (querymode = 0; querymode < NUM_QUERYMODE; querymode++) + if (strcmp(optarg, QUERYMODE[querymode]) == 0) + break; + if (querymode >= NUM_QUERYMODE) + { + fprintf(stderr, "invalid querymode(-M): %s\n", optarg); + exit(1); + } + break; default: usage(); exit(1); @@ -1385,7 +1695,7 @@ main(int argc, char **argv) { char logpath[64]; - snprintf(logpath, 64, "pgbench_log.%d", getpid()); + snprintf(logpath, 64, "pgbench_log.%d", (int) getpid()); LOGFILE = fopen(logpath, "w"); if (LOGFILE == NULL) @@ -1456,63 +1766,32 @@ main(int argc, char **argv) if (!is_no_vacuum) { fprintf(stderr, "starting vacuum..."); - res = PQexec(con, "vacuum branches"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); - - res = PQexec(con, "vacuum tellers"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); - - res = PQexec(con, "delete from history"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); - res = PQexec(con, "vacuum history"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); - + executeStatement(con, "vacuum branches"); + executeStatement(con, "vacuum tellers"); + executeStatement(con, "delete from history"); + executeStatement(con, "vacuum history"); fprintf(stderr, "end.\n"); - if (is_full_vacuum) + if (do_vacuum_accounts) { - fprintf(stderr, "starting full vacuum..."); - res = PQexec(con, "vacuum analyze accounts"); - if (PQresultStatus(res) != PGRES_COMMAND_OK) - { - fprintf(stderr, "%s", PQerrorMessage(con)); - exit(1); - } - PQclear(res); + fprintf(stderr, "starting vacuum accounts..."); + executeStatement(con, "vacuum analyze accounts"); fprintf(stderr, "end.\n"); } } PQfinish(con); /* set random seed */ - gettimeofday(&tv1, NULL); - srandom((unsigned int) tv1.tv_usec); + gettimeofday(&start_time, NULL); + srandom((unsigned int) start_time.tv_usec); /* get start up time */ - gettimeofday(&tv1, NULL); + gettimeofday(&start_time, NULL); if (is_connect == 0) { + struct timeval t, now; + /* make connections to the database */ for (i = 0; i < nclients; i++) { @@ -1520,11 +1799,12 @@ main(int argc, char **argv) if ((state[i].con = doConnect()) == NULL) exit(1); } + /* time after connections set up */ + gettimeofday(&now, NULL); + diffTime(&now, &start_time, &t); + addTime(&conn_total_time, &t, &conn_total_time); } - /* time after connections set up */ - gettimeofday(&tv2, NULL); - /* process bultin SQL scripts */ switch (ttype) { @@ -1571,8 +1851,8 @@ main(int argc, char **argv) { /* all done ? */ disconnect_all(state); /* get end time */ - gettimeofday(&tv3, NULL); - printResults(ttype, state, &tv1, &tv2, &tv3); + gettimeofday(&end_time, NULL); + printResults(ttype, state, &start_time, &end_time); if (LOGFILE) fclose(LOGFILE); exit(0); @@ -1581,11 +1861,33 @@ main(int argc, char **argv) FD_ZERO(&input_mask); maxsock = -1; + min_usec = -1; for (i = 0; i < nclients; i++) { Command **commands = sql_files[state[i].use_file]; - if (state[i].con && commands[state[i].state]->type != META_COMMAND) + if (state[i].sleeping) + { + int this_usec; + int sock = PQsocket(state[i].con); + + if (min_usec < 0) + { + gettimeofday(&now, NULL); + min_usec = 0; + } + + this_usec = (state[i].until.tv_sec - now.tv_sec) * 1000000 + + state[i].until.tv_usec - now.tv_usec; + + if (this_usec > 0 && (min_usec == 0 || this_usec < min_usec)) + min_usec = this_usec; + + FD_SET(sock, &input_mask); + if (maxsock < sock) + maxsock = sock; + } + else if (state[i].con && commands[state[i].state]->type != META_COMMAND) { int sock = PQsocket(state[i].con); @@ -1602,8 +1904,18 @@ main(int argc, char **argv) if (maxsock != -1) { - if ((nsocks = select(maxsock + 1, &input_mask, (fd_set *) NULL, - (fd_set *) NULL, (struct timeval *) NULL)) < 0) + if (min_usec >= 0) + { + timeout.tv_sec = min_usec / 1000000; + timeout.tv_usec = min_usec % 1000000; + + nsocks = select(maxsock + 1, &input_mask, (fd_set *) NULL, + (fd_set *) NULL, &timeout); + } + else + nsocks = select(maxsock + 1, &input_mask, (fd_set *) NULL, + (fd_set *) NULL, (struct timeval *) NULL); + if (nsocks < 0) { if (errno == EINTR) continue; @@ -1612,6 +1924,7 @@ main(int argc, char **argv) fprintf(stderr, "select failed: %s\n", strerror(errno)); exit(1); } +#ifdef NOT_USED else if (nsocks == 0) { /* timeout */ fprintf(stderr, "select timeout\n"); @@ -1622,6 +1935,7 @@ main(int argc, char **argv) } exit(0); } +#endif } /* ok, backend returns reply */ @@ -1638,7 +1952,7 @@ main(int argc, char **argv) if (state[i].ecnt > prev_ecnt && commands[state[i].state]->type == META_COMMAND) { - fprintf(stderr, "Client %d aborted in state %d. Execution meta-command failed.\n", i, state[i].state); + fprintf(stderr, "Client %d aborted in state %d. Execution of meta-command failed.\n", i, state[i].state); remains--; /* I've aborted */ PQfinish(state[i].con); state[i].con = NULL;