Re: MVCC catalog access - Mailing list pgsql-hackers

First I ran the simple read test:
$ time for s in `seq 1 1000`
> do
> rm -f ~/bin/pgsql/master/global/pg_internal.init && psql -c 'SELECT 2+2' >/dev/null;
> done

And then the create/drop test.

I have done those tests with 250, 500, 1000 and 2000 open connections:

1) 250 open connections
1-1) read test

Round 1:
mvcc_catalog_access off:
real    0m9.124s
user    0m0.200s
sys    0m0.392s
mvcc_catalog_access on:
real    0m9.297s
user    0m0.148s
sys    0m0.444s

Round 2:
mvcc_catalog_access off:
real    0m8.985s
user    0m0.160s
sys    0m0.372s
mvcc_catalog_access on:
real    0m9.244s
user    0m0.240s
sys    0m0.400s

1-2) DDL test (drop and creation of 100,000 objects)
mvcc off: Create: 24554.849, Drop: 29755.146
mvcc on: Create: 26904.755, Drop: 32891.556
mvcc off: Create: 23337.342, Drop: 29921.990
mvcc on: Create: 24533.708, Drop: 31670.840

2) 500 open connections
2-1) read test

Round 1:
mvcc_catalog_access off:
real    0m9.123s
user    0m0.200s
sys    0m0.396s
mvcc_catalog_access on:
real    0m9.627s
user    0m0.156s
sys    0m0.460s
Round 2:
mvcc_catalog_access off:
real    0m9.221s
user    0m0.316s
sys    0m0.392s
mvcc_catalog_access on:
real    0m9.592s
user    0m0.160s
sys    0m0.484s

2-2) DDL test (drop and creation of 100,000 objects)
mvcc off: Create: 25872.886, Drop: 31723.921
mvcc on: Create: 27076.429, Drop: 33674.336
mvcc off: Create: 24039.456, Drop: 30434.019
mvcc on: Create: 29105.713, Drop: 33821.170

3) 1000 open connections

3-1) read test

Round 1:
mvcc_catalog_access off:
real    0m9.240s
user    0m0.192s
sys    0m0.396s
mvcc_catalog_access on:
real    0m9.674s
user    0m0.236s
sys    0m0.440s
Round 2:
mvcc_catalog_access off:
real    0m9.302s
user    0m0.308s
sys    0m0.392s
mvcc_catalog_access on:
real    0m9.746s
user    0m0.204s
sys    0m0.436s

3-2) DDL test (drop and creation of 100,000 objects)
mvcc off: Create: 25563.705, Drop: 31747.451
mvcc on: Create: 33281.246, Drop: 36618.166
mvcc off: Create: 28178.210, Drop: 30550.166
mvcc on: Create: 31849.825, Drop: 36831.245

4) 2000 open connections

4-1) read test

Round 1:
mvcc_catalog_access off:
real    0m9.066s
user    0m0.128s
sys    0m0.420s
mvcc_catalog_access on:
real    0m9.978s
user    0m0.168s
sys    0m0.412s

Round 2:
mvcc_catalog_access off:
real    0m9.113s
user    0m0.152s
sys    0m0.444s
mvcc_catalog_access on:
real    0m9.974s
user    0m0.176s
sys    0m0.436s

More or less the same results as previously with 10% performance drop.

4-2) DDL test (drop and creation of 100,000 objects)
mvcc off: Create: 28708.095 ms, Drop: 32510.057 ms

mvcc on: Create: 39987.815 ms, Drop: 43157.006 ms

mvcc off: Create: 28409.853 ms, Drop: 31248.163 ms
mvcc on: Create: 41669.829 ms, Drop: 44645.109 ms

For read tests, we can see a performance drop up to 10% for 2000 connections.

For the write tests, we can see a performance drop of 9~10% for 250 connections, up to 30% performance drop with 2000 connections.

We barely see users drop that many objects at the same time with so much open transactions, they'll switch to a connection pooler before opening that many connections to the server. I am not sure that such a performance drop is acceptable as-is, but perhaps it is if we consider the functionality gain we can have thanks to MVCC catalogs.

--

Michael