Horton

Re: ALTER TABLE lock strength reduction patch is unsafe

Noah Misch <noah@leadboat.com>

View thread
From: Noah Misch <noah@leadboat.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>, Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2011-12-20T03:10:02Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add bytea_agg, parallel to string_agg.

  2. Fix ALTER TABLE ONLY .. DROP CONSTRAINT.

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.