Home > mailing lists

Re: MVCC catalog access - Mailing list pgsql-hackers

From	Michael Paquier
Subject	Re: MVCC catalog access
Date	June 4, 2013 07:52:14
Msg-id	CAB7nPqSwoUWXb6ftJvqvMLnTP55rQi32L==YR_L_Pjfwy_iYAQ@mail.gmail.com Whole thread Raw
In response to	Re: MVCC catalog access (Robert Haas <robertmhaas@gmail.com>)
List	pgsql-hackers

Tree view

On Tue, Jun 4, 2013 at 3:57 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, May 30, 2013 at 1:39 AM, Michael Paquier
<michael.paquier@gmail.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

pgsql-hackers by date:

From: Tom Lane
Date: 04 June 2013, 05:57:13
Subject: Re: pg_dump with postgis extension dumps rules separately

From: "Ben Zeev, Lior"
Date: 04 June 2013, 08:59:16
Subject: Re: PostgreSQL Process memory architecture

Re: MVCC catalog access - Mailing list pgsql-hackers

Previous

Next