content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: MVCC performance issue
Date: Fri, 12 Nov 2010 19:13:14 +0200
Message-ID: <8BCBF9DB739F034B87FE7C7D30EAE55C2AB8F887@hqex2k.francoudi.com>
Thread-Topic: [PERFORM] MVCC performance issue
Thread-Index: AcuChapnv/jOpQoaTM6ny6yZaYyq5gAA+R1A
References: <8BCBF9DB739F034B87FE7C7D30EAE55C279F054F@hqex2k.francoudi.com>
	<4CDD69A3.5000804@squeakycode.net>
From: "Kyriacos Kyriacou" <kyriacosk@prime-tel.com>
To: "Andy Colson" <andy@squeakycode.net>
Cc: <pgsql-performance@postgresql.org>

My suggestion had just a single difference from what currently MVCC is
doing (btw I never said that MVCC is bad).=20

NOW =3D=3D=3D> on COMMIT previous version record is expired and the=20
         new version record (created in new dynamically allocated=20
         spot, as you said) is set as "active"
=20
MY  =3D=3D=3D> on COMMIT, to update new version data over the same =
physical
location that initial version was=20
         and release the space used to keep the new version (that was
dynamically allocated).

The rest are all the same! I do not think that this is breaking anything
and I still believe that this might help.

I will try to plan upgrade the soonest possible to the newest version.
Reading few words about HOT updates=20
it seems that more or less is similar to what I have described and will
be very helpful.

Kyriacos

> -----Original Message-----
> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Friday, November 12, 2010 6:22 PM
> To: Kyriacos Kyriacou
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] MVCC performance issue
>=20
> On 11/12/2010 7:47 AM, Kyriacos Kyriacou wrote:
> >
> > SUGGESTION
> > --------------
> > 1) When a raw UPDATE is performed, store all "new raw versions"
either
> > in separate temporary table space
> >     or in a reserved space at the end of each table (can be
allocated
> > dynamically) etc
>=20
> Your use of "raw" is confusing.  I'll just ignore the word.  New row
> versions are already stored in a dynamically allocated spot, right
along
> with the other versions of the table.  You are assuming that getting
to
> the "correct" version of the row is very slow?  That's only going to
be
> the case if you have lots and lots of versions.  And your solution
will
> not actually help if there are lots of versions.  While one person who
> is hitting the most recent version might be ok, everyone else will
still
> have to search for theirs.  Just as they do now.
>=20
> > 2) Any SELECT queries within the same session will be again
accessing
> > the new version of the row
>=20
> I don't see how this is different from what we currently have.  "same
> session" could have been dropped from your separate table space, and
> then you'd have to go search through previous versions of the row...
> exactly like you do now.
>=20
> And worse, if you dont want to drop your version of the row from the
> separate table space until you commit/rollback, then no other user can
> start a transaction on that table until your done!  oh no!  You have
> reads and writes blocking each other.
>=20
> > 3) Any SELECT queries from other users will still be accessing the
old
> > version
>=20
> Again.. the same.
>=20
> > 4) When UPDATE transaction is ROLLBACK just release the space used
in
> > new temporary location
>=20
> current layout makes rollback very very fast.
>=20
> > 5) When UPDATE transaction is COMMIT then try to LOCK the old
version
> > and overwrite it at the same physical location (NO FRAGMENTATION).
>=20
> Not sure what you mean by lock, but lock requires single user access
and
> slow's things down.  Right now we just bump the "most active
transaction
> number", which is very efficient, and requires no locks.  As soon as
you
> lock anything, somebody, by definition, has to wait.
>=20
>=20
> > 6) Similar mechanism can be applied on INSERTS and DELETES
> > 7) In case that transaction was COMMIT, the temporary location can
be
> > either released or archived/cleaned on a pre-scheduled basis. This
will
> > possibly allow the introduction of a TRANSACTION LOG backup
mechanism as
> > a next step.
>=20
> You are kind of assuming there will only ever be one new transaction,
> and one old transaction.  What about a case where 10 people start a
> transaction, and there are 10 versions of the row?
>=20
>=20
> It seems to me like you are using very long transactions, which is
> causing lots of row versions to show up.  Have you run explain analyze
> on your slow querys to find out the problems?
>=20
> Have you checked to see if you are cpu bound or io bound?  If you are
> dealing with lots of row versions, I'd assume you are cpu bound.  If
you
> check your system though, and see you are io bound, I think that might
> invalidate your assumptions above.
>=20
> MVCC makes multi user access very nice because readers and writers
dont
> block each other, and there are very few locks.  It does come with
some
> kinks (gotta vacuum, keep transactions short, you must commit, etc).
>=20
> select count(*) for example is always going to be slow... just expect
> it, lets not destroy what works well about the database just to make
it
> fast.  Instead, find a better alternative so you dont have to run it.
>=20
> Just like any database, you have to work within MVCC's good points and
> try to avoid the bad spots.
>=20
> -Andy