Message-ID: <67370d03-9244-c7eb-1b87-8052659457ba@garret.ru>
Date: Thu, 15 Jun 2023 22:36:30 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Let's make PostgreSQL multi-threaded
Content-Language: en-US
To: James Addison <jay@jp-hosting.net>
Cc: Pavel Borisov <pashkin.elfe@gmail.com>,
 Dilip Kumar <dilipbalaut@gmail.com>, Hannu Krosing <hannuk@google.com>,
 Heikki Linnakangas <hlinnaka@iki.fi>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi>
 <CAMT0RQR+NLJw6hmN88D7RPe=vUEr7DB52dKmS9tWPO_eX2Np0g@mail.gmail.com>
 <CAFiTN-vJqo4TSBpkQTJqhYz6CL0M=cPhQZUXnop1uDC47s2hBg@mail.gmail.com>
 <CALT9ZEH_ZT6Fv8KFmEPf2qM7g0Y0mdU-J4PQDVRN+vhn0CLSOQ@mail.gmail.com>
 <2c2665d2-c513-c12e-9097-9b1805bc2471@garret.ru>
 <CALDQ5NwfEA+s8O=G-frW6WfRA_yXj_AyhLf2Lb_ZgXWjH3nyEg@mail.gmail.com>
 <36f61a71-3bbb-b7b0-0d99-db5e69715af7@garret.ru>
 <CALDQ5NwotYMZtXA2z6EkBQ72jVBrfyrn-+a9xU8=w54VBQPOhg@mail.gmail.com>
From: Konstantin Knizhnik <knizhnik@garret.ru>
In-Reply-To: <CALDQ5NwotYMZtXA2z6EkBQ72jVBrfyrn-+a9xU8=w54VBQPOhg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/67370d03-9244-c7eb-1b87-8052659457ba%40garret.ru>
Precedence: bulk


On 15.06.2023 11:41 AM, James Addison wrote:
> On Thu, 15 Jun 2023 at 08:12, Konstantin Knizhnik <knizhnik@garret.ru> wrote:
>>
>>
>> On 15.06.2023 1:23 AM, James Addison wrote:
>>
>> On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik <knizhnik@garret.ru> wrote:
>>
>>
>> On 12.06.2023 3:23 PM, Pavel Borisov wrote:
>>
>> Is the following true or not?
>>
>> 1. If we switch processes to threads but leave the amount of session
>> local variables unchanged, there would be hardly any performance gain.
>> 2. If we move some backend's local variables into shared memory then
>> the performance gain would be very near to what we get with threads
>> having equal amount of session-local variables.
>>
>> In other words, the overall goal in principle is to gain from less
>> memory copying wherever it doesn't add the burden of locks for
>> concurrent variables access?
>>
>> Regards,
>> Pavel Borisov,
>> Supabase
>>
>>
>> IMHO both statements are not true.
>> Switching to threads will cause less context switch overhead (because
>> all threads are sharing the same memory space and so preserve TLB.
>> How big will be this advantage? In my prototype I got ~10%. But may be
>> it is possible to fin workloads when it is larger.
>>
>> Hi Konstantin - do you have code/links that you can share for the
>> prototype and benchmarks used to gather those results?
>>
>>
>>
>> Sorry, I have already shared the link:
>> https://github.com/postgrespro/postgresql.pthreads/
> Nope, my mistake for not locating the existing link - thank you.
>
> Is there a reason that parser-related files (flex/bison) are added as
> part of the changeset?  (I'm trying to narrow it down to only the
> changes necessary for the functionality.  so far it looks mostly
> fairly minimal, which is good.  the adjustments to progname are
> another thing that look a bit unusual/maybe unnecessary for the
> feature)

Sorry, absolutely no reason - just my fault.

>> As you can see last commit was 6 years ago when I stopped work on this project.
>> Why?  I already tried to explain it:
>> - benefits from switching to threads were not so large. May be I just failed to fid proper workload, but is was more or less expected result,
>> because most of the code was not changed - it uses the same sync primitives, the same local catalog/relation caches,..
>> To take all advantage of multithreadig model it is necessary to rewrite many components, especially related with interprocess communication.
>> But maintaining such fork of Postgres and synchronize it with mainstream requires too much efforts and I was not able to do it myself.
> I get the feeling that there are probably certain query types or
> patterns where a significant, order-of-magnitude speedup is possible
> with threads - but yep, I haven't seen those described in detail yet
> on the mailing list (but as hinted by my not noticing the github link
> previously, maybe I'm not following the list closely enough).
>
> What workloads did you try with your version of the project?

I do not remember now precisely (6 years passed).
But definitely I tried pgbench, especially read-only pgbench (to be more 
CPU rather than disk bounded)