*** a/contrib/pgbench/pgbench.c --- b/contrib/pgbench/pgbench.c *************** *** 40,45 **** --- 40,46 ---- #include #include #include + #include #ifndef WIN32 #include *************** *** 176,181 **** int progress_nthreads = 0; /* number of threads for progress report */ --- 177,183 ---- bool is_connect; /* establish connection for each transaction */ bool is_latencies; /* report per-command latencies */ int main_pid; /* main process id used in log filename */ + double stdev_threshold = 5; /* standard deviation threshold */ char *pghost = ""; char *pgport = ""; *************** *** 267,272 **** typedef enum QueryMode --- 269,280 ---- NUM_QUERYMODE } QueryMode; + typedef enum DistType + { + DIST_UNIFORM, /* normal random distribution */ + DIST_GAUSSIAN /* gaussian random distribution */ + } DistType; + static QueryMode querymode = QUERY_SIMPLE; static const char *QUERYMODE[] = {"simple", "extended", "prepared"}; *************** *** 338,343 **** static char *select_only = { --- 346,392 ---- "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n" }; + /* --gaussian case */ + static char *gaussian_tpc_b = { + "\\set nbranches " CppAsString2(nbranches) " * :scale\n" + "\\set ntellers " CppAsString2(ntellers) " * :scale\n" + "\\set naccounts " CppAsString2(naccounts) " * :scale\n" + "\\setgaussian aid 1 :naccounts :stdev_threshold\n" + "\\setrandom bid 1 :nbranches\n" + "\\setrandom tid 1 :ntellers\n" + "\\setrandom delta -5000 5000\n" + "BEGIN;\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" + }; + + /* --gaussian-n case */ + static char *gaussian_simple_update = { + "\\set nbranches " CppAsString2(nbranches) " * :scale\n" + "\\set ntellers " CppAsString2(ntellers) " * :scale\n" + "\\set naccounts " CppAsString2(naccounts) " * :scale\n" + "\\setgaussian aid 1 :naccounts :stdev_threshold\n" + "\\setrandom bid 1 :nbranches\n" + "\\setrandom tid 1 :ntellers\n" + "\\setrandom delta -5000 5000\n" + "BEGIN;\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" + }; + + /* --gaussian-s case */ + static char *gaussian_select_only = { + "\\set naccounts " CppAsString2(naccounts) " * :scale\n" + "\\setgaussian aid 1 :naccounts :stdev_threshold\n" + "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n" + }; + /* Function prototypes */ static void setalarm(int seconds); static void *threadRun(void *arg); *************** *** 381,386 **** usage(void) --- 430,438 ---- " -v, --vacuum-all vacuum all four standard tables before tests\n" " --aggregate-interval=NUM aggregate data over NUM seconds\n" " --sampling-rate=NUM fraction of transactions to log (e.g. 0.01 for 1%%)\n" + " --gaussian=NUM gaussian tpc-b with NUM standard deviation threshold\n" + " --gaussian-n=NUM gaussian -N option benchmark with NUM standard deviation threshold\n" + " --gaussian-s=NUM gaussian -S option benchmark with NUM standard deviation threshold\n" "\nCommon options:\n" " -d, --debug print debugging output\n" " -h, --host=HOSTNAME database server host or socket directory\n" *************** *** 461,469 **** gotdigits: return ((sign < 0) ? -result : result); } ! /* random number generator: uniform distribution from min to max inclusive */ static int64 ! getrand(TState *thread, int64 min, int64 max) { /* * Odd coding is so that min and max have approximately the same chance of --- 513,521 ---- return ((sign < 0) ? -result : result); } ! /* random number generator: uniform or gaussian distribution from min to max inclusive */ static int64 ! getrand(TState *thread, int64 min, int64 max, DistType dist_type, double value1) { /* * Odd coding is so that min and max have approximately the same chance of *************** *** 474,480 **** getrand(TState *thread, int64 min, int64 max) * protected by a mutex, and therefore a bottleneck on machines with many * CPUs. */ ! return min + (int64) ((max - min + 1) * pg_erand48(thread->random_state)); } /* call PQexec() and exit() on failure */ --- 526,567 ---- * protected by a mutex, and therefore a bottleneck on machines with many * CPUs. */ ! double rand = pg_erand48(thread->random_state); ! ! switch(dist_type) ! { ! /* Generate uniform distribution. */ ! case DIST_UNIFORM : ! break; ! ! /* Generate gaussian distribution. */ ! case DIST_GAUSSIAN : ! { ! double rand1 = (rand * (LONG_MAX - 1.0) + 0.5) / LONG_MAX; ! double rand2; ! double stdev; ! double stdev_threshold = value1; ! ! /* get user specified random number until appeared ranged number in this loop */ ! do ! { ! rand2 = pg_erand48(thread->random_state); ! /* Box-Muller transform */ ! stdev = sqrt(-2.0 * log(rand1)) * sin(2.0 * M_PI * rand2); ! } while ( stdev < (-1.0 * stdev_threshold) || stdev > stdev_threshold); ! ! /* normalization */ ! rand = (stdev + value1) / (value1 * 2.0); ! break; ! } ! ! /* maybe bug.. */ ! default : ! return 0; ! } ! ! /* return int64 random number within between min and max */ ! return min + (int64) (max - min + 1) * rand ; } /* call PQexec() and exit() on failure */ *************** *** 942,948 **** top: * a transaction, the next transaction will start right away. */ int64 wait = (int64) (throttle_delay * ! 1.00055271703 * -log(getrand(thread, 1, 10000)/10000.0)); thread->throttle_trigger += wait; --- 1029,1035 ---- * a transaction, the next transaction will start right away. */ int64 wait = (int64) (throttle_delay * ! 1.00055271703 * - log(getrand(thread, 1, 10000, DIST_UNIFORM, 0)/10000.0)); thread->throttle_trigger += wait; *************** *** 1179,1185 **** top: if (commands[st->state] == NULL) { st->state = 0; ! st->use_file = (int) getrand(thread, 0, num_files - 1); commands = sql_files[st->use_file]; st->is_throttled = false; /* --- 1266,1273 ---- if (commands[st->state] == NULL) { st->state = 0; ! st->use_file = (int) getrand(thread, 0, num_files - 1, ! DIST_UNIFORM, 0); commands = sql_files[st->use_file]; st->is_throttled = false; /* *************** *** 1379,1387 **** top: } #ifdef DEBUG ! printf("min: " INT64_FORMAT " max: " INT64_FORMAT " random: " INT64_FORMAT "\n", min, max, getrand(thread, min, max)); #endif ! snprintf(res, sizeof(res), INT64_FORMAT, getrand(thread, min, max)); if (!putVariable(st, argv[0], argv[1], res)) { --- 1467,1475 ---- } #ifdef DEBUG ! printf("min: " INT64_FORMAT " max: " INT64_FORMAT " random: " INT64_FORMAT "\n", min, max, getrand(thread, min, max, DIST_UNIFORM, 0)); #endif ! snprintf(res, sizeof(res), INT64_FORMAT, getrand(thread, min, max, DIST_UNIFORM, 0)); if (!putVariable(st, argv[0], argv[1], res)) { *************** *** 1391,1396 **** top: --- 1479,1574 ---- st->listen = 1; } + else if (pg_strcasecmp(argv[0], "setgaussian") == 0) + { + char *var; + char *endptr; + int64 min; + int64 max; + double stdev_threshold; + char res[64]; + + if (*argv[2] == ':') + { + if((var = getVariable(st, argv[2] + 1)) == NULL) + { + fprintf(stderr, "%s: undefined variable %s\n", argv[0], argv[2]); + st->ecnt++; + return true; + } + min = strtoint64(var); + } + else + min = strtoint64(argv[2]); + #ifdef NOT_USED + if (min < 0) + { + fprintf(stderr, "%s: invalid minimum number %d\n", argv[0], min); + st->ecnt++; + return; + } + #endif + if (*argv[3] == ':') + { + if((var = getVariable(st, argv[3] + 1)) == NULL) + { + fprintf(stderr, "%s: invalid maximum number %s\n", argv[0], argv[3]); + st->ecnt++; + return true; + } + max = strtoint64(var); + } + else + max = strtoint64(argv[3]); + + /* check if min and max are appropriate value */ + if(max < min) + { + fprintf(stderr, "%s: maximum is less than minimum\n", argv[0]); + st->ecnt++; + return true; + } + + /* for not overflowing when generating random number */ + if(max - min < 0 || (max - min) + 1 < 0) + { + fprintf(stderr, "%s: range too large\n", argv[0]); + st->ecnt++; + return true; + } + + if(*argv[4] == ':') + { + if((var = getVariable(st, argv[4] + 1)) == NULL) + { + fprintf(stderr, "%s: invalid gausian threshold number %s\n", argv[0], argv[4]); + st->ecnt++; + return true; + } + stdev_threshold = strtod(var, NULL); + } + else + stdev_threshold = strtod(argv[4], &endptr); + + if ( stdev_threshold < 2) + { + fprintf(stderr, "%s: gaussian threshold must be more than 2\n,", argv[4]); + st->ecnt++; + return true; + } + #ifdef DEBUG + printf("min: " INT64_FORMAT " max: " INT64_FORMAT " random: " INT64_FORMAT "\n", min, max, getrand(thread, min, max, DIST_GAUSSIAN, stdev_threshold)); + #endif + snprintf(res, sizeof(res), INT64_FORMAT, getrand(thread, min, max, DIST_GAUSSIAN, stdev_threshold)); + + if(!putVariable(st, argv[0], argv[1], res)) + { + st->ecnt++; + return true; + } + + st->listen = 1; + } else if (pg_strcasecmp(argv[0], "set") == 0) { char *var; *************** *** 1915,1920 **** process_commands(char *buf) --- 2093,2110 ---- fprintf(stderr, "%s: extra argument \"%s\" ignored\n", my_commands->argv[0], my_commands->argv[j]); } + else if (pg_strcasecmp(my_commands->argv[0], "setgaussian") == 0) + { + if (my_commands->argc < 5) + { + fprintf(stderr, "%s: missing argument\n", my_commands->argv[0]); + exit(1); + } + + for (j = 5; j < my_commands->argc; j++) + fprintf(stderr, "%s: extra argument \"%s\" ignored\n", + my_commands->argv[0], my_commands->argv[j]); + } else if (pg_strcasecmp(my_commands->argv[0], "set") == 0) { if (my_commands->argc < 3) *************** *** 2193,2203 **** printResults(int ttype, int normal_xacts, int nclients, --- 2383,2411 ---- s = "Update only pgbench_accounts"; else if (ttype == 1) s = "SELECT only"; + else if (ttype == 4) + s = "Gaussian distributed TPC-B (sort of)"; + else if (ttype == 5) + s = "Gaussian distributed update only pgbench_accounts"; + else if (ttype == 6) + s = "Gaussian distributed SELECT only"; else s = "Custom query"; printf("transaction type: %s\n", s); printf("scaling factor: %d\n", scale); + + /* gaussian distributed */ + if(ttype == 4 || ttype == 5 || ttype == 6) + { + printf("standard deviation threshold: %.5f\n", stdev_threshold); + printf("access probability of top 20%%, 10%% and 5%% records: %.5f %.5f %.5f\n", + (double) ((erf (stdev_threshold * 0.2 / sqrt(2.0))) / (erf (stdev_threshold / sqrt(2.0)))), + (double) ((erf (stdev_threshold * 0.1 / sqrt(2.0))) / (erf (stdev_threshold / sqrt(2.0)))), + (double) ((erf (stdev_threshold * 0.05 / sqrt(2.0))) / (erf (stdev_threshold / sqrt(2.0)))) + ); + } + printf("query mode: %s\n", QUERYMODE[querymode]); printf("number of clients: %d\n", nclients); printf("number of threads: %d\n", nthreads); *************** *** 2327,2332 **** main(int argc, char **argv) --- 2535,2543 ---- {"unlogged-tables", no_argument, &unlogged_tables, 1}, {"sampling-rate", required_argument, NULL, 4}, {"aggregate-interval", required_argument, NULL, 5}, + {"gaussian", required_argument, NULL, 6}, + {"gaussian-n", required_argument, NULL, 7}, + {"gaussian-s", required_argument, NULL, 8}, {"rate", required_argument, NULL, 'R'}, {NULL, 0, NULL, 0} }; *************** *** 2606,2611 **** main(int argc, char **argv) --- 2817,2849 ---- } #endif break; + case 6: + ttype = 4; + stdev_threshold = atof(optarg); + if(stdev_threshold < 2) + { + fprintf(stderr, "--gaussian=NUM must be more than 2: %f\n", stdev_threshold); + exit(1); + } + break; + case 7: + ttype = 5; + stdev_threshold = atof(optarg); + if(stdev_threshold < 2) + { + fprintf(stderr, "--gaussian-n=NUM must be more than 2: %f\n", stdev_threshold); + exit(1); + } + break; + case 8: + ttype = 6; + stdev_threshold = atof(optarg); + if(stdev_threshold < 2) + { + fprintf(stderr, "--gaussian-s=NUM must be more than 2: %f\n", stdev_threshold); + exit(1); + } + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); *************** *** 2803,2808 **** main(int argc, char **argv) --- 3041,3057 ---- } } + /* set :stdev_threshold variable */ + if(getVariable(&state[0], "stdev_threshold") == NULL) + { + snprintf(val, sizeof(val), "%lf", stdev_threshold); + for (i = 0; i < nclients; i++) + { + if (!putVariable(&state[i], "startup", "stdev_threshold", val)) + exit(1); + } + } + if (!is_no_vacuum) { fprintf(stderr, "starting vacuum..."); *************** *** 2841,2847 **** main(int argc, char **argv) sql_files[0] = process_builtin(simple_update); num_files = 1; break; ! default: break; } --- 3090,3107 ---- sql_files[0] = process_builtin(simple_update); num_files = 1; break; ! case 4: ! sql_files[0] = process_builtin(gaussian_tpc_b); ! num_files = 1; ! break; ! case 5: ! sql_files[0] = process_builtin(gaussian_simple_update); ! num_files = 1; ! break; ! case 6: ! sql_files[0] = process_builtin(gaussian_select_only); ! num_files = 1; ! break; default: break; } *************** *** 3035,3041 **** threadRun(void *arg) Command **commands = sql_files[st->use_file]; int prev_ecnt = st->ecnt; ! st->use_file = getrand(thread, 0, num_files - 1); if (!doCustom(thread, st, &result->conn_time, logfile, &aggs)) remains--; /* I've aborted */ --- 3295,3302 ---- Command **commands = sql_files[st->use_file]; int prev_ecnt = st->ecnt; ! st->use_file = getrand(thread, 0, num_files - 1, DIST_UNIFORM, 0); ! if (!doCustom(thread, st, &result->conn_time, logfile, &aggs)) remains--; /* I've aborted */ *** a/doc/src/sgml/pgbench.sgml --- b/doc/src/sgml/pgbench.sgml *************** *** 320,325 **** pgbench options dbname --- 320,340 ---- + standard deviation + standard deviation + standard deviation + + + Gaussian distribution pgbench option. Need the standard deviation threshold. + If we set larger standard deviation threshold, pgbench access patern limited + more specific records. Min standard deviation threshold is 2. If you add '-n' + or '-s' options at tail, you can execute gaussian distribution pgbench which + is like '-N' or '-S' option. + + + + + threads threads *************** *** 770,775 **** pgbench options dbname --- 785,812 ---- + \setgaussian varname min max + standard deviation threshold + + + + + Sets variable varname to a gaussian random integer value + 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. Min standard deviation threshold is 2. + + + + Example: + + \setgaussian aid 1 :naccounts 5 + + + + + \sleep number [ us | ms | s ]