Re: MVCC catalog access

On Wed, May 22, 2013 at 11:11 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> Make that actually having acquired an xid. We skip a large part of the
> work if a transaction doesn't yet have one afair. I don't think the mere
> presence of 600 idle connections without an xid in contrast to just
> having max_connection at 600 should actually make a difference in the
> cost of acquiring a snapshot?

Attached is a slightly updated version of the patch I'm using for
testing, and an updated version of the pg_cxn source that I'm using to
open lotsa connections.  With this version, I can do this:

./pg_cxn -n 600 -c BEGIN -c 'SELECT txid_current()'

...which I think is sufficient to make sure all those transactions
have XIDs.  Then I reran the "depend" test case (create a schema with
1000,000 functions and then drop the schema with CASCADE) that I
mentioned in my original posting.  Here are the results:

MVCC Off: Create 8685.662 ms, Drop 9973.233 ms
MVCC On: Create 7931.039 ms, Drop 10189.189 ms
MVCC Off: Create 7810.084 ms, Drop 9594.580 ms
MVCC On: Create 8854.577 ms, Drop 10240.024 ms

OK, let's try the rebuild-the-relcache test using the same pg_cxn
scenario (600 transactions that have started a transaction and
selected txid_current()).

[rhaas ~]$ time for s in `seq 1 1000`; do rm -f
pgdata/global/pg_internal.init && psql -c 'SELECT 2+2' >/dev/null;
done

MVCC catalog access on:
real    0m11.006s
user    0m2.746s
sys    0m2.664s

MVCC catalog access off:
real    0m10.583s
user    0m2.745s
sys    0m2.661s

MVCC catalog access on:
real    0m10.646s
user    0m2.750s
sys    0m2.661s

MVCC catalog access off:
real    0m10.823s
user    0m2.756s
sys    0m2.681s

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company