Thread: pgbench - rework variable management
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
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.
Attachment
Patch v4 is a just a rebase. -- Fabien.
Attachment
> Patch v4 is a just a rebase. Patch v5 is a rebase with some adjustements. -- Fabien.
Attachment
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.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
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
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
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.