Date: Fri, 5 Mar 2021 14:05:22 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Georgios <gkokolatos@protonmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	Julien Rouhaud <julien.rouhaud@free.fr>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	"robertmhaas@gmail.com" <robertmhaas@gmail.com>
Subject: Re: Shared memory size computation oversight?
Message-ID: <20210305060522.5ogugff254szu5hf@nol>
References: <20210227080815.jryyhy6lrn6gahkm@nol>
 <SeVx6SxBxMHIm_66gE4M8rzAn5vqXw8akcyJ-26bJxHeNC_wW9fN-xDr_G3AGiSH328-rxMzXbNf6H4TsaNIG-OJFE8LvmdflG9Zb-yIKq4=@protonmail.com>
 <1183451.1614788634@sss.pgh.pa.us>
 <20210303164052.f336wina7qpchabd@nol>
 <YECGppkDKJArPOUb@paquier.xyz>
 <20210304072333.4yydog47md7cfrda@nol>
 <YECYj2KORHb06xbA@paquier.xyz>
 <GbQO126b8MXGYDkI94ilDHoxqywWUmuvOEj85ocdMIPp8er2GTA_rTFQVBIGuWXu9UYu_xCaBETEkERwMxNIoJzMjDXnATbzrU8XnY_R3TQ=@protonmail.com>
 <1374257.1614880434@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1374257.1614880434@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/20210305060522.5ogugff254szu5hf%40nol>
Precedence: bulk

On Thu, Mar 04, 2021 at 12:53:54PM -0500, Tom Lane wrote:
> My conclusion is that we don't need to do anything, indeed the proposed
> changes will probably just lead to overestimation.
> 
> It's certainly possible that there's something amiss somewhere.  These
> numbers were all taken with out-of-the-box configuration, so it could be
> that changing some postgresql.conf entries would expose that some module
> is not scaling its request correctly.  Also, I don't have any extensions
> loaded, so this proves nothing about the correctness of any of those.
> But it appears to me that the general scheme is working perfectly fine,
> so we do not need to complicate it.

Thanks for looking at it.  I agree that most of the changes aren't worth the
complication and risk to overestimate the shmem size.

But I did some more experiments comparing the current version and the patched
version of the lock and pred lock shared hash table size estimations with some
configuration changes, and I find the following delta in the estimated size:

- original configuration : 15kB
- max_connection = 1000 : 30kB
- max_connection = 1000 and max_locks_per_xact = 256 : 96kB

I don't know if my version is totally wrong or not, but if it's not it could be
worthwhile to apply the hash tab part as it would mean that it doesn't scale
properly.