Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-10-31 Thread Kyotaro HORIGUCHI
This is a rebased version of the patch. At Fri, 17 Mar 2017 14:23:13 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170317.142313.232290068.horiguchi.kyot...@lab.ntt.co.jp> > At Tue, 7 Mar 2017 19:23:14 -0800, David Steele

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-09-05 Thread Kyotaro HORIGUCHI
Thank you for the comment. At Mon, 28 Aug 2017 21:31:58 -0400, Robert Haas wrote in > On Mon, Aug 28, 2017 at 5:24 AM, Kyotaro HORIGUCHI > wrote: > > This patch have had

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-09-05 Thread Kyotaro HORIGUCHI
Thank you for reviewing this. At Sat, 2 Sep 2017 12:12:47 +1200, Thomas Munro wrote in

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-09-01 Thread Thomas Munro
On Mon, Aug 28, 2017 at 9:24 PM, Kyotaro HORIGUCHI wrote: > This patch have had interferences from several commits after the > last submission. I amended this patch to follow them (up to > f97c55c), removed an unnecessary branch and edited some comments. Hi

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-08-28 Thread Robert Haas
On Mon, Aug 28, 2017 at 5:24 AM, Kyotaro HORIGUCHI wrote: > This patch have had interferences from several commits after the > last submission. I amended this patch to follow them (up to > f97c55c), removed an unnecessary branch and edited some comments. I think

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-08-28 Thread Kyotaro HORIGUCHI
Thank you for your attention. At Mon, 14 Aug 2017 17:33:48 -0400, Peter Eisentraut wrote in <09fa011f-4536-b05d-0625-11f3625d8...@2ndquadrant.com> > On 1/24/17 02:58, Kyotaro HORIGUCHI wrote: > >> BTW, if you set a slightly larger > >> context size on the

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-08-14 Thread Peter Eisentraut
On 1/24/17 02:58, Kyotaro HORIGUCHI wrote: >> BTW, if you set a slightly larger >> context size on the patch you might be able to avoid rebases; right >> now the patch doesn't include enough context to uniquely identify the >> chunks against cacheinfo[]. > git format-patch -U5 fuses all hunks on

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-03-16 Thread Kyotaro HORIGUCHI
At Tue, 7 Mar 2017 19:23:14 -0800, David Steele wrote in <3b7b7f90-db46-8c37-c4f7-443330c3a...@pgmasters.net> > On 3/3/17 4:54 PM, David Steele wrote: > > > On 2/1/17 1:25 AM, Kyotaro HORIGUCHI wrote: > >> Hello, thank you for moving this to the next CF. > >> > >> At Wed, 1

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-03-07 Thread David Steele
On 3/3/17 4:54 PM, David Steele wrote: On 2/1/17 1:25 AM, Kyotaro HORIGUCHI wrote: Hello, thank you for moving this to the next CF. At Wed, 1 Feb 2017 13:09:51 +0900, Michael Paquier wrote in

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-03-03 Thread David Steele
On 2/1/17 1:25 AM, Kyotaro HORIGUCHI wrote: > Hello, thank you for moving this to the next CF. > > At Wed, 1 Feb 2017 13:09:51 +0900, Michael Paquier > wrote in > >> On Tue, Jan 24, 2017 at 4:58

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-31 Thread Kyotaro HORIGUCHI
Hello, thank you for moving this to the next CF. At Wed, 1 Feb 2017 13:09:51 +0900, Michael Paquier wrote in > On Tue, Jan 24, 2017 at 4:58 PM, Kyotaro HORIGUCHI >

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-31 Thread Michael Paquier
On Tue, Jan 24, 2017 at 4:58 PM, Kyotaro HORIGUCHI wrote: > Six new syscaches in 665d1fa was conflicted and 3-way merge > worked correctly. The new syscaches don't seem to be targets of > this patch. To be honest, I am not completely sure what to think about this

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-24 Thread Kyotaro HORIGUCHI
Hello, I have tried to cap the number of negative entries for myself (by removing negative entries in least recentrly created first order) but the ceils cannot be reasonably determined both absolutely or relatively to positive entries. Apparently it differs widely among caches and applications.

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-24 Thread Kyotaro HORIGUCHI
Hello, thank you for lookin this. At Mon, 23 Jan 2017 16:54:36 -0600, Jim Nasby wrote in <21803f50-a823-c444-ee2b-9a153114f...@bluetreble.com> > On 1/21/17 6:42 PM, Jim Nasby wrote: > > On 12/26/16 2:31 AM, Kyotaro HORIGUCHI wrote: > >> The points of discussion are the

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-23 Thread Jim Nasby
On 1/21/17 6:42 PM, Jim Nasby wrote: On 12/26/16 2:31 AM, Kyotaro HORIGUCHI wrote: The points of discussion are the following, I think. 1. The first patch seems working well. It costs the time to scan the whole of a catcache that have negative entries for other reloids. However, such

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-23 Thread Jim Nasby
On 1/22/17 5:03 PM, Tom Lane wrote: Ok, after reading the code I see I only partly understood what you were saying. In any case, it might still be useful to do some testing with CATCACHE_STATS defined to see if there's caches that don't accumulate a lot of negative entries. There definitely

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-22 Thread Tom Lane
Jim Nasby writes: >> Ahh, I hadn't considered that. So one idea would be to only track >> negative entries on caches where we know they're actually useful. That >> might make the performance hit of some of the other ideas more >> tolerable. Presumably you're much less

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-22 Thread Jim Nasby
On 1/22/17 4:41 PM, Jim Nasby wrote: On 1/21/17 8:54 PM, Tom Lane wrote: Jim Nasby writes: The other (possibly naive) question I have is how useful negative entries really are? Will Postgres regularly incur negative lookups, or will these only happen due to user

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-22 Thread Jim Nasby
On 1/21/17 8:54 PM, Tom Lane wrote: Jim Nasby writes: The other (possibly naive) question I have is how useful negative entries really are? Will Postgres regularly incur negative lookups, or will these only happen due to user activity? It varies depending on the

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-21 Thread Tom Lane
Jim Nasby writes: > The other (possibly naive) question I have is how useful negative > entries really are? Will Postgres regularly incur negative lookups, or > will these only happen due to user activity? It varies depending on the particular syscache, but in at

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-21 Thread Jim Nasby
On 12/26/16 2:31 AM, Kyotaro HORIGUCHI wrote: The points of discussion are the following, I think. 1. The first patch seems working well. It costs the time to scan the whole of a catcache that have negative entries for other reloids. However, such negative entries are created by rather

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-17 Thread Michael Paquier
On Sat, Jan 14, 2017 at 9:36 AM, Tomas Vondra wrote: > On 01/14/2017 12:06 AM, Andres Freund wrote: >> On 2017-01-13 17:58:41 -0500, Tom Lane wrote: >>> >>> But, again, the catcache isn't the only source of per-process bloat >>> and I'm not even sure it's the main

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Tomas Vondra
On 01/14/2017 12:06 AM, Andres Freund wrote: Hi, On 2017-01-13 17:58:41 -0500, Tom Lane wrote: But, again, the catcache isn't the only source of per-process bloat and I'm not even sure it's the main one. A more holistic approach might be called for. It'd be helpful if we'd find a way to

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Andres Freund
Hi, On 2017-01-13 17:58:41 -0500, Tom Lane wrote: > But, again, the catcache isn't the only source of per-process bloat > and I'm not even sure it's the main one. A more holistic approach > might be called for. It'd be helpful if we'd find a way to make it easy to get statistics about the size

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Tom Lane
Michael Paquier writes: > ... There are even some apps that do not use pgbouncer, but drop > sessions after a timeout of inactivity to avoid a memory bloat because > of the problem of this thread. Yeah, a certain company I used to work for had to do that, though their

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Michael Paquier
On Sat, Jan 14, 2017 at 12:32 AM, Robert Haas wrote: > On Fri, Jan 13, 2017 at 8:58 AM, Tom Lane wrote: >> Michael Paquier writes: >>> Have there been ever discussions about having catcache entries in a >>> shared memory

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Robert Haas
On Fri, Jan 13, 2017 at 8:58 AM, Tom Lane wrote: > Michael Paquier writes: >> Have there been ever discussions about having catcache entries in a >> shared memory area? This does not sound much performance-wise, I am >> just wondering about the

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Tom Lane
Michael Paquier writes: > Have there been ever discussions about having catcache entries in a > shared memory area? This does not sound much performance-wise, I am > just wondering about the concept and I cannot find references to such > discussions. I'm sure it's been

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2017-01-13 Thread Michael Paquier
On Wed, Dec 21, 2016 at 5:10 AM, Tom Lane wrote: > If I thought that "every ten minutes" was an ideal way to manage this, > I might worry about that, but it doesn't really sound promising at all. > Every so many queries would likely work better, or better yet make it >

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-26 Thread Kyotaro HORIGUCHI
At Wed, 21 Dec 2016 10:21:09 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20161221.102109.51106943.horiguchi.kyot...@lab.ntt.co.jp> > At Tue, 20 Dec 2016 15:10:21 -0500, Tom Lane wrote in > <23492.1482264...@sss.pgh.pa.us> > >

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Kyotaro HORIGUCHI
Thank you for the discussion. At Tue, 20 Dec 2016 15:10:21 -0500, Tom Lane wrote in <23492.1482264...@sss.pgh.pa.us> > The bigger picture here though is that we used to have limits on syscache > size, and we got rid of them (commit 8b9bc234a, see also >

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Robert Haas
On Tue, Dec 20, 2016 at 3:10 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Dec 20, 2016 at 10:09 AM, Tom Lane wrote: >>> I don't understand why we'd make that a system-wide behavior at all, >>> rather than expecting each

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Tom Lane
Robert Haas writes: > On Tue, Dec 20, 2016 at 10:09 AM, Tom Lane wrote: >> I don't understand why we'd make that a system-wide behavior at all, >> rather than expecting each process to manage its own cache. > Individual backends don't have a really

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Robert Haas
On Tue, Dec 20, 2016 at 10:09 AM, Tom Lane wrote: > Craig Ringer writes: >> On 20 December 2016 at 21:59, Robert Haas wrote: >>> We could implement this by having >>> some process, like the background writer, >>>

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Tom Lane
Craig Ringer writes: > On 20 December 2016 at 21:59, Robert Haas wrote: >> We could implement this by having >> some process, like the background writer, >> SendProcSignal(PROCSIG_HOUSEKEEPING) to every process in the system >> every 10 minutes or

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Craig Ringer
On 20 December 2016 at 21:59, Robert Haas wrote: > We could implement this by having > some process, like the background writer, > SendProcSignal(PROCSIG_HOUSEKEEPING) to every process in the system > every 10 minutes or so. ... on a rolling basis. Otherwise that'll be

Re: [HACKERS] Protect syscache from bloating with negative cache entries

2016-12-20 Thread Robert Haas
On Mon, Dec 19, 2016 at 6:15 AM, Kyotaro HORIGUCHI wrote: > Hello, recently one of my customer stumbled over an immoderate > catcache bloat. This isn't only an issue for negative catcache entries. A long time ago, there was a limit on the size of the relcache,

[HACKERS] Protect syscache from bloating with negative cache entries

2016-12-19 Thread Kyotaro HORIGUCHI
Hello, recently one of my customer stumbled over an immoderate catcache bloat. This is a known issue living on the Todo page in the PostgreSQL wiki. https://wiki.postgresql.org/wiki/Todo#Cache_Usage > Fix memory leak caused by negative catcache entries