Message-ID: <2ce25d83-39ea-569d-b73f-d4e9a3eb9cc1@gmail.com>
Date: Thu, 27 Jul 2023 15:27:57 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: Let's make PostgreSQL multi-threaded
Content-Language: en-US
To: Andres Freund <andres@anarazel.de>,
 "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi>
 <4178104.1685978307@sss.pgh.pa.us>
 <4ce6c0f8-e8a4-1672-93fd-49d3fa975ee5@iki.fi>
 <cd27db0c-01ae-ba4d-f141-8712cbb4188c@postgresql.org>
 <20230607213721.al3etgcgtija3ytz@awork3.anarazel.de>
From: David Geier <geidav.pg@gmail.com>
In-Reply-To: <20230607213721.al3etgcgtija3ytz@awork3.anarazel.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/2ce25d83-39ea-569d-b73f-d4e9a3eb9cc1%40gmail.com>
Precedence: bulk

Hi,

On 6/7/23 23:37, Andres Freund wrote:
> I think we're starting to hit quite a few limits related to the process model,
> particularly on bigger machines. The overhead of cross-process context
> switches is inherently higher than switching between threads in the same
> process - and my suspicion is that that overhead will continue to
> increase. Once you have a significant number of connections we end up spending
> a *lot* of time in TLB misses, and that's inherent to the process model,
> because you can't share the TLB across processes.

Another problem I haven't seen mentioned yet is the excessive kernel 
memory usage because every process has its own set of page table entries 
(PTEs). Without huge pages the amount of wasted memory can be huge if 
shared buffers are big.

For example with 256 GiB of used shared buffers a single process needs 
about 256 MiB for the PTEs (for simplicity I ignored the tree structure 
of the page tables and just took the number of 4k pages times 4 bytes 
per PTE). With 512 connections, which is not uncommon for machines with 
many cores, a total of 128 GiB of memory is just spent on page tables.

We used non-transparent huge pages to work around this limitation but 
they come with plenty of provisioning challenges, especially in cloud 
infrastructures where different services run next to each other on the 
same server. Transparent huge pages have unpredictable performance 
disadvantages. Also if some backends only use shared buffers sparsely, 
memory is wasted for the remaining, unused range inside the huge page.

-- 
David Geier
(ServiceNow)