Subject: Re: Protect syscache from bloating with negative cache
 entries
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, <tgl@sss.pgh.pa.us>
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>
CC: <robertmhaas@gmail.com>, <craig@2ndquadrant.com>,
	<pgsql-hackers@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <21803f50-a823-c444-ee2b-9a153114f454@BlueTreble.com>
Date: Mon, 23 Jan 2017 16:54:36 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: <ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Jan 2017 22:54:41.7618 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1636
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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 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.

I did a make installcheck run with CATCACHE_STATS to see how often we 
get negative entries in the 3 caches affected by this patch. The caches 
on pg_attribute get almost no negative entries. pg_statistic gets a good 
amount of negative entries, presumably because we start off with no 
entries in there. On a stable system that presumably won't be an issue, 
but if temporary tables are in use and being analyzed I'd think there 
could be a moderate amount of inval traffic on that cache. I'll leave it 
to a committer to decide if they thing that's an issue, but you might 
want to try and quantify how big a hit that is. I think it'd also be 
useful to know how much bloat you were seeing in the field.

The patch is currently conflicting against master though, due to some 
caches being added. Can you rebase? 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[].
-- 
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