Re: pgbench - implement strict TPC-B benchmark

Hello Andres,

>> Which is a (somehow disappointing) * 3.3 speedup. The impact on the 3
>> complex expressions tests is not measurable, though.
>
> I don't know why that could be disappointing. We put in much more work
> for much smaller gains in other places.

Probably, but I thought I would have a better deal by eliminating most 
string stuff from variables.

>> Questions:
>>  - how likely is such a patch to pass? (IMHO not likely)
>
> I don't see why? I didn't review the patch in any detail, but it didn't
> look crazy in quick skim? Increasing how much load can be simulated
> using pgbench, is something I personally find much more interesting than
> adding capabilities that very few people will ever use.

Yep, but my point is that the bottleneck is mostly libpq/system, as I 
tried to demonstrate with the few experiments I reported.

> FWIW, the areas I find current pgbench "most lacking" during development
> work are:
>
> 1) Data load speed. The data creation is bottlenecked on fprintf in a
>   single process.

snprintf actually, could be replaced.

I submitted a patch to add more control on initialization, including a 
server-side loading feature, i.e. the client does not send data, the 
server generates its own, see 'G':

     https://commitfest.postgresql.org/24/2086/

However on my laptop it is slower than client-side loading on a local 
socket. The client version is doing around 70 MB/s, the client load is 
20-30%, postgres load is 85%, but I'm not sure I can hope for much more on 
my SSD. On my laptop the bottleneck is postgres/disk, not fprintf.

> The index builds are done serially. The vacuum could be replaced by COPY 
> FREEZE.

Well, it could be added?

> For a lot of meaningful tests one needs 10-1000s of GB of testdata - 
> creating that is pretty painful.

Yep.

> 2) Lack of proper initialization integration for custom
>   scripts.

Hmmm…

You can always write a psql script for schema and possibly simplistic data 
initialization?

However, generating meaningful pseudo-random data for an arbitrary schema 
is a pain. I did an external tool for that a few years ago:

     http://www.coelho.net/datafiller.html

but it is still a pain.

> I.e. have steps that are in the custom script that allow -i, vacuum, etc 
> to be part of the script, rather than separately executable steps. 
> --init-steps doesn't do anything for that.

Sure. It just gives some control.

> 3) pgbench overhead, although that's to a significant degree libpq's fault

I'm afraid that is currently the case.

> 4) Ability to cancel pgbench and get approximate results. That currently
>   works if the server kicks out the clients, but not when interrupting
>   pgbench - which is just plain weird.  Obviously that doesn't matter
>   for "proper" benchmark runs, but often during development, it's
>   enough to run pgbench past some events (say the next checkpoint).

Do you mean have a report anyway on "Ctrl-C"?

I usually do a -P 1 to see the progress, but making Ctrl-C work should be 
reasonably easy.

>>  - what is its impact to overall performance when actual queries
>>    are performed (IMHO very small).
>
> Obviously not huge - I'd also not expect it to be unobservably small
> either.

Hmmm… Indeed, the 20 \set script runs at 2.6 M/s, that is 0.019 µs per 
\set, and any discussion over the connection is at least 15 µs (for one 
client on a local socket).

-- 
Fabien.