Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gjThJ-0000yx-NN for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Jan 2019 18:44:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gjThI-0006vm-56 for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Jan 2019 18:44:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gjThH-0006vf-Qq for pgsql-hackers@lists.postgresql.org; Tue, 15 Jan 2019 18:44:23 +0000 Received: from aart.rice.edu ([168.7.56.48]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gjThE-0002MX-Eb for pgsql-hackers@lists.postgresql.org; Tue, 15 Jan 2019 18:44:21 +0000 Received: by aart.rice.edu (Postfix, from userid 18612) id A6ADE427F283; Tue, 15 Jan 2019 12:44:19 -0600 (CST) Date: Tue, 15 Jan 2019 12:44:19 -0600 From: Kenneth Marshall To: Tom Lane Cc: "Tsunakawa, Takayuki" , 'Kyotaro HORIGUCHI' , "Ideriha, Takeshi" , "pgsql-hackers@lists.postgresql.org" , "alvherre@alvh.no-ip.org" , "andres@anarazel.de" , "robertmhaas@gmail.com" , "michael.paquier@gmail.com" , "david@pgmasters.net" , "Jim.Nasby@bluetreble.com" , "craig@2ndquadrant.com" Subject: Re: Protect syscache from bloating with negative cache entries Message-ID: <20190115184419.GF22649@aart.rice.edu> References: <4E72940DA2BF16479384A86D54D0988A6F1BCB6F@G01JPEXMBKW04> <20181019.173457.68080786.horiguchi.kyotaro@lab.ntt.co.jp> <4E72940DA2BF16479384A86D54D0988A6F1F4165@G01JPEXMBKW04> <20181127.193622.252197705.horiguchi.kyotaro@lab.ntt.co.jp> <4E72940DA2BF16479384A86D54D0988A6F3D09C1@G01JPEXMBKW04> <4E72940DA2BF16479384A86D54D0988A6F3D0D5F@G01JPEXMBKW04> <7925.1547397565@sss.pgh.pa.us> <0A3221C70F24FB45833433255569204D1FB67733@G01JPEXMBYT05> <2365.1547577156@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2365.1547577156@sss.pgh.pa.us> User-Agent: Mutt/1.5.21 (2010-09-15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, Jan 15, 2019 at 01:32:36PM -0500, Tom Lane wrote: > ... > > FYI, Oracle provides one parameter, shared_pool_size, that determine the > > size of a memory area that contains SQL plans and various dictionary > > objects. Oracle decides how to divide the area among constituents. So > > it could be possible that one component (e.g. table/index metadata) is > > short of space, and another (e.g. SQL plans) has free space. Oracle > > provides a system view to see the free space and hit/miss of each > > component. If one component suffers from memory shortage, the user > > increases shared_pool_size. This is similar to what Horiguchi-san is > > proposing. > > Oracle seldom impresses me as having designs we ought to follow. > They have a well-earned reputation for requiring a lot of expertise to > operate, which is not the direction this project should be going in. > In particular, I don't want to "solve" cache size issues by exposing > a bunch of knobs that most users won't know how to twiddle. > > regards, tom lane +1 Regards, Ken