From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
To: tgl@sss.pgh.pa.us
Cc: robertmhaas@gmail.com
Cc: craig@2ndquadrant.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: Protect syscache from bloating with negative cache entries
Date: Sat, 21 Jan 2017 18:42:28 -0600
Message-ID: <ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com> (raw)
In-Reply-To: <20161226.173147.73783918.horiguchi.kyotaro@lab.ntt.co.jp>
References: <CA+TgmoaMPbN5vvH33zLK7MR+PiCYWPvTHmSSrDab4vurLOXS-Q@mail.gmail.com>
<23492.1482264621@sss.pgh.pa.us>
<20161221.102109.51106943.horiguchi.kyotaro@lab.ntt.co.jp>
<20161226.173147.73783918.horiguchi.kyotaro@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
> unusual usages. Accesing to undefined columns, and accessing
> columns on which no statistics have created. The
> whole-catcache scan occurs on ATTNAME, ATTNUM and
> STATRELATTINH for every invalidation of a relcache entry.
I took a look at this. It looks sane, though I've got a few minor
comment tweaks:
+ * Remove negative cache tuples maching a partial key.
s/maching/matching/
+/* searching with a paritial key needs scanning the whole cache */
s/needs/means/
+ * a negative cache entry cannot be referenced so we can remove
s/referenced/referenced,/
I was wondering if there's a way to test the performance impact of
deleting negative entries.
> 2. The second patch also works, but flushing negative entries by
> hash values is inefficient. It scans the bucket corresponding
> to given hash value for OIDs, then flushing negative entries
> iterating over all the collected OIDs. So this costs more time
> than 1 and flushes involving entries that is not necessary to
> be removed. If this feature is valuable but such side effects
> are not acceptable, new invalidation category based on
> cacheid-oid pair would be needed.
I glanced at this and it looks sane. Didn't go any farther since this
one's pretty up in the air. ISTM it'd be better to do some kind of aging
instead of patch 2.
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? I can't think of a case
where an app would need to depend on fast negative lookup (in other
words, it should be considered a bug in the app). I can see where
getting rid of them completely might be problematic, but maybe we can
just keep a relatively small number of them around. I'm thinking a
simple LRU list of X number of negative entries; when that fills you
reuse the oldest one. You'd have to pay the LRU maintenance cost on
every negative hit, but if those shouldn't be that common it shouldn't
be bad.
That might well necessitate another GUC, but it seems a lot simpler than
most of the other ideas.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, horiguchi.kyotaro@lab.ntt.co.jp, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, craig@2ndquadrant.com
Subject: Re: Protect syscache from bloating with negative cache entries
In-Reply-To: <ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox