Re: ALTER TABLE lock strength reduction patch is unsafe

On Mon, Dec 19, 2011 at 12:05:09PM -0500, Robert Haas wrote:
> Yet another option, which I wonder whether we're dismissing too
> lightly, is to just call GetSnapshotData() and do the scan using a
> plain old MVCC snapshot. Sure, it's more expensive than SnapshotNow,
> but is it expensive enough to worry about?

Good point. For the most part, we already regard a catalog scan as too
expensive for bulk use, hence relcache and catcache. That's not license to
slow them down recklessly, but it's worth discovering how much of a hit we'd
actually face. I created a function that does this in a loop:

HeapTuple t;

CatalogCacheFlushCatalog(ProcedureRelationId);
t = SearchSysCache1(PROCOID, ObjectIdGetDatum(42) /* int4in */);
if (!HeapTupleIsValid(t))
elog(ERROR, "cache lookup failed for function 42");
ReleaseSysCache(t);

Then, I had pgbench call the function once per client with various numbers of
clients and a loop iteration count such that the total number of scans per run
was always 19200000. Results for master and for a copy patched to use MVCC
snapshots in catcache.c only:

2 clients, master: 4:30.66elapsed
4 clients, master: 4:26.82elapsed
32 clients, master: 4:25.30elapsed
2 clients, master: 4:25.67elapsed
4 clients, master: 4:26.58elapsed
32 clients, master: 4:26.40elapsed
2 clients, master: 4:27.54elapsed
4 clients, master: 4:26.60elapsed
32 clients, master: 4:27.20elapsed
2 clients, mvcc-catcache: 4:35.13elapsed
4 clients, mvcc-catcache: 4:30.40elapsed
32 clients, mvcc-catcache: 4:37.91elapsed
2 clients, mvcc-catcache: 4:28.13elapsed
4 clients, mvcc-catcache: 4:27.06elapsed
32 clients, mvcc-catcache: 4:32.84elapsed
2 clients, mvcc-catcache: 4:32.47elapsed
4 clients, mvcc-catcache: 4:24.35elapsed
32 clients, mvcc-catcache: 4:31.54elapsed

I see roughly a 2% performance regression. However, I'd expect any bulk
losses to come from increased LWLock contention, which just doesn't
materialize in a big way on this 2-core box. If anyone would like to rerun
this on a larger machine, I can package it up for reuse.