Thread: pgbench - rework variable management

pgbench - rework variable management

From
Fabien COELHO
Date:
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.
Attachment

Re: pgbench - rework variable management

From
Thomas Munro
Date:
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



Re: pgbench - rework variable management

From
Fabien COELHO
Date:
>> 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.



Re: pgbench - rework variable management

From
Thomas Munro
Date:
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



Re: pgbench - rework variable management

From
Fabien COELHO
Date:
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.
Attachment

Re: pgbench - rework variable management

From
Fabien COELHO
Date:
Patch v4 is a just a rebase.

-- 
Fabien.
Attachment

Re: pgbench - rework variable management

From
Fabien COELHO
Date:
> Patch v4 is a just a rebase.

Patch v5 is a rebase with some adjustements.

-- 
Fabien.
Attachment

Re: pgbench - rework variable management

From
David Steele
Date:
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



Re: pgbench - rework variable management

From
Fabien COELHO
Date:
>> 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.

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.
Attachment

Re: pgbench - rework variable management

From
David Steele
Date:
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.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.
> 
> 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:

https://www.postgresql.org/message-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q%40mail.gmail.com

-- 
-David
david@pgmasters.net



Re: pgbench - rework variable management

From
Michael Paquier
Date:
On Fri, Mar 27, 2020 at 06:42:49PM -0400, David Steele wrote:
> Regarding Windows testing you may find this thread useful:
>
> https://www.postgresql.org/message-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q%40mail.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

Attachment

Re: pgbench - rework variable management

From
Fabien COELHO
Date:
Bonjour Michaël,

>> https://www.postgresql.org/message-id/CAMN686ExUKturcWp4POaaVz3gR3hauSGBjOCd0E-Jh1zEXqf_Q%40mail.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.