pgbench - rework variable management
Hello pgdevs,
The attached patch improves pgbench variable management as discussed in:
/messages/by-id/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.
On Wed, Aug 14, 2019 at 3:54 AM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Some windows-specific hacks are note tested.
Somehow this macro hackery has upset the Windows socket headers:
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.55019
--
Thomas Munro
https://enterprisedb.com
Some windows-specific hacks are note tested.
Somehow this macro hackery has upset the Windows socket headers:
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.55019
I noticed, but I do not have any windows host so I cannot test locally.
The issue is how to do a mutex on Windows, which does not have pthread so
it has to be emulated. I'll try again by sending a blind update to the
patch and see how it goes.
--
Fabien.
On Tue, Sep 3, 2019 at 4:57 PM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
I noticed, but I do not have any windows host so I cannot test locally.
The issue is how to do a mutex on Windows, which does not have pthread so
it has to be emulated. I'll try again by sending a blind update to the
patch and see how it goes.
If you have the patience and a github account, you can push code onto
a public github branch having also applied the patch mentioned at
https://wiki.postgresql.org/wiki/Continuous_Integration, go to
appveyor.com and tell it to watch your git hub account, and then it'll
build and test every time you push a new tweak. Takes a few minutes
to get the answer each time you try something, but I have managed to
get things working on Windows that way.
--
Thomas Munro
https://enterprisedb.com
Hello Thomas,
I noticed, but I do not have any windows host so I cannot test locally.
The issue is how to do a mutex on Windows, which does not have pthread so
it has to be emulated. I'll try again by sending a blind update to the
patch and see how it goes.If you have the patience and a github account, you can push code onto
a public github branch having also applied the patch mentioned at
https://wiki.postgresql.org/wiki/Continuous_Integration, go to
appveyor.com and tell it to watch your git hub account, and then it'll
build and test every time you push a new tweak. Takes a few minutes
to get the answer each time you try something, but I have managed to
get things working on Windows that way.
Thanks for the tip.
I'll try that if the blind attempt attached version does not work.
--
Fabien.
Attachments:
pgbench-symbols-3.patchtext/x-diff; name=pgbench-symbols-3.patchDownload+802-356
Patch v4 is a just a rebase.
--
Fabien.
Attachments:
pgbench-symbols-4.patchtext/x-diff; name=pgbench-symbols-4.patchDownload+802-355
Patch v4 is a just a rebase.
Patch v5 is a rebase with some adjustements.
--
Fabien.
Attachments:
pgbench-symbols-5.patchtext/x-diff; name=pgbench-symbols-5.patchDownload+808-357
Hi Fabien,
On 1/9/20 5:04 PM, Fabien COELHO wrote:
Patch v4 is a just a rebase.
Patch v5 is a rebase with some adjustements.
This patch is failing on the Windows build:
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.85698
I'm not sure if this had been fixed in v3 and this is a new issue or if
it has been failing all along. Either way, it should be updated.
Marked Waiting on Author.
BTW -- sorry if I seem to be picking on your patches but these happen to
be the patches with the longest time since any activity.
Regards,
--
-David
david@pgmasters.net
Patch v5 is a rebase with some adjustements.
This patch is failing on the Windows build:
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.85698I'm not sure if this had been fixed in v3 and this is a new issue or if it
has been failing all along. Either way, it should be updated.
I don't do windows, so the mutex stuff for windows is just blind
programming.
Marked Waiting on Author.
BTW -- sorry if I seem to be picking on your patches but these happen to be
the patches with the longest time since any activity.
Basically, my areas of interest do not match committers' areas of
interest.
v6 is a yet-again blind attempt at fixing the windows mutex. If someone
with a windows could tell me if it is ok, and if not what to fix, it would
be great.
--
Fabien.
Attachments:
pgbench-symbols-6.patchtext/x-diff; name=pgbench-symbols-6.patchDownload+808-357
On 3/27/20 6:25 PM, Fabien COELHO wrote:
Patch v5 is a rebase with some adjustements.
This patch is failing on the Windows build:
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.85698I'm not sure if this had been fixed in v3 and this is a new issue or
if it has been failing all along.� Either way, it should be updated.I don't do windows, so the mutex stuff for windows is just blind
programming.Marked Waiting on Author.
BTW -- sorry if I seem to be picking on your patches but these happen
to be the patches with the longest time since any activity.Basically, my areas of interest do not match committers' areas of interest.
v6 is a yet-again blind attempt at fixing the windows mutex. If someone
with a windows could tell me if it is ok, and if not what to fix, it
would be great.
Regarding Windows testing you may find this thread useful:
/messages/by-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q@mail.gmail.com
--
-David
david@pgmasters.net
On Fri, Mar 27, 2020 at 06:42:49PM -0400, David Steele wrote:
Regarding Windows testing you may find this thread useful:
/messages/by-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q@mail.gmail.com
Since then, the patch is failing to apply. As this got zero activity
for the last six months, I am marking the entry as returned with
feedback in the CF app.
--
Michael
Bonjour Michaël,
/messages/by-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q@mail.gmail.com
Since then, the patch is failing to apply. As this got zero activity
for the last six months, I am marking the entry as returned with
feedback in the CF app.
Hmmm… I did not notice it did not apply anymore. I do not have much time
to contribute much this round and probably the next as well, so fine with
me.
--
Fabien.