[PATCH] add --throttle option to pgbench
Hello,
Please find attached a small patch to add a throttling capability to
pgbench, that is pgbench aims at a given client transaction rate instead
of maximizing the load. The throttling relies on Poisson-distributed
delays inserted after each transaction.
I wanted that to test the impact of various load levels, and for
functionnal tests on my laptop which should not drain the battery.
sh> ./pgbench -T 10 -c 2 --throttle 10tps test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 10 s
number of transactions actually processed: 214
tps = 21.054216 (including connections establishing)
tps = 21.071253 (excluding connections establishing)
--
Fabien.
Attachments:
pgbench-throttle.patchtext/x-diff; name=pgbench-throttle.patchDownload
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index bc01f07..c11aa26 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -137,6 +137,12 @@ int unlogged_tables = 0;
double sample_rate = 0.0;
/*
+ * whether clients are throttled to a given rate, expressed as a delay in us.
+ * 0, the default means no throttling.
+ */
+int64 throttle = 0;
+
+/*
* tablespace selection
*/
char *tablespace = NULL;
@@ -204,6 +210,8 @@ typedef struct
int nvariables;
instr_time txn_begin; /* used for measuring transaction latencies */
instr_time stmt_begin; /* used for measuring statement latencies */
+ instr_time txn_time; /* cumulated transaction time for throttling */
+ bool throttled; /* whether current transaction was throttled */
int use_file; /* index in sql_files for this client */
bool prepared[MAX_FILES];
} CState;
@@ -361,6 +369,9 @@ usage(void)
" -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"
+ " -H SPEC, --throttle SPEC\n"
+ " delay in second to throttle each client\n"
+ " sample specs: 0.025 40tps 25ms 25000us\n"
" -v vacuum all four standard tables before tests\n"
"\nCommon options:\n"
" -d print debugging output\n"
@@ -1027,7 +1038,7 @@ top:
}
}
- if (commands[st->state]->type == SQL_COMMAND)
+ if (!st->throttled && commands[st->state]->type == SQL_COMMAND)
{
/*
* Read and discard the query result; note this is not included in
@@ -1049,26 +1060,64 @@ top:
discard_response(st);
}
+ /* some stuff done at the end */
if (commands[st->state + 1] == NULL)
{
- if (is_connect)
+ /* disconnect if required and needed */
+ if (is_connect && st->con)
{
PQfinish(st->con);
st->con = NULL;
}
- ++st->cnt;
- if ((st->cnt >= nxacts && duration <= 0) || timer_exceeded)
- return clientDone(st, true); /* exit success */
+ /* update transaction counter once, and possibly end */
+ if (!st->throttled)
+ {
+ ++st->cnt;
+ if ((st->cnt >= nxacts && duration <= 0) || timer_exceeded)
+ return clientDone(st, true); /* exit success */
+ }
+
+ /* handle throttling once, as the last post-transaction stuff */
+ if (throttle && !st->throttled)
+ {
+ instr_time now, run;
+ int64 avg_txn_time;
+ st->throttled = true;
+ INSTR_TIME_SET_CURRENT(now);
+ run = now;
+ INSTR_TIME_SUBTRACT(run, st->txn_begin);
+ INSTR_TIME_ADD(st->txn_time, run);
+ avg_txn_time = INSTR_TIME_GET_MICROSEC(st->txn_time) / st->cnt;
+ if (avg_txn_time < throttle)
+ {
+ /* compute delay to approximate a Poisson distribution
+ * based on the client's current transaction mean time
+ * 1000000 => 13.8 .. 0 multiplier
+ */
+ int64 wait = (int64)
+ ((throttle - avg_txn_time) *
+ -log(getrand(thread, 1, 1000000)/1000000.0));
+ if (debug)
+ fprintf(stderr, "client %d throttling %d us\n",
+ st->id, (int) wait);
+ st->sleeping = 1;
+ st->until = INSTR_TIME_GET_MICROSEC(now) + wait;
+ return true;
+ }
+ /* else WARNING transactions too long, cannot throttle... */
+ }
}
/* increment state counter */
st->state++;
if (commands[st->state] == NULL)
{
+ /* reset */
st->state = 0;
st->use_file = (int) getrand(thread, 0, num_files - 1);
commands = sql_files[st->use_file];
+ st->throttled = false;
}
}
@@ -1087,8 +1136,8 @@ top:
INSTR_TIME_ACCUM_DIFF(*conn_time, end, start);
}
- /* Record transaction start time if logging is enabled */
- if (logfile && st->state == 0)
+ /* Record transaction start time if logging or throttling is enabled */
+ if ((logfile || throttle) && st->state == 0)
INSTR_TIME_SET_CURRENT(st->txn_begin);
/* Record statement start time if per-command latencies are requested */
@@ -2086,6 +2135,7 @@ main(int argc, char **argv)
{"unlogged-tables", no_argument, &unlogged_tables, 1},
{"sampling-rate", required_argument, NULL, 4},
{"aggregate-interval", required_argument, NULL, 5},
+ {"throttle", required_argument, NULL, 'H'},
{NULL, 0, NULL, 0}
};
@@ -2152,7 +2202,7 @@ main(int argc, char **argv)
state = (CState *) pg_malloc(sizeof(CState));
memset(state, 0, sizeof(CState));
- while ((c = getopt_long(argc, argv, "ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:", long_options, &optindex)) != -1)
+ while ((c = getopt_long(argc, argv, "ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:H:", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -2307,6 +2357,26 @@ main(int argc, char **argv)
exit(1);
}
break;
+ case 'H':
+ {
+ /* get a double from the beginning of option value */
+ double throttle_value = atof(optarg);
+ if (throttle_value <= 0.0)
+ {
+ fprintf(stderr, "invalid throttle value: %s\n", optarg);
+ exit(1);
+ }
+ /* rough handling of possible units */
+ if (strstr(optarg, "us"))
+ throttle = (int64) throttle_value;
+ else if (strstr(optarg, "ms"))
+ throttle = (int64) (1000.0 * throttle_value);
+ else if (strstr(optarg, "tps"))
+ throttle = (int64) (1000000.0 / throttle_value);
+ else /* assume that default is in second */
+ throttle = (int64) (1000000.0 * throttle_value);
+ }
+ break;
case 0:
/* This covers long options which take no argument. */
break;
@@ -2533,6 +2603,17 @@ main(int argc, char **argv)
INSTR_TIME_SET_CURRENT(start_time);
srandom((unsigned int) INSTR_TIME_GET_MICROSEC(start_time));
+ /* initial throttling setup with regular increasing delays */
+ if (throttle && nclients>1)
+ {
+ int delay = throttle / nclients;
+ for (i=1; i<nclients; i++)
+ {
+ state[i].sleeping = 1;
+ state[i].until = INSTR_TIME_GET_MICROSEC(start_time) + i*delay;
+ }
+ }
+
/* process builtin SQL scripts */
switch (ttype)
{
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index 79b4baf..4dc2338 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -310,6 +310,26 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</varlistentry>
<varlistentry>
+ <term><option>-H</option> <replaceable>rate</></term>
+ <term><option>--throttle</option> <replaceable>rate</></term>
+ <listitem>
+ <para>
+ Do client transaction throttling at the specified rate instead of
+ maximizing the load.
+ Each client connection targets this rate by inserting
+ Poisson-distributed pauses between transactions.
+ Obviously, the targetted rate must be below the maximum possible rate
+ of the system.
+ Example equivalent <replaceable>rate</> specifications which aim at
+ 40 transactions-per-second, that is one transaction every 25 ms:
+ <litteral>0.025</>, <litteral>0.025s</>, <litteral>25ms</>,
+ <litteral>25000us</> and finally <litteral>40tps</>.
+ Default is no throttling.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-j</option> <replaceable>threads</></term>
<listitem>
<para>
Fabien COELHO <coelho@cri.ensmp.fr> writes:
Please find attached a small patch to add a throttling capability to
pgbench, that is pgbench aims at a given client transaction rate instead
of maximizing the load. The throttling relies on Poisson-distributed
delays inserted after each transaction.
I'm having a hard time understanding the use-case for this feature.
Surely, if pgbench is throttling its transaction rate, you're going
to just end up measuring the throttle rate.
I wanted that to test the impact of various load levels, and for
functionnal tests on my laptop which should not drain the battery.
How does causing a test to take longer result in reduced battery drain?
You still need the same number of transactions if you want an honest
test, so it seems to me the machine would have to be on longer and thus
you'd eat *more* battery to get an equivalently trustworthy result.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 29, 2013 at 8:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Fabien COELHO <coelho@cri.ensmp.fr> writes:
Please find attached a small patch to add a throttling capability to
pgbench, that is pgbench aims at a given client transaction rate instead
of maximizing the load. The throttling relies on Poisson-distributed
delays inserted after each transaction.I'm having a hard time understanding the use-case for this feature.
Surely, if pgbench is throttling its transaction rate, you're going
to just end up measuring the throttle rate.
While I don't understand the part about his laptop battery, I think that
there is a good use case for this. If you are looking at latency
distributions or spikes, you probably want to see what they are like with a
load which is like the one you expect having, not the load which is the
highest possible. Although for this use case you would almost surely be
using custom transaction files, not default ones, so I think you could just
use \sleep. However, I don't know if there is an easy way to dynamically
adjust the sleep value by subtracting off the overhead time and randomizing
it a bit, like is done here.
It does seem to me that we should Poissonize the throttle time, then
subtract the average overhead, rather than Poissonizing the difference.
Cheers,
Jeff
Hello Tom,
I'm having a hard time understanding the use-case for this feature.
Surely, if pgbench is throttling its transaction rate, you're going
to just end up measuring the throttle rate.
Indeed, I do not want to measure the tps if I throttle it.
The point is to generate a continuous but not necessarily maximal load,
and to test other things under such load such as possiby cascading
replication, failover, various dump strategies, whatever.
I wanted that to test the impact of various load levels, and for
functionnal tests on my laptop which should not drain the battery.How does causing a test to take longer result in reduced battery drain?
If I test a replication setup on my laptop at maximum load, I can see the
battery draining in a few seconds by looking at the effect on the time
left widget. This remark is mostly for functional tests, not for
performance test.
If I want to test the maximum load of a setup, obviously I will not do
that on my laptop, and I will not use --throttle...
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello Jeff,
While I don't understand the part about his laptop battery, I think that
there is a good use case for this. If you are looking at latency
distributions or spikes, you probably want to see what they are like with a
load which is like the one you expect having, not the load which is the
highest possible. Although for this use case you would almost surely be
using custom transaction files, not default ones, so I think you could just
use \sleep. However, I don't know if there is an easy way to dynamically
adjust the sleep value by subtracting off the overhead time and randomizing
it a bit, like is done here.
Indeed, my thoughts:-) Having regularly (\sleep n) or uniformly
distributed (\sleep :random_value) is not very realistic, and I would have
to do some measures to find the right value for a target load.
It does seem to me that we should Poissonize the throttle time, then
subtract the average overhead, rather than Poissonizing the difference.
I actually thought about doing it the way you suggested, because it was
"right". However I did not do it, because if the Poisson gives, possibly
quite frequently, a time below the transaction time, one ends up with an
artificial sequence of stuck transactions, as a client cannot start the
second transaction while the previous one is not finished, and this does
not seem realistic. To really do that more cleanly, it would require
distributing the events between clients, so having some kind of
coordination between clients, which would really be another test
application. Having an approximation of that seemed good enough for my
purpose.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I'm having a hard time understanding the use-case for this feature.
Here is an example functional use case I had in mind.
Let us say I'm teaching a practice session about administrating
replication. Students have a desktop computer on which they can install
several instances or postgresql, or possibly use virtual machines. I'd
like them to setup one server, put it under a continuous load, then create
a first slave, then a second, and things like that. The thing I do not
want is the poor desktop and its hard drive to be at maximum speed for the
whole afternoon while doing the session, making it hard to do anything
else on the host. So I want something both realistic (the database is
under a load, the WAL is advancing, let us dump it, base backup it,
replicate it, monitor it, update it, whatever...), but gentle all the
same.
Using pgbench with --throttle basically provides the adjustable continuous
load I need. I understand that this is not at all the intent for which it
was developed.
Note that I will probably propose another patch to provide a heart beat
while things are going on, but I thought that one patch at a time was
enough.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 4/29/13 1:08 PM, Fabien COELHO wrote:
While I don't understand the part about his laptop battery, I think that
there is a good use case for this. If you are looking at latency
distributions or spikes, you probably want to see what they are like with a
load which is like the one you expect having, not the load which is the
highest possible. Although for this use case you would almost surely be
using custom transaction files, not default ones, so I think you could just
use \sleep. However, I don't know if there is an easy way to dynamically
adjust the sleep value by subtracting off the overhead time and randomizing
it a bit, like is done here.Indeed, my thoughts:-) Having regularly (\sleep n) or uniformly distributed (\sleep :random_value) is not very realistic, and I would have to do some measures to find the right value for a target load.
+1 to being able to throttle to make latency measurements.
I'm also wondering if it would be useful to be able to set a latency target and have something adjust concurrency to see how well you can hit it. Certainly feature creep for the proposed patch; I only bring it up because there may be enough similarity to consider that use case at this time, even if we don't implement it yet.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
It does seem to me that we should Poissonize the throttle time, then
subtract the average overhead, rather than Poissonizing the difference.
After thinking again about Jeff's point and failing to sleep, I think that
doing exactly that is better because:
- it is "right"
- the code is simpler and shorter
- my transaction stuck sequence issue is not that big an issue anyway
Here is a patch to schedule transactions along Poisson-distributed events.
This patch replaces my previous proposal.
Note that there is no reference to the current time after the stochastic
process is initiated. This is necessary, and mean that if transactions lag
behind the throttle at some point they will try to catch up later. Neither
a good nor a bad thing, mostly a feature.
--
Fabien
Attachments:
pgbench-throttle-2.patchtext/x-diff; charset=us-ascii; name=pgbench-throttle-2.patchDownload
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index bc01f07..0142ed0 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -137,6 +137,12 @@ int unlogged_tables = 0;
double sample_rate = 0.0;
/*
+ * whether clients are throttled to a given rate, expressed as a delay in us.
+ * 0, the default means no throttling.
+ */
+int64 throttle = 0;
+
+/*
* tablespace selection
*/
char *tablespace = NULL;
@@ -204,6 +210,8 @@ typedef struct
int nvariables;
instr_time txn_begin; /* used for measuring transaction latencies */
instr_time stmt_begin; /* used for measuring statement latencies */
+ int64 trigger; /* previous/next throttling (us) */
+ bool throttled; /* whether current transaction was throttled */
int use_file; /* index in sql_files for this client */
bool prepared[MAX_FILES];
} CState;
@@ -361,6 +369,9 @@ usage(void)
" -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"
+ " -H SPEC, --throttle SPEC\n"
+ " delay in second to throttle each client\n"
+ " sample specs: 0.025 40tps 25ms 25000us\n"
" -v vacuum all four standard tables before tests\n"
"\nCommon options:\n"
" -d print debugging output\n"
@@ -1027,7 +1038,7 @@ top:
}
}
- if (commands[st->state]->type == SQL_COMMAND)
+ if (!st->throttled && commands[st->state]->type == SQL_COMMAND)
{
/*
* Read and discard the query result; note this is not included in
@@ -1049,26 +1060,54 @@ top:
discard_response(st);
}
+ /* some stuff done at the end */
if (commands[st->state + 1] == NULL)
{
- if (is_connect)
+ /* disconnect if required and needed */
+ if (is_connect && st->con)
{
PQfinish(st->con);
st->con = NULL;
}
- ++st->cnt;
- if ((st->cnt >= nxacts && duration <= 0) || timer_exceeded)
- return clientDone(st, true); /* exit success */
+ /* update transaction counter once, and possibly end */
+ if (!st->throttled)
+ {
+ ++st->cnt;
+ if ((st->cnt >= nxacts && duration <= 0) || timer_exceeded)
+ return clientDone(st, true); /* exit success */
+ }
+
+ /* handle throttling once, as the last post-transaction stuff */
+ if (throttle && !st->throttled)
+ {
+ /* compute delay to approximate a Poisson distribution
+ * 1000000 => 13.8 .. 0 multiplier
+ * if transactions are too slow or a given wait shorter than
+ * a transaction, the next transaction will start right away.
+ */
+ int64 wait = (int64)
+ throttle * -log(getrand(thread, 1, 1000000)/1000000.0);
+ st->trigger += wait;
+ st->sleeping = 1;
+ st->until = st->trigger;
+ st->throttled = true;
+ if (debug)
+ fprintf(stderr, "client %d throttling %d us\n",
+ st->id, (int) wait);
+ return true;
+ }
}
/* increment state counter */
st->state++;
if (commands[st->state] == NULL)
{
+ /* reset */
st->state = 0;
st->use_file = (int) getrand(thread, 0, num_files - 1);
commands = sql_files[st->use_file];
+ st->throttled = false;
}
}
@@ -2086,6 +2125,7 @@ main(int argc, char **argv)
{"unlogged-tables", no_argument, &unlogged_tables, 1},
{"sampling-rate", required_argument, NULL, 4},
{"aggregate-interval", required_argument, NULL, 5},
+ {"throttle", required_argument, NULL, 'H'},
{NULL, 0, NULL, 0}
};
@@ -2152,7 +2192,7 @@ main(int argc, char **argv)
state = (CState *) pg_malloc(sizeof(CState));
memset(state, 0, sizeof(CState));
- while ((c = getopt_long(argc, argv, "ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:", long_options, &optindex)) != -1)
+ while ((c = getopt_long(argc, argv, "ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:H:", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -2307,6 +2347,26 @@ main(int argc, char **argv)
exit(1);
}
break;
+ case 'H':
+ {
+ /* get a double from the beginning of option value */
+ double throttle_value = atof(optarg);
+ if (throttle_value <= 0.0)
+ {
+ fprintf(stderr, "invalid throttle value: %s\n", optarg);
+ exit(1);
+ }
+ /* rough handling of possible units */
+ if (strstr(optarg, "us"))
+ throttle = (int64) throttle_value;
+ else if (strstr(optarg, "ms"))
+ throttle = (int64) (1000.0 * throttle_value);
+ else if (strstr(optarg, "tps"))
+ throttle = (int64) (1000000.0 / throttle_value);
+ else /* assume that default is in second */
+ throttle = (int64) (1000000.0 * throttle_value);
+ }
+ break;
case 0:
/* This covers long options which take no argument. */
break;
@@ -2533,6 +2593,18 @@ main(int argc, char **argv)
INSTR_TIME_SET_CURRENT(start_time);
srandom((unsigned int) INSTR_TIME_GET_MICROSEC(start_time));
+ /* initial throttling setup with regular increasing delays */
+ if (throttle)
+ {
+ int delay = throttle / nclients;
+ for (i=0; i<nclients; i++)
+ {
+ state[i].trigger = INSTR_TIME_GET_MICROSEC(start_time) + i*delay;
+ state[i].sleeping = 1;
+ state[i].until = state[i].trigger;
+ }
+ }
+
/* process builtin SQL scripts */
switch (ttype)
{
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index 79b4baf..b5c6c1c 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -310,6 +310,26 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</varlistentry>
<varlistentry>
+ <term><option>-H</option> <replaceable>rate</></term>
+ <term><option>--throttle</option> <replaceable>rate</></term>
+ <listitem>
+ <para>
+ Do client transaction throttling at the specified rate instead of
+ maximizing the load.
+ Each client connection targets this rate by starting transactions
+ along a Poisson-distributed event time line.
+ Obviously, the targetted rate must be below the maximum possible rate
+ of the system.
+ Example equivalent <replaceable>rate</> specifications which aim at
+ 40 transactions-per-second, that is one transaction every 25 ms:
+ <litteral>0.025</>, <litteral>0.025s</>, <litteral>25ms</>,
+ <litteral>25000us</> and finally <litteral>40tps</>.
+ Default is no throttling.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-j</option> <replaceable>threads</></term>
<listitem>
<para>