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 1gu14o-0004bR-CO for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Feb 2019 20:24:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gu14m-00051J-MU for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Feb 2019 20:24:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gu14m-000517-FP for pgsql-hackers@lists.postgresql.org; Wed, 13 Feb 2019 20:24:12 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gu14j-0003ut-Uc for pgsql-hackers@lists.postgresql.org; Wed, 13 Feb 2019 20:24:11 +0000 Received: from bruce by momjian.us with local (Exim 4.84_2) (envelope-from ) id 1gu14c-0007HU-F9; Wed, 13 Feb 2019 15:24:02 -0500 Date: Wed, 13 Feb 2019 15:24:02 -0500 From: Bruce Momjian To: Tomas Vondra Cc: "Tsunakawa, Takayuki" , "andres@anarazel.de" , Robert Haas , Tom Lane , Kyotaro HORIGUCHI , "Ideriha, Takeshi" , "pgsql-hackers@lists.postgresql.org" , "alvherre@alvh.no-ip.org" , "michael.paquier@gmail.com" , "david@pgmasters.net" , "craig@2ndquadrant.com" Subject: Re: Protect syscache from bloating with negative cache entries Message-ID: <20190213202402.GA6933@momjian.us> References: <1391.1547845037@sss.pgh.pa.us> <20190118212330.t5ibohxlavuy55bg@alap3.anarazel.de> <20190119010941.6ruftewah7t3k3yk@alap3.anarazel.de> <20190121205624.GB1060@momjian.us> <0d3806bf-5087-a75f-3592-ee1d508a79db@2ndquadrant.com> <883b2580-027e-4cb4-fb21-679c14fdb50a@2ndquadrant.com> <0A3221C70F24FB45833433255569204D1FB9723F@G01JPEXMBYT05> <9ebe0ac4-b59e-6397-0586-4e7125de7d5b@2ndquadrant.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9ebe0ac4-b59e-6397-0586-4e7125de7d5b@2ndquadrant.com> User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, Feb 12, 2019 at 02:53:40AM +0100, Tomas Vondra wrote: > Right. But the logic behind time-based approach is that evicting such > entries should not cause any issues exactly because they are accessed > infrequently. It might incur some latency when we need them for the > first time after the eviction, but IMHO that's acceptable (although I > see Andres did not like that). > > FWIW we might even evict entries after some time passes since inserting > them into the cache - that's what memcached et al do, IIRC. The logic is > that frequently accessed entries will get immediately loaded back (thus > keeping cache hit ratio high). But there are reasons why the other dbs > do that - like not having any cache invalidation (unlike us). Agreed. If this fixes 90% of the issues people will have, and it applies to the 99.9% of users who will never tune this, it is a clear win. If we want to add something that requires tuning later, we can consider it once the non-tuning solution is done. > That being said, having a "minimal size" threshold before starting with > the time-based eviction may be a good idea. Agreed. I see the minimal size as a way to keep the systems tables in cache, which we know we will need for the next query. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +