Re: pgbench--new transaction type - Mailing list pgsql-hackers

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us