From: Jonah H. Harris <jonah.harris@gmail.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@lusis.org, "PGSQL Performance" <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: Fri, 16 Jun 2006 08:51:33 -0400
Message-ID: <36e682920606160551v6b98bcfek32f01b895c53e057@mail.gmail.com> (raw)
In-Reply-To: <87ver18hu9.fsf@stark.xeocode.com>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
<1150396425.31200.66.camel@archimedes>
<87ver18hu9.fsf@stark.xeocode.com>
On 16 Jun 2006 07:23:26 -0400, Greg Stark <gsstark@mit.edu> wrote:
> The flip side is that Oracle and others like it have to
> do a lot of extra footwork to do if you query data
> that hasn't been committed yet. That footwork
> has performance implications.
Not disagreeing here at all, but considering that Oracle, DB2, and SQL
Server, et al have proven themselves to perform extremely well under
heavy load (in multiple benchmarks), the overhead of an UNDO
implementation has a calculable break even point.
Feel free to debate it, but the optimistic approach adopted by nearly
every commercial database vendor is *generally* a better approach for
OLTP.
Consider Weikum & Vossen (p. 442):
We also need to consider the extra work that the recovery algorithm
incurs during normal operation. This is exactly the catch with the
class of no-undo/no-redo algorithms. By and large, they come at the
expense of a substantial overhead during normal operations that may
increase the execution cost per transaction by a factor of two or even
higher. In other words, it reduces the achievable transaction
throughput of a given server configuration by a factor of two or more.
Now, if we're considering UPDATES (the worst case for PostgreSQL's
current MVCC architecture), then this is (IMHO) a true statement.
There aren't many *successful* commercial databases that incur the
additional overhead of creating another version of the record, marking
the old one as having been updated, inserting N-number of new index
entries to point to said record, and having to WAL-log all
aforementioned changes. I have yet to see any successful commercial
RDBMS using some sort of no-undo algorithm that doesn't follow the,
"factor of two or more" performance reduction. However, if you
consider an INSERT or DELETE in PostgreSQL, those are implemented much
better than in most commercial database systems due to PostgreSQL's
MVCC design. I've done a good amount of research on enhancing
PostgreSQL's MVCC in UPDATE conditions and believe there is a nice
happy medium for us.
/me waits for the obligatory and predictable, "the benchmarks are
flawed" response.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
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: jonah.harris@gmail.com, gsstark@mit.edu, mark.lewis@mir3.com
Subject: Re: Optimizer internals
In-Reply-To: <36e682920606160551v6b98bcfek32f01b895c53e057@mail.gmail.com>
* 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