Re: pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away

On 24/07/2015 22:51, Jeff Janes wrote:

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.

Indeed it was!

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

With a scale of 1000, everything except the END took roughly the latency time. Interestingly, the END still seems to take more, when threads/clients are really ramped up (100 vs 8). Why would that be?

Alternatively, you could write a custom file so that all 7 commands are sent down in one packet.

How would you restructure the sql so as the make that happen?

cheers,

Chris