Re: pgbench--new transaction type

On Sun, Jun 19, 2011 at 9:34 PM, Itagaki Takahiro
<itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> On Mon, Jun 20, 2011 at 07:30, 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
>
> Is it OK to define the value as constant?

I think so. I think anyone in a position to want to change it would
not be adverse to recompiling.

I consider it analogous to nbranches, ntellers, and naccounts, which
are also constants.

> Also, it executes much more queries if -t option (transactions) specified;
> Of course it runs the specified number of "transactions", but actually
> runs plpgsql_loops times than other modes.

Am I being overly punctilious in maintaining the distinction between a
transaction proper, and a select?

In a similar vane, the reporting where I have both a tps and a select
per second, seems a bit messy, but I wanted to be overly-explicit, at
least until someone recommended a less confusing alternative.

>> 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,
>
> What is the most important part of the changes? The proposal includes
> 3 improvements. It might defocus the most variable tuning point.
>
>  #1 Execute multiple queries in one transaction.
>  #2 Run multiple queries in the server with stored procedure.
>  #3 Return only one value instead of 512.

#2 is the most important change. The other changes are just "along
for the ride" as a side effect of #2.

I think #1 issue is probably minor in single-client cases, although it
can avoid major contention in multi client cases (although recent work
by Robert Haas may alleviate much of that).
Since transactions cannot be started and ended inside server-side
code, I am not able to isolate and remove #1 from the rest of my
changes. One can take the other approach, however, by running queries
the normal way except all in one transaction, as a comparison. The
"-1" option of the attached toy patch does that (applies to head,
minor conflict at getopt if applied over the main patch of this
thread). Numbers for various combination in single client
(unfortunately, run on slightly slower CPU than my previous example):

9,164.85 -S
10,144.71 -S -1
13,980.64 -S -M prepared
16,004.97 -S -M prepared -1
39,600.67 -P

I had never even considered #3--it is just an accident of how I wrote
the code. I only returned anything at all because a) in early code I
wanted to see the sum, just as a sanity check that the returned value
seemed reasonable, to indicate it was doing what I thought it was
doing, and b) I was worried some optimizer might decide to avoid
executing the selects altogether, if it detected the results of them
were never used. Should I find a way to return 512 values from a
single function call, either as part of the submitted code, or just as
a side test to show if it makes any difference?

> Anyway, I'm not sure we need to include the query mode into the pgbench's
> codes. Instead, how about providing "a sample script" as a separate sql
> file? pgbench can execute any script files with -f option.

In the absence of -s and presence of -f, :scale gets set to 1, rather
than to "select count(*) from pgbench_branches".

I don't think it is nice to rely on people to correctly specify -s. I
would like to change -f so that in the absence of -s it uses the same
scale as -S, etc., do. But that would probably be too backwards
incompatible to be an acceptable change.

The other thing would be doing initialization, like creating the
function in this case. Perhaps this could be solved by adding a new
line prefix category to the -f language. Now "\" indicates a
metacommand to be done by pgbench itself. Maybe "!" could indicate a
real SQL command, but one that would be submitted only upon reading
the -f file, and not once per execution. This one might be backwards
compatible, as I don't see why anyone would have historical sql files
sitting around that have lines starting with "!".

Cheers,

Jeff