Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkQiJ-00015e-Er for pgsql-general@arkaria.postgresql.org; Thu, 06 Apr 2023 14:35:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pkQhK-0001uj-5K for pgsql-general@arkaria.postgresql.org; Thu, 06 Apr 2023 14:34:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkQhJ-0001uY-F4 for pgsql-general@lists.postgresql.org; Thu, 06 Apr 2023 14:34:45 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkQhB-0001Qc-Nd for pgsql-general@postgresql.org; Thu, 06 Apr 2023 14:34:43 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id F325E5F7B9; Thu, 6 Apr 2023 10:34:30 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1680791671; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=uyWlH9FBAvGbqP7HdWJVwyE5TNTpANSsFTPkbR5FuWE=; b=B2sUmUscfC3Nr/9Smls9zv4NsUnjy53wKEx92rXqFClFsmbQu1dchml/h+OrotO/45fRFN 1E5Wdgcf6sXg+Hr7cBo0MgfxWQM+VNyKz31q/7QRxinrHAf4VeSmQ/TUH1QmYvl6x5APzi Xs5cJuPOV+NJTLaV7D1zVTAr/DK/Tzk= Date: Thu, 6 Apr 2023 10:34:30 -0400 From: Stephen Frost To: Marco Fortina Cc: "pgsql-general@postgresql.org" Subject: Re: R: Proposal: Shared Work Mem Area Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="RgBwXLHwGaz8GUZD" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --RgBwXLHwGaz8GUZD Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, Please don't top-post on the PG mailing lists, it makes it harder to follow the discussion. * Marco Fortina (marco_fortina@hotmail.it) wrote: > https://commitfest.postgresql.org/42/3867/ is not exacly what I proposed = as new feature to developers. I understood what you were proposing. > If I'm not wrong, almost all main memory areas have a fixed size: >=20 > shared_buffers > effective_cache_size > wal_buffers >=20 > Instead, work_mem is per-process dynamically allocated up to defined size= limit. That's not how work_mem works actually. It's a per-node amount and it's not a per-process overall limit, nor is it really a hard limit though some nodes will do their best to respect the amount configured. > What I suggested is to replace work_mem from per-process allocation to gl= obal and fixed size allocation (see pga_aggregate_target on Oracle) and sha= red to worker processes. I understood the suggestion and it's a lot of work for an unclear gain. You noted that having it be pulled from a single area would allow administrators to configure an overall memory usage limit- but that's not the only way to do that and there's an existing effort to do exactly that already underway that's a lot simpler than what you're proposing. While there might be other advantages to having a shared memory segment be used for work_mem, you've not outlined any. > Let's assume the new parameter name is worker_mem_area and this was set t= o 8GB: with my proposal method each worker process do not use it's own dedi= cated work_mem but the shared one. I understand your suggestion, but making such a large change just to make it isn't sensible, there should be reasoning behind why that's better than what we're doing already or proposing to do. > In this way each worker is also able to peek free pages from the worker_m= em_area if needed. This can be done with the existing approach and doesn't require a shared memory segment for work_mem. We are pretty far from having an actual acceptance system for queries though but I do agree that would be a useful thing to work towards. I don't know that it requires work_mem being in shared memory though. Thanks, Stephen --RgBwXLHwGaz8GUZD Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmQu2HMACgkQ7WyKOINH ZFXwlw//YNi33yvmnCwL6a9Hlu4JlDFbrYPa8oUwDLXF2A/Bc8tGYvqQ80/oHno/ 4zLnx3p5ayG5mWsgbtnwunO0RMITv2hKqvucw9PZe1fyJ/zoOgAXHD9N0XswWDpC FSIUynE8uYNJ3TNh11UvVA3Ln+8hJwwujT5f7LMT/Rvyx79Z+HebvGlJeLkO8mcE NkVA4aXFxtT7GwSPRElrio1LZKSVqzNaoZ+9I9lzvyH/Dx4BrqHQlqbVUirBTSUR T+nThbfP/XyHlIknmgkTh5wREfz1qEA8biMnnMNRYeO/kpp8wbAImcRvpmzzUkhS h1vCg9g2DfxCQyuSwAeCn/xnTgyOGKx7OIZ4Z/loyTnJr4/5TpfgdaUJ4T7RM1lK LS9Mns6Ffjeri/jctaV9MRF4c8z3VhxHQ9YLqAC868sKrDA6C6PFu5tU82MvCn8z ogsq+X81XI9IhTXA/TIEzAsZQJnhiFzhC1/P1gXBK6OIBUntrytUTG5RH+WHgnRK g6SxNDu+UUxLLJXCkFKQGPjcCNXrmCw//FVYL3PKt6MF0I/uO5WzDkFsuZYv2OEz sRjJbBXi9wmUhtlvQQCdt1OEnd0NDfBYWLyAyNCH60YYA0jFGlQCxa5ItDYuFxao EBirCVec/1qJ3yJzNZ6xv4QOKQ7SZEVhqDs+PtMrhM5XNuAbHqw= =i1/r -----END PGP SIGNATURE----- --RgBwXLHwGaz8GUZD--