Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away - Mailing list pgsql-general

From Jeff Janes
Subject Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away
Date
Msg-id CAMkU=1ysfNuW=Kw52HX7QNtXXaH-a2=ps+R4DRqpnAGfj3_LxA@mail.gmail.com
Whole thread Raw
In response to Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away  (Chris Withers <chris@simplistix.co.uk>)
Responses Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away  (Chris Withers <chris@simplistix.co.uk>)
List pgsql-general
On Fri, Jul 24, 2015 at 12:13 PM, Chris Withers <chris@simplistix.co.uk> wrote:
On 24/07/2015 19:21, Jan Lentfer wrote:

I've been doing some lightweight load testing with
“pgbench -c8 -j8 -T10”

When run locally on the postgres server I've testing, this gives
around 5000tps

When I do it from a server that has a 13ms ping latency, it drops to
37tps.

This is using the default pgbench script, is it to be expected?
If so, why?


...
 
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1

This is your problem.  There is only one row in the pgbench_branch table, and every transaction has to update that one row.  This is inherently a seriaized event.

You trying to overcome the latency by cramming more stuff through the pipe at a time, but what you are cramming through must be done in single-file.

One solution is to just use a large scale on the benchmark so that they upate random pgbench_branch rows, rather than all updating the same row:

pgbench -i -s50

Alternatively, you could write a custom file so that all 7 commands are sent down in one packet. That way the releasing COMMIT shows up at the same time as the locking UPDATE does, rather than having 2 more round trips between them.  But this would violate the spirit of the benchmark, as presumably you are expected to inspect the results of the SELECT statement before proceeding with the rest of the transaction.

Or you could write a custom benchmark which more closely resembles whatever your true workload will be.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Chris Withers
Date:
Subject: Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away
Next
From: Jon Lapham
Date:
Subject: Re: Using the database to validate data