Re: MVCC catalog access

On Tue, Jun 4, 2013 at 3:57 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, May 30, 2013 at 1:39 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
> > +1.
>
> Here's a more serious patch for MVCC catalog access. This one
> involves more data copying than the last one, I think, because the
> previous version did not register the snapshots it took, which I think
> is not safe. So this needs to be re-tested for performance, which I
> have so far made no attempt to do.
>
> It strikes me as rather unfortunate that the snapshot interface is
> designed in such a way as to require so much data copying. It seems
> we always take a snapshot by copying from PGXACT/PGPROC into
> CurrentSnapshotData or SecondarySnapshotData, and then copying data a
> second time from there to someplace more permanent. It would be nice
> to avoid that, at least in common cases.
>
And here are more results comparing master branch with and without this
patch...

1) DDL CREATE/DROP test:
1-1) master:
250 connections:
Create: 24846.060, Drop: 30391.713
Create: 23771.394, Drop: 29769.396
500 connections:
Create: 24339.449, Drop: 30084.741
Create: 24152.176, Drop: 30643.471
1000 connections:
Create: 26007.960, Drop: 31019.918
Create: 25937.592, Drop: 30600.341
2000 connections:
Create: 26900.324, Drop: 30741.989
Create: 26910.660, Drop: 31577.247

1-2) mvcc catalogs:
250 connections:
Create: 25371.342, Drop: 31458.952
Create: 25685.094, Drop: 31492.377
500 connections:
Create: 28557.882, Drop: 33673.266
Create: 27901.910, Drop: 33223.006
1000 connections:
Create: 31910.130, Drop: 36770.062
Create: 32210.093, Drop: 36754.888
2000 connections:
Create: 40374.754, Drop: 43442.528
Create: 39763.691, Drop: 43234.243

2) backend startup
2-1) master branch:
250 connections:
real 0m8.993s
user 0m0.128s
sys 0m0.380s
500 connections:
real 0m9.004s
user 0m0.212s
sys 0m0.340s
1000 connections:
real 0m9.072s
user 0m0.272s
sys 0m0.332s
2000 connections:
real 0m9.257s
user 0m0.204s
sys 0m0.392s

2-2) MVCC catalogs:
250 connections:
real 0m9.067s
user 0m0.108s
sys 0m0.396s
500 connections:
real 0m9.034s
user 0m0.112s
sys 0m0.376s
1000 connections:
real 0m9.303s
user 0m0.176s
sys 0m0.328s
2000 connections
real 0m9.916s
user 0m0.160s
sys 0m0.428s

Except for the case of backend startup test for 500 connections that looks
to have some noise, performance degradation reaches 6% for 2000
connections, and less than 1% for 250 connections. This is better than last
time.
For the CREATE/DROP case, performance drop reaches 40% for 2000 connections
(32% during last tests). I also noticed a lower performance drop for 250
connections now (3~4%) compared to the 1st time (9%).

I compiled the main results on tables here:
http://michael.otacoo.com/postgresql-2/postgres-9-4-devel-mvcc-catalog-access-take-2-2/
The results of last time are also available here:
http://michael.otacoo.com/postgresql-2/postgres-9-4-devel-mvcc-catalog-access-2/

Regards,
--
Michael