Re: MVCC catalog access - Mailing list pgsql-hackers

On Fri, Jun 28, 2013 at 12:22 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
>> The difference is 3-4%, which is quite a lot less than what you
>> measured before, although on different hardware, so results may vary.
>
> 3-4% on that synthetic benchmark sounds pretty acceptable to me, as
> well.

Here's a further update of this patch.  In this version, I added some
mechanism to send a new kind of sinval message that is sent when a
catalog without catcaches is updated; it doesn't apply to all
catalogs, just to whichever ones we want to have this treatment.  That
means we don't need to retake snapshots for those catalogs on every
access, so backend startup requires just one extra MVCC snapshot as
compared with current master.  Assorted cleanup has been done, along
with the removal of a few more SnapshotNow references.

It's still possible to construct test cases that perform badly by
pounding the server with 1000 clients running Andres's
readonly-busy.sql.  Consider the following test case: use a DO block
to create a schema with 10,000 functions in it and then DROP ..
CASCADE.  When the server is unloaded, the extra MVCC overhead is
pretty small.

master
Create: 1010.225 ms, Drop: 444.891 ms
Create: 1001.237 ms, Drop: 444.084 ms
Create: 979.621 ms, Drop: 446.091 ms

patched
Create: 992.366 ms, Drop: 459.334 ms
Create: 992.436 ms, Drop: 459.921 ms
Create: 990.971 ms, Drop: 459.573 ms

The create case is actually running just a hair faster with the patch,
and the drop case is about 3% slower.  Now let's add 1000 clients
running Andres's readonly-busy.sql in the background and retest:

master
Create: 21554.387 ms, Drop: 2594.534 ms
Create: 32189.395 ms, Drop: 2493.213 ms
Create: 30627.964 ms, Drop: 1813.160 ms

patched
Create: 44312.107 ms, Drop: 11718.305 ms
Create: 46683.021 ms, Drop: 11732.284 ms
Create: 50766.615 ms, Drop: 9363.742 ms

Well, now the create is 52% slower and the drop is a whopping 4.7x
slower.  It's worth digging into the reasons just a bit.  I was able
to speed up this case quite a bit - it was 30x slower a few hours ago
- by adding a few new relations to the switch in
RelationInvalidatesSnapshotsOnly().  But the code still takes one MVCC
snapshot per object dropped, because deleteOneObject() calls
CommandCounterIncrement() and that, as it must, invalidates our
previous snapshot.  We could, if we were inclined to spend the effort,
probably work out that although we need to change curcid, the rest of
the snapshot is still OK, but I'm not too convinced that it's worth
adding an even-more-complicated mechanism for this.  We could probably
also optimize the delete code to increment the command counter fewer
times, but I'm not convinced that's worth doing either.

I think my general feeling about this is that we're going to have to
accept that there's no such thing as a free lunch, but maybe that's
OK.  The testing done to date shows that MVCC snapshots are not
terribly expensive when PGXACT isn't heavily updated, even if you take
an awful lot of them, but with enough concurrent activity on PGXACT
they do get expensive enough to care about.  And that's still not a
big problem on normal workloads, but if you combine code that with a
workload that requires an abnormally high number of snapshots compared
to the overall work it does (like a DROP CASCADE on a schema with many
objects but no tables) then you can make it quite slow.  That's not
great, but on the other hand, if it had always been that slow, I'm not
all that sure anyone would have complained.  DDL performance is not
something we've spend a lot of cycles on, and for good reason.

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