From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim Nasby <Jim.Nasby@bluetreble.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
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 21:54:44 -0500
Message-ID: <19334.1485053684@sss.pgh.pa.us> (raw)
In-Reply-To: <ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com>
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>
<ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Jim Nasby <Jim.Nasby@bluetreble.com> 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 least some
of them, negative cache entries are critical for performance.
See for example RelnameGetRelid(), which basically does a RELNAMENSP
cache lookup for each schema down the search path until it finds a
match. For any user table name with the standard search_path, there's
a guaranteed failure in pg_catalog before you can hope to find a match.
If we don't have negative cache entries, then *every invocation of this
function has to go to disk* (or at least to shared buffers).
It's possible that we could revise all our lookup patterns to avoid this
sort of thing. But I don't have much faith in that always being possible,
and exactly none that we won't introduce new lookup patterns that need it
in future. I spent some time, for instance, wondering if RelnameGetRelid
could use a SearchSysCacheList lookup instead, doing the lookup on table
name only and then inspecting the whole list to see which entry is
frontmost according to the current search path. But that has performance
failure modes of its own, for example if you have identical table names in
a boatload of different schemas. We do it that way for some other cases
such as function lookups, but I think it's much less likely that people
have identical function names in N schemas than that they have identical
table names in N schemas.
If you want to poke into this for particular test scenarios, building with
CATCACHE_STATS defined will yield a bunch of numbers dumped to the
postmaster log at each backend exit.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, Jim.Nasby@bluetreble.com, horiguchi.kyotaro@lab.ntt.co.jp, robertmhaas@gmail.com, craig@2ndquadrant.com
Subject: Re: Protect syscache from bloating with negative cache entries
In-Reply-To: <19334.1485053684@sss.pgh.pa.us>
* 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