Re: pgbench--new transaction type

On 05/29/2011 03:11 PM, Jeff Janes wrote:
> If you use "pgbench -S -M prepared" at a scale where all data fits in
> memory, most of what you are benchmarking is network/IPC chatter, and
> table locking.

If you profile it, you'll find a large amount of the time is actually
spent doing more mundane things, like memory allocation. The network
and locking issues are really not the bottleneck at all in a surprising
number of these cases. Your patch isn't really dependent on your being
right about the cause here, which means this doesn't impact your
submissions any. Just wanted to clarify that what people expect are
slowing things down in this situation and what actually shows up when
you profile are usually quite different.

I'm not sure whether this feature makes sense to add to pgbench, but
it's interesting to have it around for developer testing. The way
you've built this isn't messing with the code too much to accomplish
that, and your comments about it being hard to do this using "-f" are
all correct. Using a custom test file aims to shoot your foot unless
you apply a strong grip toward doing otherwise.

> some numbers for single client runs on 64-bit AMD Opteron Linux:
> 12,567 sps under -S
> 19,646 sps under -S -M prepared
> 58,165 sps under -P
>

10,000 is too big of a burst to run at once. The specific thing I'm
concerned about is what happens if you try this mode when using "-T" to
enforce a runtime limit, and your SELECT rate isn't high. If you're
only doing 100 SELECTs/second because your scale is big enough to be
seek bound, you could overrun by nearly two minutes.

I think this is just a matter of turning the optimization around a bit.
Rather than starting with a large batch size and presuming that's ideal,
in this case a different approach is really needed. You want the
smallest batch size that gives you a large win here. My guess is that
most of the gain here comes from increasing batch size to something in
the 10 to 100 range; jumping to 10K is probably overkill. Could you try
some smaller numbers and see where the big increases start falling off at?

Obligatory code formatting nitpick: try not to overrun the right margin
any further than the existing code around line 1779, where you add more
ttype comments. That needs to turn into a multi-line comment. Rest of
the patch looks fine, and don't worry about resubmitting for that; just
something to tweak on your next update. A slightly more descriptive
filename for the patch would help out those of us who look at a lot of
pgbench patches, too. Something like "pgbench_loop_v1.patch" for
example would make it easier for me to remember which patch this was by
its name.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us