pgbench - rework variable management

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

pgbench - rework variable management

Fabien COELHO-3

Hello pgdevs,

The attached patch improves pgbench variable management as discussed in:

https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1904081752210.5867@lancre

As discussed there as well, the overall effect is small compared to libpq
& system costs when pgbench is talking to a postgres server. When someone
says "pgbench is slow", they really mean "libpq & <my-system> are slow",
because pgbench does not do much beyond jumping from one libpq call to the
next. Anyway, the patch has a measurable positive effect.

###

Rework pgbench variables and associated values for better performance

  - a (hopefully) thread-safe symbol table which maps variable names to integers
    note that all variables are statically known, but \gset stuff.
  - numbers are then used to access per-client arrays

The symbol table stores names as distinct leaves in a tree on bytes.
Each symbol name is the shortest-prefix leaf, possibly including the final
'\0'. Some windows-specific hacks are note tested. File "symbol_table_test.c"
does what it says and can be compiled standalone.

Most malloc/free cycles are taken out of running a benchmark:
  - there is a (large?) maximum number of variables of 32*MAX_SCRIPTS
  - variable names and string  values are statically allocated,
    and limited to, 64 bytes
  - a per-client persistent buffer is used for various purpose,
    to avoid mallocs/frees.

Functions assignVariables & parseQuery basically shared the same variable
substitution logic, but differed in what was substituted. The logic has been
abstracted into a common function.

This patch brings pgbench-specific overheads down on some tests, one
thread one client, on my laptop, with the attached scripts, in tps:
  - set_x_1.sql: 11.1M -> 14.2M
  - sets.sql: 0.8M -> 2.7M # 20 \set
  - set.sql: 1.5M -> 2.0M # 3 \set & "complex" expressions
  - empty.sql: 63.9K -> 64.1K (…)
  - select_aid.sql: 29.3K -> 29.3K
  - select_aids.sql: 23.4K -> 24.2K
  - gset_aid.sql: 28.3K -> 29.2K

So we are talking significant improvements on pgbench-only scripts, only
a few percents once pgbench must interact with a CPU-bound server, because
time is spent elsewhere.

--
Fabien.

empty.sql (6 bytes) Download Attachment
select_aids.sql (108 bytes) Download Attachment
select_aid.sql (100 bytes) Download Attachment
pgbench-symbols-2.patch (67K) Download Attachment
set_x_1.sql (14 bytes) Download Attachment
sets.sql (294 bytes) Download Attachment
gset_aid.sql (92 bytes) Download Attachment