Content-Type: multipart/alternative;
 boundary="------------0NoE0R15bAEWUv6nqRL0wEL4"
Message-ID: <36f61a71-3bbb-b7b0-0d99-db5e69715af7@garret.ru>
Date: Thu, 15 Jun 2023 10:12:32 +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>
From: Konstantin Knizhnik <knizhnik@garret.ru>
In-Reply-To: <CALDQ5NwfEA+s8O=G-frW6WfRA_yXj_AyhLf2Lb_ZgXWjH3nyEg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/36f61a71-3bbb-b7b0-0d99-db5e69715af7%40garret.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0NoE0R15bAEWUv6nqRL0wEL4
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


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/

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.

There are three different but related directions of improving current 
Postgres:
1. Replacing processes with threads
2. Builtin connection pooler
3. Lightweight backends (shared catalog/relation/prepared statements caches)

The motivation for such changes are also similar:
1. Increase Postgres scalability
2. Reduce memory consumption
3. Make Postgres better fir cloud and serverless requirements

I am not sure now which one should be addressed first or them can be 
done together.

Replacing static variables with thread-local is the first and may be the 
easiest step.
It requires more or less mechanical changes. More challenging thing is 
replacing private per-backend data structures
with shared ones (caches, file descriptors,...)

--------------0NoE0R15bAEWUv6nqRL0wEL4
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <br>
    <div class="moz-cite-prefix">On 15.06.2023 1:23 AM, James Addison
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALDQ5NwfEA+s8O=G-frW6WfRA_yXj_AyhLf2Lb_ZgXWjH3nyEg@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik <a class="moz-txt-link-rfc2396E" href="mailto:knizhnik@garret.ru">&lt;knizhnik@garret.ru&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">


On 12.06.2023 3:23 PM, Pavel Borisov wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">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


</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">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.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hi Konstantin - do you have code/links that you can share for the
prototype and benchmarks used to gather those results?
</pre>
    </blockquote>
    <br>
    <br>
    Sorry, I have already shared the link:<br>
    <a class="moz-txt-link-freetext" href="https://github.com/postgrespro/postgresql.pthreads/">https://github.com/postgrespro/postgresql.pthreads/</a><br>
    <br>
    As you can see last commit was 6 years ago when I stopped work on
    this project.<br>
    Why?  I already tried to explain it:<br>
    - 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,<br>
    because most of the code was not changed - it uses the same sync
    primitives, the same local catalog/relation caches,..<br>
    To <span class="Y2IQFc" lang="en">take all advantage of
      multithreadig model it is necessary to rewrite many components,
      especially related with interprocess communication.<br>
      But maintaining such fork of Postgres and synchronize it with
      mainstream requires too much efforts and I was not able to do it
      myself.<br>
      <br>
      There are three different but related directions of improving
      current Postgres:<br>
      1. Replacing processes with threads<br>
      2. Builtin connection pooler<br>
      3. Lightweight backends (shared catalog/relation/prepared
      statements caches)<br>
      <br>
    </span><span class="Y2IQFc" lang="en"><span class="Y2IQFc" lang="en">The
        motivation for such changes are also similar:<br>
        1. Increase Postgres scalability <br>
        2. Reduce memory consumption<br>
        3. Make Postgres better fir cloud and serverless requirements<br>
        <br>
      </span>I am not sure now which one should be addressed first or
      them can be done together.<br>
      <br>
      Replacing static variables with thread-local is the first and may
      be the easiest step.<br>
      It requires more or less mechanical changes. More challenging
      thing is replacing private per-backend data structures<br>
      with shared ones (caches, file descriptors,...)<br>
      <br>
    </span>
  </body>
</html>

--------------0NoE0R15bAEWUv6nqRL0wEL4--