From: Craig Ringer <craig@postnewspapers.com.au>
To: Kyriacos Kyriacou <kyriacosk@prime-tel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: MVCC performance issue
Date: Sat, 13 Nov 2010 13:53:27 +0800
Message-ID: <4CDE27D7.7070706@postnewspapers.com.au> (raw)
In-Reply-To: <8BCBF9DB739F034B87FE7C7D30EAE55C240D4BC8@hqex2k.francoudi.com>
References: <8BCBF9DB739F034B87FE7C7D30EAE55C240D4BC8@hqex2k.francoudi.com>
On 11/12/2010 02:25 AM, Kyriacos Kyriacou wrote:
> The
> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates.
Yep. It's all about trade-offs. For some workloads the in-table MVCC
storage setup works pretty darn poorly, but for most it seems to work
quite well.
There are various other methods of implementing relational storage with
ACID properties. You can exclude all other transactions while making a
change to a table, ensuring that nobody else can see "old" or "new" rows
so there's no need to keep them around. You can use an out-of-line redo
log (a-la Oracle). Many other methods exist, too.
They all have advantages and disadvantages for different workloads. It's
far from trivial to mix multiple schemes within a single database, so
mixing and matching schemes for different parts of your DB isn't
generally practical.
> 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
OK, so you want a redo log a-la Oracle?
> 2) Any SELECT queries within the same session will be again accessing
> the new version of the row
> 3) Any SELECT queries from other users will still be accessing the old
> version
... and incurring horrible random I/O penalties if the redo log doesn't
fit in RAM. Again, a-la Oracle.
Even read-only transactions have to hit the undo log if there's an
update in progress, because rows they need may have been moved out to
the undo log as they're updated in the main table storage.
[snip description]
> I understand that my suggestion seems to be too simplified and also that
> there are many implementation details and difficulties that I am not
> aware.
It sounds like you're describing Oracle-style MVCC, using redo logs.
http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/http://en.wikipedia.org/wiki/Multiversion_concurrency_control
Oracle's MVCC approach has its own costs. Like Pg's, those costs
increase with update/delete frequency. Instead of table bloat, Oracle
suffers from redo log growth (or redo log size management issues).
Instead of increased table scan costs from dead rows, Oracle suffers
from random I/O costs as it looks up the out-of-line redo log for old
rows. Instead of long-running writer transactions causing table bloat,
Oracle can have problems with long-running reader transactions aborting
when the redo log runs out of space.
Personally, I don't know enough to know which is "better". I suspect
they're just different, with different trade-offs. If redo logs allow
you to do without write-ahead logging, that'd be interesting - but
then, the WAL is useful for all sorts of replication options, and the
use of linear WALs means that write ordering in the tables doesn't need
to be as strict, which has performance advantages.
--
Craig Ringer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, kyriacosk@prime-tel.com
Subject: Re: MVCC performance issue
In-Reply-To: <4CDE27D7.7070706@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox