Thread: pgbench vs. SERIALIZABLE
Hackers,
I recently had a reason to benchmark a database which is default
SERIALIZABLE mode. I was startled to discover that pgbench is set up to
abort the client once it hits a serialization failure. You get a bunch
of these:
Client 7 aborted in state 11: ERROR: could not serialize access due to
read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
Client 0 aborted in state 11: ERROR: could not serialize access due to
read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
... which continue until you're down to one client, which then finished
out the pgbench (at very low rates, of course).
The problem is this code here:
if (commands[st->state]->type == SQL_COMMAND) { /*
* Read and discard the query result; note this
is not included in * the statement latency numbers.
*/ res = PQgetResult(st->con); switch
(PQresultStatus(res)) { case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK: break; /* OK */
default: fprintf(stderr, "Client %d
aborted in state %d: %s", st->id,
st->state, PQerrorMessage(st->con)); PQclear(res);
return clientDone(st, false); } PQclear(res);
discard_response(st);
The way I read that, if the client encounters any errors at all, it
gives up and halts that client. This doesn't seem very robust, and it
certainly won't work with SERIALIZABLE.
My thinking is that what pgbench should do is:
* track an error count
* if it finds an error, don't increment the transaction count, but do
increment the error count.
* then continue to the next transaction.
Does that seem like the right approach?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
> My thinking is that what pgbench should do is: > * track an error count > * if it finds an error, don't increment the transaction count, but do > increment the error count. > * then continue to the next transaction. > > Does that seem like the right approach? Could it retry the *same* transaction instead of the next one? Should it give up trying under some conditions, say there are more errors than transactions? -- Fabien.
On 2013-05-19 00:03, Fabien COELHO wrote: >> My thinking is that what pgbench should do is: >> * track an error count >> * if it finds an error, don't increment the transaction count, but do >> increment the error count. >> * then continue to the next transaction. >> >> Does that seem like the right approach? > > Should it give up trying under some conditions, say there are more errors > than transactions? I don't really see the point of that. I can't think of a scenario where you would get too many serialization errors to even finish the pgbench test. At any rate, as proposed, this would fail horribly if the very first transaction fails, or the second transaction fails twice, etc.. Regards, Marko Tiikkaja
>> Should it give up trying under some conditions, say there are more errors >> than transactions? > > I don't really see the point of that. I can't think of a scenario where you > would get too many serialization errors to even finish the pgbench test. My point is really to avoid in principle a potential infinite loop under option -t in these conditions, if all transactions are failing because of a table lock for instance. If pgbench is just killed, I'm not sure you get a report. > At any rate, as proposed, this would fail horribly if the very first > transaction fails, or the second transaction fails twice, etc.. Yep. Or maybe some more options to control the expected behavior on transaction failures ? --stop-client-on-fail (current behavior), --keep-trying-indefinitely, --stop-client-after=<nfails>... or nothing if this is not a problem:-) -- Fabien.
Josh Berkus <josh@agliodbs.com> wrote:
> I recently had a reason to benchmark a database which is default
> SERIALIZABLE mode. I was startled to discover that pgbench is set up to
> abort the client once it hits a serialization failure. You get a bunch
> of these:
>
> Client 7 aborted in state 11: ERROR: could not serialize access due to
> read/write dependencies among transactions
> DETAIL: Reason code: Canceled on identification as a pivot, during write.
> HINT: The transaction might succeed if retried.
> Client 0 aborted in state 11: ERROR: could not serialize access due to
> read/write dependencies among transactions
> DETAIL: Reason code: Canceled on identification as a pivot, during write.
>
> ... which continue until you're down to one client, which then finished
> out the pgbench (at very low rates, of course).
>
> The problem is this code here:
>
> if (commands[st->state]->type == SQL_COMMAND)
> {
> /*
>
> * Read and discard the query result; note this
> is not included in
> * the statement latency numbers.
>
> */
> res = PQgetResult(st->con);
> switch (PQresultStatus(res))
> {
> case PGRES_COMMAND_OK:
> case PGRES_TUPLES_OK:
> break; /* OK */
> default:
> fprintf(stderr, "Client %d
> aborted in state %d: %s",
> st->id,
> st->state, PQerrorMessage(st->con));
> PQclear(res);
> return clientDone(st, false);
> }
> PQclear(res);
> discard_response(st);
>
>
> The way I read that, if the client encounters any errors at all, it
> gives up and halts that client. This doesn't seem very robust, and it
> certainly won't work with SERIALIZABLE.
Yes, I ran into this and wound up testing with a hacked copy of
pgbench. Anyone using SERIALIZABLE transactions needs to be
prepared to handle serialization failures. Even REPEATABLE READ
can see a lot of serialization failures due to write conflicts in
some workloads, and READ COMMITTED can see deadlocks on certain
workloads.
> My thinking is that what pgbench should do is:
> * track an error count
> * if it finds an error, don't increment the transaction count, but do
> increment the error count.
> * then continue to the next transaction.
>
> Does that seem like the right approach?
The main thing is to not consider a transaction complete on a
serialization failure. Many frameworks will retry a transaction
from the start on a serialization failure. pgbench should do the
same. The transaction should be rolled back and retried without
incrementing the transaction count. It would not hurt to keep
track of the retries, but as long as the transaction is retried
until successful you will get meaningful throughput numbers.
Perhaps other errors should also be counted and handled better.
For SQLSTATE values *other than* 40001 and 40P01 we should not
retry the same transaction, though, because it could well be a case
where the same attempt will head-bang forever. Serialization
failures, by definition, can be expected to work on retry. (Not
always on the *first* retry, but any benchmark tool should keep at
it until the transaction succeeds or gets a hard error if you want
meaningful numbers, because that's what a software frameworks
should be doing -- and many do.)
I raised this issue near the end of SSI development, but nobody
seemed very interested and someone argued that a tool to do that
would be good but we shouldn't try to do it in pgbench -- so I let
it drop at the time.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 20.05.2013 14:50, Kevin Grittner wrote: > I raised this issue near the end of SSI development, but nobody > seemed very interested and someone argued that a tool to do that > would be good but we shouldn't try to do it in pgbench -- so I let > it drop at the time. +1 on doing it in pgbench. - Heikki
On Mon, May 20, 2013 at 4:50 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
seemed very interested and someone argued that a tool to do thatI raised this issue near the end of SSI development, but nobody
would be good but we shouldn't try to do it in pgbench -- so I let
it drop at the time.
I think it would be good to do it in pgbench, provided it can be done fairly cleanly.
Presumably we would want to repeat all of the ordinary commands, in the file, but not any of the backslash set commands that precede any ordinary commands. But what if backslash set commands are sprinkled between ordinary commands?
Cheers,
Jeff
> I think it would be good to do it in pgbench, provided it can be done > fairly cleanly. > > Presumably we would want to repeat all of the ordinary commands, in the > file, but not any of the backslash set commands that precede any ordinary > commands. But what if backslash set commands are sprinkled between > ordinary commands? As I have spent some time in pgbench recently, ISTM that when restarting the transaction (reset state = 0), you want to keep track of the farthest state that was reached on previous attempts, so as to skip backslash commands for those states, and restart execute them where they were left. That would mean one integer for this purpose in the client structure. -- Fabien
> Presumably we would want to repeat all of the ordinary commands, in the > file, but not any of the backslash set commands that precede any ordinary > commands. But what if backslash set commands are sprinkled between > ordinary commands? See, this is why I had no intention of retrying. Since people can run custom workloads, there's no good way for us to handle the different permutations of what kinds of scripts people might write. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com