pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Rob Nagler <nagler@bivio.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: 24 Oct 2003 11:36:12 -0400
Message-ID: <87vfqeveab.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <16279.61590.158000.290397@gargle.gargle.HOWL>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
	<x7d6cpcdsr.fsf@yertle.int.kciLink.com>
	<16279.4988.496000.539525@gargle.gargle.HOWL>
	<200310230814.56738.mweilguni@sime.com>
	<29123.1066915615@sss.pgh.pa.us>
	<16279.61590.158000.290397@gargle.gargle.HOWL>

Rob Nagler <nagler@bivio.biz> writes:

> Incorrect.  If the tuples smaller, Oracle does the right thing.  If
> there's enough space in the page, it shifts the tuples to make room.
> That's what pctfree, pctused and pctincrease allow you to control.
> It's all in memory so its fast, and I don't think it has to update any
> indices.

Note that pctfree/pctused are a big performance drain on the usual case. Try
setting them to 0/100 on a table that doesn't get updates (like a many-many
relation table) and see how much faster it is to insert and scan.

> > transactions that touch enough tuples to overflow your undo segment
> 
> That's easily configured, and hasn't been a problem in the databases
> I've managed.

Judging by the number of FAQ lists out there that explain various quirks of
rollback segment configuration I wouldn't say it's so easily configured.

> > (or even just sit there for a long time, preventing you from recycling
> 
> That's probably bad software or a batch system--which is tuned
> differently.  Any OLTP system has to be able to partition its problems
> to keep transactions short and small.  If it doesn't, it will not be
> usable.

Both DSS style and OLTP style databases can be accomodated with rollback
segments though it seems to me that DSS style databases lose most of the
advantage of rollback segments and optimistic commit.

The biggest problem is on systems where there's a combination of both users.
You need tremendous rollback segments to deal with the huge volume of oltp
transactions that can occur during a single DSS query. And the DSS query
performance is terrible as it has to check the rollback segments for a large
portion of the blocks it reads.

> Oracle seems to make the assumption that data changes, 

Arguably it's the other way around. Postgres's approach wins whenever most of
the tuples in a table have been updated, in that case it just has to scan the
whole table ignoring old records not visible to the transaction. Oracle has to
consult the rollback segment for any recently updated tuple. Oracle's wins in
the case where most of the tuples haven't changed so it can just scan the
table without consulting lots of rollback segments.

-- 
greg




view thread (45+ messages)  latest in thread

Message-ID: <87vfqeveab.fsf@stark.dyndns.tv>
Permalink:  ../87vfqeveab.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87vfqeveab.fsf@stark.dyndns.tv

 · 

reply

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: gsstark@mit.edu, nagler@bivio.biz
  Subject: Re: vacuum locking
  In-Reply-To: <87vfqeveab.fsf@stark.dyndns.tv>

* 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