Re: pgbench - implement strict TPC-B benchmark

Hello Andres,

Thanks a lot for these feedbacks and comments.

> Using pgbench -Mprepared -n -c 8 -j 8 -S pgbench_100 -T 10 -r -P1
> e.g. shows pgbench to use 189% CPU in my 4/8 core/thread laptop. That's
> a pretty significant share.

Fine, but what is the corresponding server load? 211%? 611%? And what 
actual time is spent in pgbench itself, vs libpq and syscalls?

Figures and discussion below.

> And before you argue that that's just about a read-only workload:

I'm fine with worth case scenarii:-) Let's do the worse for my 2 cores 
running at 2.2 GHz laptop:


(0) we can run a really do nearly nothing script:

   sh> cat nope.sql
   \sleep 0
   # do not sleep, so stay awake…

   sh> time pgbench -f nope.sql -T 10 -r
   latency average = 0.000 ms
   tps = 12569499.226367 (excluding connections establishing) # 12.6M
   statement latencies in milliseconds:
          0.000  \sleep 0
   real 0m10.072s, user 0m10.027s, sys 0m0.012s

Unsurprisingly pgbench is at about 100% cpu load, and the transaction cost 
(transaction loop and stat collection) is 0.080 µs (1/12.6M) per script 
execution (one client on one thread).


(1) a pgbench complex-commands-only script:

   sh> cat set.sql
   \set x random_exponential(1, :scale * 10, 2.5) + 2.1
   \set y random(1, 9) + 17.1 * :x
   \set z case when :x > 7 then 1.0 / ln(:y) else 2.0 / sqrt(:y) end

   sh> time pgbench -f set.sql -T 10 -r
   latency average = 0.001 ms
   tps = 1304989.729560 (excluding connections establishing) # 1.3M
   statement latencies in milliseconds:
     0.000  \set x random_exponential(1, :scale * 10, 2.5) + 2.1
     0.000  \set y random(1, 9) + 17.1 * :x
     0.000  \set z case when :x > 7 then 1.0 / ln(:y) else 2.0 / sqrt(:y) end
   real 0m10.038s, user 0m10.003s, sys 0m0.000s

Again pgbench load is near 100%, with only pgbench stuff (thread loop, 
expression evaluation, variables, stat collection) costing about 0.766 µs 
cpu per script execution. This is about 10 times the previous case, 90% of 
pgbench cpu cost is in expressions and variables, not a surprise.

Probably this under-a-µs could be reduced… but what overall improvements 
would it provide? An answer with the last test:


(2) a ridiculously small SQL query, tested through a local unix socket:

   sh> cat empty.sql
   ;
   # yep, an empty query!

   sh> time pgbench -f empty.sql -T 10 -r
   latency average = 0.016 ms
   tps = 62206.501709 (excluding connections establishing) # 62.2K
   statement latencies in milliseconds:
          0.016  ;
   real 0m10.038s, user 0m1.754s, sys 0m3.867s

We are adding minimal libpq and underlying system calls to pgbench 
internal cpu costs in the most favorable (or worst:-) sql query with the 
most favorable postgres connection.

Apparent load is about (1.754+3.867)/10.038 = 56%, so the cpu cost per 
script is 0.56 / 62206.5 = 9 µs, over 100 times the cost of a do-nothing 
script (0), and over 10 times the cost of a complex expression command 
script (1).

Conclusion: pgbench-specific overheads are typically (much) below 10% of 
the total client-side cpu cost of a transaction, while over 90% of the cpu 
cost is spent in libpq and system, for the worst case do-nothing query.

A perfect bench driver which would have zero overheads would reduce the 
cpu cost by at most 10%, because you still have to talk to the database. 
through the system. If pgbench cost were divided by two, which would be a 
reasonable achievement, the benchmark client cost would be reduced by 5%.

Wow?

I have already given some thought in the past to optimize "pgbench", 
especially to avoid long switches (eg in expression evaluation) and maybe 
to improve variable management, but as show above I would not expect a 
gain worth the effort and assume that a patch would probably be justly 
rejected, because for a realistic benchmark script these costs are already 
much less than other inevitable libpq/syscall costs.

That does not mean that nothing needs to be done, but the situation is 
currently quite good.

In conclusion, ISTM that current pgbench allows to saturate a postgres 
server with a client significantly smaller than the server, which seems 
like a reasonable benchmarking situation. Any other driver in any other 
language would necessarily incur the same kind of costs.


> [...] And the largest part of the overhead is in pgbench's interpreter 
> loop:

Indeed, the figures below are very interesting! Thanks for collecting 
them.

> +   12.35%  pgbench  pgbench                [.] threadRun
> +    3.54%  pgbench  pgbench                [.] dopr.constprop.0
> +    3.30%  pgbench  pgbench                [.] fmtint
> +    1.93%  pgbench  pgbench                [.] getVariable

~ 21%, probably some inlining has been performed, because I would have 
expected to see significant time in "advanceConnectionState".

> +    2.95%  pgbench  libpq.so.5.13          [.] PQsendQueryPrepared
> +    2.15%  pgbench  libpq.so.5.13          [.] pqPutInt
> +    4.47%  pgbench  libpq.so.5.13          [.] pqParseInput3
> +    1.66%  pgbench  libpq.so.5.13          [.] pqPutMsgStart
> +    1.63%  pgbench  libpq.so.5.13          [.] pqGetInt

~ 13%

> +    3.16%  pgbench  libc-2.28.so           [.] __strcmp_avx2
> +    2.95%  pgbench  libc-2.28.so           [.] malloc
> +    1.85%  pgbench  libc-2.28.so           [.] ppoll
> +    1.85%  pgbench  libc-2.28.so           [.] __strlen_avx2
> +    1.85%  pgbench  libpthread-2.28.so     [.] __libc_recv

~ 11%, str is a pain… Not sure who is calling though, pgbench or libpq.

This is basically 47% pgbench, 53% lib*, on the sample provided. I'm 
unclear about where system time is measured.

> And that's the just the standard pgbench read/write case, without
> additional script commands or anything.

> Well, duh, that's because you're completely IO bound. You're doing
> 400tps. That's *nothing*. All you're measuring is how fast the WAL can
> be fdatasync()ed to disk.  Of *course* pgbench isn't a relevant overhead
> in that case.  I really don't understand how this can be an argument.

Sure. My interest in running it was to show that the \set stuff was 
ridiculous compared to processing an actual SQL query, but it does not 
allow to analyze all overheads. I hope that the 3 above examples allow to 
make my point more understandable.

>> Also, pgbench overheads must be compared to an actual client application,
>> which deals with a database through some language (PHP, Python, JS, Java…)
>> the interpreter of which would be written in C/C++ just like pgbench, and
>> some library (ORM, DBI, JDBC…), possibly written in the initial language and
>> relying on libpq under the hood. Ok, there could be some JIT involved, but
>> it will not change that there are costs there too, and it would have to do
>> pretty much the same things that pgbench is doing, plus what the application
>> has to do with the data.
>
> Uh, but those clients aren't all running on a single machine.

Sure.

The cumulated power of the clients is probably much larger than the 
postgres server itself, and ISTM that pgbench allows to simulate such 
things with much smaller client-side requirements, and that any other tool 
could not do much better.

-- 
Fabien.