30#if defined(WIN32) && FD_SETSIZE < 1024
31#error FD_SETSIZE needs to have been increased
46#if defined(HAVE_PPOLL) && !defined(PGBENCH_USE_SELECT)
47#define POLL_USING_PPOLL
52#define POLL_USING_SELECT
56#include "catalog/pg_class_d.h"
74#define M_PI 3.14159265358979323846
77#define ERRCODE_T_R_SERIALIZATION_FAILURE "40001"
78#define ERRCODE_T_R_DEADLOCK_DETECTED "40P01"
79#define ERRCODE_UNDEFINED_TABLE "42P01"
84#define FNV_PRIME UINT64CONST(0x100000001b3)
85#define FNV_OFFSET_BASIS UINT64CONST(0xcbf29ce484222325)
86#define MM2_MUL UINT64CONST(0xc6a4a7935bd1e995)
87#define MM2_MUL_TIMES_8 UINT64CONST(0x35253c9ade8f4ca8)
94#ifdef POLL_USING_PPOLL
95#define SOCKET_WAIT_METHOD "ppoll"
106#ifdef POLL_USING_SELECT
107#define SOCKET_WAIT_METHOD "select"
124#define GETERRNO() (_dosmaperr(GetLastError()), errno)
125#define THREAD_T HANDLE
126#define THREAD_FUNC_RETURN_TYPE unsigned
127#define THREAD_FUNC_RETURN return 0
128#define THREAD_FUNC_CC __stdcall
129#define THREAD_CREATE(handle, function, arg) \
130 ((*(handle) = (HANDLE) _beginthreadex(NULL, 0, (function), (arg), 0, NULL)) == 0 ? errno : 0)
131#define THREAD_JOIN(handle) \
132 (WaitForSingleObject(handle, INFINITE) != WAIT_OBJECT_0 ? \
133 GETERRNO() : CloseHandle(handle) ? 0 : GETERRNO())
134#define THREAD_BARRIER_T SYNCHRONIZATION_BARRIER
135#define THREAD_BARRIER_INIT(barrier, n) \
136 (InitializeSynchronizationBarrier((barrier), (n), 0) ? 0 : GETERRNO())
137#define THREAD_BARRIER_WAIT(barrier) \
138 EnterSynchronizationBarrier((barrier), \
139 SYNCHRONIZATION_BARRIER_FLAGS_BLOCK_ONLY)
140#define THREAD_BARRIER_DESTROY(barrier)
144#define THREAD_T pthread_t
145#define THREAD_FUNC_RETURN_TYPE void *
146#define THREAD_FUNC_RETURN return NULL
147#define THREAD_FUNC_CC
148#define THREAD_CREATE(handle, function, arg) \
149 pthread_create((handle), NULL, (function), (arg))
150#define THREAD_JOIN(handle) \
151 pthread_join((handle), NULL)
152#define THREAD_BARRIER_T pthread_barrier_t
153#define THREAD_BARRIER_INIT(barrier, n) \
154 pthread_barrier_init((barrier), NULL, (n))
155#define THREAD_BARRIER_WAIT(barrier) pthread_barrier_wait((barrier))
156#define THREAD_BARRIER_DESTROY(barrier) pthread_barrier_destroy((barrier))
163#define DEFAULT_INIT_STEPS "dtgvp"
164#define ALL_INIT_STEPS "dtgGvpf"
166#define LOG_STEP_SECONDS 5
167#define DEFAULT_NXACTS 10
169#define MIN_GAUSSIAN_PARAM 2.0
171#define MIN_ZIPFIAN_PARAM 1.001
172#define MAX_ZIPFIAN_PARAM 1000.0
247#define naccounts 100000
256#define SCALE_32BIT_THRESHOLD 20000
312#define VARIABLES_ALLOC_MARGIN 8
349#define MAX_SCRIPTS 128
350#define SHELL_COMMAND_SIZE 256
681#define META_COMMAND 2
716static const char *
const QUERYMODE[] = {
"simple",
"extended",
"prepared"};
786 "<builtin: TPC-B (sort of)>",
790 "\\set delta random(-5000, 5000)\n"
792 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
793 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
794 "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
795 "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n"
796 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
801 "<builtin: simple update>",
805 "\\set delta random(-5000, 5000)\n"
807 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
808 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
809 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
814 "<builtin: select only>",
816 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
830 StatsData *agg,
bool skipped,
double latency,
double lag);
858 const char *params[1] = {table};
860 "SELECT relkind FROM pg_catalog.pg_class WHERE oid=$1::pg_catalog.regclass";
894#define PG_TIME_GET_DOUBLE(t) (0.000001 * (t))
899 printf(
"%s is a benchmarking tool for PostgreSQL.\n\n"
901 " %s [OPTION]... [DBNAME]\n"
902 "\nInitialization options:\n"
903 " -i, --initialize invokes initialization mode\n"
905 " run selected initialization steps, in the specified order\n"
906 " d: drop any existing pgbench tables\n"
907 " t: create the tables used by the standard pgbench scenario\n"
908 " g: generate data, client-side\n"
909 " G: generate data, server-side\n"
910 " v: invoke VACUUM on the standard tables\n"
911 " p: create primary key indexes on the standard tables\n"
912 " f: create foreign keys between the standard tables\n"
913 " -F, --fillfactor=NUM set fill factor\n"
914 " -n, --no-vacuum do not run VACUUM during initialization\n"
915 " -q, --quiet quiet logging (one message each 5 seconds)\n"
916 " -s, --scale=NUM scaling factor\n"
917 " --foreign-keys create foreign key constraints between tables\n"
918 " --index-tablespace=TABLESPACE\n"
919 " create indexes in the specified tablespace\n"
920 " --partition-method=(range|hash)\n"
921 " partition pgbench_accounts with this method (default: range)\n"
922 " --partitions=NUM partition pgbench_accounts into NUM parts (default: 0)\n"
923 " --tablespace=TABLESPACE create tables in the specified tablespace\n"
924 " --unlogged-tables create tables as unlogged tables\n"
925 "\nOptions to select what to run:\n"
926 " -b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)\n"
927 " (use \"-b list\" to list available scripts)\n"
928 " -f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)\n"
929 " -N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches\n"
930 " (same as \"-b simple-update\")\n"
931 " -S, --select-only perform SELECT-only transactions\n"
932 " (same as \"-b select-only\")\n"
933 "\nBenchmarking options:\n"
934 " -c, --client=NUM number of concurrent database clients (default: 1)\n"
935 " -C, --connect establish new connection for each transaction\n"
936 " -D, --define=VARNAME=VALUE\n"
937 " define variable for use by custom script\n"
938 " -j, --jobs=NUM number of threads (default: 1)\n"
939 " -l, --log write transaction times to log file\n"
940 " -L, --latency-limit=NUM count transactions lasting more than NUM ms as late\n"
941 " -M, --protocol=simple|extended|prepared\n"
942 " protocol for submitting queries (default: simple)\n"
943 " -n, --no-vacuum do not run VACUUM before tests\n"
944 " -P, --progress=NUM show thread progress report every NUM seconds\n"
945 " -r, --report-per-command report latencies, failures, and retries per command\n"
946 " -R, --rate=NUM target rate in transactions per second\n"
947 " -s, --scale=NUM report this scale factor in output\n"
948 " -t, --transactions=NUM number of transactions each client runs (default: 10)\n"
949 " -T, --time=NUM duration of benchmark test in seconds\n"
950 " -v, --vacuum-all vacuum all four standard tables before tests\n"
951 " --aggregate-interval=NUM aggregate data over NUM seconds\n"
952 " --exit-on-abort exit when any client is aborted\n"
953 " --failures-detailed report the failures grouped by basic types\n"
954 " --log-prefix=PREFIX prefix for transaction time log file\n"
955 " (default: \"pgbench_log\")\n"
956 " --max-tries=NUM max number of tries to run transaction (default: 1)\n"
957 " --progress-timestamp use Unix epoch timestamps for progress\n"
958 " --random-seed=SEED set random seed (\"time\", \"rand\", integer)\n"
959 " --sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%%)\n"
960 " --show-script=NAME show builtin script code, then exit\n"
961 " --verbose-errors print messages of all errors\n"
962 "\nCommon options:\n"
963 " --debug print debugging output\n"
964 " -d, --dbname=DBNAME database name to connect to\n"
965 " -h, --host=HOSTNAME database server host or socket directory\n"
966 " -p, --port=PORT database server port number\n"
967 " -U, --username=USERNAME connect as specified database user\n"
968 " -V, --version output version information, then exit\n"
969 " -?, --help show this help, then exit\n"
971 "Report bugs to <%s>.\n"
972 "%s home page: <%s>\n",
980 const char *ptr =
str;
983 while (*ptr && isspace((
unsigned char) *ptr))
987 if (*ptr ==
'+' || *ptr ==
'-')
991 if (*ptr && !isdigit((
unsigned char) *ptr))
995 while (*ptr && isdigit((
unsigned char) *ptr))
1017 const char *ptr =
str;
1030 while (*ptr && isspace((
unsigned char) *ptr))
1039 else if (*ptr ==
'+')
1043 if (
unlikely(!isdigit((
unsigned char) *ptr)))
1044 goto invalid_syntax;
1047 while (*ptr && isdigit((
unsigned char) *ptr))
1049 int8 digit = (*ptr++ -
'0');
1057 while (*ptr !=
'\0' && isspace((
unsigned char) *ptr))
1061 goto invalid_syntax;
1091 *dv = strtod(
str, &end);
1149 cut = exp(-parameter);
1156 Assert((1.0 - cut) != 0.0);
1157 rand = -log(cut + (1.0 - cut) * uniform) / parameter;
1159 return min + (
int64) ((max - min + 1) * rand);
1189 while (stdev < -parameter || stdev >= parameter);
1192 rand = (stdev + parameter) / (parameter * 2.0);
1195 return min + (
int64) ((max - min + 1) * rand);
1217 return (
int64) (-log(uniform) * center + 0.5);
1230 double b = pow(2.0, s - 1.0);
1246 x = floor(pow(u, -1.0 / (s - 1.0)));
1248 t = pow(1.0 + 1.0 /
x, s - 1.0);
1250 if (v *
x * (t - 1.0) / (
b - 1.0) <= t /
b &&
x <= n)
1260 int64 n = max - min + 1;
1278 for (
i = 0;
i < 8; ++
i)
1283 result = result ^ octet;
1313 return (
int64) result;
1352 mask = (((
uint64) 1) << masklen) - 1;
1383 for (
i = 0;
i < 6;
i++)
1394 v = ((v * m) ^ r) & mask;
1395 v = ((v << 1) & mask) | (v >> (masklen - 1));
1404 t = ((t * m) ^ r) & mask;
1405 t = ((t << 1) & mask) | (t >> (masklen - 1));
1432 if (ss->
count == 0 || val < ss->min)
1495 stats->
retries += (tries - 1);
1521 pg_fatal(
"unexpected error status: %d", estatus);
1570#define PARAMS_ARRAY_SIZE 7
1585 keywords[5] =
"fallback_application_name";
1636 if (variables->
nvars <= 0)
1661 char stringform[64];
1673 snprintf(stringform,
sizeof(stringform),
"NULL");
1675 snprintf(stringform,
sizeof(stringform),
1678 snprintf(stringform,
sizeof(stringform),
1681 snprintf(stringform,
sizeof(stringform),
1698 slen = strlen(var->
svalue);
1742 pg_log_error(
"malformed variable \"%s\" value: \"%s\"",
1767 const unsigned char *ptr = (
const unsigned char *)
name;
1775 strchr(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz"
1785 strchr(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz"
1786 "_0123456789", *ptr) != NULL)
1803 needed += variables->
nvars;
1839 var = &(variables->
vars[variables->
nvars]);
1923 strchr(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz"
1924 "_", sql[
i]) != NULL)
1930 strchr(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz"
1931 "_0123456789", sql[
i]) != NULL)
1935 memcpy(
name, &sql[1],
i - 1);
1945 int valueln = strlen(
value);
1949 size_t offset = param - *sql;
1952 param = *sql + offset;
1956 memmove(param + valueln, param +
len, strlen(param +
len) + 1);
1957 memcpy(param,
value, valueln);
1959 return param + valueln;
1970 while ((p = strchr(p,
':')) != NULL)
2000 const char **params)
2004 for (
i = 0;
i < command->
argc - 1;
i++)
2035 *bval = pval->
u.
bval;
2058 return pval->
u.
bval;
2060 return pval->
u.
ival != 0;
2062 return pval->
u.
dval != 0.0;
2076 *ival = pval->
u.
ival;
2081 double dval = rint(pval->
u.
dval);
2088 *ival = (
int64) dval;
2104 *dval = pval->
u.
dval;
2109 *dval = (double) pval->
u.
ival;
2254 if (
args->next == NULL)
2283 bool has_null =
false;
2291 for (nargs = 0; nargs <
MAX_FARGS && l != NULL; nargs++, l = l->
next)
2539 double d = varg->
u.
dval;
2554 fprintf(stderr,
"debug(script=%d,command=%d): ",
2560 fprintf(stderr,
"boolean %s\n", varg->
u.
bval ?
"true" :
"false");
2564 fprintf(stderr,
"double %.*g\n", DBL_DIG, varg->
u.
dval);
2623 for (
i = 0;
i < nargs;
i++)
2637 for (
i = 1;
i < nargs;
i++)
2644 extremum =
Min(extremum, dval);
2646 extremum =
Max(extremum, dval);
2656 for (
i = 1;
i < nargs;
i++)
2663 extremum =
Min(extremum, ival);
2665 extremum =
Max(extremum, ival);
2720 pg_log_error(
"gaussian parameter must be at least %f (not %f)",
2727 imin, imax, param));
2733 pg_log_error(
"zipfian parameter must be in range [%.3f, %.0f] (not %f)",
2745 pg_log_error(
"exponential parameter must be greater than zero (not %f)",
2752 imin, imax, param));
2830 pg_log_error(
"permute size parameter must be greater than zero");
2866 switch (expr->
etype)
2887 *retval = var->
value;
2899 pg_fatal(
"unexpected enode type in evaluation: %d", expr->
etype);
2967 for (
i = 0;
i < argc;
i++)
2972 if (argv[
i][0] !=
':')
2976 else if (argv[
i][1] ==
':')
2982 pg_log_error(
"%s: undefined variable \"%s\"", argv[0], argv[
i]);
2986 arglen = strlen(
arg);
2989 pg_log_error(
"%s: shell command is too long", argv[0]);
2994 command[
len++] =
' ';
2995 memcpy(command +
len,
arg, arglen);
2999 command[
len] =
'\0';
3006 if (system(command))
3009 pg_log_error(
"%s: could not launch shell command", argv[0]);
3016 if ((fp = popen(command,
"r")) == NULL)
3018 pg_log_error(
"%s: could not launch shell command", argv[0]);
3021 if (fgets(
res,
sizeof(
res), fp) == NULL)
3024 pg_log_error(
"%s: could not read result of shell command", argv[0]);
3030 pg_log_error(
"%s: could not run shell command: %m", argv[0]);
3035 retval = (int) strtol(
res, &endptr, 10);
3036 while (*endptr !=
'\0' && isspace((
unsigned char) *endptr))
3038 if (*
res ==
'\0' || *endptr !=
'\0')
3040 pg_log_error(
"%s: shell command must return an integer (not \"%s\")", argv[0],
res);
3046 pg_log_debug(
"%s: shell parameter name: \"%s\", value: \"%s\"", argv[0], argv[1],
res);
3057 pg_log_error(
"client %d aborted in command %d (%s) of script %d; %s",
3068 pg_log_info(
"client %d got an error in command %d (SQL) of script %d; %s",
3106 for (numcmds = 0; script->
commands[numcmds] != NULL; numcmds++)
3133 command->
argv[0], command->
argc - 1, NULL);
3168 for (
j = st->
command + 1; commands[
j] != NULL;
j++)
3199 const char *sql = command->
argv[0];
3206 NULL, params, NULL, NULL, 0);
3217 params, NULL, NULL, 0);
3237 if (sqlState != NULL)
3290 is_last = (next_res == NULL);
3298 pg_log_error(
"client %d script %d command %d query %d: expected one row, got %d",
3313 pg_log_error(
"client %d script %d command %d query %d: expected one row, got %d",
3318 else if (meta ==
META_ASET && ntuples <= 0)
3330 if (*varprefix !=
'\0')
3331 varname =
psprintf(
"%s%s", varprefix, varname);
3338 pg_log_error(
"client %d script %d command %d query %d: error storing into variable %s",
3344 if (*varprefix !=
'\0')
3352 pg_log_debug(
"client %d pipeline ending, ongoing syncs: %d",
3356 pg_log_error(
"client %d failed to exit pipeline mode: %s", st->
id,
3374 pg_log_error(
"client %d script %d aborted in command %d query %d: %s",
3415 if (*argv[1] ==
':')
3417 if ((var =
getVariable(variables, argv[1] + 1)) == NULL)
3419 pg_log_error(
"%s: undefined variable \"%s\"", argv[0], argv[1] + 1);
3426 if (usec == 0 && !isdigit((
unsigned char) *var))
3428 pg_log_error(
"%s: invalid sleep time \"%s\" for variable \"%s\"",
3429 argv[0], var, argv[1] + 1);
3434 usec = atoi(argv[1]);
3506 pg_log_error(
"client %d aborted: failed to send a pipeline sync",
3529 pg_log_error(
"client %d aborted: failed to exit pipeline mode for rolling back the failed transaction",
3565 pg_log_error(
"unexpected transaction status %d", tx_status);
3589 "repeats the transaction after the error" :
3590 "ends the failed transaction"));
3669 if (st->
con == NULL)
3679 pg_log_error(
"client %d aborted while establishing connection", st->
id);
3787 if (now < st->txn_scheduled)
3804 if (command == NULL)
3810 pg_log_error(
"client %d aborted: end of script reached with pipeline open",
3833 commandFailed(st,
"gset",
"\\gset is not allowed in pipeline mode");
3839 commandFailed(st,
"aset",
"\\aset is not allowed in pipeline mode");
3995 commandFailed(st,
"SQL",
"perhaps the backend died while processing");
4029 if (now < st->sleep_until)
4093 pg_log_error(
"client %d aborted: failed to send sql command for rolling back the failed transaction",
4112 pg_log_error(
"perhaps the backend died while processing");
4114 pg_log_error(
"client %d aborted while receiving the transaction status", st->
id);
4130 pg_log_error(
"client %d aborted while rolling back the transaction after an error; perhaps the backend died while processing",
4159 pg_log_error(
"client %d aborted while rolling back the transaction after an error; %s",
4239 pg_log_error(
"client %d aborted: end of script reached without completing the last transaction",
4247 pg_log_error(
"perhaps the backend died while processing");
4249 pg_log_error(
"client %d aborted while receiving the transaction status", st->
id);
4322 argc = command->
argc;
4323 argv = command->
argv;
4332 for (
int i = 1;
i < argc;
i++)
4352 commandFailed(st,
"sleep",
"execution of meta-command failed");
4367 commandFailed(st, argv[0],
"evaluation of meta-command failed");
4373 commandFailed(st,
"set",
"assignment of meta-command failed");
4386 commandFailed(st, argv[0],
"evaluation of meta-command failed");
4409 commandFailed(st, argv[0],
"evaluation of meta-command failed");
4443 commandFailed(st,
"setshell",
"execution of meta-command failed");
4451 commandFailed(st,
"shell",
"execution of meta-command failed");
4463 commandFailed(st,
"startpipeline",
"cannot use pipeline mode with the simple query protocol");
4479 commandFailed(st,
"startpipeline",
"already in pipeline mode");
4484 commandFailed(st,
"startpipeline",
"failed to enter pipeline mode");
4497 commandFailed(st,
"syncpipeline",
"failed to send a pipeline sync");
4511 commandFailed(st,
"endpipeline",
"failed to send a pipeline sync");
4553 return "serialization";
4558 pg_fatal(
"unexpected error status: %d", estatus);
4576 StatsData *agg,
bool skipped,
double latency,
double lag)
4604 double lag_sum = 0.0;
4605 double lag_sum2 = 0.0;
4606 double lag_min = 0.0;
4607 double lag_max = 0.0;
4609 int64 serialization_failures = 0;
4610 int64 deadlock_failures = 0;
4653 serialization_failures,
4672 now / 1000000,
now % 1000000);
4698 double latency = 0.0,
4723 doLog(thread, st, agg, skipped, latency, lag);
4738 for (
i = 0;
i < length;
i++)
4748 fprintf(stderr,
"dropping old tables...\n");
4755 "pgbench_accounts, "
4756 "pgbench_branches, "
4786 "create%s table pgbench_accounts_%d\n"
4787 " partition of pgbench_accounts\n"
4788 " for values from (",
4813 "create%s table pgbench_accounts_%d\n"
4814 " partition of pgbench_accounts\n"
4815 " for values with (modulus %d, remainder %d)",
4854 const char *bigcols;
4855 int declare_fillfactor;
4857 static const struct ddlinfo DDLs[] = {
4860 "tid int,bid int,aid int,delta int,mtime timestamp,filler char(22)",
4861 "tid int,bid int,aid bigint,delta int,mtime timestamp,filler char(22)",
4866 "tid int not null,bid int,tbalance int,filler char(84)",
4867 "tid int not null,bid int,tbalance int,filler char(84)",
4872 "aid int not null,bid int,abalance int,filler char(84)",
4873 "aid bigint not null,bid int,abalance int,filler char(84)",
4878 "bid int not null,bbalance int,filler char(88)",
4879 "bid int not null,bbalance int,filler char(88)",
4886 fprintf(stderr,
"creating tables...\n");
4892 const struct ddlinfo *ddl = &DDLs[
i];
4904 else if (ddl->declare_fillfactor)
4912 char *escape_tablespace;
4935 "pgbench_accounts, "
4936 "pgbench_branches, "
4978 char copy_statement[256];
4979 const char *copy_statement_fmt =
"copy %s from stdin";
4984 int log_interval = 1;
4987 char eol = isatty(fileno(stderr)) ?
'\r' :
'\n';
4994 copy_statement_fmt =
"copy %s from stdin with (freeze on)";
4997 n =
pg_snprintf(copy_statement,
sizeof(copy_statement), copy_statement_fmt, table);
4998 if (n >=
sizeof(copy_statement))
4999 pg_fatal(
"invalid buffer size: must be at least %d characters long", n);
5011 for (k = 0; k < total; k++)
5029 double remaining_sec = ((double) total -
j) * elapsed_sec /
j;
5033 (
int) ((
j * 100) / total),
5034 table, elapsed_sec, remaining_sec);
5041 if (prev_chars >
chars)
5050 double remaining_sec = ((double) total -
j) * elapsed_sec /
j;
5057 (
int) ((
j * 100) / total),
5058 table, elapsed_sec, remaining_sec);
5065 if (prev_chars >
chars)
5076 if (
chars != 0 && eol !=
'\n')
5080 pg_fatal(
"very last PQputline failed");
5096 fprintf(stderr,
"generating data (client-side)...\n");