Re: pgbench--new transaction type

On Sun, Jun 19, 2011 at 3:30 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> I applied Jeff's patch but changed this to address concerns about the
> program getting stuck running for too long in the function:
>
> #define plpgsql_loops   512
>
> This would be better named as "plpgsql_batch_size" or something similar
> instead, the current name suggests it's how many loops to run which is
> confusing.
>
> My main performance concern here was whether this change really matter so
> much once a larger number of clients were involved.  Some of the other
> things you can do to optimize single-client performance aren't as useful
> with lots of them.  Here's how the improvements in this mode worked for me
> on a server with 4 Hyper-Threaded cores (i870); shared_buffers=256MB,
> scale=100:
>
> 1 client:
> -S: 11533
> -S -M prepared: 19498
> -P: 49547
>
> 12 clients, 4 workers:
> -S:  56052
> -S -M prepared: 82043
> -P: 159443
>
> 96 clients, 8 workers:
> -S: 49940
> -S -M prepared: 76099
> -P: 137942
>
> I think this is a really nice new workload to demonstrate.  One of the
> things we tell people is that code works much faster when moved server-side,
> but how much faster isn't always easy to show.  Having this mode available
> lets them see how dramatic that can be quite easily.  I know I'd like to be
> able to run performance tests for clients of new hardware using PostgreSQL
> and tell them something like this:  "With simple clients executing a
> statement at a time, this server reaches 56K SELECTs/section.  But using
> server-side functions to execute them in larger batches it can do 159K".
>
> The value this provides for providing an alternate source for benchmark load
> generation, with a very different profile for how it exercises the server,
> is good too.
>
> Things to fix in the patch before it would be a commit candidate:
>
> -Adjust the loop size/name, per above
> -Reformat some of the longer lines to try and respect the implied right
> margin in the code formatting
> -Don't include the "plgsql function created." line unless in debugging mode.
> -Add the docs.  Focus on how this measures how fast the database can execute
> SELECT statements using server-side code.  An explanation that the
> "transaction" block size is 512 is important to share.  It also needs a
> warning that time based runs ("-T") may have to wait for a block to finish
> and go beyond its normally expected end time.
> -The word "via" in the "transaction type" output description is probably not
> the best choice.  Changing to "SELECT only using PL/pgSQL" would translate
> better, and follow the standard case use for the name of that language.

Sorry it has taken me a year to get back to this patch. I have wanted
to use it, and to ask other people to run it and report their results,
several time recently, so I would like to get it into the core.

I've attached a new patch which addresses several of your concerns,
and adds the documentation. The description is much longer than the
descriptions of other nearby options, which mostly just give a simple
statement of what they do rather than a description of why that is
useful. I don't know if that means I'm starting a good trend, or a
bad one, or I'm just putting the exposition in the wrong place.

In addition to showing the benefits of coding things on the server
side when that is applicable, it also allows hackers to stress parts
of the server code that are not easy to stress otherwise.

Cheers,

Jeff